什麼是10202trace event？10202：consistent read block header

即是用來分析consistent read的，在深入區的一個大家討論的帖子中提到一個問題，為什麼一個index full scan的10202 trace event中看不到讀root block呢？

http://www.itpub.net/thread-1231064-1-1.html
《請問如何解釋10202 event的內容？》

順著這樣的問題我做了幾個實驗，最後得出來兩個結論：

1.index full scan和index range scan是肯定會讀index root block的。（這貌似是廢話。。）

用index full scan為例：

env：

compatible                         string    10.2.0.4
optimizer_features_enable          string    10.2.0.4

create table haotest4 as select object_id,object_name from dba_objects;
create unique index haotestidx4 on haotest4(object_id);
select DATA_OBJECT_ID from dba_objects where OBJECT_NAME='HAOTEST4';
40664
select DATA_OBJECT_ID from dba_objects where OBJECT_NAME='HAOTESTIDX4';
40665

alter session set events '10202 trace name context forever';
----- begin tree dump
branch: 0x2801eb9 41950905 (0: nrow: 23, level: 1)
leaf: 0x40dd23 4250915 (-1: nrow: 520 rrow: 520)
leaf: 0x40dd24 4250916 (0: nrow: 513 rrow: 513)
leaf: 0x2801eba 41950906 (1: nrow: 513 rrow: 513)
leaf: 0x2801ebb 41950907 (2: nrow: 513 rrow: 513)
leaf: 0x2801ebc 41950908 (3: nrow: 513 rrow: 513)
leaf: 0x40dd25 4250917 (4: nrow: 513 rrow: 513)
leaf: 0x40dd26 4250918 (5: nrow: 513 rrow: 513)
leaf: 0x40dd27 4250919 (6: nrow: 513 rrow: 513)
leaf: 0x40dd28 4250920 (7: nrow: 513 rrow: 513)
leaf: 0x40dd29 4250921 (8: nrow: 513 rrow: 513)
leaf: 0x2801ebd 41950909 (9: nrow: 513 rrow: 513)
leaf: 0x2801ebe 41950910 (10: nrow: 513 rrow: 513)
leaf: 0x2801ebf 41950911 (11: nrow: 513 rrow: 513)
leaf: 0x2801ec0 41950912 (12: nrow: 513 rrow: 513)
leaf: 0x2801ec1 41950913 (13: nrow: 513 rrow: 513)
leaf: 0x2801ec2 41950914 (14: nrow: 513 rrow: 513)
leaf: 0x2801ec3 41950915 (15: nrow: 513 rrow: 513)
leaf: 0x2801ec4 41950916 (16: nrow: 486 rrow: 486)
leaf: 0x2801ec5 41950917 (17: nrow: 478 rrow: 478)
leaf: 0x2801ec6 41950918 (18: nrow: 479 rrow: 479)
leaf: 0x40dd2a 4250922 (19: nrow: 478 rrow: 478)
leaf: 0x40dd2b 4250923 (20: nrow: 478 rrow: 478)
leaf: 0x40dd2c 4250924 (21: nrow: 302 rrow: 302)
----- end tree dump

SQL> select DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x2801eb9','0x'),'xxxxxxxx')) rfile#,
  2  DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x2801eb9','0x'),'xxxxxxxx')) block# from dual;
RFILE#    BLOCK#
---------- ----------
      10    7865

可見10，7865就是root/1 level branch block。
檢視buffer header被touch的次數：

SQL> select DBARFIL,DBABLK,CLASS,TCH from x$bh where BJ=40665 order by 1,2;
DBARFIL    DBABLK    CLASS       TCH
---------- ---------- ---------- ----------
       1    56611       1       10
      1    56612       1       6
      1    56613       1       6
      1    56614       1       6
      1    56615       1       6
      1    56616       1       6
      1    56617       1       6
      1    56618       1       6
      1    56619       1       6
      1    56620       1       6
      10    7864       4       3
      10    7865       1       11
      10    7866       1       6
      10    7867       1       6
      10    7868       1       6
      10    7869       1       6
      10    7870       1       6
      10    7871       1       6
      10    7872       1       6
      10    7873       1       6
      10    7874       1       6
      10    7875       1       6
      10    7876       1       6
      10    7877       1       6
      10    7878       1       6
25 rows selected.

run一下：
select /*+index(t haotestidx4)*/ object_id from haotest4 t where object_id<100;

由於返回肯定不到100行，所以猜測只需掃描一個index leaf block就足夠了。
因為前面trea dump出來每個leaf block儲存500多行。

SQL> /
DBARFIL    DBABLK    CLASS       TCH
---------- ---------- ---------- ----------
      1    56611       1       11
      1    56612       1       6
      1    56613       1       6
      1    56614       1       6
      1    56615       1       6
      1    56616       1       6
      1    56617       1       6
      1    56618       1       6
      1    56619       1       6
      1    56620       1       6
      10    7864       4       3
     10    7865       1       12
      10    7866       1       6
      10    7867       1       6
      10    7868       1       6
      10    7869       1       6
      10    7870       1       6
      10    7871       1       6
      10    7872       1       6
      10    7873       1       6
      10    7874       1       6
      10    7875       1       6
      10    7876       1       6
      10    7877       1       6
      10    7878       1       6

果然，根據x$bh,root block和第一個leaf block都被讀了一次。

2.10202不會顯示index full scan/index range scan的root block的consistent read。

alter session set events '10202 trace name context forever';
select /*+index(t haotestidx4)*/ object_id from haotest4 t where object_id<100;

grep "^Block header dump" 10202.trc | uniq
Block header dump:  0x0040dd23

SQL> select DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x0040dd23','0x'),'xxxxxxxx')) rfile#,
  2  DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x0040dd23','0x'),'xxxxxxxx')) block# from dual;
RFILE#    BLOCK#
---------- ----------
      1    56611

可見，10202 trace結果只顯示一個consistent read，這個就是第一個leaf block的讀。
並沒有顯示root block。但實際肯定需要讀root block的。

同樣的實驗也應用於index range scan。所以結論一樣。

10202 event並不顯示index scan的root block讀

相關文章