常常有初学者问，全表扫描是否只有db file scattered read等待？
答案明显是否定的。
全表扫描也可能会产生db file sequential read,也可能不产生读db file等待；当然，也可能产生我们常见的direct path read等待。
对于db file sequential read，大家觉得不太可能，他最常见发生在读segment header时；当然也会发生在用不着多块读的时候，例如数据block只有一个时。
对于不产生读db file等待当然有可能，即这个segment的所有blocks都在buffer cache里。
对于direct path read等待，即是本文重点内容。
那何为direct path read ？Reference是这样说的：
During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).
原来direct path read其实就是一种异步读取datafile的方式，他跳过SGA的buffer cache，直接去物理datafile上去拿数据，然后读取给PGA。
1.我们都知道并行查询会走direct path read
例如：
--Session A:
SQL> select count(*) from test3;
COUNT(*)
----------
200704
另一个session查询逻辑读和物理读的情况：
--Session B:
select n.name,s.value from
v$sesstat s,v$statname n
where s.STATISTIC# = n.STATISTIC#
and (n.name in
('session logical reads','physical reads',
'physical reads cache','physical reads direct')
or n.name like 'consistent gets%'
)
and s.sid=212
;
NAME VALUE
---------------------------------------- ----------
session logical reads 15042
consistent gets 15041
consistent gets from cache 15041
consistent gets - examination 3274
consistent gets direct 0
physical reads 4
physical reads cache 4
physical reads direct 0
--Session A:
SQL> /
COUNT(*)
----------
200704
--Session B:
SQL> /
NAME VALUE
---------------------------------------- ----------
session logical reads 20891
consistent gets 20890
consistent gets from cache 20890
consistent gets - examination 3274
consistent gets direct 0
physical reads 4
physical reads cache 4
physical reads direct 0
由此可见这样的一个非并行FTS的LIO大约是5000。
--Session A:
SQL> select /*+full(a) parallel(a 2)*/ count(*) from test3 a;
COUNT(*)
----------
200704
--Session B:
SQL> /
NAME VALUE
---------------------------------------- ----------
session logical reads 26930
consistent gets 26929
consistent gets from cache 21093
consistent gets - examination 3274
consistent gets direct 5836
physical reads 5840
physical reads cache 4
physical reads direct 5836
由此可见并行查询走了5000多次physical reads direct，即direct path read。
需要注意的是，这时，physical reads direct是包含在session logical reads里面的。
即，逻辑读的统计信息包含物理读的统计信息。
2.控制非并行（即串行）FTS查询是否走direct path reads由两个隐含参数控制--_small_table_threshold和_serial_direct_read。
但并不完全由这两个参数决定。
_serial_direct_read -- enable direct read in serial
_small_table_threshold -- threshold level of table size for direct reads
当然，要走direct path read，首先要enable _serial_direct_read。否则不走direct path read。
SQL> alter system set "_serial_direct_read"=true;
System altered.
然后Oracle会判断走FTS的表是否是“大表”，即根据是否有_small_table_threshold这么多个blocks来判断。
我当前_small_table_threshold=464，blocksize=8k,即在我测试库上的要3.625MB才算是“大表”。
SQL> select 464*8192/1024/1024 from dual;
464*8192/1024/1024
------------------
3.625
即，大于3.625MB的表串行FTS会走direct path reads，小于3.625MB的表的物理读等待大都是db file scatterred read.
(注意我说的是“大都”，意味着也可能会有db file sequential read)。
下面做实验证明以上结论。
--Session A:
SQL> select BYTES/1024/1024 MB from user_segments where SEGMENT_NAME='TEST4';
MB
----------
8
SQL> select BYTES/1024/1024 MB from user_segments where SEGMENT_NAME='TEST5';
MB
----------
2
SQL> select sid from v$mystat where rownum=1;
SID
----------
212
--Session B:
SQL> /
NAME VALUE
------------------------------ ----------
session logical reads 682
consistent gets 679
consistent gets from cache 679
consistent gets - examination 309
consistent gets direct 0
physical reads 37
physical reads cache 37
physical reads direct 0
--Session A:
SQL> select count(*) from TEST5;
COUNT(*)
----------
4264
--Session B:
SQL> /
NAME VALUE
------------------------------ ----------
session logical reads 890
consistent gets 887
consistent gets from cache 887
consistent gets - examination 320
consistent gets direct 0
physical reads 224
physical reads cache 224
physical reads direct 0
--Session A:
SQL> select count(*) from TEST4;
COUNT(*)
----------
31972
--Session B:
NAME VALUE
------------------------------ ----------
session logical reads 1898
consistent gets 1895
consistent gets from cache 911
consistent gets - examination 331
consistent gets direct 984
physical reads 1213
physical reads cache 229
physical reads direct 984
由此可见，2M的表FTS产生的都是physical reads cache，8M的表FTS产生的都是physical reads direct。
两者分别归入consistent gets from cache和consistent gets direct统计信息。
但千万不要以为“大表”FTS一定会产生physical reads direct，Oracle会根据一些指标（目前我还不知道是哪些参数或指标决定这个）自行判断是否采用physical reads direct。
例如我再一次执行那个8M“大表”的FTS，再查看统计信息：
SQL> /
NAME VALUE
------------------------------ ----------
session logical reads 2890
consistent gets 2887
consistent gets from cache 1903
consistent gets - examination 331
consistent gets direct 984
physical reads 2197
physical reads cache 1213
physical reads direct 984
这时发现，physical reads direct并没有增加，反倒是physical reads cache增加了。这时，可能Oracle根据前一次知道走physical reads direct效率并不高，于是改走physical reads cache了。所以此时8M“大表”都被读入了buffer cache里了。如果我再FTS这个“大表”，再查询统计信息：
SQL> /
NAME VALUE
------------------------------ ----------
session logical reads 3882
consistent gets 3879
consistent gets from cache 2895
consistent gets - examination 331
consistent gets direct 984
physical reads 2197
physical reads cache 1213
physical reads direct 984
我们看到，此时对这个8M的“大表”FTS已经不消耗任何的physical reads了，因为前面一次查询已经将这个表都放入了SGA了。
这次查询仅仅只有逻辑读。
接着，我将这个8M的“大表”扩大到30MB：
SQL> select BYTES/1024/1024 MB from user_segments where SEGMENT_NAME='TEST4';
MB
----------
30
NAME VALUE
------------------------------ ----------
session logical reads 70258
consistent gets 44215
consistent gets from cache 39459
consistent gets - examination 3551
consistent gets direct 4756
physical reads 6462
physical reads cache 1706
physical reads direct 4756
SQL> select count(*) from TEST4;
COUNT(*)
----------
127888
NAME VALUE
------------------------------ ----------
session logical reads 74033
consistent gets 47990
consistent gets from cache 39462
consistent gets - examination 3551
consistent gets direct 8528
physical reads 10234
physical reads cache 1706
physical reads direct 8528
SQL> select count(*) from TEST4;
COUNT(*)
----------
127888
NAME VALUE
------------------------------ ----------
session logical reads 77808
consistent gets 51765
consistent gets from cache 39465
consistent gets - examination 3551
consistent gets direct 12300
physical reads 14006
physical reads cache 1706
physical reads direct 12300
这时，我们可以看到，每次FTS都会只用physical reads direct了。
最后对第二点下一个结论：
当_serial_direct_read=false时，串行查询不会使用direct path reads；
当_serial_direct_read=true时，并且表大于_small_table_threshold这么多个blocks时，第一次执行时会走direct path reads。接着Oracle会自行判断，有可能根据buffer cache的大小等因素，判断是否适合走direct path reads。但一般情况下，表大到一定程度时（这个“程度”我现在不知道是什么因素控制的），每次串行FTS查询都会走direct path reads了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-619894/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15415488/viewspace-619894/
739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
