在Oracle Database 11g中有一个新特性,全表扫描可以通过直接路径读的方式来执行(Direct Path Read),这是一个合理的变化,如果全表扫描的大量数据读取是偶发性的,则直接路径读可以避免大量数据对于Buffer Cache的冲击。
可是现实往往是残酷的:在很多业务系统中,全表扫描是普遍存在的常态,将大表的全表扫描全部转化为直接路径读,反而不如Cache在Buffer Cache中效率高,Direct Path Read反而成为了一个严重的负担。
当然对于小表来说,Oracle允许通过Buffer Cache来进行全表扫描,因为这可能更快,也对性能影响不大。小表受到隐含参数:_small_table_threshold 影响。如果表大于 5 倍的小表限制,则自动会使用DPR替代FTS。
Oracle通过一个内部参数_small_table_threshold来定义大表和小表的界限。缺省的该参数等于2%的Buffer数量,如果表的大小小于该参数定义,Oracle认为该表为小表,否则Oracle认为该表为大表。
我们看一下Oracle9iR2中的情况:
SQL> @@GetParDescrb.sql NAME VALUE DESCRIB |
以上数据库中,200正好约为Buffer数量的2%:
SQL> show parameter db_cache_size NAME TYPE VALUE (83886080/8192)*2/100 |
SQL> SELECT x.ksppinm NAME,y.ksppstvl VALUE,x.KSPPDESC PDESC
FROM SYS.x$ksppi x,SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%small_table_threshold%'; 2 3
NAME VALUE PDESC
------------------------------ -------------------- --------------------------------------------------
_small_table_threshold 676 lower threshold level of table size for direct rea
缺省情况下Oracle认为在2%的cache buffer以下的表格认为是小表,在FTS操作中被放到MRU end。
在_small_table_threshold以上的表格标记为大表,FTS操作结果被放置到LRU end。也就是说Oracle仅仅缓冲一次操作的结果。
如果遇到这个特性的负面影响,可以设置初始化参数: _serial_direct_read 来禁用串行直接路径读,其默认值为AUTO,设置为NEVER时禁用 11g 的自动direct path read的特性。该参数可以动态在实例或会话级别修改,而无需重启实例(可以结合Event 10949设置)。
SQL> alter system set "_serial_direct_read"=auto;
SQL> alter system set "_serial_direct_read"=never;
以下的AWR信息是典型的DPR症状,我们看到Direct Path Read在这个报告中处于最占用DB Time的部分:
如果结合ASH报告更加一目了然,显示全表扫描的SQL,都在以Direct Path Read的方式执行 Table Access Full: