Oracle 11g新特性触发Direct Path Read 等待事件案例

最近单位的一台生产数据库出现性能问题,同事处理后给我分享了这个案例。 在这里我整理一下分享给各位同学

数据库环境:

Oracle 11.2.0.3单实例,操作系统是Windows Server 2008 R2。

故障现象:

数据库访问缓慢,I/O使用率达到100%

故障分析:

1. DB Time高,数据库压力大。将近60分钟的采样时间内DB Time高达6983.24。

Oracle 11g新特性触发Direct Path Read 等待事件案例

2. 物理读(Physical read)和逻辑读(Logical read)的数量级相同。看来这么大的物理读就是I/O达到100%的原因。

Oracle 11g新特性触发Direct Path Read 等待事件案例

3. SGA区的Buffer Nowait 100%,看起来和大量的物理读有些矛盾。

Oracle 11g新特性触发Direct Path Read 等待事件案例

4. 前台等待事件排在第一位的是直接路径读direct path read, 占据整个DB Time的85.97%。直接路径读的特点是不经过SGA的缓冲区,直接从存储获取数据。

Oracle 11g新特性触发Direct Path Read 等待事件案例

5. 从TOP SQL上可以看到最耗时的sql语句都是在等待I/O,并且这些I/O来自同一张大表CX_BAS_CUS_CON_SUMUP。系统产生的逻辑读、物理读、直接路径读都来自于这张大表。问题找到了!通过执行计划看到了访问这张大表的sql执行计划是全表扫,该表大小为488M。


Oracle 11g新特性触发Direct Path Read 等待事件案例

Oracle 11g新特性触发Direct Path Read 等待事件案例

Oracle 11g新特性触发Direct Path Read 等待事件案例

Oracle 11g新特性触发Direct Path Read 等待事件案例

最终结论:

在Oracle 11g中有一个新特性,为了保护已经缓存在buffer cache的数据,当出现全表扫的查询时会判断该表的大小。如果该表过大,则使用直接路径读(Direct Path Read)来获取数据。避免大量冷数据对Buffer Cache的冲击。此次问题的原因就是因为这个新特性。大量的并发查询CX_BAS_CUS_CON_SUMUP,并且执行计划都是采用了全表扫,满足了11g的这个新特性,通过直接路径读的方式绕过SGA从存储上获取数据。由于没有SGA的缓存,每一次查询都需要从存储读取产生了大量的物理读,最终导致I/O 100%。由于处理速度慢,CPU又产生了大量的等待队列,所以DB Time也非常高。

新特性中如何判断全表扫的大小呢?

下面看一个隐含参数:_small_table_threshold

该参数默认为Buffer Cache的2%,如果表大于5倍_small_table_threshold就触发该特性。

可以通过设置10949事件屏蔽这个特性

alter session set events '10949 trace name context forever, level 1';

解决方案:

应用团队确认了该表的数据,删除了大量的历史数据,使得全表扫后远低于_small_table_threshold x 5后的数值。再次执行该sql语句就可以缓存在buffer cache中了,物理读和I/O负载全部恢复到合理的范围。

由于不让修改应用程序,我们无法优化该SQL。所以该问题没有从根本上解决。当数据量增大到阈值,问题会卷土重来。

最后感谢我的同事分享这个案例给我

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/09e5e15941a7fd9550939add3886b4c3.html