ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 理解PGA Advice

理解PGA Advice

原创 Linux操作系统 作者:mingyan926 时间:2009-11-11 14:24:10 0 删除 编辑

理解PGA的两个空间大小限制

1、每个Session 的PGA大小限制 

     当PGA_TARGET设置在1G以下时,每个Process最多能够获得200M的PGA空间,但是如果PGA_TARGET设置在1G以上,每个Process可以获得最到20%的PGA空间。

   因此,如果PGA_TARGET值在1G以下,那么无论怎么调整,因为能获得的PGA空间有限,性能的改善可能也不明显。但是要是超过1GB,那么性能会因为PGA_TARGET的改变而明显改变。

2、每个Workarea 空间的大小限制

    如果PGA_TARGET大小小于512M,则一个Workarea最大可以是20%的PGA_TARGET;

    但如果PGA_TARGET大于512M,则一个Workarea最大可以使用该Session的PGA的一半。

   如果启用的并行处理,那么整个SQL语句的所有Workarea所能使用的空间是PGA_TARGET的一半。

   比如一个DOP=8的操作,会有8个Workarea,那么每个Wrokarea最大能够使用 50/8=6.25,即6.25%的PGA_TARGET。

  

    这些限制可以通过隐藏参数获得或者修改,如

SELECT ksppinm name, ksppdesc description,
  CASE WHEN ksppinm LIKE '_smm%' THEN ksppstvl/1024
  ELSE ksppstvl/1048576 END as MB
  FROM sys.x$ksppi JOIN sys.x$ksppcv
  USING (indx)
  WHERE ksppinm IN
  ('pga_aggregate_target',
  '_pga_max_size',
  '_smm_max_size',
   '_smm_px_max_size','_ _pga_aggregate_target'
   );

NAME                      DESCRIPTION                                                MB
------------------------- -------------------------------------------------- ----------
pga_aggregate_target      Target size for the aggregate PGA memory consumed        5120
                                   by the instance

_pga_max_size             Maximum size of the PGA memory for one process     1023.98438
_smm_max_size            maximum work area size in auto mode (serial)       511.992188
_smm_px_max_size        maximum work area size in auto mode (global)             2560

决定是否应该调整PGA

    在调整PGA之前,首先必须有明确的理由来支持进行调整。也就是说,必须确信是因为PGA的不当设置导致的性能问题。因为PGA最大的影响就是排序、Hash空间不足使用临时表空间,所以可以根据Direct IO对整体的性能影响来评价是否有必要进行PGA调整。

  使用下面语句,可以获得Direct IO对于整体性能的影响

WITH system_event AS
  (SELECT CASE WHEN event LIKE 'direct path%temp'
  THEN event ELSE wait_class
  END wait_type, e.*
  FROM v$system_event e)
SELECT wait_type,SUM(total_waits) total_waits,
  round(SUM(time_waited_micro)/1000000,2)  time_waited_seconds,
   ROUND( SUM(time_waited_micro)      * 100   / SUM(SUM(time_waited_micro)) OVER (), 2) pct
 FROM (SELECT wait_type, event, total_waits, time_waited_micro
   FROM system_event e
   UNION
   SELECT 'CPU', stat_name, NULL, VALUE
   FROM v$sys_time_model
   WHERE stat_name IN ('background cpu time', 'DB CPU')) l
   WHERE wait_type <> 'Idle'
   GROUP BY wait_type
   ORDER BY 4 DESC;

WAIT_TYPE                      TOTAL_WAITS TIME_WAITED_SECONDS        PCT
------------------------------ ----------- ------------------- ----------
User I/O                          13876907           124387.77      39.57
CPU                                                   75292.22      23.95
Commit                             2860671            41300.02      13.14
Network                          214878603            29347.26       9.34
Other                              7913378            19856.82       6.32
Concurrency                        5608252            10729.61       3.41
Configuration                       297903             8032.31       2.56
System I/O                         3371769             5381.18       1.71
Application                            879               24.54        .01
direct path write temp               13278                 .07          0
direct path read temp               394453                 .69          0

     在这个系统中,PGA导致的性能因素可以忽略不计,所以没有必要调整PGA。

PGA顾问功能

通过视图 v$pga_target_advice可以获得PGA顾问功能,这个视图包括下面列:

SQL> DESC V$PGA_TARGET_ADVICE
Name                       
----------------------------- 
PGA_TARGET_FOR_ESTIMATE     假设的PGA_TARGET的大小,在数值上等于当前PGA_TARGET的设置和下一个假设因子的乘积。        
PGA_TARGET_FACTOR                   假设因子:可以理解为上当前PGA_TARGET设置值的倍数。
ADVICE_STATUS                                PGA顾问是否启用
BYTES_PROCESSED                        Workarea处理的数据量,以Byte为单位;
ESTD_EXTRA_BYTES_RW               如果不能在内存中完成数据处理(比如排序、哈希),就会用到磁盘,这个指标就是代表写磁盘、读磁盘导致的而外的数据量。         
ESTD_PGA_CACHE_HIT_PERCENTAGE      PGA的命中率=Bytes_porcessed/(bytes_processed+estd_extra_bytes_rw)                    
ESTD_OVERALLOC_COUNT              在这个假设的PGA_TARGET配置下,估计发生的OVERALLOC操作的次数。所谓OVERALLOC是指Oracle不得不分配更多的内存,即便已经超过了PGA_TARGET的限制。这就意味着,PGA_TARGET分配不足。

看下面这个查询

 select
 trunc(pga_target_for_estimate/1024/1024)
 pga_target_for_estimate,
 to_char(pga_target_factor * 100,'999.9') ||'%'
 pga_target_factor,
 trunc(bytes_processed/1024/1024) bytes_processed,
 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
 to_char(estd_pga_cache_hit_percentage,'999') || '%'
 estd_pga_cache_hit_percentage,
 estd_overalloc_count
 from v$pga_target_advice;

640   12.5% 27163 13529   67% 0
1280   25.0% 27163 10605   72% 0
2560   50.0% 27163 5469   83% 0
3840   75.0% 27163 5469   83% 0
5120  100.0% 27163 5469   83% 0
6144  120.0% 27163 2571   91% 0
7168  140.0% 27163 2571   91% 0
8192  160.0% 27163 2571   91% 0
9216  180.0% 27163 2571   91% 0
10240  200.0% 27163 2571   91% 0
15360  300.0% 27163 2571   91% 0
20480  400.0% 27163 2571   91% 0
30720  600.0% 27163 2571   91% 0
40960  800.0% 27163 2571   91% 0

当前PGA_TARGET=5G,命中率83%,从这个建议中可以看到,即便把PGA_TARGET扩大8倍,命中率也只能提高到91%。

有了PGA的一个整体印象后,接下来就可以深入的分析一下。PGA中是Shadow Process进行数据处理的主要区域,最主要的也是消耗最大的部分就是Workarea,我们可以看一下Workarea的使用情况,v$sql_workarea_histogram视图就是根据workarea的大小进行的统计。


SELECT
 case when low_optimal_size < 1024*1024
 then to_char(low_optimal_size/1024,'999999') ||
 'kb <= PGA < ' ||
 (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
 else to_char(low_optimal_size/1024/1024,'999999') ||
 'mb <= PGA < ' ||
 (high_optimal_size+1)/1024/1024|| 'mb'
 end ,
 optimal_executions,
 onepass_executions,
 multipasses_executions
 from v$sql_workarea_histogram
 where total_executions <> 0
 order by low_optimal_size

     2kb <= PGA < 4kb          649405 0 0
     64kb <= PGA <               128kb 416 0 0
    128kb <= PGA < 256kb   126 0 0
    256kb <= PGA < 512kb   138 0 0
    512kb <= PGA < 1024kb   1298 0 0
      1mb <= PGA < 2mb      395 0 0
      2mb <= PGA < 4mb       53 0 0
      4mb <= PGA < 8mb       9 0 0
      8mb <= PGA < 16mb      15 2 0
     16mb <= PGA < 32mb       51 0 0
     32mb <= PGA < 64mb       71 0 0
     64mb <= PGA < 128mb    39 0 0
    128mb <= PGA < 256mb    10 0 0
    256mb <= PGA < 512mb    3 14 0
    512mb <= PGA < 1024mb 0 6 0          
 从这个结果可以看到,Workarea在256M和512M之间的SQL,以Optimal方式执行了3次,以one-pass的方式执行了14次。multi-pass的方式没有。

     用下面查询,也可以获得相同的汇总信息

select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
from
(
select name,value count,(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);

workarea executions - optimal      656183        100
workarea executions - onepass          22          0
workarea executions - multipass          0          0

接下来就要看看那些SQL语句用到了这么大的Workarea,是否可以调整,这个信息可以从v$sql_workarea视图获得。这个视图记录了每个SQL语句使用的Workarea消耗情况,可以使用的列有

ESTIMATED_OPTIMAL_SIZE
 ESTIMATED_ONEPASS_SIZE 
 LAST_MEMORY_USED       
 LAST_EXECUTION         
 LAST_DEGREE            
 TOTAL_EXECUTIONS       
 OPTIMAL_EXECUTIONS     
 ONEPASS_EXECUTIONS     
 MULTIPASSES_EXECUTIONS 

下面这个语句就是取出了消耗最大的Top 5个SQL语句。

 WITH sql_workarea AS
(
SELECT sql_id || '-' || child_number SQL_ID_Child,
operation_type operation ,
last_execution last_exec,
ROUND (active_time / 1000000,
2) seconds,
optimal_executions || '/'
|| onepass_executions || '/'
 || multipasses_executions o1m,
 ' ' || SUBSTR (sql_text, 1, 155) sql_text,
 RANK () OVER (ORDER BY  estimated_optimal_size DESC) ranking
 FROM v$sql_workarea JOIN v$sql
 USING (sql_id, child_number) )
 SELECT sql_id_child "SQL ID - CHILD",seconds,operation,
 last_exec, o1m "O/1/M",sql_text
 FROM sql_workarea
 WHERE ranking <= 5
 ORDER BY ranking;

未完...

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

上一篇: sar
请登录后发表评论 登录
全部评论

注册时间:2008-10-24

  • 博文量
    23
  • 访问量
    102224