11g direct path read 等待事件的实验分析

上次,进行过一次11g direct path read等待事件的初步探讨,现在,就以我的一次实验来完善这个等待事件的研究:

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 查看隐含参数 _small_table_threshold

SELECT A.KSPPINM PARAMETER, A.KSPPDESC DESCRIPTION, B.KSPPSTVL SESSION_VALUE,

C.KSPPSTVL INSTANCE_VALUE

FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C

WHERE A.INDX = B.INDX AND A.INDX = C.INDX AND

--SUBSTR(A.KSPPINM, 1, 1) = '_' and

a.KSPPINM ='_small_table_threshold'

ORDER BY A.KSPPINM ;

小表的定义值为734block大小

PARAMETER         DESCRIPTION                                      SESSION_VALUE   INST

------------------------------ ------------------------------------------------------------ --------------- ----

_small_table_threshold   lower threshold level of table size for direct reads     734               734

 

当表的规模大于5倍小表阀值时候

SQL> create table TT(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));

SQL> insert into TT

  2  select level, 'x', 'x', 'x'

  3  from dual connect by level <= 10 + 5*&sth;

Enter value for sth: 734

old   3: from dual connect by level <= 10 + 5*&sth

new   3: from dual connect by level <= 10 + 5*734

 

3680 rows created.

 

SQL>  select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS';

 

        MB SEGMENT_TYPE

---------- ------------------------------------------------------

        29 TABLE

 

SQL> alter system flush buffer_cache;

alter session set events '10046 trace name context forever, level 8';

select count(*) from TT;

 

可以看到跟踪事件中,以直接路径读进行全表扫描

WAIT #7: nam='db file sequential read' ela= 32539 file#=1 block#=33064 blocks=1 obj#=14517 tim=18808750986

WAIT #7: nam='direct path read' ela= 15931 file number=1 first dba=33065 block cnt=127 obj#=14517 tim=18808769895

WAIT #7: nam='direct path read' ela= 24746 file number=1 first dba=247040 block cnt=128 obj#=14517 tim=18808795968

WAIT #7: nam='direct path read' ela= 17911 file number=1 first dba=247168 block cnt=128 obj#=14517 tim=18808815059

而执行

alter system flush buffer_cache;

alter session set events '10046 trace name context forever, level 8';

alter session set events '10949 trace name context forever, level 1';

select count(*) from TT;

见到的就是另一番景象,数据库采用离散读来进行全表扫描。

WAIT #7: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=14517 tim=18746048764

WAIT #7: nam='db file scattered read' ela= 832 file#=1 block#=33065 blocks=7 obj#=14517 tim=18746049844

WAIT #7: nam='db file scattered read' ela= 712 file#=1 block#=33072 blocks=8 obj#=14517 tim=18746051116

WAIT #7: nam='db file scattered read' ela= 577 file#=1 block#=33080 blocks=6 obj#=14517 tim=18746052075

WAIT #7: nam='db file sequential read' ela= 289 file#=1 block#=33087 blocks=1 obj#=14517 tim=18746052647

现在开始验证表大小与_very_large_object_threshold的比较,对10949事件的影响。

 

查看隐含参数_very_large_object_threshold,这里的单位为MB

SELECT A.KSPPINM PARAMETER, A.KSPPDESC DESCRIPTION, B.KSPPSTVL SESSION_VALUE,

C.KSPPSTVL INSTANCE_VALUE

FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C

WHERE A.INDX = B.INDX AND A.INDX = C.INDX AND

--SUBSTR(A.KSPPINM, 1, 1) = '_' and

a.KSPPINM =' _very_large_object_threshold'

ORDER BY A.KSPPINM ;

 

PARAMETER

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

SESSION_VALUE

--------------------------------------------------------------------------------

INSTANCE_VALUE

--------------------------------------------------------------------------------

_very_large_object_threshold

upper threshold level of object size for direct reads

500

500

 

理论上表的大小小于500M还是会自动使用直接路径读,10949事件也可以禁止直接路径读。因此直接增加表的size到超过500MB

SQL> insert into TT

  2  select level, 'x', 'x', 'x'

  3  from dual connect by level <= 10 + 5*&sth;

Enter value for sth: 10000

old   3: from dual connect by level <= 10 + 5*&sth

new   3: from dual connect by level <= 10 + 5*10000

 

50010 rows created.

 

SQL> select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS'

        MB SEGMENT_TYPE

---------- ------------------------------------------------------

       856 TABLE

 

此时验证10949事件能否阻止直接路径读:

SQL> alter system flush buffer_cache;

SQL> alter session set events '10949 trace name context forever, level 1';

 

SQL> select count(*) from TT;                                       10:21执行

 

  COUNT(*)

----------

109115

 

*** 2011-07-08 10:21:02.115

WAIT #2: nam='SQL*Net message from client' ela= 40831353 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9243629861

CLOSE #2:c=0,e=15,dep=0,type=1,tim=9243630106

=====================

PARSING IN CURSOR #1 len=23 dep=0 uid=0 ct=3 lid=0 tim=9243630258 hv=3499053100 ad='27ef3938' sqlid='9330tqb88yn1c'

select count(*) from TT

END OF STMT

PARSE #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9243630254

EXEC #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9243630577

WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9243630667

WAIT #1: nam='db file scattered read' ela= 32813 file#=1 block#=33225 blocks=7 obj#=14509 tim=9243663820

WAIT #1: nam='db file scattered read' ela= 854 file#=1 block#=33232 blocks=8 obj#=14509 tim=9243665083

WAIT #1: nam='db file scattered read' ela= 692 file#=1 block#=33240 blocks=8 obj#=14509 tim=9243666078

显然并没有令10949事件失效。

 

现在看表的大小超过5buffer cache的试验:

算出5buffer大小为1480M

增加表size

SQL> select bytes/1024/1024 MB ,SEGMENT_TYPE from dba_segments where segment_name='TT' and wner='SYS';

 

        MB SEGMENT_TYPE

---------- ------------------------------------------------------

      1670 TABLE

 

SQL> alter system flush buffer_cache;

SQL> alter session set events '10949 trace name context off';

SQL> select count(*) from TT;                                 10:26

  COUNT(*)

----------

209125

 

SQL> alter system flush buffer_cache;

SQL> alter session set events '10949 trace name context forever, level 1';

SQL> select count(*) from TT;                                   10:31

  COUNT(*)

----------

    209125

 

*** 2011-07-08 10:31:02.503

WAIT #2: nam='SQL*Net message from client' ela= 68444968 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9844017130

CLOSE #2:c=0,e=15,dep=0,type=3,tim=9844017436

=====================

PARSING IN CURSOR #1 len=23 dep=0 uid=0 ct=3 lid=0 tim=9844017611 hv=3499053100 ad='27ef3938' sqlid='9330tqb88yn1c'

select count(*) from TT

END OF STMT

PARSE #1:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9844017607

EXEC #1:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3133740314,tim=9844017928

WAIT #1: nam='SQL*Net message to client' ela= 8 driver id=1111838976 #bytes=1 p3=0 obj#=14509 tim=9844018021

WAIT #1: nam='db file sequential read' ela= 30665 file#=1 block#=33224 blocks=1 obj#=14509 tim=9844048839

WAIT #1: nam='direct path read' ela= 50296 file number=1 first dba=33225 block cnt=55 obj#=14509 tim=9844100911

WAIT #1: nam='direct path read' ela= 29366 file number=1 first dba=92672 block cnt=128 obj#=14509 tim=9844132883

。。。

 

无论10949事件有没有开启;天王老子都不能阻止全表扫描采用直接路径读的方式了。

 

看来 10949 事件还是有其局限性,但是话说回来,对一个如此大的表采用直接路径读,也是无奈的办法,而对这么个大表,最根本的还是建立索引,不走全表扫描还是主要的方法。

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

转载于:http://blog.itpub.net/24582392/viewspace-705133/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值