Oracle 数据库禁止全表访问的时候direct path read

一般在OLAP环境中，大表在进行全表扫描的时候一般会出现direct path read等待事件，如果在OLTP环境中，出现大量的direct path read直接路径读取，这样就有问题了。一般在OLTP中，都是事物型的sql，如果想sql执行效率，那么最好从内存当中读取数据，直接从数据文件中读取，后果可想而知了。这里就可能需要我们禁用direct path read。

第一种方法：使用10949事件禁止全表扫描的时候，使用direct path read

1 2 3 4 5 6 7 8 9

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 x.ksppinm= '_serial_direct_read');   NAME                            VALUE                                               DESCRIB ------------------------------ -------------------------------------------------- ---------------------------------------------------------------------------------------------------- _small_table_threshold          382                                                  lower threshold level of table size for direct reads _serial_direct_read             auto                                                enable direct read in serial

可以看到默认_serial_direct_read参数为auto，那么在oracle中，什么样的表会被断定为大表？可以看到_small_table_threshold隐含参数，其value为382，单位是block，默认值是db cache size的2%大小，这个是在实例启动的时候动态决定的。在11gr2之后，当一个表的块数大于_small_table_threshold的时候，就被判定为大表，否则就视为小表。对于大表，在进行全表扫描的时候，oracle会根据_serial_direct_read的值来确定是否执行direct path read，如果值为auto，那就会direct path read。

使用10949事件禁用direct path read：

1 2 3

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

使用alter命令禁用direct path read：

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL > alter session set "_serial_direct_read"= false;   Session altered.   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 x.ksppinm= '_serial_direct_read');   NAME                                      VALUE                 DESCRIB ---------------------------------------- -------------------- ------------------------------------------------------------ _small_table_threshold                    382                    lower threshold level of table size for direct reads _serial_direct_read                        FALSE                  enable direct read in serial

开启direct path read：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL >    alter session set "_serial_direct_read"=auto;   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 x.ksppinm= '_serial_direct_read');   NAME                                      VALUE                 DESCRIB ---------------------------------------- -------------------- ------------------------------------------------------------ _small_table_threshold                   382                    lower threshold level of table size for direct reads _serial_direct_read                       AUTO                  enable direct read in serial

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