Test case of Event 10224 - Dump Index Block Splits / Deletes
alter system set events '10224 trace name context forever, level 2'
create tablespace work datafile 'C:\ORACLE\ORADATA\DEMO\WORK.DBF' size 20M;
create table test ( x number ) tablespace work;
create unique index x_ind on test (x) tablespace work;
analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/
begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10224 trace name context forever,level 2
Statement processed.
SQL> begin
for i in 1..100 2 0 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
Index analyzed.
column name format a15
column blocks headingSQL> "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 32 1 16351
SQL> SQL>
SQL> alter index x_ind rebuild;
Index altered.
SQL> analyze index x_ind validate structure;
Index analyzed.
SQL> column name format a15
column blocks hSQL> eading "ALLOCATED|BLOCKS"
colSQL> umn lf_blks heading "LEAF|BLOCKS"
column br_blks headiSQL> ng "BRANCH|BLOCKS"
column SQL> Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> delete from test where x <500;
489 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from test;
511 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
Index analyzed.
SQL> column name format a15
cSQL> olumn blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty headiSQL> SQL> SQL> ng "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
SQL>
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> begin
for i in 1..1000 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
SQL> column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lfblks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> delete from test;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blSks heading "BRANCH|BLOCKS"
cSQL> olumn Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365
SQL> delete from test;
10000 rows deleted.
SQL> commit;
Commit complete.
############# I have deleted the rows but as we can see blocks are there .########################
Actually FREE BLOCKS are added to free list but not linked to index structure.Rebuilding the index will be linked.
kdimod adding block to free list,dba 0x21c0c018,time 16:46:33.868
kdimod adding block to free list,dba 0x21c0c019,time 16:46:33.877
kdimod adding block to free list,dba 0x21c0c014,time 16:46:34.86
kdimod adding block to free list,dba 0x21c0c015,time 16:46:34.153
kdimod adding block to free list,dba 0x21c0c013,time 16:46:34.277
kdimod adding block to free list,dba 0x21c0c011,time 16:46:34.352
kdimod adding block to free list,dba 0x21c0c012,time 16:46:34.355
kdimod adding block to free list,dba 0x21c0c017,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c016,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c01b,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c01a,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c00e,time 16:46:34.669
kdimod adding block to free list,dba 0x21c0c00b,time 16:46:34.733
kdimod adding block to free list,dba 0x21c0c00d,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00c,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00f,time 16:46:34.848
kdimod adding block to free list,dba 0x21c0c010,time 16:46:34.911
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365
SQL>
SQL> alter index x_ind rebuild;
Index altered.
SQL> analyze index x_ind validate structure;
column name forma
Index analyzed.
SQL> t a15
column bloSQL> cks heading "ALLOCATED|BLOCKS"
column lfSQL> _blks heading "LEAF|BLOCKS"
SQL> column br_blks heading "BRANCH|BLOCKS"
columSQL> n Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 1 0 16383
SQL>
create tablespace work datafile 'C:\ORACLE\ORADATA\DEMO\WORK.DBF' size 20M;
create table test ( x number ) tablespace work;
create unique index x_ind on test (x) tablespace work;
analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/
begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10224 trace name context forever,level 2
Statement processed.
SQL> begin
for i in 1..100 2 0 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
Index analyzed.
column name format a15
column blocks headingSQL> "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 32 1 16351
SQL> SQL>
SQL> alter index x_ind rebuild;
Index altered.
SQL> analyze index x_ind validate structure;
Index analyzed.
SQL> column name format a15
column blocks hSQL> eading "ALLOCATED|BLOCKS"
colSQL> umn lf_blks heading "LEAF|BLOCKS"
column br_blks headiSQL> ng "BRANCH|BLOCKS"
column SQL> Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> delete from test where x <500;
489 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from test;
511 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
Index analyzed.
SQL> column name format a15
cSQL> olumn blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty headiSQL> SQL> SQL> ng "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
SQL>
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> begin
for i in 1..1000 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
for i in 1..10 loop
update test set x=(x +1);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
SQL> column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lfblks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 2 1 16381
SQL> delete from test;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> SQL> commit;
Commit complete.
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blSks heading "BRANCH|BLOCKS"
cSQL> olumn Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365
SQL> delete from test;
10000 rows deleted.
SQL> commit;
Commit complete.
############# I have deleted the rows but as we can see blocks are there .########################
Actually FREE BLOCKS are added to free list but not linked to index structure.Rebuilding the index will be linked.
kdimod adding block to free list,dba 0x21c0c018,time 16:46:33.868
kdimod adding block to free list,dba 0x21c0c019,time 16:46:33.877
kdimod adding block to free list,dba 0x21c0c014,time 16:46:34.86
kdimod adding block to free list,dba 0x21c0c015,time 16:46:34.153
kdimod adding block to free list,dba 0x21c0c013,time 16:46:34.277
kdimod adding block to free list,dba 0x21c0c011,time 16:46:34.352
kdimod adding block to free list,dba 0x21c0c012,time 16:46:34.355
kdimod adding block to free list,dba 0x21c0c017,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c016,time 16:46:34.433
kdimod adding block to free list,dba 0x21c0c01b,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c01a,time 16:46:34.512
kdimod adding block to free list,dba 0x21c0c00e,time 16:46:34.669
kdimod adding block to free list,dba 0x21c0c00b,time 16:46:34.733
kdimod adding block to free list,dba 0x21c0c00d,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00c,time 16:46:34.829
kdimod adding block to free list,dba 0x21c0c00f,time 16:46:34.848
kdimod adding block to free list,dba 0x21c0c010,time 16:46:34.911
SQL> analyze index x_ind validate structure;
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 18 1 16365
SQL>
SQL> alter index x_ind rebuild;
Index altered.
SQL> analyze index x_ind validate structure;
column name forma
Index analyzed.
SQL> t a15
column bloSQL> cks heading "ALLOCATED|BLOCKS"
column lfSQL> _blks heading "LEAF|BLOCKS"
SQL> column br_blks heading "BRANCH|BLOCKS"
columSQL> n Empty heading "UNUSED|BLOCKS"
select nSQL> ame,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
X_IND 16384 1 0 16383
SQL>
posted by Miladin at
10:03 AM
1 Comments:
Great article
I was thinking if I can apply this event only for specific indexes since my application has a couple of hundreds of indexes.
I also presume we can use the same event also on local partitioned indexes
Thank you in advance
khair
Post a Comment
Subscribe to Post Comments [Atom]
<< Home