1. 减少了对栓的使用,避免可能的栓争用
2.物理IO的大小不再取决于buffer_cache中所存在的块;试想某个8个块的extent中1,3,5,7号块在高速缓存中,而2,4,6,8块没有被缓存,传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,这是一种十分可怕的状况,其效率往往要比单次读取这个区间的所有8个块还要低得多,虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);而direct path read则可以完全避免这类问题,尽可能地单次读入更多的物理块。
当然直接路径读取也会引入一些缺点:
1.在直接路径读取某段前需要对该对象进行一次段级的检查点(A segment checkpoint).
2.可能导致重复的延迟块清除操作(我们假设你了解delayed block cleanout是什么).
http://www.oracledatabase12g.com/archives/direct-read-impact-on-delayed-block-read.html
metalink 文档[id 793845.1] 对该新版本中的变化进行了描述:
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.7
This problem can occur on any platform.
Symptoms
After migrating an 11g database from a standalone to a 4-node RAC, a noticeable
increase of 'direct path read' waits were observed at times.
Here are the Cache sizes and Top 5 events.
waits
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,232M 3,616M Std Block Size: 8K
Shared Pool Size: 6,736M 6,400M Log Buffer: 8,824K
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 13,916 42.1
direct path read 1,637,344 13,359 8 40.4 User I/O
db file sequential read 47,132 1,111 24 3.4 User I/O
DFS lock handle 301,278 1,028 3 3.1 Other
db file parallel read 14,724 554 38 1.7 User I/O
Changes
Migrated from a standalone database to a 4-node RAC.
Moved from Unix file system storage to ASM.
Using Automatic Shared Memory Management (ASMM).
The setting of db_cache_size in spfile/pfile is low compared to normal workload requirements.
Cause
There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats.
Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches.
Solution
When using Automatic Shared Memory Management (ASMM) and with buffer cache low limit set at a low end compared to the normal workload requirements and usually after startup, 11g might choose to do serial direct path read scans for large tables that do not fit in the SGA. When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables. If you like to avoid this from happening, you should note the buffer cache and share pool requirements for a normal workload and set the low limits of buffer cache and shared pool in spfile/pfile close to these normal workload values.
db_cache_size
shared_pool_size
下面我们对直接路径读取对于延迟块清除造成的影响进行测试:
SQL> create table tv as select rownum rn,rpad('A',600,'Z') rp from dual
2 connect by level <=300000;
表已创建。
新建一个会话a:
SQL> set linesize 200 pagesize 1400;
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 27281
25 physical reads 27273
25 physical reads direct 27273
25 cleanouts only - consistent read gets 0
-- 显然查询采用了直接路径读取方式
SQL> update tv set rn=rn+1; -- 尝试批量更新
SQL> alter system flush buffer_cache;
-- 刷新高速缓存,造成延迟块清除的情景,并提交
系统已更改。
SQL> commit;
提交完成。
新建一个会话b:
SQL> set linesize 200 pagesize 1400;
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct','redo size');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 54554
25 physical reads 27273
25 physical reads direct 27273
25 redo size 0
25 cleanouts only - consistent read gets 27273
--查询采用direct path read时产生了延迟块清除操作,但不产生redo