• 1. Oracle数据库性能优化 第六讲:DB CACHE分析与优化主讲人:白鳝
  • 2. Oracle的DB CACHE存储最近使用过的数据块 通过LRU 算法管理(8i开始引入了tch) DB_BLOCK_SIZE 定义了标准的数据块大小SGA
  • 3. Buffer Head第3页每个BUFFER都有一个头 BH和BUFFER存储在相同的GRANULE里 BH包含以下主要内容Buffer headers include LRU链 Hash链 指向BUFFER的指针Buffer HeadersGranuleBuffers
  • 4. 多缓冲技术缓冲池的分类 DEFAULT POOL:通常使用的缓冲池 KEEP POOL:存放热点数据的缓冲池 RECYCLE POOL:临时统计使用的缓冲池 使用多缓冲的好处 提供缓冲池的效率 减少数据库操作之间的相互影响 减少LRU链相关闩锁的争用
  • 5. NK缓冲NK缓冲的目的是为不同块大小的表空间提供独立缓冲池 NK缓冲池的优点 配合独立块大小的表空间使用 更为合理的将数据存储在不同块大小的表空间中 减少热块冲突 减轻LRU链的争用 提高缓冲池的效率 注意事项 在创建NK表空间前先要配置好NK缓冲池 热块冲突较为严重的数据可以通过减少BLOCKSIZE来优化 行长度较大的表适合放到BLOCKSIZE较大的数据块中 LOB字段适合使用较大的数据块
  • 6. 使用KEEP POOLKEEP POOL的主要作用是最大限度减少IO 存放最常用的对象 减少DEFAULT POOL对敏感数据的影响 什么对象适合放入KEEP POOL 常用的表和索引 相对静态的数据 如何启用KEEP POOL 设置db_keep_cache_size alert table|index ... storage (buffer_pool keep);
  • 7. DB CACHE的优化要点要配置足够大的DB CACHE DB CACHE过小的迹象 DB CACHE命中率不高 CACHE BUFFER CHAINS闩锁争用严重 CACHE BUFFER LRU CHAINS争用严重 物理IO较大 DBWR十分繁忙 出现大量CHECKPOINT的等待 双刃剑:DB CACHE越大,CPU开销越大 多缓冲池是一项十分优秀的技术 NK CACHE技术与NK表空间
  • 8. 关于DB CACHE的命中率DB CACHE的命中率直接反映出DB CACHE的效率 一般针对OLTP系统,DB CACHE的命中率越高越好 OLAP系统的DB CACHE命中率往往不高 加大DB CACHE 提高命中率 减少IO开销 会消耗更多的物理内存和CPU资源 关于DB CACHE命中率的几个问题 DB CACHE的命中率并不是优化的核心所在 优化DB CACHE的核心所在是平衡IO量和物理内存之间的平衡
  • 9. 资源紧张情况下优化DB CACHECPU资源紧张时 加大DB CACHE可能会消耗更多的CPU资源 在某些情况下减少某些资源的IO等待可能会降低CPU资源 IO资源紧张时 加大DB CACHE可以减少IO开销 内存紧张时 使用KEEP POOL
  • 10. DB CACHE分析分析DB CACHE的总体性能 分析DB CACHE设置是否合理 分析DB CACHE调整的策略
  • 11. DB CACHE分析小技巧(1)通过AWR报告查看LOAD PROFILE和命中率指标Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 1,030,352.77 2,106.74 Logical reads: 790,444.41 1,616.20 Block changes: 7,245.20 14.81 Physical reads: 18,130.43 37.07 Physical writes: 200.46 0.41 User calls: 25,398.80 51.93 Parses: 6,705.15 13.71 Hard parses: 38.14 0.08 Sorts: 12,338.31 25.23 Logons: 1.75 0.00 Executes: 11,005.20 22.50 Transactions: 489.07 % Blocks changed per Read: 0.92 Recursive Call %: 34.33 Rollback per transaction %: 0.34 Rows per Sort: 3.81 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.94 Redo NoWait %: 100.00 Buffer Hit %: 97.71 In-memory Sort %: 100.00 Library Hit %: 99.90 Soft Parse %: 99.43 Execute to Parse %: 39.07 Latch Hit %: 99.89 Parse CPU to Parse Elapsd %: 73.51 % Non-Parse CPU: 97.11
  • 12. DB CACHE分析小技巧(2)通过AWR报告查看DB CACHE相关闩锁 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ cache buffer handles 765,561 0.1 0.0 0 0 cache buffers chains 886,820,519 0.1 0.1 1198 23,647,973 0.0 cache buffers lru chain 931,514 0.2 0.1 0 23,741,504 0.2
  • 13. DB CACHE分析小技巧(3)通过AWR报告查看BUFFER CACHE相关情况 Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 6,203,125 97.7 531,142,401 12,077,091 119,319 2 0 317,988 K 744,375 0 0 0 0 0 0
  • 14. DB CACHE分析小技巧(3)通过AWR报告查看DB CACHE Advisory Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 29,952 .6 3,715,920 1.57 1,777,770,246 D 34,944 .7 4,335,240 1.40 1,583,288,243 D 39,936 .8 4,954,560 1.24 1,411,786,927 D 44,928 .9 5,573,880 1.11 1,261,570,495 D 49,920 1.0 6,193,200 1.00 1,136,170,566 D 50,000 1.0 6,203,125 1.00 1,134,324,766 D 54,912 1.1 6,812,520 0.91 1,030,197,173 D 59,904 1.2 7,431,840 0.83 941,461,891 D 64,896 1.3 8,051,160 0.76 863,666,000 D 69,888 1.4 8,670,480 0.70 797,738,505 D 74,880 1.5 9,289,800 0.65 742,123,542 D 79,872 1.6 9,909,120 0.61 694,454,835 D 84,864 1.7 10,528,440 0.58 652,546,848 D 89,856 1.8 11,147,760 0.54 614,028,920 D 94,848 1.9 11,767,080 0.51 578,615,501 D 99,840 2.0 12,386,400 0.48 547,054,847 -------------------------------------------------------------
  • 15. DB CACHE分析小技巧(4)通过图表分析
  • 16. DB CACHE分析小技巧(5)SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS ----------------- -------------------- ------------------------- ------------------- 3664 439451 3.4933 7.6854E+10 7328 878902 1.8821 4.1408E+10 10992 1318353 1.4774 3.2504E+10 14656 1757804 1.3188 2.9013E+10 18320 2197255 1.1799 2.5957E+10 21984 2636706 1.0888 2.3954E+10 25648 3076157 1.0548 2.3206E+10 29312 3515608 1.0323 2.2711E+10 32976 3955059 1.0148 2.2325E+10 36640 4394510 1.0002 2.2005E+10 36704 4402186 1 2.2000E+10 40304 4833961 .9883 2.1742E+10 43968 5273412 .9775 2.1506E+10 47632 5712863 .969 2.1318E+10 51296 6152314 .9622 2.1168E+10 54960 6591765 .9566 2.1045E+10 58624 7031216 .952 2.0943E+10 62288 7470667 .9474 2.0844E+10 65952 7910118 .9429 2.0744E+10 69616 8349569 .9385 2.0647E+10 73280 8789020 .8903 1.9587E+10
  • 17. DB CACHE优化小技巧(6)查找放入KEEP POOL的对象 放入KEEP POOL的对象最好从业务角度考虑 通过X$BH中的TCH字段可以进行辅助筛选
  • 18. 经验分享:分析X$BH的脚本Select decode(pd.bp_id,1, 'KEEP', 2,'RECYCLE', 3,'DEFAULT', 4, '2K SUBCACHE', 5,'4K SUBCACHE', 6,'8K SUBCACHE', 7, '16K SUBCACHE', 8,'32K SUBCACHE', 'UNKNOWN') subcache, bh.object_name object_name, bh.blocks, tch from x$kcbwds ds, x$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS,tch from obj$ o, x$bh x where o.dataobj# = x.obj and x.state !=0 and o.owner# !=0 group by set_ds,o.name,tch) bh where ds.set_id >= pd.bp_lo_sid and ds.set_id <= pd.bp_hi_sid and pd.bp_size != 0 and ds.addr=bh.set_ds order by subcache,object_name;
  • 19. 例子SUBCACHE OBJECT_NAME BLOCKS TCH ------------ ------------------------------ ------- ---------- DEFAULT IDX_EXECORDER_BUREAUNO 102 3879 DEFAULT IDX_EXECORDER_BUREAUNO 111 3243 DEFAULT IDX_FILEWFPU_PUSTATE 229 3038 DEFAULT IDX_FILEWFPU_PUSTATE 231 3148 DEFAULT IDX_PREPAY_FAV_PLAN_USER 293 1145 DEFAULT IDX_STAFF_STAFFNO 10 1107 DEFAULT IDX_TBA_ASKLIST_ASKDATE 1,681 19122 DEFAULT IDX_TBA_ASKLIST_ASKDATE 2,184 13636 DEFAULT IDX_TCH_FEELIST_LIST_PAYTIME 393 3005 DEFAULT IDX_TCH_FEELIST_LIST_PAYTIME 417 3554 DEFAULT IDX_TCM_USER 1,732 6747
  • 20. DB CACHE相关的闩锁cache buffer handles cache buffers chains cache buffers lru chain multiblock read objects
  • 21. DB CACHE优化与共享内存自动管理SGA_TARGET足够的情况下,使用共享内存自动管理具有较好的性能 SGA_TARGET不足的情况下,需要设置DB CACHE的最小值以确保DB CACHE的效率 情况严重时关闭共享内存自动管理 关注V$SGA_RESIZE_OPS
  • 22. DB CACHE和热块冲突DB CACHE配置不合理会加大热块冲突的机会 热块冲突也会影响DB CACHE的性能
  • 23. 什么是热块冲突热块冲突在OLTP系统中十分常见 并发会话同时访问相同的数据块会产生热块冲突 热块冲突严重时会导致严重的系统性能问题 在RAC环境下的热块冲突可能导致系统性能急剧下降,甚至HANG住实例
  • 24. 热块冲突的主要原因IO系统性能不佳 大并发量的数据访问 索引根节点的热块冲突 索引叶节点的热块冲突 SEGMENT HEADER的冲突 ITL参数设置问题
  • 25. 解决热块冲突的主要思路打散数据分布 使用较小的块大小 使用HASH簇表 使用HASH分区表 反转键索引 较大的SEQUENCE CACHE(RAC下) 优化表存储参数 PCTFREE INITRANS FREELISTS/FREELIST GROUPS 提高DB CACHE命中率 优化IO 使用物化视图 KEEP热表 优化应用
  • 26. 查找热块的技巧(1)AWR报告Segments by Buffer Busy Waits DB/Inst: SFOSS/sfoss1 Snaps: 10825-10826 -> % of Capture shows % of Buffer Busy Waits for each top segment compared -> with total Buffer Busy Waits for all segments captured by the Snapshot Buffer Tablespace Subobject Obj. Busy % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- EXP5 EXP5_TEMP_ TT_WAYBILL_EXPORT_ES TABLE 39,627 61.55 EXP5 EXP5_BASE_ IPK_TT_THRASH_BILL INDEX 7,024 10.91 EXP5 EXP5_BASE_ IDX_TT_IMAGE_SCAN_IN INDEX 5,623 8.73 EXP5 EXP5_BASE_ IPK_TT_IMAGE_SCAN_IN INDEX 3,281 5.10 EXP5 EXP5_BASE_ IPK_TM_SYS_MES INDEX 2,214 3.44 -------------------------------------------------------------
  • 27. 查找热块的技巧(2)select event,p1,p2,p3 from v$session_wait where event like 'buffer busy wait%'EVENT P1 P2 P3 ---------------------- ---------- ---------- ---------- buffer busy waits 17 26121 130 buffer busy waits 49 78591 130 buffer busy waits 17 26121 130 buffer busy waits 17 26121 130 buffer busy waits 17 26121 130 buffer busy waits 49 78582 130select owner,segment_name from dba_extents where file_id=&p1 and block_id<=&p2 and (block_id+blocks)>=&p2
  • 28. 下节预告-REDO LOG和CHECKPOINT了解REDO LOG优化相关的技术 了解CHECKPOINT和相关优化技术
  • 29. 感谢您对华章培训网的支持!http://www. hztraining.com