To enable automatic memory management:

1. Start SQL*Plus and connect to the Oracle Database instance with the SYSDBA administrative privilege.

   See "Connecting to the Database with SQL*Plus" and "Database Administrator Authentication" for instructions.

2. Calculate the minimum value for MEMORY_TARGET as follows:

   1. Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET in megabytes by entering the following SQL*Plus commands:

      SHOW PARAMETER SGA_TARGET
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- --------------------------
      sga_target                           big integer 272M
      
      SHOW PARAMETER PGA_AGGREGATE_TARGET
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- --------------------------
      pga_aggregate_target                 big integer 90M
      

      See "Enabling Automatic Shared Memory Management" for information about setting the SGA_TARGET parameter if it is not set.

   2. Run the following query to determine the maximum instance PGA allocated in megabytes since the database was started:

      SELECT VALUE/1048576 FROM V$PGASTAT WHERE NAME='maximum PGA allocated';
      

   3. Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value.

      MEMORY_TARGET = SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, MAXIMUM PGA ALLOCATED)
      

   For example, if SGA_TARGET is 272M and PGA_AGGREGATE_TARGET is 90M as shown above, and if the maximum PGA allocated is determined to be 120M, then MEMORY_TARGET should be at least 392M (272M + 120M).

3. Choose the value for MEMORY_TARGET that you want to use.

   This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available.

4. For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.

5. Do one of the following:

   • If you started your Oracle Database instance with a server parameter file, which is the default if you created the database with the Database Configuration Assistant (DBCA), enter the following command:

     ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
     

     where n is the value that you computed in step 4.

     The SCOPE = SPFILE clause sets the value only in the server parameter file, and not for the running instance. You must include this SCOPE clause because MEMORY_MAX_TARGET is not a dynamic initialization parameter.

   • If you started your instance with a text initialization parameter file, manually edit the file so that it contains the following statements:

     memory_max_target = nM
     memory_target = mM
     

     where n is the value that you determined in step 4, and m is the value that you determined in step 3.

   Note:

   In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, then the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

6. Shut down and restart the database.

   See Oracle Database SQL Language Reference for instructions.

7. If you started your Oracle Database instance with a server parameter file, enter the following commands:

   ALTER SYSTEM SET MEMORY_TARGET = nM;
   ALTER SYSTEM SET SGA_TARGET = 0;
   ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
   

   where n is the value that you determined in step 3.

• Query the V$MEMORY_TARGET_ADVICE view for tuning advice for the MEMORY_TARGET initialization parameter.

For example, run the following query:

SQL>  select * from v$memory_target_advice order by memory_size;
 
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        180                 .5          458               1.344          0
        270                .75          367              1.0761          0
        360                  1          341                   1          0
        450               1.25          335               .9817          0
        540                1.5          335               .9817          0
        630               1.75          335               .9817          0
        720                  2          335               .9817          0

The row with the MEMORY_SIZE_FACTOR of 1 shows the current size of memory, as set by the MEMORY_TARGET initialization parameter, and the amount of DB time required to complete the current workload. In previous and subsequent rows, the results show several alternative MEMORY_TARGET sizes. For each alternative size, the database shows the size factor (the multiple of the current size), and the estimated DB time to complete the current workload if the MEMORY_TARGET parameter were changed to the alternative size. Notice that for a total memory size smaller than the current MEMORY_TARGET size, estimated DB time increases. Notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450MB. However, this situation might change if a complete workload has not yet been run.

EM Express provides an easy-to-use graphical memory advisor to help you select an optimal size for MEMORY_TARGET.

Oracle Database then tries to ensure that the total amount of PGA memory allocated across all database server processes and background processes never exceeds this target.

If you create your database with DBCA, you can specify a value for the total instance PGA. DBCA then sets the PGA_AGGREGATE_TARGET initialization parameters in the server parameter file (SPFILE) that it creates. If you do not specify the total instance PGA, DBCA chooses a reasonable default.

If you create the database with the CREATE DATABASE SQL statement and a text initialization parameter file, you can provide a value for PGA_AGGREGATE_TARGET. If you omit this parameter, the database chooses a default value for it.

With automatic PGA memory management, sizing of SQL work areas is automatic and all *_AREA_SIZE initialization parameters are ignored. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

There are dynamic performance views that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.

• Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:

• V$SYSSTAT
• V$SESSTAT
• V$PGASTAT
• V$SQL_WORKAREA
• V$SQL_WORKAREA_ACTIVE

• The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle Database process:

• PGA_USED_MEM
• PGA_ALLOC_MEM
• PGA_MAX_MEM

The PGA_AGGREGATE_TARGET setting is a target. Therefore, Oracle Database tries to limit PGA memory usage to the target, but usage can exceed the setting at times. To specify a hard limit on PGA memory usage, use the PGA_AGGREGATE_LIMIT initialization parameter. Oracle Database ensures that the PGA size does not exceed this limit. If the database exceeds the limit, then the database terminates calls from sessions that have the highest untunable PGA memory allocations. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management. If PGA_AGGREGATE_LIMIT is not set, then Oracle Database determines an appropriate default limit. See Oracle Database Reference for more information about this parameter.

In releases earlier than Oracle Database 10g, the database administrator controlled the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary greatly from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are difficult to tune under the best of circumstances.

For this reason, Oracle strongly recommends that you leave automatic PGA memory management enabled.

If you decide to tune SQL work areas manually, you must set the WORKAREA_SIZE_POLICY initialization parameter to MANUAL.

For a single instance database, the metadata consumes approximately 100 bytes. For an Oracle Real Application Clusters (Oracle RAC) database, it is closer to 200 bytes. You must therefore take this extra memory requirement into account when adding Database Smart Flash Cache.

To tune memory for the Database Smart Flash Cache, complete one of the following actions:

• If you are managing memory manually, then increase the size of the buffer cache by an amount approximately equal to the number of database blocks that fit into the Database Smart Flash Cache as configured, multiplied by 100 (or 200 for Oracle RAC).

• If you are using automatic memory management, then increase the size of the MEMORY_TARGET initialization parameter using the algorithm described above. You may first have to increase the size of the MEMORY_MAX_TARGET initialization parameter .

• If you are using automatic shared memory management, then increase the size of the SGA_TARGET initialization parameter .

Also, for an Oracle RAC database that uses the flash cache, additional memory must be allocated to the shared pool for Global Cache Service (GCS) resources. Each GCS resource requires approximately 208 bytes in the shared pool.

For example, assume that your Database Smart Flash Cache uses following flash devices:

You can set the initialization parameters to the following values:

DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc

DB_FLASH_CACHE_SIZE = 32G, 32G, 64G

You can query the V$FLASHFILESTAT view to determine the cumulative latency and read counts of each file and compute the average latency.

You can use ALTER SYSTEM to set DB_FLASH_CACHE_SIZE to zero for each flash device you wish to disable. You can also use ALTER SYSTEM to set the size for any disabled flash device back to its original size to reenable it. However, dynamically changing the size of Database Smart Flash Cache is not supported.

Typically, there is no need to specify this parameter, because the default maximum size is chosen by the database based on total memory available to the SGA and on the memory management method currently in use. You can view the current default maximum size by displaying the value of the RESULT_CACHE_MAX_SIZE parameter. To change this maximum size, you can set RESULT_CACHE_MAX_SIZE with an ALTER SYSTEM statement, or you can specify this parameter in the text initialization parameter file. The value may be rounded up due to internal memory granularity.

If RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is disabled. To reenable it you must set RESULT_CACHE_MAX_SIZE to a nonzero value (or remove this parameter from the text initialization parameter file to get the default maximum size) and then restart the database.

Note that after starting the database with the result cache disabled, if you use an ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE parameter returns a nonzero value even though the result cache is still disabled. The value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to determine if the result cache is enabled. You can use the following query instead:

SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
---------------------------------------------
ENABLED

The result cache takes its memory from the shared pool, so if you increase the maximum result cache size, consider also increasing the shared pool size.

The view V$RESULT_CACHE_STATISTICS and the PL/SQL package procedure DBMS_RESULT_CACHE.MEMORY_REPORT display information to help you determine the amount of memory currently allocated to the result cache.

The PL/SQL package function DBMS_RESULT_CACHE.FLUSH clears the result cache and releases all the memory back to the shared pool.

You can query V$RESULT_CACHE_OBJECTS to determine whether temporary segments have been used. A value of Temp in the TYPE column indicates the use of temporary segments.