Check Index Health – Oracle


Here is a small script which shows the index health in your oracle database.

You need to provide your index health threshold and get the result. Then Break Free 😉 on those indexes in your database to give them some medicine

Column index_name format A40
Column rowlen format 99999999999999999999
Column leaves format 99999999999999999999
Column net_mb format 999999999999
Column gross_mb format 999999999999
Set Lines 200
-- Modify as you require the page size
set pages 5000
Spool index_stat.txt
SELECT
  INDEX_NAME, NUM_ROWS, ROWLEN, LEAVES, NET_MB, GROSS_MB, QUALITY
FROM
 ( SELECT I.INDEX_NAME INDEX_NAME,
   I.NUM_ROWS NUM_ROWS,
   SUM(TC.AVG_COL_LEN + 1) + 7 ROWLEN,
   I.LEAF_BLOCKS LEAVES,
   ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /1000000, 0) NET_MB,
   ROUND(I.LEAF_BLOCKS * (8079 - 23 * NVL(I.INI_TRANS, 2)) * (1 - NVL(I.PCT_FREE, 10) / 100) / 1000000, 0) GROSS_MB,
   ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS / (I.LEAF_BLOCKS * (8079 - 23 * NVL(I.INI_TRANS, 2)) * (1 - NVL(I.PCT_FREE, 10) / 100)) * 100, 0) QUALITY
 FROM
  DBA_INDEXES I, DBA_IND_COLUMNS IC, DBA_TAB_COLUMNS TC
 WHERE I.INDEX_NAME = IC.INDEX_NAME AND
  I.OWNER = IC.INDEX_OWNER AND
  TC.TABLE_NAME = IC.TABLE_NAME AND
  TC.OWNER = IC.INDEX_OWNER AND
  TC.COLUMN_NAME = IC.COLUMN_NAME AND
  I.INDEX_TYPE = 'NORMAL' AND
  I.LEAF_BLOCKS > 1000
 GROUP BY I.NUM_ROWS, I.LEAF_BLOCKS, I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
 )
WHERE
  QUALITY <= &threshold_pct
ORDER BY QUALITY;
spool off.

3 thoughts on “Check Index Health – Oracle”

  1. What does this QUALITY mean? Is it a percentage value? Most probably not – because in my Database I already have value greater than 100. Can you please explain this query a little bit further?

  2. Blue Sky said:

    When did you built your database and dictionary statistics?

  3. Well… almost a month ago. My dba use automatic segment space management……

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