Identifying Bloated Index in Oracle

Indexes have always been a topic of interest for DBA/Developers. When it comes to index rebuild there have been many opinions floating across internet on when to rebuild these indexes. Many do say when the BLEVEL is > 3 one should rebuild the indexes. I don’t believe in that and i think i have never seen BLEVEL > 3 for index till now.

Over a period of time, the index can get fragmented because of the DML’s occurring on the table. The free space within the block of index can get used depending on the incoming column value, maintaining the index structure (sorted).

Now, suppose you have a table with one of the column as CREATION_DATE sysdate(DEFAULT), and every night data is loaded into it and suppose as per the application logic previous date data is deleted. Now an index having creation_date column will slowly and gradually increase in size and as the left side of the index will always be empty and index keeps growing toward right side.

With this kind of indexes, the performance many degrade for sqls , the plans many flip etc. It is sometimes good to rebuild indexes.But how to identify which indexes to be rebuild, is the question.

I was working on finding which indexes are bloated and below is the sql based on few logic

WITH spv AS (select di.table_name,
spv.object_name,
di.leaf_blocks,
di.index_type,
di.num_rows,
decode(di.uniqueness,'UNIQUE',0,1)uniq_ind,
di.last_analyzed,sum(bytes),
sum(io_cost) 
from v$sql_plan spv,dba_indexes di
     where spv.object_owner = 'ANAND' and         
           spv.object_type LIKE '%INDEX%' and 
           spv.object_name=di.index_name and 
           spv.object_owner=di.owner and 
           di.leaf_blocks > 1000
     group by di.table_name,
              spv.object_name,
              di.leaf_blocks,
              di.index_type,
              di.num_rows,
              di.uniqueness,
              di.last_analyzed order by 3)
select spv.table_name,
       spv.OBJECT_NAME index_name,
       spv.leaf_blocks leaf_blocks,
       round (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) + sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192))target_blocks, 
       round(((((spv.LEAF_BLOCKS) - (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) +  sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192)))/spv.LEAF_BLOCKS)*100)) DIFF_PCT
from spv, 
     (select table_name,num_rows,decode(partitioned,'YES',10,6) rowid_length  from dba_tables where owner='ANAND') tab,
     dba_tab_cols tc,
     dba_ind_columns ic 
where
     spv.table_name=tab.table_name and
     tc.column_name = ic.column_name  and
     tab.TABLE_NAME=tc.table_name and 
     ic.TABLE_NAME=tab.table_name and 
     spv.object_name=ic.INDEX_NAME
having round(((((spv.LEAF_BLOCKS) - (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) + sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192)))/spv.LEAF_BLOCKS)*100)) > 70
group by spv.table_name,
         spv.object_name,
         spv.leaf_blocks,
         spv.num_rows,spv.uniq_ind, 
         tab.rowid_length 
order by 5

Output from a test db –

TABLE_NAME                     INDEX_NAME                     LEAF_BLOCKS TARGET_BLOCKS   DIFF_PCT
------------------------------ ------------------------------ ----------- ------------- ----------
COMPLETED_CUST                 I_CCS_COND                           25340          7236         71
CUST_ONE_ITEMS                 I_CSI_REQ_ID                          7999          2351         71
DEMANDS                        I_DEMAND                             26920          7478         72
.....................................
.....................................
PROP_VALUES                    PK_SID_VALUE                         11847           831         93
SLA_METRS                      PK_SLA_METCS                         12840           129         99

–> leaf_blocks is the actual leaf blocks of the index from dba_indexes.
–> Target_Blocks represents the estimated no. of leaf blocks based on the current stats on the table. So, stats needs to be latest on the table.
–> The above sql displays all the index name where the % difference between leaf_blocks and estimated target blocks is > 70
–> The index names comes from v$sql_plan as those are the indexes being used by the optimizer.
–> The indexes having leaf_blocks > 1000 are selected

Now, lets rebuild the index and see do we get any closer target_blocks

12:53:24 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SLA_METCS';

LEAF_BLOCKS
-----------
        12840

12:52:59 DBA@test:1> alter index xxx.PK_SLA_METCS rebuild online parallel 4;

Index altered.

Elapsed: 00:00:00.87
12:53:24 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SLA_METCS';

LEAF_BLOCKS
-----------
        135  <-- Estimated TARGET_BLOCK was 129 

Elapsed: 00:00:00.43

13:15:46 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SID_VALUE';

LEAF_BLOCKS
-----------
      11847

Elapsed: 00:00:00.45
13:15:55 DBA@test:1> alter index xxx.PK_SID_VALUE rebuild online parallel 4;


Index altered.

Elapsed: 00:00:25.25
13:16:42 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SID_VALUE';

LEAF_BLOCKS
-----------
        840 <-- Estimated TARGET_BLOCK was 831

 

I would say this is just the version 1.0 :)

REFERENCE –> Script to investigate a b-tree index structure (Doc ID 989186.1)

About these ads
    • sandeep
    • August 28th, 2013

    very simple& nice explanation Anand…. thanks for the post. Its been quite longtime of new blog entry :)

    • Pavan Kumar
    • August 29th, 2013

    It’s good one buddy,
    I was decoding b-tree for your next version…. :)
    dba_ind_columns – not required… give a check

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 462 other followers

%d bloggers like this: