使用treedump事件查看索引结构

可以使用如下事件获取索引结构:
alter session set events 'immediate trace name treedump level obj#';
测试如下:

[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 12:08:29 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t
  2  as
  3  select * from dba_objects;

Table created.

SQL> create unique index uidx_t_objectid on t(object_id);

Index created.

SQL> create index idx_t_objectname on t(object_name);

Index created.

SQL> create bitmap index bidx_t_owner on t(owner);

Index created.

SQL> select object_id,object_name from dba_objects where wner=user and object_name like '%IDX_T%';

 OBJECT_ID       OBJECT_NAME
--------------------------------------------------------------------------------
    155083       UIDX_T_OBJECTID
    155084       IDX_T_OBJECTNAME
    155085       BIDX_T_OWNER

SQL> alter session set events 'immediate trace name treedump level 155083';
Session altered.

SQL> alter session set events 'immediate trace name treedump level 155084';
Session altered.

SQL> alter session set events 'immediate trace name treedump level 155085';
Session altered.

SQL> host pwd
/u01/app/oracle/product/10.2.0/db_1/admin/bnet/udump

SQL> set linesize 120
SQL> col index_name for a16
SQL> col index_type for a8
SQL> r
  1  select index_name,
  2         index_type,
  3         uniqueness,
  4         blevel,
  5         leaf_blocks,
  6         distinct_keys,
  7         clustering_factor,
  8         num_rows
  9    from dba_indexes
 10   where wner = user
 11*    and index_name like '%IDX_T%'

INDEX_NAME       INDEX_TY UNIQUENES     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
---------------- -------- --------- ---------- ----------- ------------- ----------------- ----------
UIDX_T_OBJECTID  NORMAL   UNIQUE             1         168         80662              2729      80662
IDX_T_OBJECTNAME NORMAL   NONUNIQUE          2         368         35693             40475      80683
BIDX_T_OWNER     BITMAP   NONUNIQUE          1           4            58                60         60

SQL> col segment_name for a20
SQL> r
  1  select segment_name,header_file,header_block,blocks,extents
  2    from dba_segments
  3   where wner = user
  4*    and segment_name like '%IDX_T%'

SEGMENT_NAME         HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
-------------------- ----------- ------------ ---------- ----------
UIDX_T_OBJECTID                4        65835        256         17
IDX_T_OBJECTNAME               4        66987        512         19
BIDX_T_OWNER                   4        67243          8          1

--查看各索引分支:
--cat bnet_ora_25123.trc

--1， create unique index uidx_t_objectid on t(object_id);
----- begin tree dump
branch: 0x101012c 16843052 (0: nrow: 168, level: 1)
   leaf: 0x101012d 16843053 (-1: nrow: 520 rrow: 520)
   leaf: 0x101012e 16843054 (0: nrow: 513 rrow: 513)
   leaf: 0x101012f 16843055 (1: nrow: 513 rrow: 513)
   leaf: 0x1010130 16843056 (2: nrow: 513 rrow: 513)
   leaf: 0x1010131 16843057 (3: nrow: 513 rrow: 513)
   ...
   leaf: 0x101063b 16844347 (164: nrow: 479 rrow: 479)
   leaf: 0x101063c 16844348 (165: nrow: 478 rrow: 478)
   leaf: 0x101063d 16844349 (166: nrow: 69 rrow: 69)
----- end tree dump

SQL> select dbms_utility.data_block_address_file(to_number('101012c', 'xxxxxxx')) fno,
  2         dbms_utility.data_block_address_block(to_number('101012c', 'xxxxxxx')) bno
  3    from dual;

       FNO        BNO
---------- ----------
         4      65836

 

--2，  create index idx_t_objectname on t(object_name);
----- begin tree dump
branch: 0x10105ac 16844204 (0: nrow: 2, level: 2)
   branch: 0x10107d4 16844756 (-1: nrow: 315, level: 1)
      leaf: 0x10105ad 16844205 (-1: nrow: 182 rrow: 182)
      leaf: 0x10105ae 16844206 (0: nrow: 187 rrow: 187)
      leaf: 0x10105af 16844207 (1: nrow: 188 rrow: 188)
      leaf: 0x10105b0 16844208 (2: nrow: 182 rrow: 182)
   ...
      leaf: 0x10107d1 16844753 (312: nrow: 201 rrow: 201)
      leaf: 0x10107d2 16844754 (313: nrow: 181 rrow: 181)
   branch: 0x101080b 16844811 (0: nrow: 53, level: 1)
      leaf: 0x10107d3 16844755 (-1: nrow: 200 rrow: 200)
      leaf: 0x10107d5 16844757 (0: nrow: 220 rrow: 220)
      leaf: 0x10107d6 16844758 (1: nrow: 189 rrow: 189)
   ...
      leaf: 0x1010806 16844806 (49: nrow: 186 rrow: 186)
      leaf: 0x1010807 16844807 (50: nrow: 186 rrow: 186)
      leaf: 0x1010808 16844808 (51: nrow: 39 rrow: 39)
----- end tree dump
SQL> select dbms_utility.data_block_address_file(to_number('10105ac', 'xxxxxxx')) fno,
  2         dbms_utility.data_block_address_block(to_number('10105ac', 'xxxxxxx')) bno
  3    from dual;

       FNO        BNO
---------- ----------
         4      66988

==>可以观察各块地址了解分配顺序:
0x10105ac--&gtroot
0x10105ad--&gtleaf block in branch 1
...
0x10107d2--&gtleaf block in branch 1
0x10107d3--&gtleaf block in branch 2    --该块无法在root维护,导致split
0x10107d4--&gtbranch block in branch 1  --此时发生split
0x10107d5--&gtleaf block in branch 2
...
0x1010808--&gtleaf block in branch 2

--dump branch 1 block:
SQL> select dbms_utility.data_block_address_file(to_number('10107d4', 'xxxxxxx')) fno,
  2         dbms_utility.data_block_address_block(to_number('10107d4', 'xxxxxxx')) bno
  3    from dual;

       FNO        BNO
---------- ----------
         4      67540
SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 67540;

System altered.

--查看branch block dump：
Block header dump:  0x010107d4
 Object id on Block? Y
 seg/obj: 0x25dcc  csc: 0x56f.f8d3fe1b  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x101078a ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x056f.f8d3fe1b
 
Branch block dump **branch
=================
header address 237323340=0xe25444c
kdxcolev 1   ++ --该block到leaf block的深度(leaf block 为0).这里branch block 的level 为1
KDXCOLEV Flags = - - -
kdxcolok 0   ++ --表示是否有事务lock了这个branch block，如果有，有多少事务
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2   ++ --索引值条目. 这里表示有2个条目
kdxcosdc 0   ++--这个block的结构被更改次数.这里0表示没有更改
kdxconro 314  ++--索引条目(不包含kdxbrlmc 指针)
kdxcofbo 656=0x290  ++ --空闲空间的起始偏移量
kdxcofeo 679=0x2a7  ++ --空闲空间的末尾偏移量
kdxcoavs 23       ++ --block中的空闲空间=kdxcofeo-kdxcofbo 
kdxbrlmc 16844205=0x10105ad  ++ --如果index value小于row#0，指向该 block 的地址
kdxbrsno 0   ++ --最后被更改的索引条目
kdxbrbksz 8056   ++--块中的可用空间
kdxbr2urrc 12
row#0[8014] dba: 16844206=0x10105ae
col 0; len 30; (30):
 2f 31 31 64 35 30 39 31 32 5f 44 61 74 65 46 6f 72 6d 61 74 5a 6f 6e 65 44
 61 74 61 5f 7a 
 ++ --列的行号，从0开始，紧接着的就是列的长度以及列的值，那么这个值称之为separator key,
 ++   这个separator key 可以区分真实的索引值，所以从这里我们也知道 branch block不会存储完整的索引值，只要能区分就行 
col 1; len 6; (6):  01 01 02 ff 00 24
row#1[8003] dba: 16844207=0x10105af
col 0; len 5; (5):  2f 31 33 38 35
col 1; TERM
....
....
row#310[739] dba: 16844751=0x10107cf
col 0; len 18; (18):  57 52 49 24 5f 41 44 56 5f 53 51 4c 41 5f 4d 41 50 5f
col 1; TERM
row#311[728] dba: 16844752=0x10107d0
col 0; len 5; (5):  58 44 42 24 4b
col 1; TERM
row#312[704] dba: 16844753=0x10107d1
col 0; len 18; (18):  5f 44 42 41 5f 41 50 50 4c 59 5f 4f 42 4a 45 43 54 5f
col 1; TERM
row#313[679] dba: 16844754=0x10107d2
col 0; len 19; (19):  63 6f 6d 2f 73 75 6e 2f 6a 6e 64 69 2f 6c 64 61 70 2f 45
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 67540 maxblk 67540

--在该块头部有如下字符串：/11d50912_DateFormatZoneData_z
SQL>
SQL> select replace(dump('/11d50912_DateFormatZoneData_z','16'),',',' ') from dual;

REPLACE(DUMP('/11D50912_DATEFORMATZONEDATA_Z','16'),',','')
--------------------------------------------------------------------------------
Typ=96 Len=30: 2f 31 31 64 35 30 39 31 32 5f 44 61 74 65 46 6f 72 6d 61 74 5a 6f
 6e 65 44 61 74 61 5f 7a
--在block dump文件中对应row#0[8014] dba: 16844206=0x10105ae，在该块底部（8014）

--3， create bitmap index bidx_t_owner on t(owner);
----- begin tree dump
branch: 0x10106ac 16844460 (0: nrow: 4, level: 1)
   leaf: 0x10106ad 16844461 (-1: nrow: 35 rrow: 35)
   leaf: 0x10106ae 16844462 (0: nrow: 5 rrow: 5)
   leaf: 0x10106af 16844463 (1: nrow: 2 rrow: 2)
   leaf: 0x10106b0 16844464 (2: nrow: 18 rrow: 18)
----- end tree dump
SQL> select dbms_utility.data_block_address_file(to_number('10106ac', 'xxxxxxx')) fno,
  2         dbms_utility.data_block_address_block(to_number('10106ac', 'xxxxxxx')) bno
  3    from dual;

       FNO        BNO
---------- ----------
         4      67244

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

转载于:http://blog.itpub.net/18922393/viewspace-754291/