今天跟研发的同学讨论到了这个问题,index block split过程中涉及到的重新排序问题.
SQL> create table idx_split (a number, b varchar2(1446), c date);
Table created
SQL> create index idx_split_idx on idx_split (a, b) tablespace idx_2k pctfree 10;
Index created
begin
for i in 1..1000
loop
insert into tx_index_contention (a, b, c) values (i, lpad('A', 10, 'A'), sysdate);
end loop;
end;
/
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> SELECT height,blocks,lf_blks,br_blks,btree_space,used_space FROM INDEX_STATS WHERE NAME='IDX_SPLIT_IDX';
HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ----------- ----------
1 8 1 0 7996 2500
SQL> ANALYZE INDEX idx_split_idx VALIDATE STRUCTURE;
Index analyzed
SQL> SELECT height,blocks,lf_blks,br_blks,btree_space,used_space FROM INDEX_STATS WHERE NAME='IDX_SPLIT_IDX';
HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE USED_SPACE
---------- ---------- ---------- ------------------- ----------
2 8 4 1 40012 25924
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='IDX_SPLIT_IDX' AND OWNER='SYS';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 6 128 8插入1000条数据,leaf blocks=4 branch block=1
SQL> alter session set events '10224 trace name context forever,level 1';
Session altered
SQL> SELECT height,blocks,lf_blks,br_blks,btree_space,used_space FROM INDEX_STATS WHERE NAME='IDX_SPLIT_IDX' ;
HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ----------- ----------
2 8 4 1 40012 25924
SQL> insert into idx_split (a, b, c) values (1, lpad('A', 10, 'A'), sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> select sysdate from dual;
SYSDATE
-----------
2012/12/12------------------------------------------------------------ *** 2012-12-12 13:44:52.311 *** SESSION ID:(668.2355) 2012-12-12 13:44:52.311 *** CLIENT ID:() 2012-12-12 13:44:52.311 *** SERVICE NAME:(yhdtest) 2012-12-12 13:44:52.311 *** MODULE NAME:(PL/SQL Developer) 2012-12-12 13:44:52.311 *** ACTION NAME:(Command Window - New) 2012-12-12 13:44:52.311 splitting leaf,dba 0x01800087,time 13:44:52.310 kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01800086,time 13:44:52.311 kdisnew_bseg_srch_cbk rejecting block ,dba 0x01800086,time 13:44:52.311 kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01800085,time 13:44:52.311 kdisnew_bseg_srch_cbk rejecting block ,dba 0x01800085,time 13:44:52.311 kdisnew_bseg_srch_cbk using block,dba 0x0180008f,time 13:44:52.313 ---------------------------------------------------------------
leaf,dba 0x01800087 发生了分裂 (第一个leaf block)
dump 第一个branch block
SQL> alter system dump datafile 6 block 131;
System altered
----------------------------------------------------------------
Block header dump: 0x01800083
Object id on Block? Y
seg/obj: 0x12ef4 csc: 0x00.17756d itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000389 0x00c003a5.00a7.02 C--- 0 scn 0x0000.0017756d
Branch block dump
=================
header address 140678466630220=0x7ff24207f24c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 3
kdxcofbo 34=0x22
kdxcofeo 8029=0x1f5d
kdxcoavs 7995
kdxbrlmc 25165959=0x1800087 -----第一个leaf block
kdxbrsno 2
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 25165956=0x1800084 -----第二个
col 0; len 3; (3): c2 04 0d
col 1; TERM
row#1[8038] dba: 25165957=0x1800085 ..3
col 0; len 3; (3): c2 07 14
col 1; TERM
row#2[8029] dba: 25165958=0x1800086 ..4
col 0; len 3; (3): c2 0a 1b
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 131 maxblk 131
-------------------------------------------------------------------SQL> variable file# number
variable block# number
execute :file#:=dbms_utility.data_block_address_file(to_number('1800087','xxxxxxx'));
execute :block#:=dbms_utility.data_block_address_block(to_number('1800087','xxxxxxx'));SQL> SQL>
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> print file#;
FILE#
----------
6
SQL> print block#;
BLOCK#
----------
135
SQL> alter system dump datafile 6 block 135;
System altered
SQL> alter system dump datafile 6 block 132;
System altered
SQL> select display_raw('c2040d','NUMBER') from dual;
DISPLAY_RAW('C2040D','NUMBER')
----------------------------------------------------------------------------------------------------
312第二个leaf block 的min value = 312
Block header dump: 0x01800084
Object id on Block? Y
seg/obj: 0x12ef4 csc: 0x00.177610 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01e.00000389 0x00c003a7.00a7.02 --U- 1 fsc 0x0000.00177611
0x02 0x000a.006.00000756 0x00c00768.0172.19 C--- 0 scn 0x0000.00177571
Leaf block dump
===============
header address 140678466630244=0x7ff24207f264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x89: opcode=9: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 2
kdxconro 307
kdxcofbo 650=0x28a
kdxcofeo 667=0x29b
kdxcoavs 17
kdxlespl 0
kdxlende 0
kdxlenxt 25165957=0x1800085
kdxleprv 25165967=0x180008f
kdxledsz 0
kdxlebksz 8032
row#0[667] flag: ------, lock: 0, len=24
col 0; len 3; (3): c2 04 0d ------------>312
col 1; len 10; (10): 41 41 41 41 41 41 41 41 41 41
col 2; len 6; (6): 00 41 74 8a 00 30
row#1[691] flag: ------, lock: 0, len=24
.....
第一个leaf block 的range <312
Block header dump: 0x01800087
Object id on Block? Y
seg/obj: 0x12ef4 csc: 0x00.177576 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01e.00000389 0x00c003a6.00a7.01 -B– 1 fsc 0x0000.00000000
0x02 0x000a.010.00000758 0x00c00770.0172.28 –U- 1 fsc 0x0000.00177649
Leaf block dump
===============
header address 140678466630244=0x7ff24207f264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 3
kdxcosdc 2
kdxconro 163
kdxcofbo 362=0x16a
kdxcofeo 4221=0x107d
kdxcoavs 3859
kdxlespl 0
kdxlende 0
kdxlenxt 25165967=0x180008f
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4221] flag: ——, lock: 0, len=23
col 0; len 2; (2): c1 02
col 1; len 10; (10): 41 41 41 41 41 41 41 41 41 41
col 2; len 6; (6): 00 41 74 89 00 00
row#1[4244] flag: ——, lock: 2, len=23
col 0; len 2; (2): c1 02
col 1; len 10; (10): 41 41 41 41 41 41 41 41 41 41
……
row#0,row#1 的偏移量发生了重组,[4221]->[4244] 按照len=23递增 即物理address 发生了排序
row#0,row#1 被reorder为1,1,2,x 而row#1对应的rowid为max block address
SQL> SELECT height,blocks,lf_blks,br_blks,btree_space,used_space FROM INDEX_STATS WHERE NAME='IDX_SPLIT_IDX';
HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ----------- ----------
2 8 4 1 40012 25924
SQL> ANALYZE INDEX idx_split_idx VALIDATE STRUCTURE;
Index analyzed
SQL>
SQL> SELECT height,blocks,lf_blks,br_blks,btree_space,used_space FROM INDEX_STATS WHERE NAME='IDX_SPLIT_IDX'
2 ;
HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ----------- ----------
2 16 5 1 48008 25960leaf block 从4增加到5,第一个leaf block 发生了分裂,具体过程为：
1. Allocate new index block from index freelist
2. Redistribute block so the lower half (by volume) of index entries remain in current block and move the other half into the new block
3. Insert the new index entry into appropriate leaf block
4. Update the previously full block such that its “next leaf block pointer” (kdxlenxt) references the new block
5. Update the leaf block that was the right of the previously full block such that its “previous leaf block pointer”(kdxleprv) also points to the new block
6. Update the branch block that references the full block and add a new entry to point to the new leaf block (effectively the lowest value in the new leaf block)
7. –add this one , split leaf block will reorder by value this is a behavior like rebuild.
