分享程序员开发的那些事...
更新时间：2022-09-13 11:02:50
[20150626]建立索引pctfree=0.txt
--自己测试看看来解答问题，不知道是否正确:-)
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table bowie (id number, name varchar2(42));
insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level commit;
--建立pctfree=0 的索引：
create index bowie_id_i on bowie(id) pctfree 0;
SCOTT@test> select object_id from dba_objects where owner=user and object_name='BOWIE_ID_I';
OBJECT_ID
----------
306580
alter session set events 'immediate trace name treedump level 306580';
----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 19, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 540 rrow: 540)
leaf: 0x100070d 16779021 (0: nrow: 533 rrow: 533)
leaf: 0x100070e 16779022 (1: nrow: 533 rrow: 533)
leaf: 0x100070f 16779023 (2: nrow: 533 rrow: 533)
leaf: 0x1000710 16779024 (3: nrow: 533 rrow: 533)
leaf: 0x1000711 16779025 (4: nrow: 533 rrow: 533)
leaf: 0x1000712 16779026 (5: nrow: 533 rrow: 533)
leaf: 0x1000713 16779027 (6: nrow: 533 rrow: 533)
leaf: 0x1000714 16779028 (7: nrow: 533 rrow: 533)
leaf: 0x1000715 16779029 (8: nrow: 533 rrow: 533)
leaf: 0x1000716 16779030 (9: nrow: 533 rrow: 533)
leaf: 0x1000717 16779031 (10: nrow: 533 rrow: 533)
leaf: 0x1000719 16779033 (11: nrow: 533 rrow: 533)
leaf: 0x100071a 16779034 (12: nrow: 533 rrow: 533)
leaf: 0x100071b 16779035 (13: nrow: 533 rrow: 533)
leaf: 0x100071c 16779036 (14: nrow: 533 rrow: 533)
leaf: 0x100071d 16779037 (15: nrow: 533 rrow: 533)
leaf: 0x100071e 16779038 (16: nrow: 533 rrow: 533)
leaf: 0x100071f 16779039 (17: nrow: 399 rrow: 399)
----- end tree dump
--说明：作者测试环境是12c,转储的信息有点不同。里面还包含剩余空间的大小。
--每块533条记录(大多数情况)。
2.删除数据看看：
--session 1:
SCOTT@test> delete bowie where id in (select rownum*499 from dual connect by level20 rows deleted.
--根据这个条件，可以看出机会每块索引叶子都有索引信息删除。看看dba=0x100070c的叶子的情况：
SCOTT@test> @dfb 100070c
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
4 1804 alter system dump datafile 4 block 1804 ;
--需要执行1次alter system checkpoint;这样脏块才能写盘。
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> alter system dump datafile 4 block 1804 ;
System altered.
Block header dump: 0x0100070c
Object id on Block? Y
seg/obj: 0x4ad94 csc: 0x02.f4bd366b itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000708 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000c.019.00003ba9 0x00c000ce.0fc4.18 ---- 1 fsc 0x000f.00000000
Leaf block dump
===============
header address 182923336292=0x2a97149a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1116=0x45c
kdxcoavs 0
kdxlespl 0
kdxlende 1
kdxlenxt 16779021=0x100070d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
....
row#498[1648] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 05 64
col 1; len 6; (6): 01 00 05 e5 00 94
row#499[1636] flag: ------, lock: 0, len=12
col 0; len 2; (2): c2 06
col 1; len 6; (6): 01 00 05 e5 00 95
...
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1804 maxblk 1804
--可以看到row#498 的lock=2,对应itl=2的事务。
3.打开新会话，执行delete操作:
--如果这个时候不提交，打开新的会话
--session 2:
SCOTT@test> delete bowie where id in (select rownum*500 from dual connect by level20 rows deleted.
SCOTT@test> alter system checkpoint;
System altered.
----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 37, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 278 rrow: 278)
leaf: 0x1000722 16779042 (0: nrow: 262 rrow: 260)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
leaf: 0x100070d 16779021 (1: nrow: 271 rrow: 271)
leaf: 0x1000723 16779043 (2: nrow: 262 rrow: 260)
leaf: 0x100070e 16779022 (3: nrow: 271 rrow: 271)
leaf: 0x1000724 16779044 (4: nrow: 262 rrow: 260)
leaf: 0x100070f 16779023 (5: nrow: 271 rrow: 271)
leaf: 0x1000725 16779045 (6: nrow: 262 rrow: 260)
leaf: 0x1000710 16779024 (7: nrow: 271 rrow: 271)
leaf: 0x1000726 16779046 (8: nrow: 262 rrow: 260)
leaf: 0x1000711 16779025 (9: nrow: 271 rrow: 271)
leaf: 0x1000727 16779047 (10: nrow: 262 rrow: 260)
leaf: 0x1000712 16779026 (11: nrow: 271 rrow: 271)
leaf: 0x1000720 16779040 (12: nrow: 262 rrow: 260)
leaf: 0x1000713 16779027 (13: nrow: 271 rrow: 269)
leaf: 0x1000721 16779041 (14: nrow: 262 rrow: 262)
leaf: 0x1000714 16779028 (15: nrow: 271 rrow: 269)
leaf: 0x100072f 16779055 (16: nrow: 262 rrow: 262)
leaf: 0x1000715 16779029 (17: nrow: 271 rrow: 269)
leaf: 0x1000729 16779049 (18: nrow: 262 rrow: 262)
leaf: 0x1000716 16779030 (19: nrow: 271 rrow: 269)
leaf: 0x100072a 16779050 (20: nrow: 262 rrow: 262)
leaf: 0x1000717 16779031 (21: nrow: 271 rrow: 269)
leaf: 0x100072e 16779054 (22: nrow: 262 rrow: 262)
leaf: 0x1000719 16779033 (23: nrow: 271 rrow: 269)
leaf: 0x100072b 16779051 (24: nrow: 262 rrow: 262)
leaf: 0x100071a 16779034 (25: nrow: 271 rrow: 269)
leaf: 0x100072c 16779052 (26: nrow: 262 rrow: 262)
leaf: 0x100071b 16779035 (27: nrow: 271 rrow: 269)
leaf: 0x100072d 16779053 (28: nrow: 262 rrow: 260)
leaf: 0x100071c 16779036 (29: nrow: 271 rrow: 271)
leaf: 0x1000732 16779058 (30: nrow: 262 rrow: 260)
leaf: 0x100071d 16779037 (31: nrow: 264 rrow: 264)
leaf: 0x1000733 16779059 (32: nrow: 269 rrow: 267)
leaf: 0x100071e 16779038 (33: nrow: 271 rrow: 271)
leaf: 0x1000734 16779060 (34: nrow: 262 rrow: 260)
leaf: 0x100071f 16779039 (35: nrow: 399 rrow: 397)
----- end tree dump
----索引发生了分裂。注意看~的信息，nrow: 262 rrow: 260，说明删除的信息在这块里面。
SCOTT@test> @dfb16 0x1000722
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
4 1826 alter system dump datafile 4 block 1826 ;
SCOTT@test> alter system dump datafile 4 block 1826 ;
System altered.
Block header dump: 0x01000722
Object id on Block? Y
seg/obj: 0x4ad94 csc: 0x02.f4bd3bc8 itc: 3 flg: E typ: 2 - INDEX
brn: 1 bdba: 0x1000718 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.001.0000d575 0x00c00aad.35a8.01 CB-- 0 scn 0x0002.f4bd3bc7
0x02 0x000c.019.00003ba9 0x00c000ce.0fc4.18 ---- 1 fsc 0x000f.00000000
0x03 0x0005.00c.0000d571 0x00c00aa9.35a8.0b ---- 1 fsc 0x000e.00000000
Leaf block dump
===============
header address 182923336316=0x2a97149a7c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 262
kdxcofbo 560=0x230
kdxcofeo 4605=0x11fd
kdxcoavs 4045
kdxlespl 0
kdxlende 2
kdxlenxt 16779021=0x100070d
kdxleprv 16779020=0x100070c
kdxledsz 0
kdxlebksz 8008
....
row#220[7463] flag: ---DS-, lock: 2, len=13
col 0; len 3; (3): c2 05 64
col 1; len 6; (6): 01 00 05 e5 00 94
row#221[7476] flag: ---D--, lock: 3, len=12
col 0; len 2; (2): c2 06
col 1; len 6; (6): 01 00 05 e5 00 95
....
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1826 maxblk 1826
SCOTT@test> @xid
X
------------------------------
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
------ ------- ------ ---------- ---------- ---------- ---------- ------ ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
5 12 54641 3 2729 30 13736 ACTIVE 2 40 05000C0071D50000 00000000BA497588 2015-06-26 10:09:54 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU5_2973757209$' XID 5 12 54641;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU5_2973757209$';
12 25 15273 3 206 42 4036 ACTIVE 1 39 0C001900A93B0000 00000000BA559658 2015-06-26 10:02:58 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU12_1585900997$' XID 12 25 15273;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU12_1585900997$';
--说明在第2个session删除记录时，要增加1个ITL，增加24字节，由于空间不足，索引块发生了50:50分裂，由于几乎每个索引块都发生
--这种分裂，导致索引变大1倍。
--如果你仔细看最后1个叶子节点：leaf: 0x100071f 16779039 (35: nrow: 399 rrow: 397),空间足够，没有发生分裂。
SCOTT@test> set verify off
SCOTT@test> @dfb16 0x100071f
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
4 1823 alter system dump datafile 4 block 1823 ;
SCOTT@test> alter system dump datafile 4 block 1823 ;
System altered.
Block header dump: 0x0100071f
Object id on Block? Y
seg/obj: 0x4ad94 csc: 0x02.f4bd3c11 itc: 3 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000718 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.000.0000d571 0x00c00acf.35a8.02 C--- 0 scn 0x0002.f4bd3c10
0x02 0x000c.019.00003ba9 0x00c000ce.0fc4.2a ---- 1 fsc 0x000f.00000000
0x03 0x0005.00c.0000d571 0x00c00aa9.35a8.1e ---- 1 fsc 0x000e.00000000
Leaf block dump
===============
header address 182923336316=0x2a97149a7c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 399
kdxcofbo 834=0x342
kdxcofeo 2825=0xb09
kdxcoavs 1991
kdxlespl 0
kdxlende 2
kdxlenxt 0=0x0
kdxleprv 16779060=0x1000734
kdxledsz 0
kdxlebksz 8008
--可以发现空间足够，没有分裂。也许会问为什么不用itl=0x01槽，实际上作为索引分裂使用而保留下来的。而第2个会话执行的是
--delete,itl=0x02已经占用，只能请求itl=0x03,而空间不足，导致索引分裂。
4.如果保留pctfree=1这个问题就可以避免：
--drop table bowie purge;
create table bowie (id number, name varchar2(42));
insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level commit;
create index bowie_id_i on bowie(id) pctfree 1;
select object_id from dba_objects where object_name='BOWIE_ID_I';
--session 1:
delete bowie where id in (select rownum*499 from dual connect by level
--session 2:
delete bowie where id in (select rownum*500 from dual connect by level
SCOTT@test> alter session set events 'immediate trace name treedump level 306583';
Session altered.
----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 19, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 534 rrow: 532)
leaf: 0x100070d 16779021 (0: nrow: 528 rrow: 526)
leaf: 0x100070e 16779022 (1: nrow: 528 rrow: 526)
leaf: 0x100070f 16779023 (2: nrow: 528 rrow: 526)
leaf: 0x1000710 16779024 (3: nrow: 528 rrow: 526)
leaf: 0x1000711 16779025 (4: nrow: 528 rrow: 526)
leaf: 0x1000712 16779026 (5: nrow: 528 rrow: 526)
leaf: 0x1000713 16779027 (6: nrow: 528 rrow: 526)
leaf: 0x1000714 16779028 (7: nrow: 528 rrow: 526)
leaf: 0x1000715 16779029 (8: nrow: 528 rrow: 526)
leaf: 0x1000716 16779030 (9: nrow: 528 rrow: 526)
leaf: 0x1000717 16779031 (10: nrow: 528 rrow: 526)
leaf: 0x1000719 16779033 (11: nrow: 528 rrow: 526)
leaf: 0x100071a 16779034 (12: nrow: 528 rrow: 526)
leaf: 0x100071b 16779035 (13: nrow: 528 rrow: 526)
leaf: 0x100071c 16779036 (14: nrow: 528 rrow: 526)
leaf: 0x100071d 16779037 (15: nrow: 528 rrow: 525)
leaf: 0x100071e 16779038 (16: nrow: 528 rrow: 525)
leaf: 0x100071f 16779039 (17: nrow: 490 rrow: 488)
----- end tree dump
--这样就不存在分裂了。
总结：
--看来重建索引不要太贪心，选择pctfree=0的索引，当然如果你的dml不存在删除修改索引字段的操作,而索引字段是线性增加的类型，
--可以考虑pctfree=0，不过即使这样***也保留1,避免当天建立索引很小，没几天变大很快的情况。