oracle 11g禁用和强制direct path read

一般在混合型环境中，大表在进行全表扫描或者走并行的时候一般会出现direct path read等待事件，如果在OLTP或者纯粹的DSS环境中，出现大量的direct path read直接路径读取，这样就有问题了，尤其是一些流水线的批处理系统中，会导致大量的free buffer wait等（因为direct path reads会导致oracle执行object level checkpoint将所有目标对象的脏块写入磁盘，然后将这些块从磁盘读取到进程的PGA，如果有未提交的事务，还必须使用PGA构造读一致性块缓冲。所以parallel是把双刃剑，用的不好，系统性能会很慢，尤其是在跑批系统中，增加了大量不必要的物理读写，要想跑的飞快，cpu利用起来，应用层的fork-join线程池设计极大的决定了系统的性能）。一般在OLTP中，都是事务型的sql，如果想sql执行效率，那么最好从内存当中读取数据，直接从数据文件中读取，后果可想而知了。这里就可能需要我们禁用direct path read。

在oracle 11.2之前，只要没有设置_serial_direct_read为true，同时没有指定并行执行，oracle会将HWM以下的块读入buffer cache。如果走了并行执行，意味着所有的表都会导致direct read，即使表不大也如此。

从11.2开始，oracle默认会自动决定要不要在非并行执行的时候绕过buffer cache并使用direct path read（为什么这是合适的呢，举个例子，假设有张表有1亿条数据，平均纪录长度200，全表扫描意味着20G的buffer cache，而服务器本身可能只配置了64G，很明显一张1亿条记录的表不太可能是所有记录都经常要访问的，但是其中的1/10经常访问是可能的，此时如果这个表没有分区显然是不合理的）。同时，更重要的是，我们可以通过参数控制并行查询的时候对某些表的扫描可以走buffer cache，而不是direct read。

oracle根据几个参数决定全表扫描的时候是否走直接路径读，首先是_small_table_threshold隐含参数，它决定oracle认为的小表阈值（同时，读取少于该值的parallel将使用buffer cache），每个环境和版本都可能不同，默认值约为默认值是db cache size的2%大小。如下：

_serial_direct_read值控制非并行执行的时候是否允许走直接读。默认情况下，如果表大于_small_table_threshold*5，则全表扫描的时候自动会被调整为direct read，也就是buffer cache的1/10左右（这个算大、也不算大）。

这个值其实应该被启用，这是对的，但是应该合理设置_small_table_threshold的阈值以最小化不必要的物理io。

可以通过两种方式是禁用直接读，如下：

SQL> alter system set event='10949 TRACE NAME CONTEXT FOREVER, level 1' scope=spfile;
System altered

SQL> alter session set "_serial_direct_read"=false;
Session altered

SQL> 
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   where x.indx = y.indx
  4     AND (x.ksppinm = '_small_table_threshold' or
  5         x.ksppinm = '_serial_direct_read');
NAME                                                                             VALUE                                                                            DESCRIB
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
_small_table_threshold                                                           30338                                                                            lower threshold level of table size for direct reads
_serial_direct_read                                                              FALSE                                                                            enable direct read in serial

SQL> 

还有一个参数 _very_large_object_threshold 用于设定（MB单位）不使用direct path read方式的上限，这个参数需要结合10949事件共同发挥作用。10049和_very_large_object_threshold参数一起的时候，只要_very_large_object_threshold小于目标表大小*0.8或者buffer cache*5，就可以不走direct path read。所以，要想完全禁用非并行执行时的直接路径读，只要将_very_large_object_threshold设置的足够大即可，不过不建议超过20%*buffer cache（否则要么是分区没使用或者不合理）。

所以在并行和串行之间存在一个矛盾点，这个矛盾点只能通过高配置服务器比如256GB/128C+良好的表结构设计或者应用层面的分而治之来解决。

当然，我没有仔细去测，最近太忙，没有时间一一去验证，后面会仔细验证效果如何。

这给帖子写的不错，可以参考下http://www.itpub.net/thread-1815281-1-1.html

参考：http://oracleinaction.com/direct-reads-11g/