使用treedump事件檢視索引結構
可以使用如下事件獲取索引結構:
alter session set events 'immediate trace name treedump level obj#';
測試如下:
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;
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
--------------------------------------------------------------------------------
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.
Session altered.
SQL> alter session set events 'immediate trace name treedump level 155084';
Session altered.
Session altered.
SQL> alter session set events 'immediate trace name treedump level 155085';
Session altered.
Session altered.
SQL> host pwd
/u01/app/oracle/product/10.2.0/db_1/admin/bnet/udump
/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
---------------- -------- --------- ---------- ----------- ------------- ----------------- ----------
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
-------------------- ----------- ------------ ---------- ----------
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
--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
----- 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;
2 dbms_utility.data_block_address_block(to_number('101012c', 'xxxxxxx')) bno
3 from dual;
FNO BNO
---------- ----------
4 65836
---------- ----------
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;
----- 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
---------- ----------
4 66988
==>可以觀察各塊地址瞭解分配順序:
0x10105ac-->root
0x10105ad-->leaf block in branch 1
...
0x10107d2-->leaf block in branch 1
0x10107d3-->leaf block in branch 2 --該塊無法在root維護,導致split
0x10107d4-->branch block in branch 1 --此時發生split
0x10107d5-->leaf block in branch 2
...
0x1010808-->leaf block in branch 2
0x10105ac-->root
0x10105ad-->leaf block in branch 1
...
0x10107d2-->leaf block in branch 1
0x10107d3-->leaf block in branch 2 --該塊無法在root維護,導致split
0x10107d4-->branch block in branch 1 --此時發生split
0x10107d5-->leaf block in branch 2
...
0x1010808-->leaf 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;
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;
---------- ----------
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
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;
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）
--------------------------------------------------------------------------------
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
---------- ----------
4 67244
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-754291/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 索引的INTERNAL 研究系列 之通過TREEDUMP檢視二叉樹索引的結構索引二叉樹
- treedump研究下oracle索引的結構Oracle索引
- Oracle Treedump命令分析索引結構內部資訊Oracle索引
- 語法檢視錶結構，索引mysql索引MySql
- [20141008]使用bbed檢視索引結構.txt索引
- 使用index_stats檢視檢視索引效率Index索引
- 物化檢視上使用bitmap索引索引
- Oracle 索引的使用情況檢視Oracle索引
- mysql 檢視索引MySql索引
- 【Oracle】如何查詢檢視時使用索引Oracle索引
- SQL Server 索引結構及其使用SQLServer索引
- Qt 5模型/檢視結構QT模型
- mongodb 如何檢視索引MongoDB索引
- DB2檢視索引的使用情況DB2索引
- MSSQL索引檢視（indexed view）之簡述及使用SQL索引IndexView
- 在列印視窗，列印檢視View的子檢視結構圖View
- SQL Server 索引結構及其使用（二）SQLServer索引
- SQL Server 索引結構及其使用（一）SQLServer索引
- Oracle常用檢視錶結構命令Oracle
- 檢視DB2表結構DB2
- 檢視sqlite中的表結構SQLite
- [zt] 聚集索引和非聚集索引(sql server索引結構及其使用)索引SQLServer
- Sql Server關於indexed view索引檢視的總結SQLServerIndexView索引
- 檢視當前使用者正在等待事件事件
- 結合載入檢視使用
- 轉載-treedump索引讀取索引儲存的資料值--非唯一性索引索引
- mysql建立索引和檢視MySql索引
- sySQL?Server索引結構的具體使用SQLServer索引
- SQL Server 索引結構及其使用（一）[轉]SQLServer索引
- SQL Server 索引結構及其使用（二）[轉]SQLServer索引
- SQL Server 索引結構及其使用（三、四）SQLServer索引
- SQL Server 索引結構及其使用（一、二）SQLServer索引
- gdb golang 檢視iface 內部結構Golang
- MySQL 索引結構MySql索引
- dump索引結構索引
- Mysql索引結構與索引原理MySql索引
- 檢視html元素繫結的事件與方法的利器HTML事件
- 事件檢視器事件ID部分說明事件