[bbk2192] 第33集 - Chapter 09-Optimizing Sore Perations(02)
cache hit percentage这个参数，可以作为DBA衡量PGA相关参数指标是否合理的一个重要标准.
SQL> SELECT low_optimal_size "Low",High_optimal_size "Hight",optimal_executions "Opt",onepass_executions "One",multipasses_executions "Multi",total_executions "Total" 2 FROM v$sql_workarea_histogram; Low Hight Opt One Multi Total ---------- ---------- ---------- ---------- ---------- ---------- 0 1023 0 0 0 0 1024 2047 0 0 0 0 2048 4095 170714 0 0 170714 4096 8191 0 0 0 0 8192 16383 0 0 0 0 16384 32767 0 0 0 0 32768 65535 0 0 0 0 65536 131071 638 0 0 638 131072 262143 392 0 0 392 262144 524287 368 0 0 368 524288 1048575 2924 0 0 2924 Low Hight Opt One Multi Total ---------- ---------- ---------- ---------- ---------- ---------- 1048576 2097151 1105 0 0 1105 2097152 4194303 225 0 0 225 4194304 8388607 203 0 0 203 8388608 16777215 2 4 0 6 16777216 33554431 12 0 0 12 33554432 67108863 2 0 0 2 67108864 134217727 0 0 0 0 134217728 268435455 0 0 0 0 268435456 536870911 0 0 0 0 536870912 1073741823 0 0 0 0 1073741824 2147483647 0 0 0 0 Low Hight Opt One Multi Total ---------- ---------- ---------- ---------- ---------- ---------- 2147483648 4294967295 0 0 0 0 4294967296 8589934591 0 0 0 0 8589934592 1.7180E+10 0 0 0 0 1.7180E+10 3.4360E+10 0 0 0 0 3.4360E+10 6.8719E+10 0 0 0 0 6.8719E+10 1.3744E+11 0 0 0 0 1.3744E+11 2.7488E+11 0 0 0 0 2.7488E+11 5.4976E+11 0 0 0 0 5.4976E+11 1.0995E+12 0 0 0 0 1.0995E+12 2.1990E+12 0 0 0 0 2.1990E+12 4.3980E+12 0 0 0 0 33 rows selected.
PGA Cache Hit Percentages
SQL> SELECT low_optimal_size/1024 AS low_kb 2 ,(high_optimal_size+1)/1024 AS high_kb 3 ,ROUND(100*optimal_executions/total_executions) AS optimal 4 ,ROUND(100*onepass_executions/total_executions) AS onepass 5 ,ROUND(100*multipasses_executions/total_executions) AS multipass 6 FROM v$sql_workarea_histogram 7 WHERE total_executions !=0 8 ORDER BY low_kb; LOW_KB HIGH_KB OPTIMAL ONEPASS MULTIPASS ---------- ---------- ---------- ---------- ---------- 2 4 100 0 0 64 128 100 0 0 128 256 100 0 0 256 512 100 0 0 512 1024 100 0 0 1024 2048 100 0 0 2048 4096 100 0 0 4096 8192 100 0 0 8192 16384 33 67 0 16384 32768 100 0 0 32768 65536 100 0 0 11 rows selected.
Determine PGA Workload
SQL> SELECT optimal_count,round(optimal_count*100/total,2) optimal_perc, 2 onepass_count,round(onepass_count*100/total,2) onepass_perc, 3 multipass_count,round(multipass_count*100/total,2) multipass_perc 4 FROM 5 (SELECT decode(sum(total_executions),0,1,sum(total_executions)) total, 6 sum(OPTIMAL_EXECUTIONS) optimal_count, 7 sum(ONEPASS_EXECUTIONS) onepass_count, 8 sum(MULTIPASSES_EXECUTIONS) multipass_count 9 FROM v$sql_workarea_histogram 10 WHERE low_optimal_size > 64*1024 11 ); OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC ------------- ------------ ------------- ------------ --------------- -------------- 5233 99.92 4 .08 0 0
解读:
OPTIMAL_COUNT OPTIMAL_PERC--->这对数据值较大，
ONEPASS_COUNT ONEPASS_PERC--->这对数据值较小或者接近于0，
MULTIPASS_COUNT MULTIPASS_PERC--->这对值很小或者接近于0,
综合上述三个统计指标，说明比较合理的状态.否则应当考虑调整PGA相关参数信息.
SQL> SELECT to_number(decode(sid,65535,NULL,sid)) sid, 2 operation_type Oper, 3 trunc(expected_size/1024) esize, 4 trunc(actual_mem_used/1024) mem, 5 trunc(max_mem_used/1024) "Max Mem", 6 number_passes pass 7 FROM v$sql_workarea_active 8 ORDER BY 1,2; SID OPER ESIZE MEM Max Mem PASS ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- 73 SORT (v2) 7228 7228 7228 0
上述动态性能视图，还需要在商榷.
SQL> SELECT to_number(decode(sid,65535,NULL,sid)) sid, 2 operation_type Oper, 3 trunc(expected_size/1024) esize, 4 trunc(actual_mem_used/1024) mem, 5 trunc(max_mem_used/1024) "Max Mem", 6 number_passes pass, 7 trunc(TEMPSEG_SIZE/1024) TSIZE 8 FROM v$sql_workarea_active 9 ORDER BY 1,2; no rows selected SQL> / SID OPER ESIZE MEM Max Mem PASS TSIZE ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- 73 SORT (v2) 7228 7228 7228 0
Monitoring SQL Memory
--历史的执行记录
SELECT sql_text, sum(onepass_executions) onepass_cnt, sum(multipasses_executions) mpass_cnbt FROM V$SQL s,V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY sql_text HAVING sum(onepass_executions+multipasses_executions) > 0;
--当前正在执行的SQL，监控
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) MAXMEM, number_passes PASS, TRUNC(tempseg_size/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2;
usefull sqlcode for monitoring sql memory
col "0/1/m" format a10 col name format a20 SELECT operation,options,object_name name, trunc(bytes/1024/1024) "input(MB)", trunc(last_memory_used/1024) last_mem, trunc(estimated_optimal_size/1024) optimal_mem, trunc(estimated_onepass_size/1024) onepass_mem, decode(optimal_executions,null,null,optimal_executions||'/'||onepass_executions||'/'||multipasses_executions) "0/1/M" FROM V$SQL_PLAN p,V$SQL_WORKAREA w WHERE p.address=w.address(+) AND p.hash_value=w.hash_value(+) AND p.id=w.operation_id(+);
