• 1. Oracle数据库性能优化实务 第9讲:PGA和排序主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. Oracle的PGA非共享部分 只允许SERVER进程写入 包含: Sort area Session information Cursor state Stack spaceUser processPGAServer process
  • 3. 排序操作可能产生排序操作的情况 ORDER BY语句 DISTINCT语句 GROUP BY语句 高级统计操作(比如CUBE) UNION操作 SORT MERGE JOIN操作
  • 4. 排序操作的三种模式OPTIMAL或者CACHED 完全使用内存排序 性能最佳 1-PASS 排序操作中产生了一次磁盘读写 M-PASS 排序操作中产生了多次磁盘读写 需要尽可能避免
  • 5. 排序模式响应时间内存使用Optimal1 pass
  • 6. 排序执行的优化尽可能使用OPTIMAL的排序 可以存在少量的1-PASS排序 尽可能避免M-PASS排序 例外情况: 物理内存不足 存在特大的排序(比如超过1G) CPU资源紧张
  • 7. PGA手工管理9.0以前版本 需要手工设置一系列参数 SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE CREATE_BITMAP_AREA_SIZE SORT_AREA_RETAINED_SIZE WORKAREA_SIZE_POLICY参数
  • 8. PGA自动管理9.0以后版本 WORKAREA_SIZE_POLICY=AUTO 设置PGA_AGGREGATE_TARGET 不需要设置*_AREA_SIZE参数
  • 9. PGA_AGGREGATE_TARGET设置了PGA的估算值 可动态修改 系统根据该参数调整PGA的使用 对于9i,只有独立服务器模式使用自动PGA管理 10G开始,所有模式都是用PGA自动管理
  • 10. PGA_AGGREGATE_TARGET与WORKAREA每个进程的PGA工作区受到两个参数的限制(串行) 不得超过PGA_AGGREGATE_TARGET的5% 不得超过_SMM_MAX_SIZE定义的大小 并行查询中PGA工作区的限制 每个PX进程不得超过_SMM_PX_MAX_SIZE/DOP的大小 每个进程的所有工作区的总和不能超过_PGA_MAX_SIZE
  • 11. 其他合PGA相关的内部参数_smm_advice_enabled:TRUE激活V$PGA_ADVICE _smm_advice_log_size:设置PGA ADVICE的历史日志的大小 _smm_auto_max_io_size/_smm_auto_min_io_size _smm_min_size _smm_trace
  • 12. 临时表空间和临时段临时表空间可以提供临时段 从7.3.4开始临时表空间中的临时段算法发生了改变 每个实例在每个临时表空间中只分配一个临时段 临时段在实例运行期间只分配不回收 排序操作在临时段中的分配单位是扩展 通过SORT EXTENT POOL机制(SEP)来管理临时段 RAC环境中,如果某个实例临时段不足,而表空间无法扩充扩展,可以从其他实例中偷取 临时表空间中的临时段的作用 排序、表连接等的临时空间 为临时表提供存储 临时段不仅仅存在于临时表空间
  • 13. 临时表空间-10G新功能好处: 不同的会话使用不同的表空间 并行查询中使用不同的表空间 在数据库级设置多个临时表空间Default tablespace EXAMPLETablespace TEMP1Tablespace TEMPn…Temporary tablespace group TEMP
  • 14. 临时表空间100%问题很多DBA发现临时表空间总是处于100%使用 分析: 数据库的正常行为 不需要回收临时段 注意监控ALERT LOG
  • 15. PGA手工管理下的优化要点设置适当的*_area_size DBA容易忽略sort_area_size以外的参数 SORT_AREA_RETAINED_SIZE不宜设置过小 注意ORA-4030,加强物理内存监控 参数设置考虑大多数会话的情况,特殊需要在会话级修改*_area_size等参数
  • 16. PGA自动管理下的PGA优化要点设置适当的PGA_AGGREGATE_TARGET参数 注意_PGA_MAX_SIZE参数的设置 注意PGA实际内存的使用 注意物理内存监控,防止ORA-4030 自顶向下的问题分析方法,从总体到某个具体的SQL
  • 17. PGA自动管理的监控-第一步SQL>select * from v$pgastat NAME VALUE UNIT ---------------------------------------- --------------- ------------ aggregate PGA target parameter 16106127360 bytes aggregate PGA auto target 13796481024 bytes global memory bound 1073741824 bytes total PGA inuse 788483072 bytes total PGA allocated 1638338560 bytes maximum PGA allocated 6631036928 bytes total freeable PGA memory 429850624 bytes process count 545 max processes count 1199 PGA memory freed back to OS 10902709927936 bytes total PGA used for auto workareas 11700224 bytes maximum PGA used for auto workareas 1505202176 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 537600 bytes over allocation count 0 bytes processed 73457958117376 bytes extra bytes read/written 875484558336 bytes cache hit percentage 98.82 percent recompute count (total) 449299
  • 18. 查看PGA的缓冲区情况SQL> select name,round(value/1024/1024,1) as Mb from v$pgastat where unit = 'bytes'; NAME MB ---------------------------------------- --------------- aggregate PGA target parameter 15360 aggregate PGA auto target 13157.4 global memory bound 1024 total PGA inuse 753.5 total PGA allocated 1567.5 maximum PGA allocated 6323.8 total freeable PGA memory 411.1 PGA memory freed back to OS 10400491.7 total PGA used for auto workareas 12.9 maximum PGA used for auto workareas 1435.5 total PGA used for manual workareas 0 maximum PGA used for manual workareas .5 bytes processed 70074058.7 extra bytes read/written 835427
  • 19. 监控V$SQL_WORKAREA_HISTOGRAMSELECT LOW_OPTIMAL_SIZE/1024/1024 low_mb,(HIGH_OPTIMAL_SIZE+1)/1024/1024 high_mb, optimal_executions,onepass_executions,multipasses_executions FROM v$sql_workarea_histogram WHERE total_executions != 0 and (low_optimal_size/1024/1024 >= 8 or total_executions > optimal_executions) ;
  • 20. 例子 LOW_MB HIGH_MB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS -------- -------- ------------------ ------------------ ---------------------- .015625 .03125 0 12 0 .0625 .125 16378352 86 0 .25 .5 8032029 12 0 1 2 1.68E+08 4 0 2 4 260798 5981 0 4 8 150184 10655 0 8 16 135299 7353 0 16 32 146704 12387 0 32 64 108725 19031 2 64 128 64057 21432 460 128 256 15153 6428 882 256 512 20438 14990 586 512 1024 4819 5096 116 1024 2048 182 2581 160 2048 4096 8 148 92 4096 8192 0 17 0 8192 16384 0 26 0 16384 32768 0 6 0 32768 65536 0 2 0
  • 21. PGA监控脚本(3)select operation_type as type, policy, sid, round(active_time/1000000,2) as a_sec, round(work_area_size/1024/1024,2) as wsize, round(expected_size/1024/1024,2) as exp, round(actual_mem_used/1024/1024,2) as act,round(max_mem_used/1024/1024,2) as max, number_passes as passes, round(tempseg_size/1024/1024,2) as temp from v$sql_workarea_active; TYPE POLIC SID A_SEC WSIZE EXP ACT MAX PASSES TEMP -------------------- ----- -------- -------- -------- -------- -------- -------- -------- -------- GROUP BY (HASH) AUTO 418 .06 3.06 3.16 0 0 0 GROUP BY (HASH) AUTO 278 41892.62 3.26 3.26 1.19 1.19 0 CONNECT-BY (SORT) AUTO 993 155.98 14.47 14.47 12.96 12.96 0 WINDOW (SORT) AUTO 1079 42802.89 .48 .48 .48 .48 0 CONNECT-BY (SORT) AUTO 993 155.47 6.73 6.73 6.57 6.57 0 GROUP BY (HASH) AUTO 339 41851.66 3.26 3.36 1.21 1.21 0 WINDOW (SORT) AUTO 312 41982.71 .48 .48 .48 .48 0 GROUP BY (HASH) AUTO 303 41262.05 3.26 3.26 1.17 1.17 0 GROUP BY (HASH) AUTO 187 375.15 3.26 3.37 1.15 1.15 0 GROUP BY (HASH) AUTO 171 .01 3.06 3.16 0 0 0 GROUP BY (HASH) AUTO 307 9409.75 3.26 3.37 1.2 1.2 0 WINDOW (SORT) AUTO 991 41867.74 1.03 1.03 1.03 1.03 0
  • 22. 分析具体SQL的情况PGA分析一般来说只需要关注总体情况 有些时候PGA总体没有问题,但是个别SQL会导致问题 了解某个SQL的PGA使用情况是解决个体问题的第一步
  • 23. PGA监控脚本(4)col op format a15 trunc col policy format a8 trunc col last format a10 trunc set numwidth 8 set line 200 select operation_type as op, operation_id as id, policy, round(estimated_optimal_size/1024/1024,2) as e_opt, round(estimated_onepass_size/1024/1024,2) as e_one, round(last_memory_used/1024/1024,2) as l_mem, last_execution as last, total_executions as tot, optimal_executions as opt, onepass_executions as one, multipasses_executions as mult, round(active_time/1000000,2) as sec, round(max_tempseg_size/1024/1024,2) as tmp_m, round(last_tempseg_size/1024/1024,2) as tmp_L from v$sql_workarea where sql_id='37qjh5yuha3x9';
  • 24. PA监控脚本(4)-例子OP E_OPT E_ONE L_MEM LAST TOT OPT ONE MULT SEC TMP_M TMP_L --------------- -------- -------- -------- -------- ---- ---- ---- ------ -------- ------ ------ SORT (v2) 59.92 2.63 53.26 OPTIMAL 1 1 0 0 289.53 GROUP BY (HASH) 136.51 8.69 20.1 1 PASS 1 0 1 0 382.3 128 128
  • 25. 查找存在问题的SQLselect sql_id,operation_type as op, operation_id as id, round(estimated_optimal_size/1024/1024,2) as e_opt, round(estimated_onepass_size/1024/1024,2) as e_one, round(last_memory_used/1024/1024,2) as l_mem, Last_execution as last, total_executions as tot, optimal_executions as opt, onepass_executions as one, multipasses_executions as mult, round(active_time/1000000,2) as sec, round(max_tempseg_size/1024/1024,2) as tmp_m, round(last_tempseg_size/1024/1024,2) as tmp_L from v$sql_workarea where max_tempseg_size is not null order by max_tempseg_size desc;
  • 26. 例子SQL_ID OP ID E_OPT E_ONE L_MEM LAST TOT OPT ONE MULT SEC TMP_M TMP_L ------------- --------------- --- -------- -------- -------- ------- --- --- ----- ---- -------- -------- -------- c940m2fhfdhqb HASH-JOIN 2 2048 33.92 1123.07 1 PASS 1 0 1 0 2515.02 3520 3520 9n9h9vbfsutgf GROUP BY (SORT) 1 1147.91 14.09 97.62 1 PASS 1 0 1 0 481.01 1792 1792 33hqyxbdddcj6 GROUP BY (HASH) 1 1130.5 28.91 142.9 1 PASS 1 0 1 0 2957.31 1088 1088 czmtvcbdamr8v HASH-JOIN 4 1412.22 34.26 1251.05 1 PASS 1 0 1 0 8726.81 768 768 71mcs1y2pguza HASH-JOIN 7 975.08 19.88 432.83 1 PASS 1 0 1 0 3071.57 768 768 47m5kq4hw5f79 GROUP BY (SORT) 13 324.85 7.01 97.62 1 PASS 1 0 1 0 376.89 448 448 gk97kydxcdhf9 GROUP BY (HASH) 2 726.91 22.47 126.81 1 PASS 1 0 1 0 206.21 448 448 7n1hu6k66a369 HASH-JOIN 13 967.37 16.18 1108.92 1 PASS 1 0 1 0 196.07 448 448 gs4aa3r85upzc GROUP BY (HASH) 1 352.85 14.63 88.09 1 PASS 1 0 1 0 270.71 384 384 cmfzdunmgr1a1 GROUP BY (HASH) 1 278.17 14.23 36.15 1 PASS 1 0 1 0 39.38 320 320 3j6f35gyq3syd GROUP BY (HASH) 1 321.92 15.43 42.42 1 PASS 1 0 1 0 288.28 320 320 0tzdnch3vc8tv WINDOW (SORT) 2 355.88 6.1 97.62 1 PASS 2 0 2 0 29026.58 320 320 0tzdnch3vc8tv WINDOW (SORT) 2 323.88 5.83 97.62 1 PASS 1 0 1 0 4853.2 320 320
  • 27. 利用STATSPACK/AWR分析PGA(1) % Blocks changed per Read: 8.94 Recursive Call %: 37.65 Rollback per transaction %: 0.81 Rows per Sort: 122.30 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.84 Redo NoWait %: 99.98 Buffer Hit %: 99.46 In-memory Sort %: 100.00 Library Hit %: 100.05 Soft Parse %: 100.00 Execute to Parse %: 60.22 Latch Hit %: 99.43 Parse CPU to Parse Elapsd %: 9.02 % Non-Parse CPU: 99.94
  • 28. 利用STATSPACK/AWR分析PGA(2)PGA Aggr Summary DB/Inst: SFOSS/sfoss2 Snaps: 12838-12839 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 98.4 302,582 4,894 -------------------------------------------------------------
  • 29. 利用STATSPACK/AWR分析PGA(3) %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - ---------- ---------- ---------- ---------- ------ ------ ------ ---------- B 10,240 7,707 3,278.3 218.4 6.7 100.0 .0 1,048,570 E 10,240 7,127 6,938.0 1,099.3 15.8 100.0 .0 1,048,570 -------------------------------------------------------------
  • 30. 利用STATSPACK/AWR分析PGA(4) Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 2,097,268 2,097,268 0 0 64K 128K 7,485 7,485 0 0 128K 256K 5,214 5,214 0 0 256K 512K 4,755 4,755 0 0 512K 1024K 394,849 394,839 10 0 1M 2M 4,695 4,695 0 0 2M 4M 776 538 238 0 4M 8M 317 139 178 0 8M 16M 218 88 130 0 16M 32M 148 74 74 0 32M 64M 4 4 0 0 -------------------------------------------------------------
  • 31. 利用STATSPACK/AWR分析PGA(5) Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 1,280 0.1 113,513,637.6 5,198,930.2 96.0 350,462 2,560 0.3 113,513,637.6 1,154,187.9 99.0 93,607 5,120 0.5 113,513,637.6 832,125.5 99.0 63,379 7,680 0.8 113,513,637.6 552,639.5 100.0 37,120 10,240 1.0 113,513,637.6 88,382.3 100.0 7,583 12,288 1.2 113,513,637.6 0.0 100.0 0 14,336 1.4 113,513,637.6 0.0 100.0 0 16,384 1.6 113,513,637.6 0.0 100.0 0 18,432 1.8 113,513,637.6 0.0 100.0 0 20,480 2.0 113,513,637.6 0.0 100.0 0 30,720 3.0 113,513,637.6 0.0 100.0 0 40,960 4.0 113,513,637.6 0.0 100.0 0 61,440 6.0 113,513,637.6 0.0 100.0 0 81,920 8.0 113,513,637.6 0.0 100.0 0 -------------------------------------------------------------
  • 32. 临时表空间优化要点7.3.4以后,临时表空间优化工作得到了较大的简化 使用PGA自动管理后,尽可能配置足够大的PGA,减少临时表空间访问 不同用户使用不同的临时表空间,减少冲突 使用临时表空间组(10G)
  • 33. PGA和ORA-4030PGA设置过大会导致系统换页甚至出现ORA-4030 不合理的设置PGA隐含参数可能导致ORA-4030 ORA-4030不一定是由于PGA设置过大导致 ORA-4030的可能原因 PGA设置过大,导致物理内存耗尽 操作系统内存限制设置不合理 程序中分配内存部分出现死循环 分配对象后没有释放(最为典型的是XML对象)
  • 34. 下节预告:文件IO分析文件IO问题可能导致的系统性能问题 如何通过ORACLE的分析手段发现文件IO性能问题 如何优化IO性能问题
  • 35. 感谢您对华章培训网的支持!http://www. hztraining.com