Oracle Treedump命令分析索引結構內部資訊

索引結構包含了root block, branch block和leaf block，可以透過treedump窺視其分佈情況。
更詳細的treedump說明參考Oracle DSI

具體實驗如下：

1 建立測試表
create table test_idx as select * from all_objects;

2 建立索引
create index test_pk on test_idx(object_id);

3 treedump出索引層次結構

首先查出data_object_id

SQL> select data_object_id,object_id,subobject_name from dba_objects where wner='GZDC' and object_name='TEST_PK';

DATA_OBJECT_ID  OBJECT_ID SUBOBJECT_NAME
-------------- ---------- ------------------------------
         33631      33631



alter session set events 'immediate trace name treedump level 33632';

這種方式dump出的是哪個塊呢?
這種方式的dump檔案不是具體的哪個block,是一個總體的資訊.


在udump目錄下，找出trace檔案，檢視檔案內容

oracle@Z813:/opt/oracle/admin/mydb/udump> cat  mydb_ora_29882.trc
/opt/oracle/admin/mydb/udump/mydb_ora_29882.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9ir2
System name:    Linux
Node name:      Z813
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 29882, image: oracle@Z813 (TNS V1-V3)

*** 2010-07-05 14:18:32.914
*** SESSION ID:(16.6451) 2010-07-05 14:18:32.913
kdxdtr: specified index object 33631 does not exist
*** 2010-07-05 14:21:48.437
----- begin tree dump
  branch: 0x2422a9c 37890716 (0: nrow: 68, level: 1)
   leaf: 0x2422a9d 37890717 (-1: nrow: 485 rrow: 485)
   leaf: 0x2422a9e 37890718 (0: nrow: 479 rrow: 479)
   leaf: 0x2422a9f 37890719 (1: nrow: 479 rrow: 479)
   leaf: 0x2422aa0 37890720 (2: nrow: 478 rrow: 478)
   leaf: 0x2422aa1 37890721 (3: nrow: 479 rrow: 479)
   leaf: 0x2422aa2 37890722 (4: nrow: 479 rrow: 479)
   leaf: 0x2422aa3 37890723 (5: nrow: 478 rrow: 478)
   leaf: 0x2422aa4 37890724 (6: nrow: 478 rrow: 478)
   leaf: 0x2422aa5 37890725 (7: nrow: 479 rrow: 479)
   leaf: 0x2422aa6 37890726 (8: nrow: 479 rrow: 479)
   leaf: 0x2422aa7 37890727 (9: nrow: 478 rrow: 478)
   leaf: 0x2422aa8 37890728 (10: nrow: 479 rrow: 479)
   leaf: 0x2422aaa 37890730 (11: nrow: 479 rrow: 479)
   leaf: 0x2422aab 37890731 (12: nrow: 478 rrow: 478)
   leaf: 0x2422aac 37890732 (13: nrow: 479 rrow: 479)
   leaf: 0x2422aad 37890733 (14: nrow: 479 rrow: 479)
   leaf: 0x2422aae 37890734 (15: nrow: 479 rrow: 479)
   leaf: 0x2422aaf 37890735 (16: nrow: 478 rrow: 478)
   leaf: 0x2422ab0 37890736 (17: nrow: 479 rrow: 479)
   leaf: 0x2422ab1 37890737 (18: nrow: 463 rrow: 463)
   leaf: 0x2422ab2 37890738 (19: nrow: 449 rrow: 449)
   leaf: 0x2422ab3 37890739 (20: nrow: 449 rrow: 449)
   leaf: 0x2422ab4 37890740 (21: nrow: 449 rrow: 449)
   leaf: 0x2422ab5 37890741 (22: nrow: 449 rrow: 449)
   leaf: 0x2422ab6 37890742 (23: nrow: 449 rrow: 449)
   leaf: 0x2422ab7 37890743 (24: nrow: 449 rrow: 449)
   leaf: 0x2422ab8 37890744 (25: nrow: 449 rrow: 449)
   leaf: 0x2422aba 37890746 (26: nrow: 449 rrow: 449)
   leaf: 0x2422abb 37890747 (27: nrow: 449 rrow: 449)
   leaf: 0x2422abc 37890748 (28: nrow: 449 rrow: 449)
   leaf: 0x2422abd 37890749 (29: nrow: 449 rrow: 449)
   leaf: 0x2422abe 37890750 (30: nrow: 449 rrow: 449)
   leaf: 0x2422abf 37890751 (31: nrow: 449 rrow: 449)
   leaf: 0x2422ac0 37890752 (32: nrow: 449 rrow: 449)
   leaf: 0x2422ac1 37890753 (33: nrow: 449 rrow: 449)
   leaf: 0x2422ac2 37890754 (34: nrow: 449 rrow: 449)
   leaf: 0x2422ac3 37890755 (35: nrow: 449 rrow: 449)
   leaf: 0x2422ac4 37890756 (36: nrow: 449 rrow: 449)
   leaf: 0x2422ac5 37890757 (37: nrow: 449 rrow: 449)
   leaf: 0x2422ac6 37890758 (38: nrow: 449 rrow: 449)
   leaf: 0x2422ac7 37890759 (39: nrow: 449 rrow: 449)
   leaf: 0x2422ac8 37890760 (40: nrow: 449 rrow: 449)
   leaf: 0x2422aca 37890762 (41: nrow: 449 rrow: 449)
   leaf: 0x2422acb 37890763 (42: nrow: 449 rrow: 449)
   leaf: 0x2422acc 37890764 (43: nrow: 449 rrow: 449)
   leaf: 0x2422acd 37890765 (44: nrow: 449 rrow: 449)
   leaf: 0x2422ace 37890766 (45: nrow: 449 rrow: 449)
   leaf: 0x2422acf 37890767 (46: nrow: 449 rrow: 449)
   leaf: 0x2422ad0 37890768 (47: nrow: 449 rrow: 449)
   leaf: 0x2422ad1 37890769 (48: nrow: 449 rrow: 449)
   leaf: 0x2422ad2 37890770 (49: nrow: 449 rrow: 449)
   leaf: 0x2422ad3 37890771 (50: nrow: 449 rrow: 449)
   leaf: 0x2422ad4 37890772 (51: nrow: 448 rrow: 448)
   leaf: 0x2422ad5 37890773 (52: nrow: 449 rrow: 449)
   leaf: 0x2422ad6 37890774 (53: nrow: 449 rrow: 449)
   leaf: 0x2422ad7 37890775 (54: nrow: 449 rrow: 449)
   leaf: 0x2422ad8 37890776 (55: nrow: 449 rrow: 449)
   leaf: 0x2422ada 37890778 (56: nrow: 449 rrow: 449)
   leaf: 0x2422adb 37890779 (57: nrow: 449 rrow: 449)
   leaf: 0x2422adc 37890780 (58: nrow: 449 rrow: 449)
   leaf: 0x2422add 37890781 (59: nrow: 449 rrow: 449)
   leaf: 0x2422ade 37890782 (60: nrow: 449 rrow: 449)
   leaf: 0x2422adf 37890783 (61: nrow: 449 rrow: 449)
   leaf: 0x2422ae0 37890784 (62: nrow: 449 rrow: 449)
   leaf: 0x2422ae1 37890785 (63: nrow: 449 rrow: 449)
   leaf: 0x2422ae2 37890786 (64: nrow: 449 rrow: 449)
   leaf: 0x2422ae3 37890787 (65: nrow: 449 rrow: 449)
   leaf: 0x2422ae4 37890788 (66: nrow: 252 rrow: 252)
----- end tree dump

####################################################################
結構說明:
leaf: 表示該資料塊是leaf block
0x2422a9d: 對應索引資料塊的十六進位地址
37890717：對應索引資料塊的十進位地址
-1: 表示索引資料塊的編號，編號起始是-1
nrow: 485 ：表示該索引資料塊中總的行數，包含被刪除的行
rrow: 485：表示該索引資料塊中實際存在有效行數
####################################################################

表示共有69個索引塊
branch:表示為根節點.1個
leaf:表示為葉子節點68個.

透過檢視查詢索引的詳細.有些列不是很清晰?

SQL> Analyze index TEST_PK validate structure;

Index analyzed

SQL> SELECT NAME, BLOCKS, HEIGHT,LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, BTREE_SPACE, USED_SPACE FROM INDEX_STATS WHERE NAME='TEST_PK';

NAME                               BLOCKS     HEIGHT    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS BTREE_SPACE USED_SPACE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
TEST_PK                                80          2      30918         68         67          1      551756     485739







4、根據branch的dba值得到檔案號和塊號。如果索引很小，

那麼就可能沒有branch，直接從leaf取。如果僅僅看某個leaf的內容，也可以在這裡直接從leaf的dba得到檔案號和塊號。
SQL> select dbms_utility.data_block_address_file(37890716),dbms_utility.data_block_address_block(37890716) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             9                         141980
1419980為根節點塊.這與上面的是相吻合的.


SQL>  select dbms_utility.data_block_address_file(37890718),dbms_utility.data_block_address_block(37890718) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             9                         141981
葉子節點塊

SQL>  select dbms_utility.data_block_address_file(37890718),dbms_utility.data_block_address_block(37890718) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             9                         141982                            
葉子節點塊


5、dump資料塊
SQL> alter system dump datafile 9 block 141980;

System altered

SQL> alter system dump datafile 9 block 141981;

System altered

SQL> alter system dump datafile 9 block 141982;

System altered     

詳細參考<>

5 分析資料塊的dump資訊
opt/oracle/admin/mydb/udump/mydb_ora_26575.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9ir2
System name: Linux
Node name: Z813
Release: 2.6.5-7.244-smp
Version: #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine: x86_64
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 26575, image: oracle@Z813 (TNS V1-V3)

*** 2010-07-05 14:36:48.961
*** SESSION ID:(19.167) 2010-07-05 14:36:48.959
Start dump data blocks tsn: 9 file#: 9 minblk 141980 maxblk 141980
buffer tsn: 9 rdba: 0x02422a9c (9/141980)
scn: 0x0273.bbd81a0c seq: 0x01 flg: 0x04 tail: 0x1a0c0601
frmt: 0x02 chkval: 0x70bb type: 0x06=trans data
Block header dump:  0x02422a9c
Object id on Block? Y
seg/obj: 0x8360  csc: 0x273.bbd819f7  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x2422a99 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0273.bbd819f7

Branch block dump (開始根節點塊資訊)
=================
header address 66832460=0x3fbc84c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 67
kdxcofbo 162=0xa2
kdxcofeo 7405=0x1ced
kdxcoavs 7243
kdxbrlmc 37890717=0x2422a9d
kdxbrsno 0
kdxbrbksz 8056

row#0[8047] dba: 37890718=0x2422a9e(rowid)
col 0; len 3; (3):  c2 06 29
col 1; TERM
row#1[8038] dba: 37890719=0x2422a9f
col 0; len 3; (3):  c2 0b 1c
.................
row#65[7415] dba: 37890787=0x2422ae3
col 0; len 4; (4):  c3 04 1b 13
col 1; TERM
row#66[7405] dba: 37890788=0x2422ae4
col 0; len 4; (4):  c3 04 20 28
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 9 file#: 9 minblk 141980 maxblk 141980 (結束根節點塊資訊)







Start dump data blocks tsn: 9 file#: 9 minblk 141981 maxblk 141981(開始第一個葉子節點塊資訊)
buffer tsn: 9 rdba: 0x02422a9d (9/141981)
scn: 0x0273.bbd819fa seq: 0x01 flg: 0x04 tail: 0x19fa0601
frmt: 0x02 chkval: 0xba19 type: 0x06=trans data
Block header dump:  0x02422a9d
Object id on Block? Y
seg/obj: 0x8360  csc: 0x273.bbd819f7  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x2422a99 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 0x0273.bbd819f7

Leaf block dump
===============
header address 66832484=0x3fbc864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1825=0x721
kdxcoavs 819
kdxlespl 0
kdxlende 0
kdxlenxt 37890718=0x2422a9e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032

###########################################################################
結構說明:

header address 55128156=0x349305c
kdxcolev 0          (index level, 0表示是leaf block)
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y 內部操作程式碼
kdxconco 2 索引列數量
kdxcosdc 0 索引結構改變次數
kdxconro 67 索引記錄數量BR_ROWS
kdxcofbo 162=0xa2 空閒空間開始偏移量
kdxcofeo 7405=0x1ced  空閒空間結束偏移量
kdxcoavs 7243  所提供的空閒空間
kdxlespl 0   在資料塊被cleaned out時，還未進行commit的數量
kdxlende 122 被刪除索引記錄的數量
kdxlenxt 67120396=0x4002d0c下一個連結資料塊的地址
kdxleprv 0=0x0上一個連結資料塊地址
kdxledsz 0  被刪除的空間大小
kdxlebksz 8036 使用的資料塊空間

kdxcolev: index level (0 represents leaf blocks)
kdxcolok: denotes whether structural block transaction is occurring
kdxcoopc: internal operation code
kdxconco: index column count
kdxcosdc: count of index structural changes involving block
kdxconro: number of index entries (does not include kdxbrlmc pointer)
kdxcofbo: offset to beginning of free space within block
kdxcofeo: offset to the end of free space (ie. first portion of block containing index data)
kdxcoavs: available space in block (effectively area between the two fields above)
###########################################################################


row#0[8020] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 42 2a 94 00 3d
row#1[8008] flag: -----, lock: 0
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  02 42 2b 19 00 2d

............

row#483[1838] flag: -----, lock: 0
col 0; len 3; (3):  c2 06 27
col 1; len 6; (6):  02 42 2a 95 00 28
row#484[1825] flag: -----, lock: 0
col 0; len 3; (3):  c2 06 28
col 1; len 6; (6):  02 42 2a 95 00 29
----- end of leaf block dump -----

###########################################################################
結構說明:


row#0[8047] dba: 37890718=0x2422a9e(rowid)
col 0; len 3; (3):  c2 06 29
col 1; TERM

row#0[8020] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 42 2a 94 00 3d

col 0: 為keyvalue.
col 1: 為Rowid

關於rowid的換算：索引中的rowid由48個bit構成，前10個bit構成檔案號，中間22個bit構成塊號，最後16個bit 構成塊中的行號。
(根據rowid如何訪問資料?)

關於整數key值的換算

先去掉c2，因為c2只表示最高位的位置，後面才是真正資料，06轉換為十進位制為6，減去1為5，3b轉換為十進位制為48＋11=59，

減去1為 58，兩個結果合併到一起，為258，這就是其儲存的真正資料，簡單表示一下：

   1. c2 03 3b －> 03 3b
   2. 03 －> 0*16+3=3－>3-1=2
   3. 3b －> 3*16+14=59 －> 59-1=58
   4. 結果合併得到258

關於字串的換算：直接由十六進位制得到各個ascii碼，然後對應到相應字元。

這個換算沒有得到驗證???????
這種dump資訊要參考DSI

################################################################################################################


End dump data blocks tsn: 9 file#: 9 minblk 141981 maxblk 141981(結束第一個葉子節點塊資訊)
Start dump data blocks tsn: 9 file#: 9 minblk 141982 maxblk 141982(開始第二個葉子節點塊資訊)
buffer tsn: 9 rdba: 0x02422a9e (9/141982)
scn: 0x0273.bbd819fa seq: 0x01 flg: 0x04 tail: 0x19fa0601
frmt: 0x02 chkval: 0x1745 type: 0x06=trans data
Block header dump:  0x02422a9e
Object id on Block? Y
seg/obj: 0x8360  csc: 0x273.bbd819f7  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x2422a99 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 0x0273.bbd819f7

Leaf block dump
===============
header address 66832484=0x3fbc864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 479
kdxcofbo 994=0x3e2
kdxcofeo 1810=0x712
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 37890719=0x2422a9f -- 下個節點RBA地址
kdxleprv 37890717=0x2422a9d -- 上一個節點RBA地址.
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: -----, lock: 0
col 0; len 3; (3):  c2 06 29
col 1; len 6; (6):  02 42 2a 95 00 2a
row#1[8006] flag: -----, lock: 0
col 0; len 3; (3):  c2 06 2a
col 1; len 6; (6):  02 42 2b 17 00 13
row#2[7993] flag: -----, lock: 0
col 0; len 3; (3):  c2 06 2b
col 1; len 6; (6):  02 42 2b 17 00 14

...............
row#477[1823] flag: -----, lock: 0
col 0; len 3; (3):  c2 0b 1a
col 1; len 6; (6):  02 42 2b cf 00 13
row#478[1810] flag: -----, lock: 0
col 0; len 3; (3):  c2 0b 1b
col 1; len 6; (6):  02 42 2b 20 00 05
----- end of leaf block dump -----
End dump data blocks tsn: 9 file#: 9 minblk 141982 maxblk 141982(結束第二個葉子節點塊資訊)