Which index you need to rebuild?


— This script shows the weak indexes I mean fragmentated index in your oracle database.
— Shows those indexes who are having more than 500 leaves and storage quality is less than 50%
— This accuracy of this script depended on the DB Statistics are fresh and almost accurate

Column index_name format 99999999999999999999
Column rowlen format 99999999999999999999
Column leaves format 99999999999999999999
Column net_mb format 999999999999
Column gross_mb format 999999999999

Spool index_stat.txt
SELECT
INDEX_NAME, NUM_ROWS, ROWLEN, LEAVES, NET_MB, GROSS_MB, QUALITY
FROM
( SELECT	SUBSTR(I.INDEX_NAME, 1, 20) 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 > 500 -- Change this as per your requirement.
GROUP BY I.NUM_ROWS, I.LEAF_BLOCKS, I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
)
WHERE
QUALITY <= 50  -- You can change this as per your requirement
ORDER BY QUALITY;
spool off.
Advertisements

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