Simplified Memory Management in Oracle

In Oracle 11g we have the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes. This allows RAM to be de-allocated from the SGA and transferred to the PGA.

To know the amount of memory_target to set you will need to run these commands:

Determine the amount of SGA and PGA you have in your system:

SHOW PARAMETER TARGET

NAME                          TYPE        VALUE

------------------------------ ----------- ----------------

archive_lag_target             integer     0

db_flashback_retention_target  integer     1440

fast_start_io_target           integer     0

fast_start_mttr_target         integer     0

memory_max_target              big integer 0

memory_target                  big integer 0

pga_aggregate_target           big integer 90M

sga_target                     big integer 272M

Run the following query to determine the maximum instance PGA allocated since the database was started:

select value from v$pgastat where name='maximum PGA allocated';

Compute the maximum value between the query result 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).

The value of MEMORY_TARGET can be the minimum value that you computed, or you can choose to use a larger value if you have enough physical memory available.

MEMORY_MAX_TARGET can be the same as MEMORY_TARGET or more if you want more physical memory to be used.

Recommendation: Its easier to set only 2 parameters than setting the PGA and SGA parameters which are very specific, so why not try to change these 2, this is set by default in the init.ora configuration text file.