[20131017]索引变大的问题.txt
昨天看别人的服务器，对方提示有一个表的索引很大，提议是否可以shrink减少大小，我仔细看发现这个表发现索引是一个sequence，也就
是线性增加的，但是看块里面的信息确实很空。突然想起Jonathan Lewis博客提到的情况，在并发大量插入的情况下，索引的ITL会占用
很多在分裂后依旧保持原有的数量，这样导致索引许多空间利用不上。
自己在做一些测试看看是否能模拟这个问题。
1.测试环境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t (id number,cr_date date,pad varchar2(20));
Table created.
SCOTT@test> create unique index i_t_id on t(id);
Index created.
create sequence scott.seq1 start with 1 increment by 1 minvalue 1 maxvalue 999999999999999999999999 cache 2000 nocycle order ;
2.建立文件ins.sql包含如下内容：
insert into t values (seq1.nextval,sysdate,'test');exec dbms_lock.sleep(0.03);commit ;
复制1999行，在使用vim编辑如下：(^M => ctrl+v ctrl+m)
:%s/;/;^M/g
--最后加入quit。
建立一个shell脚本执行如下：
#! /bin/bash
for i in $(seq 20); do
sqlplus scott/xxxx @ins.sql &
done
3.测试：
打开新的回话执行：
SQL> exec dbms_workload_repository.create_snapshot();
SYS@test> select * from v$sysstat where name like '%splits%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ------------- ----------
412 leaf node splits 128 8106 1417124053
413 leaf node 90-10 splits 128 2627 1285138251
414 branch node splits 128 208 399027615
415 root node splits 128 8 708722985
430 queue splits 128 0 1168925905
--执行脚本后，等待全部结束:
$ . ins.sh
SQL> exec dbms_workload_repository.create_snapshot();
SYS@test> select * from v$sysstat where name like '%splits%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ------------- ----------
412 leaf node splits 128 8344 1417124053
413 leaf node 90-10 splits 128 2766 1285138251
414 branch node splits 128 208 399027615
415 root node splits 128 8 708722985
430 queue splits 128 0 1168925905
--8344-8106=238
--2766-2627=139
--可以发现即使是插入线性增加的记录，索引分裂并没有90-10分裂。依旧存在一部分50-50分裂。
4.做一个转储看看:
SCOTT@test> select object_name,object_id,data_object_id from dba_objects where object_name='I_T_ID' and wner=user;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
I_T_ID 275000 275000
SYS@test> alter session set events 'immediate trace name treedump level 275000';
Session altered.
----- begin tree dump
branch: 0x1000213 16777747 (0: nrow: 194, level: 1)
leaf: 0x1000215 16777749 (-1: nrow: 281 rrow: 281)
leaf: 0x1000214 16777748 (0: nrow: 266 rrow: 266)
leaf: 0x1000216 16777750 (1: nrow: 274 rrow: 274)
leaf: 0x100022d 16777773 (2: nrow: 267 rrow: 267)
leaf: 0x1000217 16777751 (3: nrow: 274 rrow: 274)
leaf: 0x100022e 16777774 (4: nrow: 539 rrow: 539)
leaf: 0x100022a 16777770 (5: nrow: 523 rrow: 523)
leaf: 0x100022b 16777771 (6: nrow: 267 rrow: 267)
leaf: 0x1000229 16777769 (7: nrow: 258 rrow: 258)
leaf: 0x100022f 16777775 (8: nrow: 267 rrow: 267)
leaf: 0x1000228 16777768 (9: nrow: 259 rrow: 259)
...
leaf: 0x1000372 16778098 (180: nrow: 130 rrow: 130)
leaf: 0x1000345 16778053 (181: nrow: 138 rrow: 138)
leaf: 0x1000337 16778039 (182: nrow: 129 rrow: 129)
leaf: 0x1000349 16778057 (183: nrow: 266 rrow: 266)
leaf: 0x100034d 16778061 (184: nrow: 266 rrow: 266)
leaf: 0x100033b 16778043 (185: nrow: 266 rrow: 266)
leaf: 0x1000368 16778088 (186: nrow: 266 rrow: 266)
leaf: 0x1000351 16778065 (187: nrow: 266 rrow: 266)
leaf: 0x100037a 16778106 (188: nrow: 138 rrow: 138)
leaf: 0x1000355 16778069 (189: nrow: 138 rrow: 138)
leaf: 0x1000319 16778009 (190: nrow: 131 rrow: 131)
leaf: 0x100033f 16778047 (191: nrow: 266 rrow: 266)
leaf: 0x1000359 16778073 (192: nrow: 142 rrow: 142)
----- end tree dump
可以发现后面一些块保存的键值很少。
SYS@test> @dfb 1000372
old 2: dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
new 2: dbms_utility.data_block_address_file(to_number('1000372','xxxxxxxxxxxxxxxx')) rfile#,
old 3: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
new 3: dbms_utility.data_block_address_block(to_number('1000372','xxxxxxxxxxxxxxxx')) block#
RFILE# BLOCK#
---------- ----------
4 882
old 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
new 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('1000372','xxxxxxxxxxxxxxxx'))||' block '||
old 2: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
new 2: dbms_utility.data_block_address_block(to_number('1000372','xxxxxxxxxxxxxxxx')) ||' ;' text
TEXT
--------------------------------------------------------------------------------------------------------------------
alter system dump datafile 4 block 882 ;
SQL> alter system dump datafile 4 block 882 ;
Block header dump: 0x01000372
Object id on Block? Y
seg/obj: 0x43238 csc: 0x00.c16bf3b9 itc: 169 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000301 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.002.000001f9 0x00c03989.0179.01 CB-- 0 scn 0x0000.c16bf3b7
0x02 0x0003.019.0000337f 0x00c0315f.16c9.38 --U- 1 fsc 0x0000.c16bf3c9
0x03 0x0002.003.000032b5 0x00c03789.17af.23 --U- 1 fsc 0x0000.c16bf3ce
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x10 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x11 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x12 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x13 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x14 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x15 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x16 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x17 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x18 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x19 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
....
0x96 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x97 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x98 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x99 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x9f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa0 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa1 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa2 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa3 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa4 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa5 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa6 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa7 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa9 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
SYS@test> @16to10 a9
16 to 10 DEC
------------
169
--通过bbed观察也能说明问题:
BBED> set dba 4,882
DBA 0x01000372 (16778098 4,882)
BBED> map /v
File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
Block: 882 Dba:0x01000372
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 4080 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[169], 4056 bytes @44
struct kdxle, 32 bytes @4108
struct kdxlexco, 16 bytes @4108
sb2 kdxlespl @4124
sb2 kdxlende @4126
ub4 kdxlenxt @4128
ub4 kdxleprv @4132
ub1 kdxledsz @4136
ub1 kdxleflg @4137
sb2 kd_off[130] @4140
ub1 freespace[2039] @4400
ub1 rowdata[1689] @6439
ub4 tailchk @8188
-- struct ktbbhitl[169], 4056 bytes @44
-- 说明索引itl槽非常多。每个占用24字节 24*169=4056,占一半空间。