ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Tune PGA_AGGREGATE_TARGET

How To Tune PGA_AGGREGATE_TARGET

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-08-05 18:03:04 0 删除 编辑

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
  Doc ID: 223730.1 Type: REFERENCE
  Modified Date : 24-JUN-2009 Status: PUBLISHED

   
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-611436/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1108212