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

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。

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

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

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

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

最终结论：

在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。所以该问题没有从根本上解决。当数据量增大到阈值，问题会卷土重来。

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

全文完

本文永久更新链接地址：