ITPub博客

首页 > 数据库 > Oracle > 等待事件direct_path_read详解

等待事件direct_path_read详解

原创 Oracle 作者:jianghongrun 时间:2020-07-16 10:47:47 0 删除 编辑


11G直接路径读、相关参数、10949事件介绍


本实验的结论:


其中 _small_table_threshold 隐藏参数指定了 ORACLE中大表的阀值,其单位为block,

即大于 _small_table_threshold 所指定的块数的表被视作大表,否之视为”small table”。 

对于大表”large table”,SQL执行层认为存在直接路径读取的意义(direct path read)。 

对于小表,将它缓存在buffer cache中的收益更大,所以直接路径读取不具有意义。

_small_table_threshold 隐藏参数的值在实例启动时动态决定,一般为 2% * DB_CACHE_SIZE。


隐藏参数”_serial_direct_read” 指定了是否启用串行全表扫描下的直接路径读取(direct path read),其默认值为AUTO,

设置为NEVER时禁用11g自动direct path read的特性


查看隐含参数

col name for a30

col value for a30

select

 x.ksppinm name,

 y.ksppstvl value,

 y.ksppstdf isdefault,

 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,

 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj

from

 sys.x$ksppi x,

 sys.x$ksppcv y

where

 x.inst_id = userenv('Instance') and

 y.inst_id = userenv('Instance') and

 x.indx = y.indx and

 x.ksppinm like '%_&par%'

order by

 translate(x.ksppinm, ' _', ' ')

/


在11g之前串行的扫描大表默认总是先将数据读取到Oracle高速缓冲中,其等待事件常为db file scattered read。

从11g开始Oracle通过内部算法来决定串行扫描大表是通过直接路径读direct path read,还是先读入到buffer cache中,此算法依据表的大小评估。


在10g中,都是通过gc buffer来读的,所以不存在direct path read的问题。

在11g中,全表扫描可能使用direct path read方式,绕过buffer cache,这样的全表扫描就是物理读了。 

direct path read较高的可能原因有:

1. 大量的磁盘排序操作,order by, group by, union, distinct, rollup, 无法在PGA中完成排序,需要利用temp表空间进行排序。 

当从临时表空间中读取排序结果时,会产生direct path read.

2. 大量的Hash Join操作,利用temp表空间保存hash区。

3. SQL语句的并行处理

4. 大表的全表扫描,在中,全表扫描的算法有新的变化,根据表的大小、高速缓存的大小等信息,决定是否绕过SGA直接从磁盘读Oracle11g取数据。

而10g则是全部通过高速缓存读取数据,称为table scan(large)。11g认为大表全表时使用直接路径读,可能比10g中的数据文件散列

读(db file scattered reads)速度更快,使用的latch也更少。大量的direct path read等待时间最可能是一个应用程序问题。 

direct path read事件由SQL语句驱动,这些SQL语句执行来自临时的或常规的表空间的直接读取操作。 当输入的内容大于PGA中的工作区域时,

带有需要排序的函数的SQL语句将排序结果写入到临时表空间中,临时表空间中的排序顺序串随后被合并,用于提供最终的结果。读取排序结果

时,Oracle会话在direct path read等待事件上等待。DB_FILE_DIRECT_IO_COUNT初始化参数可能影响direct path read的性能。

direct path read的优势:


1. 减少了对闩(latch)的使用,避免可能的闩争用

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则可以完全避免这类问题,尽可能地单次读入更多的物理块。


direct path read的缺点:


1.即便在buffer cache足够大到可以放下整个大表的情况下,direct path read无法从高速缓冲受益,每次扫描大表均需重复等量的直接路径物理读取IO

2.在直接路径读取某段前需要对该对象进行一次段级的检查点(A segment checkpoint).

3.可能导致重复的延迟块清除操作


该11g自动判断direct path read的特性适用场景:


1. 对大表不频繁地串行全表扫描的场景

2. Db Cache Size高速缓冲大小远小于表的大小的场景


不推荐在以下场景中开启该11g自动判断direct path read特性:


1. 从运行稳定的老版本(9i、10g)升级到11g的数据库

2. 对大表频繁地串行全表扫描的场景


SQL> select * from v$version;


BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production


查看大表值及直接路径读参数是否开启

col name for a30

col value for a20

col DESCRIB for a60

set linesize 140 pagesize 1400

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

FROM SYS.x$ksppi x, SYS.x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND (x.ksppinm ='_small_table_threshold' or x.ksppinm='_serial_direct_read');


NAME                           VALUE                DESCRIB

—————————— ——————– ————————————————————

_small_table_threshold         1143                 lower threshold level of table size for direct reads

_serial_direct_read            auto                 enable direct read in serial


增加db_cache_size值,重启查看大表值是否修改为 2% * DB_CACHE_SIZE

SQL> alter system set db_cache_size=1024M scope=spfile;

System altered.


shu immediate;

startup


col name for a30

col value for a20

col DESCRIB for a60

set linesize 140 pagesize 1400

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

FROM SYS.x$ksppi x, SYS.x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND (x.ksppinm ='_small_table_threshold' or x.ksppinm='_serial_direct_read');


NAME                           VALUE                DESCRIB

—————————— ——————– ————————————————————

_small_table_threshold         2522                 lower threshold level of table size for direct reads

_serial_direct_read            auto                 enable direct read in serial


2522 block = 2522 * 8k = =  19.7 M 约等于 1024 * 2%


建表插入数据(值大于上边查出的大表值)

SQL> create table tmac (t1 char(2000)) pctfree 99 pctused 1 tablespace users;

Table created.


SQL> insert into tmac select 'MACLEAN' from dual connect by level <=2530;

2530 rows created.


SQL> commit;

Commit complete.


查看是否有直接路径读,收集统计信息,查看块,清除buffer_cache缓存,全表扫描,查看是否有直接路径读


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SQL> exec dbms_stats.gather_table_stats('SYS','TMAC');

PL/SQL procedure successfully completed.


SQL> select blocks,empty_blocks from dba_tables where table_name='TMAC';


BLOCKS

———-

2638


SQL> alter system flush buffer_cache;


SQL> select count(*) from tmac;


COUNT(*)

———-

2530


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SID NAME                                                                  VALUE

———- —————————————————————- ———-

135 session logical reads                                                  2859

135 physical reads                                                         2763

135 physical reads direct                                                  2576

135 cleanouts only – consistent read gets                                     0


physical reads direct  增加说明上面的查询使用了direct path read


修改参数为不开启直接路径读,全表扫描,查看是否使用直接路径读,全表扫描,查看是否使用直接路径读


##alter system set "_serial_direct_read"=never scope=both sid='orcl';

SQL> alter session set "_serial_direct_read"=never;

Session altered.


SQL> select count(*) from tmac;


COUNT(*)

———-

2530


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SID NAME                                                                  VALUE

———- —————————————————————- ———-

135 session logical reads                                                  5497

135 physical reads                                                         5339

135 physical reads direct                                                  2576

135 cleanouts only – consistent read gets                                     0


SQL> select count(*) from tmac;


COUNT(*)

———-

2530


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SID NAME                                                                  VALUE

———- —————————————————————- ———-

135 session logical reads                                                  8135

135 physical reads                                                         5339

135 physical reads direct                                                  2576

135 cleanouts only – consistent read gets                                     0


physical reads direct 不再增加说明以上2次查询未使用direct path read

隐藏参数”_serial_direct_read” 指定了是否启用串行全表扫描下的直接路径读取(direct path read),其默认值为AUTO,

设置为NEVER时禁用11g自动direct path read的特性


还原session级别的_serial_direct_read 参数


SQL> alter session set "_serial_direct_read"=auto;

Session altered.


查看是否有直接路径读,将TMAC表缩小到 _small_table_threshold以下,清除buffer_cache缓存,全表扫描,查看是否有直接路径读


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SQL> delete tmac where rownum<2000;

1999 rows deleted.


SQL> commit;

Commit complete.


SQL> alter table tmac move tablespace users pctfree 10 pctused 90;

Table altered.


SQL>  exec dbms_stats.gather_table_stats('SYS','TMAC');

PL/SQL procedure successfully completed.


SQL> select blocks from dba_tables where table_name='TMAC';


BLOCKS

———-

189


SQL> alter system flush buffer_cache;

System altered.


SQL> select count(*) from tmac;


COUNT(*)

———-

531


col VALUE for 999999

select vm.sid, vs.name, vm.value from v$mystat vm, v$sysstat vs where vm.statistic# = vs.statistic# and vs.name in 

('cleanouts only – consistent read gets','session logical reads','physical reads','physical reads direct');


SID NAME                                                                  VALUE

———- —————————————————————- ———-

135 session logical reads                                                   524

135 physical reads                                                          349

135 physical reads direct                                                     0

135 cleanouts only – consistent read gets                                     1


以上演示证明对于small table(块数小于_small_table_threshold),SQL执行层自动并不决定使用direct path read,

而是将之读取到buffer cache中并逻辑读。


结论:


其中 _small_table_threshold 隐藏参数指定了 ORACLE中大表的阀值,其单位为block,

即大于 _small_table_threshold 所指定的块数的表被视作大表,否之视为”small table”。 

对于大表”large table”,SQL执行层认为存在直接路径读取的意义(direct path read)。 

对于小表,将它缓存在buffer cache中的收益更大,所以直接路径读取不具有意义。

_small_table_threshold 隐藏参数的值在实例启动时动态决定,一般为 2% * DB_CACHE_SIZE。


隐藏参数”_serial_direct_read” 指定了是否启用串行全表扫描下的直接路径读取(direct path read),其默认值为AUTO,

设置为NEVER时禁用11g自动direct path read的特性


“As of 11.2.0.2 the legal settings are

true, false, always, auto, and never

true is the same effect as always

false is the same effect as auto

Default value is “auto”

Setting event 10949 or event 10354 may also have the side effect of making oracle behave as if _serial_direct_read = never”


该参数可以动态在实例或会话级别修改,而无需重启实例。


类似的10949 EVENT事件也可以起到类似的作用。

设置event 10949可以避免采用直接路径读取方式,该事件可以在线设置,但对现有session可能不会生效:


在实例级别设置:

ALTER SYSTEM SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';


设置到SPFILE中:

alter system set event='10949 TRACE NAME CONTEXT FOREVER' scope=spfile;


在session级别设置:

ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';



主要参考



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69976145/viewspace-2704869/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle rac 启停
请登录后发表评论 登录
全部评论

注册时间:2020-06-01

  • 博文量
    7
  • 访问量
    4154