Oracle 11g中直接路径读取对于延迟块清除的影响

Oracle 11g中直接路径读取对于延迟块清除的影响

在Oracle 11g版本中串行的全表扫描可能使用直接路径读取(direct path read)的方式取代之前版本中一直使用的DB FILE SCATTERED READ, 显然direct path read具备更多的优势：

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

1 2 下一页