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.
Tags: Performance Tips