ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Treedump & block dump来认识索引

Treedump & block dump来认识索引

原创 Linux操作系统 作者:tolywang 时间:2007-07-11 00:00:00 0 删除 编辑
创建测试用表:
select dbms_metadata.get_ddl('TABLE','T_INDEX_1') from dual;
CREATE TABLE "MVREP"."T_INDEX_1"
( "C1" CHAR(2000),
"C2" CHAR(2000),
CONSTRAINT "PK_T_INDEX_1" PRIMARY KEY ("C1")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
由于主键字段"C1"类型为CHAR(2000),在8k的数据块中,每个块只能容纳3个索引项。这里是为了使研究简化。

索引的object_id,注意这里是用object_id,不是data_object_id,后者在非空情况下truncate以后会改变,前者不变。
SQL> select object_id from user_objects where object_name='PK_T_INDEX_1';
OBJECT_ID
----------
53789
1 row selected.
这里object_id在treedump时会要求指定。
在没有任何索引项的情况下,看一下treedump的情况:
alter session set events 'immediate trace name treedump level 53789';
*** 2007-04-02 10:50:16.227
----- begin tree dump
leaf: 0x1052494 17114260 (0: nrow: 0 rrow: 0)
----- end tree dump
可以使用dbms_utility来得到相应的文件和块号,比如:
SQL> select dbms_utility.data_block_address_file(&rba),
2 dbms_utility.data_block_address_block(&rba)
3 from dual;
Enter value for rba: 17114260
old 1: select dbms_utility.data_block_address_file(&rba),
new 1: select dbms_utility.data_block_address_file(17114260),
Enter value for rba: 17114260
old 2: dbms_utility.data_block_address_block(&rba)
new 2: dbms_utility.data_block_address_block(17114260)
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17114260)
----------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17114260)
-----------------------------------------------
4
337044
SQL> insert into t_index_1 values('&c1','&c2');
Enter value for c1: a
Enter value for c2: a
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('a','a')
1 row created.
没有提交,在另一个session 2里,执行:
SQL> r
1* alter session set events 'immediate trace name treedump level 53789'
Session altered.
*** 2007-04-02 11:01:46.081
----- begin tree dump
leaf: 0x1052494 17114260 (0: nrow: 1 rrow: 1)
----- end tree dump
可以看到nrow和rrow都增加为1。这个还有一点要说明,treedump更像是当前读而不是一致读。
SQL> r
1* insert into t_index_1 values('&c1','&c2')
Enter value for c1: b
Enter value for c2: b
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('b','b')
1 row created.
SQL> r
1* insert into t_index_1 values('&c1','&c2')
Enter value for c1: c
Enter value for c2: c
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('c','c')
1 row created.
SQL> r
1* alter session set events 'immediate trace name treedump level 53789'
Session altered.
----- begin tree dump
leaf: 0x1052494 17114260 (0: nrow: 3 rrow: 3)
----- end tree dump
现在已经有3条记录在索引里面,按照预期,再增加记录会将生成其它的leaf块。
SQL> r
1* insert into t_index_1 values('&c1','&c2')
Enter value for c1: d
Enter value for c2: d
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('d','d')
1 row created.
SQL> r
1* alter session set events 'immediate trace name treedump level 53789'
Session altered.
----- begin tree dump
branch: 0x1052494 17114260 (0: nrow: 2, level: 1)
leaf: 0x1052497 17114263 (-1: nrow: 3 rrow: 3)
leaf: 0x1052498 17114264 (0: nrow: 1 rrow: 1)
----- end tree dump
原来的rba17114260成为branch,另外分配两个块来作为leaf。
下面insert几条比'a'小的记录看看:
SQL> r
1* select chr(97-1) from dual
C
-
`
SQL> c /1/2
1* select chr(97-2) from dual
SQL> r
1* select chr(97-2) from dual
C
-
_
SQL> c /2/3
1* select chr(97-3) from dual
SQL> r
1* select chr(97-3) from dual
C
-
^
SQL>
SQL> r
1* insert into t_index_1 values('&c1','&c2')
Enter value for c1: `
Enter value for c2: `
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('`','`')
1 row created.
----- begin tree dump
branch: 0x1052494 17114260 (0: nrow: 3, level: 1)
leaf: 0x1052497 17114263 (-1: nrow: 3 rrow: 3)
leaf: 0x1052495 17114261 (0: nrow: 1 rrow: 1)
leaf: 0x1052498 17114264 (1: nrow: 1 rrow: 1)
----- end tree dump
在中间挤入了17114261,似乎可以推断'`'进入了17114263,而'c'进入了17114261。
SQL> r
1* insert into t_index_1 values('&c1','&c2')
Enter value for c1: _
Enter value for c2: _
old 1: insert into t_index_1 values('&c1','&c2')
new 1: insert into t_index_1 values('_','_')
1 row created.
----- begin tree dump
branch: 0x1052494 17114260 (0: nrow: 4, level: 1)
leaf: 0x1052497 17114263 (-1: nrow: 3 rrow: 3)
leaf: 0x1052496 17114262 (0: nrow: 1 rrow: 1)
leaf: 0x1052495 17114261 (1: nrow: 1 rrow: 1)
leaf: 0x1052498 17114264 (2: nrow: 1 rrow: 1)
----- end tree dump
似乎可以推断'_'挤入了17114263,挤出了'b',而'c'又小于17114261中的最小值'c',然后又不得不构筑其它的一个17114262 leaf块用于存储'b',这样下去会不断的从第一个leaf块17114263中挤出一个最大的项,然后再申请一个leaf块用于存储这个被挤出的项。如果这种推理成立,那么就会有可能在主键索引的情况下出现大量的只有一条记录的索引块,这种极端的方式应该是很可怕的。
要验证上面的猜想,只要dump相应的索引块就可以看到端倪。(其实也不用dump了。)
17114263对应的file:4,block:337047
17114262对应的file:4,block:337046
17114261对应的file:4,block:337045
17114264对应的file:4,block:337048
如果猜想正确,file:4,block:337047中存储的是'_','`','a' 对应的ASCII码95->97,16进制表示为'5f','60','61'。后面的块依次类推。
SQL> alter system dump datafile 4 block 337047;
System altered.
row#0[2002] flag: ------, lock: 2, len=2010, data:(6): 01 c0 04 d3 00 00
col 0; len 2000; (2000):
5f 20 ......(省略大量20)
row#1[4012] flag: ----S-, lock: 2, len=2010, data:(6): 01 05 24 8e 00 00
col 0; len 2000; (2000):
60 20 ......(省略大量20)
row#2[6022] flag: ----S-, lock: 2, len=2010, data:(6): 01 05 24 8f 00 00
col 0; len 2000; (2000):
61 20 ......(省略大量20)
和想的一样。
备注:其中20代表空格' ',因为char后面用' '补齐。
SQL> r
1* alter system dump datafile 4 block 337046
row#0[6022] flag: ----S-, lock: 2, len=2010, data:(6): 01 05 24 90 00 00
col 0; len 2000; (2000):
62 20 ......(省略大量20)
为了进一步强化这样的猜想的可能性,写一个循环来逐步将前值来插入table,即每一次插入的记录是前面插入的ASCII小一个的值,一直下去,看是否会生成大量的单个记录的leaf块。
SQL> declare
2 n integer;
3 begin
4 n:=94;
5 while n>=35 loop
6 insert into t_index_1 values(chr(n),chr(n));
7 n:=n-1;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
----- begin tree dump
branch: 0x1052494 17114260 (0: nrow: 64, level: 1)
leaf: 0x1052497 17114263 (-1: nrow: 3 rrow: 3)
leaf: 0x1052d3d 17116477 (0: nrow: 1 rrow: 1)
leaf: 0x1052d3c 17116476 (1: nrow: 1 rrow: 1)
leaf: 0x1052d3b 17116475 (2: nrow: 1 rrow: 1)
leaf: 0x1052d3a 17116474 (3: nrow: 1 rrow: 1)
leaf: 0x1052d40 17116480 (4: nrow: 1 rrow: 1)
leaf: 0x1052d3f 17116479 (5: nrow: 1 rrow: 1)
leaf: 0x1052d3e 17116478 (6: nrow: 1 rrow: 1)
leaf: 0x1052d2a 17116458 (7: nrow: 1 rrow: 1)
leaf: 0x1052d29 17116457 (8: nrow: 1 rrow: 1)
leaf: 0x1052d30 17116464 (9: nrow: 1 rrow: 1)
leaf: 0x1052d2f 17116463 (10: nrow: 1 rrow: 1)
leaf: 0x1052d2e 17116462 (11: nrow: 1 rrow: 1)
leaf: 0x1052d2d 17116461 (12: nrow: 1 rrow: 1)
leaf: 0x1052d2c 17116460 (13: nrow: 1 rrow: 1)
leaf: 0x1052d2b 17116459 (14: nrow: 1 rrow: 1)
leaf: 0x1052d1d 17116445 (15: nrow: 1 rrow: 1)
leaf: 0x1052d1c 17116444 (16: nrow: 1 rrow: 1)
leaf: 0x1052d1b 17116443 (17: nrow: 1 rrow: 1)
leaf: 0x1052d1a 17116442 (18: nrow: 1 rrow: 1)
leaf: 0x1052d20 17116448 (19: nrow: 1 rrow: 1)
leaf: 0x1052d1f 17116447 (20: nrow: 1 rrow: 1)
leaf: 0x1052d1e 17116446 (21: nrow: 1 rrow: 1)
leaf: 0x1052d0a 17116426 (22: nrow: 1 rrow: 1)
leaf: 0x1052d09 17116425 (23: nrow: 1 rrow: 1)
leaf: 0x1052d10 17116432 (24: nrow: 1 rrow: 1)
leaf: 0x1052d0f 17116431 (25: nrow: 1 rrow: 1)
leaf: 0x1052d0e 17116430 (26: nrow: 1 rrow: 1)
leaf: 0x1052d0d 17116429 (27: nrow: 1 rrow: 1)
leaf: 0x1052d0c 17116428 (28: nrow: 1 rrow: 1)
leaf: 0x1052d0b 17116427 (29: nrow: 1 rrow: 1)
leaf: 0x1c004f5 29361397 (30: nrow: 1 rrow: 1)
leaf: 0x1c004f4 29361396 (31: nrow: 1 rrow: 1)
leaf: 0x1c004f3 29361395 (32: nrow: 1 rrow: 1)
leaf: 0x1c004f2 29361394 (33: nrow: 1 rrow: 1)
leaf: 0x1c004f8 29361400 (34: nrow: 1 rrow: 1)
leaf: 0x1c004f7 29361399 (35: nrow: 1 rrow: 1)
leaf: 0x1c004f6 29361398 (36: nrow: 1 rrow: 1)
leaf: 0x1c004ea 29361386 (37: nrow: 1 rrow: 1)
leaf: 0x1c004e9 29361385 (38: nrow: 1 rrow: 1)
leaf: 0x1c004f0 29361392 (39: nrow: 1 rrow: 1)
leaf: 0x1c004ef 29361391 (40: nrow: 1 rrow: 1)
leaf: 0x1c004ee 29361390 (41: nrow: 1 rrow: 1)
leaf: 0x1c004ed 29361389 (42: nrow: 1 rrow: 1)
leaf: 0x1c004ec 29361388 (43: nrow: 1 rrow: 1)
leaf: 0x1c004eb 29361387 (44: nrow: 1 rrow: 1)
leaf: 0x10524a5 17114277 (45: nrow: 1 rrow: 1)
leaf: 0x10524a4 17114276 (46: nrow: 1 rrow: 1)
leaf: 0x10524a3 17114275 (47: nrow: 1 rrow: 1)
leaf: 0x10524a2 17114274 (48: nrow: 1 rrow: 1)
leaf: 0x10524a8 17114280 (49: nrow: 1 rrow: 1)
leaf: 0x10524a7 17114279 (50: nrow: 1 rrow: 1)
leaf: 0x10524a6 17114278 (51: nrow: 1 rrow: 1)
leaf: 0x1c004da 29361370 (52: nrow: 1 rrow: 1)
leaf: 0x1c004d9 29361369 (53: nrow: 1 rrow: 1)
leaf: 0x1c004e0 29361376 (54: nrow: 1 rrow: 1)
leaf: 0x1c004df 29361375 (55: nrow: 1 rrow: 1)
leaf: 0x1c004de 29361374 (56: nrow: 1 rrow: 1)
leaf: 0x1c004dd 29361373 (57: nrow: 1 rrow: 1)
leaf: 0x1c004dc 29361372 (58: nrow: 1 rrow: 1)
leaf: 0x1c004db 29361371 (59: nrow: 1 rrow: 1)
leaf: 0x1052496 17114262 (60: nrow: 1 rrow: 1)
leaf: 0x1052495 17114261 (61: nrow: 1 rrow: 1)
leaf: 0x1052498 17114264 (62: nrow: 1 rrow: 1)
----- end tree dump
这时,在leaf: 0x1052497 17114263 (-1: nrow: 3 rrow: 3)
和leaf: 0x1052496 17114262 (60: nrow: 1 rrow: 1)之间产生了大量只有一行的leaf。
这里可以发现,在表中插入一个比表中所有记录都小的值时,会进入第一个leaf,同时由于空间的不足,挤出第一个leaf中的最大值,而这个值又比写一个leaf中的最小值要小,所以没有办法进入下一个leaf,只能重新构造出一个leaf来存储这个记录。
而从这个实验来说,如果一个表的主键的插入不是递增的话,也即是说如果插入的时候也有往小值方向走的话,那么这棵二叉树是有可能是不平衡的,也就是说有些leaf上只有很少的记录,甚至只有一条记录,而其它的leaf上是满的。具体的分析略。
在这种情况下rebuild index可以改善这种情况。
备注:无论是rebuild或者rebuild online都可以,rebuild是使用INDEX FAST FULL SCAN,而rebuild online是使用FULL TABLE SCAN,只是读取的源不同罢了,其实还是都要排序的。如果是效率的话,通常是rebuild快一点;而如果要保证系统的高可用性rebuild online更好一些。
降序索引呢,有点怀疑?!
目前可以得出这样的现象分析。如果要进一步的分析原因,应该要dump相应的branch块来分析原理。

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

上一篇: 中国现状
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13133319