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 ;

小表的定義值為734個block大小。

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事件失效。

 

現在看錶的大小超過5倍buffer cache的試驗：

算出5倍buffer大小為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/，如需轉載，請註明出處，否則將追究法律責任。