玉面飞龙的BLOG

Below is one example that an empty index leaf block can’t be reused due to internal dead lock.

The scenario was found by my coworker Daniel. Please review script and index split trace file.

Script generate a 3-block b-tree index.  1 root block and 1 full leaf block and 1 empty leaf block on 8k block_size.

column file_id new_value v_file_id;
column block_id new_value v_block_id;
column object_id new_value v_object_id;

drop table test;

create table test (a int);
create index test_idx on test(a);

declare
i int;
begin
for i in 1..540
loop
insert into test values(i);
commit;
end loop;
end;
/

ANALYZE INDEX test_idx VALIDATE STRUCTURE;
SELECT * FROM index_stats where lower(name)=’test_idx’;

insert into test values(1000000);
commit;

delete test where a=1000000;
commit;

select object_id from DBA_objects where object_name=upper(’test_idx’);
alter session set events ‘immediate trace name treedump level &v_object_id’;

select K.header_file  file_id,K.header_block block_id from dba_segments k where k.segment_name=upper(’TEST_IDX’);
alter system dump datafile &v_file_id block &v_block_id;

See below index tree dump. 3 blocks. 1 root block(0×441fba2), 1 full leaf block ( 0×441fba3 ) ,1 empty leaf block(0×441fba4) after we delete “a=1000000″.

—– begin tree dump
branch: 0×441fba2 71433122 (0: nrow: 2, level: 1)
leaf: 0×441fba3 71433123 (-1: nrow: 540 rrow: 540)
leaf: 0×441fba4 71433124 (0: nrow: 1 rrow: 0)
—– end tree dump

See below empty leaf block,it’s in free-list:flg: O.

block header dump:  0×0441fba4
Object id on Block? Y
seg/obj: 0×466cd  csc: 0×33b.15410644  itc: 2  flg: O  typ: 2 - INDEX
fsl: 0  fnx: 0×0 ver: 0×01

See below index header block dump,below HWM,there is 3 blocks. 1 block(0×0441fba4) is on free-list. And this free-list only has 1 block (lhd: 0×0441fba4 ltl: 0×0441fba4).

Extent Control Header
—————————————————————–
Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 1279
last map  0×00000000  #maps: 0      offset: 4128
Highwater::  0×0441fba5  ext#: 0      blk#: 3 ext size: 1279
#blocks in seg. hdr’s freelists: 1
#blocks below: 3
mapblk  0×00000000  offset: 0
Unlocked
Map Header:: next  0×00000000  #extents: 1    obj#: 288461 flag: 0×40000000
Extent Map
—————————————————————–
0×0441fba2  length: 1279

nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 2
SEG LST:: flg: USED   lhd: 0×0441fba4 ltl: 0×0441fba4

So in concept, when we insert a new row to full leaf block 0×441fba3, a 50:50 split will happen and Oracle will re-use the empty index block 0×0441fba4.

let us see results. Event 10224 represent “ORA-10224: index block split/delete trace”.

alter session set events ‘10224 trace name context forever,level 1′;
insert into test values (541);
commit;
alter session set events ‘10224 trace name context off’;

Then from index tree dump and event dump file, we can see that a new block 0×441fba5 was involved.

Empty index block 0×0441fba4 failed to be re-used due to internal self deadlock.

Then we continue insert a few hundreds of rows,it will cause 50:50 block split and that empty leaf block still can’t be re-used.  (still being 2 level btree index)

/**********block 0×441fba4*****************/
—– begin tree dump
branch: 0×441fba2 71433122 (0: nrow: 3, level: 1)
leaf: 0×441fba3 71433123 (-1: nrow: 279 rrow: 279)
leaf: 0×441fba5 71433125 (0: nrow: 262 rrow: 262)
leaf: 0×441fba4 71433124 (1: nrow: 0 rrow: 0)
—– end tree dump
/**********index split event dump*****************/
*** SESSION ID:(18842.59029) 2009-07-29 20:32:34.747
splitting leaf,dba 0×0441fba3,time 20:32:34.747
kdisnew first,dba 0×0441fba4,time 20:32:34.749
kdisdelete would deadlock,dba 0×0441fba4,time 20:32:34.750
kdisnew failed to delete block,dba 0×0441fba4,time 20:32:34.750
kdisnew regetting block,dba 0×0441fba4,time 20:32:34.750
kdiswalked 1 times
kdisnew using block,dba 0×0441fba5,time 20:32:34.750
/**************************************/

Why does Oracle warn “deadlock”? Is it due to that splited block share same brach block with that empty leaf block? Just Guess.

Then I test on a 3-level b-tree index. If splited block and empty block doesn’t share same branch block, empty block would be re-used. And if splited block and empty index block has same branch block, empty block can’t be re-used and we can see self deadlock warnning during index block split.Just guess.

It’s a b-tree index specific algorithm for Oracle．