Oracle DB buffer cache优化
问题描述:
有个oracle 9i的数据库,提高的是oltp服务,每秒事务数大概1000左右。主机CPU频繁地超过70%。数据库的大小大概是30T。经常性地出现latch,enqueue等待。
问题分析:
根据AWR报告,top 5事件中,第一位总是数据块单块读,高峰期保持在130左右。
占用的cpu也较高。由于业务量较大,数据库负载高,只要数据库稍微有点性能问题,系统就会被堵塞。
为了避免总是救火的状态,需要降低数据库的负载,也就是CPU的使用率。根据AWR报告,数据库单块读占用的CPU最高,决定调大数据库的db cache大小。
调整DB buffer评估:
buffer cache是一个共享内存区域,用来存放从数据文件读的数据块。主要是用来减少物理IO读写,提高访问数据块的效率。调大buffer cache可以减少数据库的单块读。降低cpu的使用率。
同时buffer cache调大后,主机的空余内存就会减少。那么主机的文件cache就会减少。这样就会导致读取文件也就是读取oracle物理文件数据块的时间相应的会变大。
另外buffer cache调大后,相关的SQL执行会变快。如果之前的系统业务有积压的话,在sql效率提高后,可能会更快的执行,反而会提高cpu的使用率。
在对该数据库主机空闲内存评估后,总共是128g内存,oracle的db cache只分配了25g,如果根据v$db_cache_advice建议将db cache调大一倍到53g,空闲内存还有很多。所以对文件cache的影响应该可控。
调整后的效果:
指标 | 调整一周前 | 调整后 | 变化百分比 |
Transactions Per Second | 1609 | 1618.47 | 0.0059 |
Buffer Hit | 97.12 | 98.81 | 0.0174 |
user commits | 2880260 | 2899235 | 0.0066 |
user rollbacks | 17575 | 17239 | -0.0191 |
Physical reads Per Second | 64604.46 | 28356.95 | -0.5611 |
Enqueue avg time | 20 | 12 | -0.4 |
latch free | 6 | 4 | -0.3333 |
hard parses | 181.39 | 167.43 | -0.077 |
CPU | 9056 | 7753 | -0.1439 |
根据本周一和上周一的(9:30~10:00)高峰期的statspack比较,CPU和transaction没有出现堆积的情况,物理读和buffer的命中率有很大的改善。物理读下降了50%左右,在事务数基本没有变化的情况下,数据库CPU下降了14%左右。主机的CPU从75%降低到43%左右。(我们知道数据库小于10ms的CPU语句是不会计算在数据库CPU中的,所以主机的CPU变化比较数据库CPU变化大很多)
数据库单块读的响应时间也从1ms ~ 2ms增长到了3ms ~ 4ms。但是这个还是在可以接受的范围之内。
经过此次调整,该数据库的稳定性也有了较大的提高。基本不用天天处于救火的状态。