How To Tune PGA_AGGREGATE_TARGET

How To Tune PGA_AGGREGATE_TARGET

1  Make a first estimate for PGA_AGGREGATE_TARGET  based on the following rule

   For OLTP systems
   PGA_AGGREGATE_TARGET  = ( * 80%) * 20%
  
   For DSS systems
   PGA_AGGREGATE_TARGET  = ( * 80%) * 50%
  
2 A second step in tuning the PGA_AGGREGATE_TARGET is to monitor performance using available PGA statistics and see if  PGA_AGGREGATE_TARGET is under sized or over sized. Several dynamic performance views are available for this purpose:

 
                                                         total bytes processed * 100
  PGA Cache Hit Ratio =  ------------------------------------------------------
                                             (total bytes processed + total extra bytes read/written)

3 V$SQL_WORKAREA_HISTOGRAM
 
  SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb, optimal_executions, onepass_executions, multipasses_executions
  FROM   v$sql_workarea_histogram
  WHERE  total_executions != 0;
 
4. V$SQL_WORKAREA_ACTIVE
   Use this view to precisely monitor the size of all active work areas and to determine if these active work areas spill to a temporary segment.

   SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
   FROM V$SQL_WORKAREA_ACTIVE
   ORDER BY 1,2;

   SID OPERATION         ESIZE     MEM       MAX MEM    PASS TSIZE
   --- ----------------- --------- --------- --------- ----- -------
   8   GROUP BY (SORT)   315       280       904         0
   8   HASH-JOIN         2995      2377      2430        1   20000
   9   GROUP BY (SORT)   34300     22688     22688       0
   11  HASH-JOIN         18044     54482     54482       0
   12  HASH-JOIN         18044     11406     21406       1   120000

   It has also spilled to a temporary segment of size 120000 KB     
  
5. tuning the PGA_AGGREGATE_TARGET
   V$PGA_TARGET_ADVICE
   this view predicts how the statistics cache hit percentage and over allocation count in V$PGASTAT will be impacted if you change the value of
   the initialization parameter PGA_AGGREGATE_TARGET.    
  
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,                       
          ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,     
          ESTD_OVERALLOC_COUNT                              
   FROM   v$pga_target_advice;                     
   TARGET_MB  CACHE_HIT_PERC ESTD_OVERALLOC_COUNT        
   ---------- -------------- --------------------
   63         23             367                
   125        24             30                 
   250        30             3                  
   375        39             0                  
   500        58             0                  
   600        59             0                  
   700        59             0                  
   800        60             0                  
   900        60             0                  
   1000       61             0                  
   1500       67             0                  
   2000       76             0                  
   3000       83             0                  
   4000       85             0                  
                                    
   e.g. lowest PGA_AGGREGATE_TARGET value we can set is 375 ( where ESTD_OVERALLOC_COUNT is 0)
  
   V$PGA_TARGET_ADVICE_HISTOGRAM
   this how the statistics displayed by the performance view V$SQL_WORKAREA_HISTOGRAM will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET.
  

參考文獻：

Subject:	Automatic PGA Memory Management
	:	223730.1	Type:	REFERENCE
	Modified Date :	24-JUN-2009	Status:	PUBLISHED