继续前面的话题：
Sql工作区执行方式
sql在workarea中有3种方式
optimal尺寸：SQL语句能够完全在所分配的SQL工作区内完成所有的操作，性能最佳。
onepass尺寸：SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作。
multipass尺寸：由于SQL工作区过小，从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。
oracle建议
workarea execution_optimal>=90%
workarea execution_multipass=0%
#查看系统中性能指标,观察三种方式的百分比
SYS >selectname,value,
100*(value
/decode((selectsum(value)from v$sysstat
wherenamelike 'workarea execution%'),0,null,
(selectsum(value)from v$sysstat
wherenamelike 'workareaexecution%'))) pct
from v$sysstatwherename like 'workarea executions%';
NAME | VALUE| PCT
----------------------------------------------------------------|----------|----------
workarea executions -optimal | 31174| 100
workarea executions -onepass | 0| 0
workarea executions -multipass | 0| 0
1 rowsselected.
Sql工作区内存分配方式
在设置了workarea_size_policy ，P_A_G后，oracle可以为sql自动分配内存，而且单个sql可以占用的内存受到之前提到的几个隐含参数的影响。那么，在sql执行周期内，oracle是怎么具体为他分配内存，分配多少？这是个值得研究的问题。
PGA内存采用反馈环方式进行自动分配，如下图所示：
SQL语句运行时候，通过local memory manager注册一个活动工作区profile,活动工作区profile是一些元数据，包含了sql工作区相关属性，例如类型（是sort还是hash），执行需要的optimal, onepass,multipass内存大小等相关信息。工作区活动profile被local memory manager维护，它反应了sql执行占用的内存和实际需要的内存,在执行期间是动态变化的。
活动工作区profile记录了sql工作区相关属性，由local memory manager控制，它的内存来源是sga，下面可以看到活动工作区profile占用的内存量。
SYS > select*from v$sgastat wherename like'work area%';
POOL |NAME | BYTES
------------|--------------------------|----------
shared pool |work area tab | 264248
v$sql_workarea_active动态性能视图记录了活动sql工作区的profile
col OPERATION_TYPE for a5
select
w.OPERATION_TYPE,w.policy,w.WORK_AREA_SIZE,w.EXPECTED_SIZE,
w.ACTUAL_MEM_USED,w.MAX_MEM_USED,w.TEMPSEG_SIZE,w.TABLESPACE,w.ACTIVE_TIME
from v$sql_workarea_active wwhere
SQL_HASH_VALUE=(select hash_valuefrom v$sqlwheresql_text='select * from test,test b order by 1,2,3,4,5,6,7,8,9');
OPERA|POLICY |WORK_AREA_SIZE|EXPECTED_SIZE|ACTUAL_MEM_USED|MAX_MEM_USED|TEMPSEG_SIZE|TABLESPACE |ACTIVE_TIME
-----|------------|--------------|-------------|---------------|------------|------------|-------------|-----------
SORT(v2)|AUTO | 13924352| 13924352| 13712384| 13917184| 159383552|TEMPGROUP11 | 29047956
注意其中的EXPECTED_SIZE，这个值实际是后台globalmemory manager确定，如上图,global memory manager要做的事情就是：以活动sql profile为渠道查看当前活动sql的相关属性（例如需要的MAX_MEM_USED为13917184,使用了临时表空间量为159383552）后，它要根据系统设置(这个数值就是之前讨论的global memory bound)和sql的需要，为sql确定一个工作区内存尺寸，就是EXPECTED_SIZE。global memory manager每3s更新一次这个EXPECTED_SIZE,但是global memory manager只是参与计算，然后把这个计算出的值告知local memory manager，让他来进行内存分配，这个告知动作就是所谓的反馈。global memory manager的活动实际通过后台CKPT进程活动实现。
SYS >select description,destfromx$messageswherelower(description)like'sql memory%';
DESCRIPTION |DEST
----------------------------------------|----------------------------------------------------------------
SQL Memory Management Calculation |CKPT
SYS >select*from v$pgastat whereNAME='globalmemory bound';
NAME | VALUE|UNIT
----------------------------------------------------------------|----------|------------
global memory bound | 13924352|bytes
global memory bound 就决定了EXPECTED_SIZE的上限。
前面提到，v$sql_workarea_active视图记录了当前活动sql的工作区信息。那么，我们还可以查看当前共享池中的缓存sql语句曾经的SQL工作区使用情况。
一般来讲存储在共享池中的sql都需要一个或者多个子游标（在v$sql中记录的），那么通过视图v$sql_workarea就可以看到这些sql的工作区使用情况。这个很好理解，为了少做事情，oracle会把执行过的sql语句相关信息和查询数据放在共享池中，也会把排序信息放在pga中。这样，在同一个会话下，第二次执行后，就可以省去一些解析和磁盘读过程，直接到缓冲区和工作区读取数据了。这部分未深入研究，暂时这样理解吧。
刚才我执行了select hash_valuefrom v$sqlwheresql_text='select * from test,test b order by 1,2,3,4,5,6,7,8,9',现在看看他的相关信息吧。
col OPERATION_TYPE for a15
select w. hash_value,w.CHILD_NUMBER,w.OPERATION_TYPE,w.LAST_MEMORY_USED,w.ACTIVE_TIME,
w.MAX_TEMPSEG_SIZE,w.LAST_TEMPSEG_SIZE
from v$sql_workarea wwhere
HASH_VALUE=(select hash_valuefrom v$sqlwheresql_text='select * from test,test b order by 1,2,3,4,5,6,7,8,9');
CHILD_NUMBER|OPERATION_TYPE|LAST_MEMORY_USED|ACTIVE_TIME|MAX_TEMPSEG_SIZE|LAST_TEMPSEG_SIZE
------------|---------------|----------------|-----------|----------------|-----------------
0|SORT(v2) | 0| 0| |
0|BUFFER | 12562432| 37184942| |
这个12562432就是单个sql允许的最大工作区内存。
于是，和v$sql联合，可以看到当前共享池中曾经使用sql工作区内存最多的前五个sql语句
col sql_text for a65
select * from (select s.SQL_TEXT,w.CHILD_NUMBER,w.OPERATION_TYPE,w.LAST_MEMORY_USED
from v$sql_workarea w,v$sql swhere
s.HASH_VALUE=w.hash_value orderby w.LAST_MEMORY_USEDdesc)where rownum<=5;
PGA视图调整建议
V$PGA_TARGET_ADVICE和V$PGA_TARGET_ADVICE_HISTOGRAM
V$PGA_TARGET_ADVICE视图以实例过去的负载为依据，预计P_A_G设置在各种值下对应的缓存命中率。我们的目标当然是尽量要让这个值达到100%,也就是说尽量让用户的排序操作直接在PGA中完成。如果P_A_G参数未设置，那么这个视图是空的。当然，为了进行统计，参数statistics_level设置成typical也是必须的了。
SYS >show parameter pga
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
pga_aggregate_target |big integer|69625446
SYS >show parameter statistics_level
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
statistics_level |string |TYPICAL
SYS >Select pga_target_for_estimate/1024/1024 ||'M' "PGA"
,estd_pga_cache_hit_percentage "SORT_Hit(%)"
,estd_extra_bytes_rw/1024/1024 ||'M' "Read/Write"
,estd_overalloc_count "IO"
From v$pga_target_advice;
PGA |SORT_Hit(%)|Read/Write | IO
-----------------------------------------|-----------|-----------------------------------------|----------
16.599609375M | 97|3.40625M | 3
33.19921875M | 97|3.40625M | 3
49.798828125M | 100|0M | 2
66.3994140625M | 100|0M | 1
79.6787109375M | 100|0M | 1
92.958984375M | 100|0M | 1
106.23828125M | 100|0M | 1
119.5185546875M | 100|0M | 1
132.798828125M | 100|0M | 0
199.1982421875M | 100|0M | 0
265.59765625M | 100|0M | 0
398.396484375M | 100|0M | 0
531.1953125M | 100|0M | 0
13 rows selected.
视图V$SQL_WORKAREA_HISTOGRAM反映了过去实例运行以来，sql工作区各种执行方式的分配情况。
SYS >select * from v$sql_workarea_HISTOGRAM whereTOTAL_EXECUTIONS<>0;
LOW_OPTIMAL_SIZE,HIGH_OPTIMAL_SIZE,OPTIMAL_EXECUTIONS,ONEPASS_EXECUTIONS,MULTIPASSES_EXECUTIONS,TOTAL_EXECUTIONS
----------------,-----------------,------------------,------------------,----------------------,----------------
2048, 4095, 9889, 0, 0, 9889
65536, 131071, 36, 0, 0, 36
131072, 262143, 6, 0, 0, 6
262144, 524287, 66, 0, 0, 66
524288, 1048575, 80, 0, 0, 80
1048576, 2097151, 18, 0, 0, 18
2097152, 4194303, 8, 0, 0, 8
4194304, 8388607, 10, 0, 0, 10
如：4194304 <= workarea < 8388607 10 0 0反应workarea的OPTIMAL_SIZE使用量在4m到8m之间时，在内存运行是8次，sql语句与磁盘交换一次就能能获得sql工作区的行为0次，多次交换到磁盘的则是0次。
V$PGA_TARGET_ADVICE_HISTOGRAM视图预测了P_A_G在不同值下视图V$SQL_WORKAREA_HISTOGRAM 的结果。实例重启或者P_A_G参数被修改时候，他就要重新开始预测。
col prediction for a44;
SELECT PGA_TARGET_FOR_ESTIMATE/1024/1024||'M',PGA_TARGET_FACTOR,LOW_OPTIMAL_SIZE,
(case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= workarea < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
when low_optimal_size < 1024*1024*1024
then to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= workarea < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024/1024|| 'mb'
else to_char(low_optimal_size/1024/1024/1024,'999999') ||
'gb <= workarea < ' ||
(high_optimal_size+1)/1024/1024/1024|| 'gb'
end ||' '||
estd_optimal_executions||' '||
estd_onepass_executions||' '||
estd_multipasses_executions) prediction
from V$PGA_TARGET_ADVICE_HISTOGRAM
where estd_total_executions <> 0
order by PGA_TARGET_FOR_ESTIMATE,low_optimal_size;
例如：
PGA_TARGET_FOR_ESTIMATE/1024/1024||'M' ,PGA_TARGET_FACTOR,LOW_OPTIMAL_SIZE,PREDICTION
-----------------------------------------,-----------------,----------------,------------------------------------------
16.599609375M , .25, 524288, 512kb <= workarea < 1024kb 79 0 1
这条记录表示如果设置P_A_G为16.6m,也就是当前P_A_G的0.25倍，那么工作区内存需求是512kb到1024kb之间的sql操作将会在内存执行79此，多次磁盘交换获得sql工作区的行为是1次。
扫一扫
686
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
