Pavan DBA's Blog

The DBA Knowledge Store

script to find which indexes require rebuilding

Posted by Pavan DBA on October 16, 2011


DECLARE
vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
vCursor  NUMBER;                            /* DBMS_SQL cursor        */
vNumRows INTEGER;                           /* DBMS_SQL return rows   */
vHeight  index_stats.height%TYPE;           /* Height of index tree   */
vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
vDLfPerc   NUMBER;                          /* Del lf Percentage      */
vMaxHeight NUMBER;                          /* Max tree height        */
vMaxDel    NUMBER;                          /* Max del lf percentage  */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE ‘SYS%’;
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel    := 20;

/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := ‘ANALYZE INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ VALIDATE STRUCTURE’;
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding?  */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN         /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE(‘ALTER INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ REBUILD;’);
END IF;

END LOOP;
CLOSE cGetIdx;
END;

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

 
%d bloggers like this: