请问如何解释10202 event的内容？

zergduan

发现oracle 9.2.0.8中cbo总是不能正确得到执行计划

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table x1 as select * from dba_objects;

Table created.

Elapsed: 00:00:00.05
SQL> create index ind_x1 on x1 (upper(object_name));

Index created.

Elapsed: 00:00:00.02
SQL> exec dbms_stats.gather_table_stats(user,'X1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> set autotrace traceonly
SQL> select * from (
  2  select * from x1 where upper(object_name) is not null order by upper(object_name)
  3  ) where rownum <11;

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=112 Card=10 Bytes=1770)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=112 Card=6263 Bytes=1108551)
   3    2       SORT (ORDER BY STOPKEY) (Cost=112 Card=6263 Bytes=538618)
   4    3         TABLE ACCESS (FULL) OF 'X1' (Cost=10 Card=6263 Bytes=538618)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         81  consistent gets
          0  physical reads
          0  redo size
       1702  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from (
  2  select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
  3  ) where rownum <11;

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2273 Card=10 Bytes=1770)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=2273 Card=6263 Bytes=1108551)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'X1' (Cost=2273 Card=6263 Bytes=538618)
   4    3         INDEX (FULL SCAN) OF 'IND_X1' (NON-UNIQUE) (Cost=25 Card=6263)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1702  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> set autotrace off

而且就算使用了index ind_x1，INDEX (FULL SCAN) OF 'IND_X1' 的card也是6263（全表的行数）~ 从逻辑读上看stop key肯定是起作用了，所以用10046看了一下

1。先做了一个treedump ind_x1
SQL> select object_id from dba_objects where object_name='IND_X1';

OBJECT_ID
----------
      6549

Elapsed: 00:00:00.04
SQL> alter session set events 'immediate trace name treedump level 6549';

Session altered.

Elapsed: 00:00:00.03

--Index IND_X1 Treedump:
*** SESSION ID9.7) 2009-10-29 12:05:39.729
----- begin tree dump
branch: 0xc0264c 12592716 (0: nrow: 24, level: 1)
   leaf: 0xc0264d 12592717 (-1: nrow: 243 rrow: 243)
   leaf: 0xc0264e 12592718 (0: nrow: 244 rrow: 244)
   leaf: 0xc0264f 12592719 (1: nrow: 262 rrow: 262)
   leaf: 0xc02650 12592720 (2: nrow: 239 rrow: 239)
   leaf: 0xc02651 12592721 (3: nrow: 247 rrow: 247)
   leaf: 0xc02652 12592722 (4: nrow: 248 rrow: 248)
   leaf: 0xc02653 12592723 (5: nrow: 252 rrow: 252)
   leaf: 0xc02654 12592724 (6: nrow: 273 rrow: 273)
   leaf: 0xc02655 12592725 (7: nrow: 347 rrow: 347)
   leaf: 0xc02656 12592726 (8: nrow: 312 rrow: 312)
   leaf: 0xc02657 12592727 (9: nrow: 253 rrow: 253)
   leaf: 0xc02658 12592728 (10: nrow: 267 rrow: 267)
   leaf: 0xc0265a 12592730 (11: nrow: 303 rrow: 303)
   leaf: 0xc0265b 12592731 (12: nrow: 250 rrow: 250)
   leaf: 0xc0265c 12592732 (13: nrow: 262 rrow: 262)
   leaf: 0xc0265d 12592733 (14: nrow: 278 rrow: 278)
   leaf: 0xc0265e 12592734 (15: nrow: 255 rrow: 255)
   leaf: 0xc0265f 12592735 (16: nrow: 250 rrow: 250)
   leaf: 0xc02660 12592736 (17: nrow: 251 rrow: 251)
   leaf: 0xc02661 12592737 (18: nrow: 238 rrow: 238)
   leaf: 0xc02662 12592738 (19: nrow: 255 rrow: 255)
   leaf: 0xc02663 12592739 (20: nrow: 268 rrow: 268)
   leaf: 0xc02664 12592740 (21: nrow: 257 rrow: 257)
   leaf: 0xc02665 12592741 (22: nrow: 209 rrow: 209)
----- end tree dump
可见一个24个叶节点一个根节点~
如果stop key起作用，那么index full scan只需要读取0xc0264c (根节点)和0xc0264d (第一个叶节点)
2。用10046验证
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  114367984 bytes
Fixed Size                   454128 bytes
Variable Size              88080384 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> conn mydb
Enter password:
Connected.
SQL> alter session set statistics_level = all;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.02
SQL> select * from (
  2  select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
  3  ) where rownum <11;

... ...

10 rows selected.

Elapsed: 00:00:00.03
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01
SQL> exit


*** 2009-10-29 15:47:58.606
*** SESSION ID9.5) 2009-10-29 15:47:58.497
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=69 dep=0 uid=24 oct=42 lid=24 tim=7232137429 hv=2004533713 ad='67998474'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #1:c=0,e=64,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7231998352
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1601545 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=132 dep=0 uid=24 oct=3 lid=24 tim=7233790753 hv=2342456978 ad='679c28d4'
select * from (
select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
) where rownum <11
END OF STMT
PARSE #1:c=0,e=1805,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7233790750
BINDS #1:
EXEC #1:c=0,e=407,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7233850195
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 20669 p1=3 p2=9804 p3=1
WAIT #1: nam='db file sequential read' ela= 157 p1=3 p2=9805 p3=1
WAIT #1: nam='db file sequential read' ela= 1020 p1=3 p2=9797 p3=1
FETCH #1:c=0,e=57818,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=7233925450
WAIT #1: nam='SQL*Net message from client' ela= 335 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 22130 p1=3 p2=5220 p3=1
WAIT #1: nam='db file sequential read' ela= 20614 p1=3 p2=9767 p3=1
FETCH #1:c=0,e=76821,p=2,cr=4,cu=0,mis=0,r=9,dep=0,og=4,tim=7234033444
WAIT #1: nam='SQL*Net message from client' ela= 896671 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=10 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=7 r=5 w=0 time=134324 us)'
STAT #1 id=2 cnt=10 pid=1 pos=1 obj=0 op='VIEW  (cr=7 r=5 w=0 time=134304 us)'
=====================
PARSING IN CURSOR #2 len=116 dep=1 uid=0 oct=3 lid=0 tim=7234985213 hv=431456802 ad='67b8b790'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #2:c=0,e=981,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=7234985209
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
   bfp=051ccc5c bln=22 avl=03 flg=05
   value=6548
EXEC #2:c=0,e=19735,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7235043031
FETCH #2:c=0,e=38,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=7235043338
STAT #1 id=3 cnt=10 pid=2 pos=1 obj=6548 op='TABLE ACCESS BY INDEX ROWID X1 (cr=7 r=5 w=0 time=134286 us)'
STAT #1 id=4 cnt=10 pid=3 pos=1 obj=6549 op='INDEX FULL SCAN IND_X1 (cr=3 r=2 w=0 time=27219 us)'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=24 oct=42 lid=24 tim=7235069076 hv=4110456808 ad='6798b574'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=282,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=7235069072
BINDS #1:
EXEC #1:c=0,e=19465,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7235113364

可见只访问了5个块~
file#=3 block#=9804,9805,9797,5220,9767

SQL> select file#,rfile# from v$datafile where file#=3;

     FILE#     RFILE#
---------- ----------
         3          3

其中9797 5220 9767应该是'TABLE ACCESS BY INDEX ROWID X1'产生的
SQL> select rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from (
  2  select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
  3  ) where rownum <11;

ROWID              DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------ ------------------------------------
AAABmUAADAAACZFAAE                                    3                                 9797
AAABmUAADAAACZFAAF                                    3                                 9797
AAABmUAADAAACZFAAi                                    3                                 9797
AAABmUAADAAACZFAAG                                    3                                 9797
AAABmUAADAAACZFAAH                                    3                                 9797
AAABmUAADAAABRkAAA                                    3                                 5220
AAABmUAADAAABRkAAB                                    3                                 5220
AAABmUAADAAABRkAAC                                    3                                 5220
AAABmUAADAAABRkAAD                                    3                                 5220
AAABmUAADAAACYnAAQ                                    3                                 9767

而9804,9805转换成rdba就是C0264C，C0264D，也就是ind_x1的根节点和叶节点~

但是忽然看到
STAT #1 id=3 cnt=10 pid=2 pos=1 obj=6548 op='TABLE ACCESS BY INDEX ROWID X1 (cr=7 r=5 w=0 time=134286 us)'
STAT #1 id=4 cnt=10 pid=3 pos=1 obj=6549 op='INDEX FULL SCAN IND_X1 (cr=3 r=2 w=0 time=27219 us)'

也就是说index full scan产生了3个逻辑读cr=3;'TABLE ACCESS BY INDEX ROWID产生了4个逻辑读cr=7

想要看看逻辑读到到底是从哪里来的

记得有人说过
consistent gets = rownum / fetch array size + used datablock
SQL> show array
arraysize 15
我的fetch array是15
那么从索引中找到10行数据需要的逻辑读就是
CEIL(10/15)+2=3
返回表取出10行数据需要的逻辑读
CEIL(10/15)+3=4
似乎很正确~

于是想用Huang版提到的10202 event确认一下
10202event的含义是Consistent read block header

SQL> alter session set events '10202 trace name context forever';

Session altered.

Elapsed: 00:00:00.00
SQL> select * from (
  2  select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
  3  ) where rownum <11;

... ...

10 rows selected.

Elapsed: 00:00:00.07
SQL> alter session set events '10202 trace name context off';

Session altered.

Elapsed: 00:00:00.00

然后看trace，结果问题来了....

--10202 (Consistent read block header) Trace:
*** 2009-10-29 12:07:26.066
Consistent read complete...
Block header dump:  0x00c0264d --第一个叶节点~
Object id on Block? Y
seg/obj: 0x1995  csc: 0x00.179da7  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0xc02649 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179da7

Consistent read complete...
Block header dump:  0x00c02645
Object id on Block? Y
seg/obj: 0x1994  csc: 0x00.179d72  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0xc02641 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179d72
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Consistent read complete...
Block header dump:  0x00c0264d --第一个叶节点
Object id on Block? Y
seg/obj: 0x1995  csc: 0x00.179da7  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0xc02649 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179da7

Consistent read complete...
Block header dump:  0x00c02645
Object id on Block? Y
seg/obj: 0x1994  csc: 0x00.179d72  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0xc02641 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179d72
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Consistent read complete...
Block header dump:  0x00c01464
Object id on Block? Y
seg/obj: 0x1994  csc: 0x00.179d72  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0xc01461 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179d72
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Consistent read complete...
Block header dump:  0x00c02627
Object id on Block? Y
seg/obj: 0x1994  csc: 0x00.179d72  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0xc02621 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00179d72
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000


SQL> select DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x00c02645',
  2                                                              '0x'),
  3                                                        'xxxxxxxx')) rfile#,
  4         DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x00c02645',
  5                                                               '0x'),
  6                                                         'xxxxxxxx')) block#
  7    from dual
  8  /

    RFILE#     BLOCK#
---------- ----------
         3       9797

Elapsed: 00:00:00.00
SQL> ed
Wrote file afiedt.buf

  1  select DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x00c01464',
  2                                                              '0x'),
  3                                                        'xxxxxxxx')) rfile#,
  4         DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x00c01464',
  5                                                               '0x'),
  6                                                         'xxxxxxxx')) block#
  7*   from dual
SQL> /

    RFILE#     BLOCK#
---------- ----------
         3       5220

Elapsed: 00:00:00.00
SQL> ed
Wrote file afiedt.buf

  1  select DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x00c02627',
  2                                                              '0x'),
  3                                                        'xxxxxxxx')) rfile#,
  4         DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x00c02627',
  5                                                               '0x'),
  6                                                         'xxxxxxxx')) block#
  7*   from dual
SQL> /

    RFILE#     BLOCK#
---------- ----------
         3       9767

Elapsed: 00:00:00.00

从trace上来看
并没有读根节点
1。第一个枝节点
2。table block#9797
3。第一个枝节点
4。table block#9797
5。table block#5220
6。table block#9767

从总数上看，才6个~ 明显不对呀~ 请问一下这是为什么？

Yong Huang

You have very detailed analysis. But I wonder if you over-analyzed this issue. Can you tell us what your "正确执行计划" is? You have "upper(object_name) is not null" in the where clause. Remember a B*-tree index does not have null entries. You don't care about object names. As long as the row has an object name (i.e. not null), you want it. How can Oracle make better use of the index?

If this is a frequent SQL, maybe you can create another column called name_exist populated with 'Y' and 'N' and create a bitmap index on it. If you don't want an extra column, create a function based bitmap index:
create bitmap index x1_bi on x1(decode(object_name,null,0,1));
Then your where clause becomes "where decode(object_name,null,0,1) = 1". Of course, since CBO knows you want most (in fact, all) rows, it may still do a FTS. But if you want the opposite result, this new index will be used:

SQL> select * from (select * from x1 where decode(object_name,null,0,1) = 0 order by upper(object_name)) where rownum <11;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=177)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=6 Card=1 Bytes=177)
   3    2       SORT (ORDER BY STOPKEY) (Cost=6 Card=1 Bytes=97)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'X1' (Cost=1 Card=1 Bytes=97)
   5    4           BITMAP CONVERSION (TO ROWIDS)
   6    5             BITMAP INDEX (SINGLE VALUE) OF 'X1_BI'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        945  bytes sent via SQL*Net to client
        357  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Yong Huang

zergduan

感谢黄版~

我觉得既然是upper(object_name) and not null，那么oracle应该考虑使用索引来避免排序~

同样的sql,在10g中


MYDB@MYDB10G >select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

已用时间:  00: 00: 00.18
MYDB@MYDB10G >set autotrace traceonly;
MYDB@MYDB10G >select * from (
  2   select * from x1 where upper(object_name) is not null order by upper(object_name)
  3   ) where rownum <11;

已选择10行。

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 1645154235

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    10 |  1770 |     7   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |        |       |       |            |          |
|   2 |   VIEW                        |        |    10 |  1770 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| X1     | 11583 |  1006K|     7   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | IND_X1 |    10 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   4 - filter(UPPER("OBJECT_NAME") IS NOT NULL)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1718  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed



可见在index full scan的时候oracle就是预估返回10行，而oracle9i中index full scan却预估要返回索引中所有的行~


SQL> explain plan for
  2  select * from (
  3  select /*+ index(x1) */* from x1 where upper(object_name) is not null order by upper(object_name)
  4  ) where rownum <11;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------


-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    10 |  1770 |  2273 |
|*  1 |  COUNT STOPKEY                |             |       |       |       |
|   2 |   VIEW                        |             |  6263 |  1082K|  2273 |
|   3 |    TABLE ACCESS BY INDEX ROWID| X1          |  6263 |   525K|  2273 |
|*  4 |     INDEX FULL SCAN           | IND_X1      |  6263 |       |    25 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   4 - filter(UPPER("X1"."OBJECT_NAME") IS NOT NULL)

Note: cpu costing is off

18 rows selected.



在9i中通过10046来看，index full scan其实也只是扫描了第一个叶节点，应该只是返回了10行~


我想问一下，10202event是否可以用来追踪Consistent gets的情况？为什么我10202的trace的内容这么怪异~ 为什么里面没有根节点的逻辑读？ 里面的顺序为什么也那么怪异~

[ 本帖最后由 zergduan 于 2009-10-30 22:00 编辑 ]

zergduan

顶一下~
http://www.itpub.net/thread-1230977-1-1.html 中EnochPersist在10g中的测试中，10202trace里也少了2个块的纪录

Yong Huang

> 可见在index full scan的时候oracle就是预估返回10行，而oracle9i中index full scan却预估要返回索引中所有的行~
> 在9i中通过10046来看，index full scan其实也只是扫描了第一个叶节点，应该只是返回了10行~

I reproduced it in 9.2.0.8. It seems to be only a display problem with set autotrace. Other than 10046 trace, you can simply use v$mystat to check the increase of 'consistent gets' (statistic# 44 in 9i). That display problem can also be simulated in 10g by alter session set optimizer_features_enable = '9.2.0'. If you want, you can narrow down to the exact underscore optimizer parameter that controls this (find all _optimizer_* params that are changed when you change optimizer_features_enable, set optimizer_features_enable back to 10g, use alter session to set each _optimizer_* to the value in 9i and see which one makes the autotrace to start to make difference.)

> 10202event是否可以用来追踪Consistent gets的情况？为什么我10202的trace的内容这么怪异~ 为什么里面没有根节点的逻辑读？ 里面的顺序为什么也那么怪异~

I read your other posting. It's very hard to follow. Can you make it simpler? Create the simplest table and index and run a full index scan SQL with event 10202. Only show these lines in the trace:
grep '^Block header dump' trace_file

Use dbms_utility.data_block_address_file and _block to translate them. (No need to show details. Just the result.)

If needed, show part of tree dump. I have an example at http://yong321.freeshell.org/oranotes/IndexFullScan.txt

It's possible you don't even need tree dump, just check dba_extents.block_id for extent 0 and dba_segments.header_block. The root block is 1 plus the header_block.

If I remember right, an index full scan won't read segment header. It should start from the root block.

Yong Huang

viadeazhu

实验得出两个结论：

1.首先index full scan是肯定会读index的root block的。
env：compatible                           string      10.2.0.4
         optimizer_features_enable            string      10.2.0.4

create table haotest4 tablespace MD_INDEX 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。


SQL> select DBARFIL,DBABLK,CLASS,TCH from x$bh where OBJ=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的root block的读。

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只显示一个读，这个就是第一个leaf block。
并没有显示root block。但实际肯定需要读root block的。

viadeazhu

index range scan也是一样的。10202不考虑root block的consistent read

magic007

我觉得9i在CBO方面的确还有很多缺陷。但是这篇贴子从实施执行的逻辑读这个角度去研究CBO为啥有这个问题恐怕是不行的。实际执行跟CBO是两回事，是先有CBO得到执行计划再去执行的啊。

Yong Huang

> 10202不会显示index full scan的root block的读。

I did all my tests in 10.2.0.1. At one point I was able to see the root block in 10202 trace. But after that one time, I can only see other index blocks. I can't reproduce the trace showing the root block, even after flushing buffer cache, shared pool, or even bouncing the instance, recreating the index, etc. That trace file is still on my hard drive. I verified it again. It does show the index root block.

Yong Huang

viadeazhu

it is so weird.但是不显示root block的consistent read是可以重现的吧？