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(結束第二個葉子節點塊資訊)
更詳細的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(結束第二個葉子節點塊資訊)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10248702/viewspace-668006/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- treedump研究下oracle索引的結構Oracle索引
- 使用treedump事件檢視索引結構事件索引
- 索引內部結構探索索引
- B樹索引的內部結構索引
- 原創：oracle data block 內部結構分析OracleBloC
- 【REDO】Oracle redo內部結構Oracle Redo
- 看索引內部的總結索引
- 索引的INTERNAL 研究系列 之通過TREEDUMP檢視二叉樹索引的結構索引二叉樹
- oracle的B-tree索引結構分析Oracle索引
- dump Oracle資料庫的內部結構Oracle資料庫
- oracle一些內部結構知識Oracle
- LoadRunner內部結構
- undo 事物內部結構
- DATA BLOCK內部結構BloC
- Oracle記憶體結構(四)----如何獲得Oracle各記憶體段的內部資訊(轉)Oracle記憶體
- oracle資料檔案內部BLOCK結構詳解OracleBloC
- oracle index索引結構（一）OracleIndex索引
- Redis 內部資料結構Redis資料結構
- DATA BLOCK內部結構圖BloC
- MySQL-InnoDB內部結構MySql
- 見微知著——Redis字串內部結構原始碼分析Redis字串原始碼
- Kafak探究之路- 內部結構小結
- CLUSTER內部結構的總結(1)
- CLUSTER內部結構的總結(2)
- CLUSTER內部結構的總結(3)
- CLUSTER內部結構的總結(4)
- 關於ORACLE點陣圖索引內部淺論Oracle索引
- Redis 字串 內部資料結構Redis字串資料結構
- 【原創】MySQLProxy-內部結構MySql
- zt_undo 事物內部結構
- date型別的內部結構型別
- 深入理解Cassandra內部結構
- RMAN備份效率之-oracle block internal（block 內部結構分解）OracleBloC
- 對Oracle資料庫內部結構進行dump的方法Oracle資料庫
- 關於ORACLE組合索引內部儲存淺談Oracle索引
- 關於轉儲Oracle索引資訊的相關命令Oracle索引
- 分析索引快速獲取索引資訊索引
- FPGA內部硬體結構簡介FPGA