Oraclue

Oracle internals, debugging and undocumented features

Test Case: Oracle Event 10224 – index block split/delete trace

This event can be used to dump index block splits and deletes :

alter system set events ‘10224 trace name context forever, level 2’

Test case:
SQL> create table test ( x  number ) tablespace lgdata;

Table created.

SQL> create  unique index x_ind on test (x) tablespace lgdata;

Index created.

 SQL> begin
  for i in 1..100000 loop
    insert into test values (i);
  end loop;
end;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.
/*  Set event */

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10224 trace name context forever,level 2
Statement processed.

/*  Analyze index and get stats */

SQL> analyze index x_ind validate structure;

Index analyzed.
SQL> column name format a15
SQL> column blocks heading “ALLOCATED|BLOCKS”
SQL> column lf_blks heading “LEAF|BLOCKS”
SQL> column br_blks heading “BRANCH|BLOCKS”
SQL> column Empty heading “UNUSED|BLOCKS”
SQL> 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                  256                  187          1         68

 There are 68 unused blocks.Run some DML on table :

 SQL> delete from test where x < 40000;

19999 rows deleted.

SQL> commit;

Commit complete.
and analyze it again

SQL> analyze index x_ind validate structure;

Index analyzed.

SQL> column name format a15
SQL> column blocks headingSQL>  “ALLOCATED|BLOCKS”
SQL> column lf_blks heading “LEAF|BLOCKS”
SQL> column br_blks heading “BRANCH|BLOCKS”
SQL> column Empty heading “UNUSED|BLOCKS”
SQL> 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                       256          187            1         68
I have deleted the rows but as we can see blocks are there.Number of unused blocks is still 68.
Take look at trace file:
SQL> oradebug tracefile_name
/u01/app/oracle/admin/demo/demo_ora_1488.trc
SQL> host vi /u01/app/oracle/admin/demo/demo_ora_1488.trc

kdimod adding block to free list,dba 0x02c56510,time 17:20:28.544
kdimod adding block to free list,dba 0x02c5650d,time 17:20:28.545
kdimod adding block to free list,dba 0x02c5650e,time 17:20:28.545
kdimod adding block to free list,dba 0x02c5650f,time 17:20:28.546
kdimod adding block to free list,dba 0x014003e8,time 17:20:28.546
kdimod adding block to free list,dba 0x014003e1,time 17:20:28.546
kdimod adding block to free list,dba 0x014003e2,time 17:20:28.547
kdimod adding block to free list,dba 0x014003e3,time 17:20:28.547
kdimod adding block to free list,dba 0x014003e4,time 17:20:28.547
kdimod adding block to free list,dba 0x014003e5,time 17:20:28.547
kdimod adding block to free list,dba 0x014003e6,time 17:20:28.548
kdimod adding block to free list,dba 0x014003e7,time 17:20:28.548
kdimod adding block to free list,dba 0x02c56513,time 17:20:28.548
kdimod adding block to free list,dba 0x02c56514,time 17:20:28.549
kdimod adding block to free list,dba 0x02c56515,time 17:20:28.549
kdimod adding block to free list,dba 0x02c56516,time 17:20:28.549
kdimod adding block to free list,dba 0x02c56517,time 17:20:28.550
kdimod adding block to free list,dba 0x02c56518,time 17:20:28.550
kdimod adding block to free list,dba 0x02c56512,time 17:20:28.550

Actually FREE BLOCKS are added to free list but not linked to index structure.

Rebuilding the index will link them to index structure.

 SQL> alter index x_ind rebuild;

Index altered.

SQL> analyze index x_ind validate structure;

Index analyzed.

SQL> column name format a15
SQL> column blocks headingSQL>  “ALLOCATED|BLOCKS”
SP2-0735: unknown COLUMN option beginning “headingSQL…”
SQL> column lf_blks heading “LEAF|BLOCKS”
SQL> column br_blks heading “BRANCH|BLOCKS”
SQL> column Empty heading “UNUSED|BLOCKS”
SQL> select name,
             blocks,
             lf_blks,
             br_blks,
             blocks-(lf_blks+br_blks) empty
from   index_stats;  6

                 ALLOCATED       LEAF     BRANCH     UNUSED
NAME                BLOCKS     BLOCKS     BLOCKS     BLOCKS
————— ———- ———- ———- ———-
X_IND                        256        168             1         87
and number of unused block is 87.

Advertisements

2 responses to “Test Case: Oracle Event 10224 – index block split/delete trace

  1. Andre November 2, 2008 at 11:44 pm

    Hi, Miladin,

    UNUSED_BLOCKS indicates how many blocks are above the high-watermark (hwm), i.e., have never contained data.

    When the rows are deleted some blocks are put in the free-list because they now have space for new rows. However, as they have already been used, and therefore are below the hwm, the don’t count as unused_blocks.

    When you rebuild the index, the data is reorganised and the index recreated from scratch, and then you have more pristine blocks (above the hwm) that appear as UNUSED.

    Cheers,
    Andre

  2. oraclue November 3, 2008 at 3:34 pm

    Hi Andrew,

    Thanks for comment.This open new topic.

    To prove it we can use oracle event 10081 – segment HWM advanced and dump the index with oradebug dump treedump command.

    Regards,

    Miladin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: