среда, 19 августа 2009 г.

Oracle ANALYZE INDEX


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 = 'OWS' and index_name in ('USAGE_APPR4')
WHERE (OWNER, index_name) in ( select owner, segment_name from dba_segments where segment_type = 'INDEX' and owner = 'BSSADMIN')
;
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;
begin
/* 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 (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE( 'ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD online compute statistics; -- ' ||vDLfPerc);
END IF ;
exception
when others then
DBMS_OUTPUT.PUT_LINE( '-- ' || SQLERRM ());
end ;
END LOOP ;
CLOSE cGetIdx;
END ;