Index validate
Fast Index Rebuild.
Execute on DBMS_SQL must be granted from SYS
validate structure command obtains a dml enqueue lock on the table that the index
SQL> select distinct INDEX_TYPE from dba_indexes ;
INDEX_TYPE
---------------------------
IOT - TOP
LOB
FUNCTION-BASED NORMAL
FUNCTION-BASED DOMAIN
BITMAP
NORMAL
CLUSTER
DOMAIN
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%' and INDEX_TYPE='NORMAL' ; ------???????????
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.NATIVE);
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;
==============
or
drop table
create table anuj_index_stats as select * from index_stats 1=2;
delete from anuj_index_stats;
BEGIN
FOR x IN (SELECT index_name FROM user_indexes WHERE index_type = 'NORMAL') LOOP
EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure';
EXECUTE IMMEDIATE 'insert into aux_index_stats select * from index_stats';
COMMIT;
END LOOP;
END;
/
=
validate structure command obtains a dml enqueue lock on the table and correspond index
First rule : If the index has height greater than 4 , rebuild the index
Second rule : { (del_lf_rows/lf_rows)*100 as ratio } if this ratio above 20% this
means high number of deletes or updates have occurred to the index
column.
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
Search This Blog
Total Pageviews
Sunday, 3 July 2011
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
4 comments:
How to Determine When an Index Should be Rebuilt? (Doc ID 1373415.1)
Script to investigate a b-tree index structure (Doc ID 989186.1)
Analyze Index Validate Structure Online Does Not Populate INDEX_STATS (Doc ID 283974.1)
How to Determine When an Index Should be Rebuilt? (Doc ID 1373415.1)
spool ind.sql
set pagesize 4500
select 'Prompt.....'||owner||'.'||index_name,'analyze index '||owner||'.'||index_name||' validate structure;' from DBA_indexes
where 1=1
and INDEX_TYPE='NORMAL'
and owner in ('****')
order by index_name;
spool off
1- Find indexes having height(blevel+1) > 4
i.e. Indexes having BLEVEL > 3
SQL> select owner, index_name, table_name, blevel from dba_indexes where BLEVEL>3
2- Analyze indexes to find ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20 by "analyzing the index with validate structure option" and then:
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
Post a Comment