PGA Tuning

Select Name,Value/1024/1024 From V$parameter where name like '%pga%';

Make a first estimate for PGA_AGGREGATE_TARGET, based on a rule of thumb. By default, Oracle uses 20% of the SGA size. However, this initial setting may be too low for a large DSS system.

You must then divide the resulting memory between the SGA and the PGA.

• For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.

• For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total (up to 2.2 GB in this example).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

• For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

• For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

  where total_mem is the total amount of physical memory available on the system.

The PGA_AGGREGATE_TARGET should be set to attempt to keep the ESTD_PGA_CACHE_HIT_PERCENTAGE greater than 95 percent. By setting this appropriately, more data will be sorted in memory that may have been sorted on disk. The next query returns the minimum value for the PGA_AGGREGATE_TARGET that is projected to yield a 95 percent or greater cache hit ratio:



Select Min(Pga_Target_For_Estimate/1024/1024) "recommended_pga"
from v$pga_target_advice
Where Estd_Pga_Cache_Hit_Percentage > 95;




alter system set pga_aggregate_target= "recommended_pga";