直接路径读取等待事件

直接路径读取等待事件

这个事件DPR在10G是没有的，是11G新等待事件，目的是让全表扫描和快速索引扫描不走SGA，直接从数据文件读到PGA

direct path read的优势

1、采用直接路径读取后，总能保证读取的块数是多块读参数设置的大小，提高了读取的效率

2、大大的降低了对于latch的使用，进而避免了可能导致的latch竞争（cbc latch等）

3、降低了全表扫描对buffer cache的冲击

4、降低了buffer pin的开销，有可能降低buffer busy waits等相关等待

direct path read可能的副作用

1、会发生段一级的检查点（后面详细介绍），因此在查询真正开始执行前，会做这个额外的准备工作。而且可能会造成IO抖动，因为要写脏数据。

2、如果你的表需要频繁的全表扫描读取，还是用传统的读取方式比较好。

3、在MOS中搜索direct path read，会发现它可能会导致多次的延迟块清除

direct path read较高的可能原因有：

1. 大量的磁盘排序操作，order by, group by, union, distinct, rollup, 无法在PGA中完成排序，需要利用temp表空间进行排序。

当从临时表空间中读取排序结果时，会产生direct path read.

2. 大量的Hash Join操作，利用temp表空间保存hash区。

3. SQL语句的并行处理

4. 大表的全表扫描，

5。全索引扫描。

在实际应用情况下尤其是OLTP还有混合类型的数据库，没有做到真正的读写分离架构的传统设计模式的C/S数据库。

那些SQL语句执行频繁又大量的全表扫描，全索引扫描。由于没有SGA的缓存，每一次查询都需要从存储读取产生了大量的物理读，最终导致I/O 100%。

由于处理速度慢，CPU又产生了大量的等待队列，所以DB Time也非常高。

另外 大量的表直接路径读会导致段的检查点的触发，把脏块回写到磁盘，如果该表读写也比较频繁的话，那么LGWR写得也多了起来，同时引起控制文件写操作。

在本来繁忙的IO时间里加上日志写，简直雪上加霜了。

直接路径读取等待事件 涉及多个隐含参数和1个事件

第一个隐含参数：

_serial_direct_read = false 禁用direct path read

_serial_direct_read = true 启用direct path read

alter sytem set "_serial_direct_read"=never scope=both sid='*';

可以显着减少direct path read

这个参数类似于总开关，可惜在11.2.0.4下不生效。

第二个隐含参数：

_small_table_threshold

单位是block, 表小于该块的数量，一定不走直接路径。

第三个隐含参数：

_very_large_object_threshold

当该表大于此倍数是，一定走直接路径。 我这里11.2.0.4 默认值是500 单位不是MB 记住不是MB

那到底是多少大小才呢？ 这涉及第3个参数

第四个隐含参数：

_db_block_buffers

意思是：Number of database blocks cached in memory: hidden parameter

我这里11.2.0.4 值是 1336660 约等于10GB 低于我的BUFFER CACHE 32GB。

公式：_very_large_object_threshold/100*_db_block_buffers

那么第三个应该是 5倍 第4个参数大小，我这里应该是50GB 就必须走直接路径读取。

第五个隐含参数：

__direct_read_decision_statistics_driven

When this parameter is FALSE, the direct path read decision is done based on the segment header’s block count (actual block count).

When TRUE (default in 11.2.0.2+), the direct path read decision is done based on the block count stored in one of the base tables (TAB$, IND$) – the optimizer statistics

意思是说 表的块值是从哪里获取，从统计信息表还是段头呢？

只所以把它拿出来说，因为很多时候统计信息和真实信息不一致，也会导致不必要的直接路径读取。

那么像我这里大于208MB 小于50GB 该怎么办呢？

这就要看另外两个条件了

第一个阀值：脏块阀值，由于direct path read需要出发一个段的检查点，因此脏块太多，刷新脏块可能会导致IO繁忙

第二个阀值：表在内存里的cache率,如果cache率很高，那么还是走传统路径更快

最后一个事件是来关闭

alter system set event= ’10949 trace name context forever, level 1′ scope=spfile;

重启数据库

也可在会话中

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

不过这个事件依旧不是强有效果的。它尽量地关闭介于208MB到50GB的表不走直接路径。

这个超级性能BUG 在12.1.0.2 引入自动大表缓存，将BUFFER CACHE 划出部分为大表缓存区

并且统计表和索引的热度，将其放到CACHE 避免频繁DIRECT PATH READ。

ALTER DATABASE NO FORCE FULL DATABASE CACHING;

alter system set db_big_table_cache_percent_target = 90;

视图：

V$Bt_scan_cache;v$Bt_scan_obj_temps

对于使用很广的11.2.0.4朋友来说该怎么办呢？

方法一：关闭它

alter system set event= ’10949 trace name context forever, level 1′ scope=spfile;

alter system set "_serial_direct_read"=false scope=spfile;

方法二：调高下限

alter system set "_small_table_threshold"=262144 scope=spfile; 约2G

方法三：避免统计信息错误

alter system set "__direct_read_decision_statistics_driven"=FALSE scope=spfile;

方法四： KEEP CACHE

ALTER TABLE T STORAGE (BUFFER_POOL KEEP)

我们人工的把一些大表KEEP进BUFFER CACHE,这样类似于12C的大表缓存特效了

alter system set db_keep_cache_size=2G scope=spfile;

KEEP池其实是一块可用内存采用类似循环的算法进行访问。

如果KEEP池里面还有剩余空间，则新的数据会首先使用剩余的空间，如果KEEP池已经存储满了，Oracle会从头开始重用KEEP池。

方法五 对全表尽可能建索引。

建索引比优化SQL来的快！