当前位置:  数据库>oracle

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

    来源: 互联网  发布时间:2017-04-19

    本文导语: 在Oracle 11g版本中串行的全表扫描可能使用直接路径读取(direct path read)的方式取代之前版本中一直使用的DB FILE SCATTERED READ, 显然direct path read具备更多的优势:1. 减少了对栓的使用,避免可能的栓争用2.物理IO的大小不再取决于buffer...

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 ServerEnterprise 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
































































































































    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • linux下通过对文件读取方式查询oracle的版本信息
  • 读取oracle数据库表写文件慢
  • 用oracle pl/sql 从A unix机器,去读取B unix机器上的一个文件,怎么实现?
  • Oracle除去数据中的换行符以免读取出现问题
  • 请问如何用Java从Oracle数据库中读取媒体文件,如图片,mpeg 等
  • apache通过php的oci函数读取Oracle(字符集ZHS16GBK)时,显示乱码,如何解决?
  • linux下用什么办法连接oracle数据库并且读取数据呢?(用c++代码实现时)
  • jsp读取oracle8.1.6数据表中的中文,读出为?,着急啊。
  • 小妹求教!!关于jsp操作oracle数据库操作的问题(文件读取,插入数据库表相应字段中)
  • 请问oracle的clob字段可不可以存富文本的文件,like .doc .rtf?如果可以如何用java读取?
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,