Index DeFragmentation / Rebuild / Index Performance Tuning

Index Defragmentation Steps

Analyzing statistics

Validating the index

Checking PCT_USED

Dropping and rebuilding (or coalescing) the index

-----The following INDEX_STATS columns are especially useful:

height----- refers to the maximum number of levels encountered within the index.

lf_rows------- refers to the total number of leafs nodes in the index.

del_lf_rows --------refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.

SQL>analyze index INDEX_NAME validate structure;

Then query INDEX_STATS view

1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.

2. If height is 4 then index should be rebuild.

3. If lf_rows is lower than lf_blks then index should be rebuild.

----------validate index------

spool c:\index_validate.sql

select 'Analyze Index '||index_name||' validate structure;' from user_indexes;

spool off

@c:\index_validate.sql

-------------following query should be run after all analyze statement

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;

select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS

from index_stats;

If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding

-------following scripts validate the indexes and find to rebuild and rebuild them-------

set serveroutput on size 100000

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;

/