理解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 <= 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/75321/viewspace-619407/
扫一扫
7055
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
