关于direct path read



描述：用户反应数据库任何操作都很慢，然后我查询到当前有大量的direct path read等待事件。数据库版本是oracle 11G。

诊断与解决步骤：
1查询当前等待事件，主要是direct path read
select event, count(1)
  from v$session_wait
 WHERE EVENT NOT IN
       (select E.NAME from V$EVENT_NAME E WHERE E.WAIT_CLASS = 'Idle')
 group by event
 order by 2 desc;

2 找出产生direct path read的SQL
select sql_id, count(1)
  from v$session
 where event = 'direct path read'
 group by sql_id;

select *
  from v$sql
 where sql_id in (select distinct sql_id
                    from v$session
                   where event = 'direct path read');
                  
发现这些SQL，主要是对一个表做全表扫描，而且都使用了一个选择性较好的字段，但是这个字段没有加索引。
加上索引即可解决问题。

拓展：
一.DirectPath Reads 说明
在oracle 11g以前的版本中，如果对大表进行全表扫描，wait event是：db file scattered read；
在11g中，如果对大表进行全表扫描，wait event是：direct path read；
即在11g中，大表全表扫描是将数据块直接读入会话的pga区域。
在11g中，大表全表扫描时数据块不经过sga而直接进pga，这样会造成每次进行大表全表扫描，物理读都是很大，
而在10g中，由于全表扫描的数据块在sga中已经存在，所以执行全表扫描时，它的物理读为0。
但是这里主要是Oracle在优化策略上的进步，即假定大表频繁全表扫描这种现象，在生产库上不会太多，
通过把数据直接读入pga，进而减少了cachebuffer的繁忙交换程度，提高了cachebuffer的使用效率.

direct path read具备更多的优势：
1.减少了对栓latch的使用，避免可能的栓争用
2.物理IO的大小不再取决于buffer_cache中所存在的块；试想某个8个块的extent中1,3,5,7号块在高速缓存中，
  而2,4,6,8块没有被缓存，传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,
  这是一种十分可怕的状况，其效率往往要比单次读取这个区间的所有8个块还要低得多，
  虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);
  而direct path read则可以完全避免这类问题，尽可能地单次读入更多的物理块。

direct path read也会引入一些缺点：
1.在直接路径读取某段前需要对该对象进行一次段级的检查点(A segmentcheckpoint).
2.可能导致重复的延迟块清除操作。
3.如果全表扫描不可避免，而且频繁，会导致IO问题，进而引起宕库。

当然对于小表来说，Oracle允许通过Buffer Cache来进行全表扫描，因为这可能更快，也对性能影响不大。
小表受到隐含参数：_small_table_threshold 影响。如果表大于 5 倍的小表限制，则自动会使用DPR替代FTS。
可以设置初始化参数： _serial_direct_read 来禁用串行直接路径读。

当然，Oracle通过一个内部的限制，来决定执行DPR的阈值。
可以通过设置10949事件屏蔽这个特性，返回到Oracle 11g之前的模式上：

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

还有一个参数 _very_large_object_threshold 用于设定（MB单位）使用DPR方式的上限，这个参数需要结合10949事件共同发挥作用。
10949 事件设置任何一个级别都将禁用DPR的方式执行FTS，但是仅限于小于 5 倍 BUFFER Cache的数据表，
同时，如果一个表的大小大于 0.8 倍的 _very_large_object_threshold  设置，也会执行DPR。

这些限定的目标在于：
对于大表的全表扫描，必须通过Direct Path Read方式执行，以减少对于Buffer Cache的冲击和性能影响。
但是我们可以通过参数调整来决定执行DPR的上限和下限。