buffer cache等待事件的发生主要原因有两个:buffer cache太小、buffer cache中块的争用
buffer cache太小思路:
1)buffer cache 内存不足;这是客观因素,如果短时间内无法解决内存问题
,在I/O和CPU资源足够的情况下,可以考虑将部分表直接路径读,从而减少对buffer cache的使用,此外可以考虑将一下使用频率较低的表写到recycle pool中,减少default pool的使用;
2)低效的sql,会使前台应用过多的请求buffer cache,会将很多无效数据读取到buffer cache中,进而冲击buffer cache中的其他数据,虽然服务进程会将大部分的块放在LRU的尾端,但是难免会牺牲掉一些原本在buffer cache中的数据块。另外,从磁盘上读取数据块会调用操作系统内核指令,进入buffer cache之前获得一系列latch,这些操作都是非常消耗CPU的,CPU资源不足又会反过来影响buffer cache中latch资源的获取,进而造成而行循环。所以在CPU资源不足的情况下盲增大buffer cache会导致更多的数据进入到buffer cache,反而会使数据库的性能更差。
3)DBWR进程不足或者存储性能缓慢。DBWR进程扫描LRU-W列表,会将脏块写到数据文件中。在写操作完成之后,脏块的位置就可以被其他数据使用,并重新挂在到LRU链中。如果系统突然运行大量的DML,将会导致LRU-W上的脏块剧增。如果此时DBWR进程不足或者是存储性能低,则脏块的产生速度远远大于脏块的写出速度,就会产生free buffer waits或者write compelete waits等待事件,出现此问题,需要检查以下几项:
a、磁盘繁忙程度 --是否达到100%
b、磁盘的响应时间--大的I/O不超过20ms,小的I/O一般在5ms左右
c、IOPS是否正常---平均每张盘在150左右
d、存储的cache是否正常
e、与存储相关的硬件是否正常
如果以上5个方面都正常,则需要考虑通过增大db_writer_processes来增加DBWR进程。在多CPU环境里,一般设置DBWR的值不超过CPU的个数,原因是服务器不仅仅运行此进程,还需要运行其他进程。Oracle 10G中一般不会超过20个
若在系统中出现这样的问题,则需要排查存储的性能(吞吐量和响应时间)和相关的存储以及cache中的信息是否正常;
若都正常,则需要明确此时数据库正在完成的工作:
定位sql,确定sql的gets,因为是buffer的问题,则定位到gets上;
若排除了执行计划的问题,则可以考虑增大db_writer_processes参数。
buffer cache中数据块的争用
1)重新设计应用,要彻底解决热块只能这样
2)使用数据库现有的技术尽心缓解热块的发生
a、在一个数据库中允许有多种类型的块,可以减小数据库的db_block
b、将数据块的pctfree增大10%,使得数据块尽量的分散到各个数据块中
c、在表中固定列的大小,使用char这样的字符类型,增加行长,使得数据快进可能的分散到各个数据块中
但是以上步骤会带来隐患,正常访问一条记录需要访问更多的块
,意味着lacth的增多
除了以上三种方法,可以考虑使用几种常见的方法来避免数据块的争用
1)使用hash分区表或者hash 簇表(hash cluster table),使用hash分区表
可以使得原本在同一个块的数据经过hash算法计算后分不到不同的数据块中;此种方法分散了数据块,但是却加大了索引的cluster factor,也就意味着当执行计划为范围扫描时,数据库的性能可能会急剧恶化,所以采用这项技术时,需要仔细分析sql,仔细权衡。
cluster factor简单讲就是row在存储中的有序性,存储的越有序,则
cluster factor的值越小,需要的逻辑读就会越少
2)使用反转键索引(reserve index),与hash表类似,用于主键等值的查询,并不适合索引范围扫描查询,弊端与hash表一致
3)增大buffer cache大小。增大buffer cache到某一个阈值后,buffer cache会自动调整buffer cache中hash bucket的数量,原本热块有可能会被打散,在内存和CPU充足的情况下,增大buffer cache并不是什么坏事,但是有一个原则,就是不能产生交换。在RAC系统中,若产生交换内存甚至导致cluster发生脑裂,导致某个节点自动重启。
4)合理使用keep pool和recycle pool。对于一些相应比较敏感的热表,在内存允许的情况下keep到keep pool中;由于keep pool和default pool使用不同的working set,在cache buffer lru chain latch等待事件时,使用多缓冲池可以从某种程度上提高数据库性能;
5)使用ASSM管理,但是也有增大cluster cache的趋势,在执行大量的DML insert操作时,可能导致L1位图块争用,除了分区技术外,还是预先插入大批量的数据来提高高水位线和增加L1位图块,进而降低位图块的争用
6)加大数据块inittrans参数可以有效减少事务槽等待而产生的冲突
其他buffer cache的优化思路
1)使用直接路径读写(direct path i/o),虽然这个优化超出了热块的解决范围,但是通过直接路径读可以避开buffer cache这个环节,从而将数据直接读取到PGA中。何种情况下使用直接路径读写
a、为了排序工作而读取排序段时
b、开启并行读取数据文件时
c、并行DML和CTAS创建表时
d、读取nocache属性创建的lob段时
e、Oracle 11g新特性,小表使用直接路径读的可能性比较高
2)在CPU资源紧张的数据库中,可以减小buffer cache,增大I/O的压力,来缓解CPU的压力,或者是将db_cache_advice设置为off,进一步缓解CPU的压力;而在I/O紧张的库里,可以增大buffer cache来缓解I/O的压力
3)在RAC环境中,为了减少节点间的数据块的传输,提供本地的buffer cache的命中率,建议将创建service,不同的业务运行在不同的节点上。