第一章 ORACLE的直接路径读
1.1 直接路径读
首先介绍一下直接路径读的概念：
传统的数据库读取的方式是先在内存中搜索，如果搜索不到数据，那么就在把数据从磁盘读到内存中，然后PGA再中SGA中获取数据，这样数据就缓存到内存中了，下次再次访问的时候，就可以直接从SGA中获取，不用再进行物理读；
direct path read(直接路径读)：直接路径读是违反传统的数据读取方式的，指服务器进程直接读取数据文件到PGA的内存，不经过buffer cache。
直接路径读的优势：存在即合理，相信这个算法的产生也肯定是有原因的；
总结优势如下：
1、采用直接路径读取后，总能保证读取的块数是多块读参数设置的大小，提高了读取的效率；
2、大大的降低了对于latch的使用，进而避免了可能导致的latch竞争（cbc latch等）；
3、降低了全表扫描对buffer cache的冲击；
4、降低了buffer pin的开销，有可能降低buffer busy waits等相关等待；
当然这种方法也是有副作用的：
1、会发生段一级的检查点，因此在查询真正开始执行前，会做这个额外的准备工作。而且可能会造成IO抖动，因为要写脏数据；
2、如果你的表需要频繁的全表扫描读取，还是用传统的读取方式比较好；
3、在MOS中搜索direct path read，会发现它可能会导致多次的延迟块清除；
1.2 隐含参数
隐含参数一：
_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。
隐含参数二：
_small_table_threshold
单位是block, 表小于该块的数量，被认为是小表，一定不走直接路径。
隐含参数三：
_db_block_buffers
DB_CACHE大小。
公式：_db_block_buffers*2%得到小表的阈值：_small_table_threshold。
隐含参数四：
_very_large_object_threshold
当该表大于此倍数时，一定走直接路径。 11G默认值是500 单位是倍数，
公式：_very_large_object_threshold/100*_db_block_buffers
隐含参数五：
_direct_read_decision_statistics_driven
表的块值是从哪里获取，从统计信息表还是段头。
隐含参数六：
10949事件用来禁用全表扫描的直接路径读。尽量地关闭介于隐含参数二到隐含参数四的表不走直接路径。
第二章 实验环境测试
下面让我们对上述隐含参数的作用做一个测试验证。实验脚本如下：
2.1 测试环境
数据库11.2.0.4。
查看DB_CACHE大小：show sga; 1216MB
查看相关隐含参数信息：
set linesize 120
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
where x.indx = y.indx
AND x.ksppinm in ( '_small_table_threshold','_serial_direct_read','_very_large_object_threshold','_db_block_buffers','_direct_read_decision_statistics_driven');
根据_db_block_buffers8/1024=1123MB近似等于Database Buffers大小。
_small_table_threshold=_db_block_buffers2%=2874块次
_very_large_object_threshold/100*_db_block_buffers=718,685块次
创建测试表，模拟直接路径读场景。
create table t as select rownum id from dual connect by level<=2;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ; --设置每个块最多有两行
insert into t select rownum+2 from dual connect by level <=5998;
commit;
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = user and segment_name = 'T'; --查看表块大小
select object_id,data_object_id from dba_objects where owner='SZT' and object_name='T'; --查看对象ID
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
alter system flush buffer_cache;
2.2 测试直接路径读及其禁用方式
2.2.1 记录直接路径读
可以看到当前表块3072>2874块次,小于 718,685块次。理论上发生全表扫描时可以走直接路径读。
查看当前会话等待信息：
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
执行测试语句：
alter system flush buffer_cache;
select count(*) from v$bh where OBJD=88735 and STATUS<>'free'
select count(*) from t;
可以看到，产生了31次直接路径读访问。
再次执行SQL：仍然是31次直接路径读
2.2.2 10949事件关闭直接路径读
测试一下通过10949事件关闭。
alter session set events '10949 trace name context forever, level 1';
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
可以看到，系统产生了39次多快读，并没有发生直接路径读。
再次测试：
系统不再产生物理读及IO等待，表明当前访问均来自于逻辑读。通过此处可以确定10949事件设置1可以关闭来自于表块>_small_table_threshold阈值的直接路径读。
调整回默认值：
alter session set events '10949 trace name context off';
alter system flush buffer_cache;
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
系统再次产生了直接路径读。
2.2.3 参数_serial_direct_read关闭直接路径读
通过参数_serial_direct_read控制系统是否走直接路径读。默认时采用直接路径读：
alter session set "_serial_direct_read"=never;
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
可以看到系统确实关闭了直接路径读，产生了多快读IO请求。
2.2.4 KEEP CACHE方式关闭
尝试将测试表放入KEEP池中，是否能避免直接路径读：
alter table T storage(BUFFER_POOL KEEP);
select table_name from dba_tables where buffer_pool='KEEP';
alter table T cache;
select table_name,cache,Buffer_pool,BLOCKS from user_tables where CACHE like '%Y';
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
可以看到将表放入KEEP池中，确实不再产生直接路径读。
2.2.5 建立索引，避免全表扫描
对T表创建索引，通过索引访问，测试是否还会使用直接路径读。
Create index idx_t_id on t(id);
select count(*) from t;
select count(*) from t where id<10;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
可以看到，通过索引访问，不再使用直接路径读。
继续执行：
不再产生物理IO。
2.3 测试相关隐含参数及特性
2.3.1 _small_table_threshold
通过上面的测试可知，当表块数> _small_table_threshold时，产生直接路径读。减小表块数量测试：
_small_table_threshold=_db_block_buffers*2%=2874块次
Delete from t where rownum<4000;
Commit;
set linesize 2000
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col HEADER_FILE for 99
col HEADER_BLOCK for 9999
col BLOCKS for 9999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = 'SZT' and segment_name = 'T'; --查看表块大小
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
alter system flush buffer_cache;
直接删除数据后表块并没有减少。
alter system flush buffer_cache;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from t;
select count(*) from v$bh where OBJD=88735 and STATUS<>'free';
由于表块数量没有减少，尽管删除了大部分行记录，但系统仍然采用直接路径读。
alter table t enable row movement;
alter table t move tablespace users; --收缩表空间
set linesize 2000
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col HEADER_FILE for 99
col HEADER_BLOCK for 9999
col BLOCKS for 9999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = 'SZT' and segment_name = 'T'; --查看表块大小
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
alter system flush buffer_cache;
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from t;
得出结论，当表的数据块减少到小于_small_table_threshold参数值时，系统会自动调整直接路径读为传统的IO访问方式。
但某些情况下直接删除数据还不能减少表的数据块，还需要通过清理表的高水平位或收缩表空间等方式。
2.3.2 _very_large_object_threshold
该参数用于控制当表大于此倍数时，一定走直接路径。 那如何理解一定走直接路径读呢，是否意味着大于该倍数的表，即使通过索引访问数据，也不会走DB_CACHE？以下测试一下：
11G默认值是500 单位是倍数。
公式：_very_large_object_threshold/100*_db_block_buffers。
为了便于测试减少该倍数。同时增大表数据量：
100/100* 143737= 143,737块次
alter session set "_very_large_object_threshold"=100;
create table t as select rownum id from dual connect by level<=2;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ; --设置每个块最多有两行
insert into t select rownum as id from xmltable('1 to 320000');
commit;
set linesize 2000
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col HEADER_FILE for 99
col HEADER_BLOCK for 9999
col BLOCKS for 9999999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = user and segment_name = 'T'; --查看表块大小
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
alter system flush buffer_cache;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from t;
可以看到，系统采用了直接路径读访问。
继续验证通过索引访问该表：
CREATE index idx_t_id on t(id);
alter system flush buffer_cache;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
select count(*) from t where id <10;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
可以看到，系统并未像我们预期的那样，只要表块数超过该倍数，则不论如何访问数据都采用直接路径读的方式。
继续查资料，该参数主要结合10949事件来确定直接路径读的上限。
即：_small_table_threshold定义了大表的下限。
_very_large_object_threshold/100*_db_block_buffers定义的表的上限。
当开启10949时，只能对下限到上限之间的表禁用直接路径读。
下面测试：
alter session set "_very_large_object_threshold"=500;
set linesize 2000
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col HEADER_FILE for 99
col HEADER_BLOCK for 9999
col BLOCKS for 9999999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = user and segment_name = 'T'; --查看表块大小
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
select table_name,num_rows,blocks from dba_tables where table_name='T';
alter system flush buffer_cache;
表块为16W+
_very_large_object_threshold/100*_db_block_buffers=718,685块次。
表块数量在下限到上限之间。
alter session set events '10949 trace name context forever, level 1';
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
关闭10949事件后，系统没有采用直接路径读。
减少上限的倍数。
alter session set “_very_large_object_threshold”=100;
表块为16W+
_very_large_object_threshold/100*_db_block_buffers=143,737块次。
表块数量大于上限。
alter session set events '10949 trace name context forever, level 1';
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
可以看到，当表块数大于上限后，即使通过10949也无法避免该表的直接路径读访问了。
那测试通过_serial_direct_read能否避免呢？
alter session set events '10949 trace name context off';
alter session set "_serial_direct_read"=never;
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
可以看到，通过参数_serial_direct_read还是可以完全禁用直接路径读的。
得出结论：
当表的数据块在下限_small_table_threshold到上限_very_large_object_threshold/100*_db_block_buffers之间时，可以通过10949事件和_serial_direct_read关闭直接路径读访问；
当表超过_very_large_object_threshold/100*_db_block_buffers上限后，只能通过_serial_direct_read关闭，10949事件已无法避免直接路径读。
2.3.3 _direct_read_decision_statistics_driven
该参数用于控制表块的取值从哪里获取。True：从统计信息获取、False：从段头获取。
enable direct read decision based on optimizer statistics。
下面测试表实际块数＜_small_table_threshold参数，但通过调整统计信息能否让小表走直接路径读。
create table t as select rownum id from dual connect by level<=2;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ; --设置每个块最多有两行
insert into t select rownum+2 from dual connect by level <=1000;
commit;
set linesize 2000
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a10
col HEADER_FILE for 99
col HEADER_BLOCK for 9999
col BLOCKS for 9999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner = user and segment_name = 'T'; --查看表块大小
exec dbms_stats.gather_table_stats(ownname=>null, tabname =>'T');--收集统计信息
alter system flush buffer_cache;
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
实际表块2048，系统采用传统IO访问。调整统计信息，增大表块数量：
EXEC DBMS_STATS.SET_TABLE_STATS(user,'T',numblks=>8000,numrows=>'4000');
select table_name,num_rows,blocks from dba_tables where table_name='T';
实际块数2048，统计信息调整块数为8000。测试是否使用直接路径读。
alter system flush buffer_cache;
select count(*) from t;
col event for a30
select event,total_waits,time_waited_micro from v$session_event
where event like '%read%' and sid=(select sid from v$mystat where rownum <2);
通过测试结果可以看到，系统并没有像我们预想的采用直接路径读，仍然通过传统IO的方式来访问该表，因此针对统计信息的调整，可能还需要调整其余信息才能让数据库根据失真的统计信息采用直接路径读，此处暂没测试出。
通过段头的信息获取数据块更为准确，但访问成本考虑会大于统计信息，因此数据库从11G开始默认采用了统计信息获取表块数。
第三章 总结
ORACLE的隐藏参数_small_table_threshold指定了 ORACLE中大表的阀值，其单位为block，即大于_small_table_threshold 所指定的块数的表被视作大表，对于大表的全表扫描将会采用直接路径读的方式。_small_table_threshold 隐藏参数的值在实例启动时动态决定，一般为 2% * DB_CACHE_SIZE。
ORACLE的隐藏参数”_serial_direct_read” 指定了是否启用串行全表扫描下的直接路径读取(direct path read)，其默认值为AUTO，设置为NEVER时禁用11g自动direct path read的特性。最有效的禁用手段。可以动态在实例或会话级别修改，而无需重启实例。
设置event 10949可以避免采用直接路径读取方式，但要受到参数_small_table_threshold和_very_large_object_threshold/100*_db_block_buffers的影响，10949事件只能禁用上述上限和下限之间的直接路径读行为。
那试想一下，如果确定直接路径读访问效率高于传统的IO访问方式，如何强制SQL采用直接路径读取呢？以下引用MOS上的相关描述：
1. Use PARALLEL hint to the SQL statements like, /*+ parallel(4) */ so that parallelism uses direct path read.
2. Setting the statistics of the tables involved in the SQL such that the no.of blocks of tables > _small_table_threshold to enable the serial direct path read.
(i) Check the value of _small_table_threshold parameter in your DB.
SQL> select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm = '_small_table_threshold';
(ii) Check the no.of blocks statistics for the table.
SQL> SELECT blocks FROM user_tables WHERE table_name = 'TABLE_NAME';
Example:
If the blocks from user_tables for the object show 100 and _small_table_threshold is set to 480 then set the blocks statistics manually to 1000 so that it would go for direct path read.
(iii) Set the no.of blocks statistics for the tables involved in the SQL manually greater than the "_small_table_threshold" value.
SQL> EXEC DBMS_STATS.SET_TABLE_STATS('username','tabname',numblks=>n);
Example:
SQL> EXEC DBMS_STATS.SET_TABLE_STATS(user,'TEST',numblks=>1000);
NOTE: Though the block counts are taken from the optimizer statistics, it is not the optimizer alone that does the direct path read decision, as there are other factors like buffer cache size, cached block counts, PX execution etc affecting the direct path reads.
