Skript für die Indexanalyse

Leider besitzt Oracle keine Automatik Indizes anzuzeigen, die reorganisiert werden sollten. Eine Reorganisation eines Index richtet sich einmal nach seiner Höhe sowie nach dem entstandenen freien Speicherplatz durch Löschen von Tabellenzeilen. Das folgende Skript verwende ich oft, um Indizes zu finden, die reorganisiert werden sollten. Dem Skript muss das zu analysierende Schema, ein Schwellwert der prozentual gelöschten Zeilen sowie eine Indexmindestgröße übergeben werden, ab der die Analyse durch geführt werden soll. Des Weiteren können im ersten Schritt auch nur die Indizes angezeigt werden, die reorganisiert werden sollten.

SET SERVEROUTPUT ON
DECLARE
V_OWNER VARCHAR2(30) :='HR'; --Hier zu analysierendes Schema angeben
V_SHOW_REBUILD_INDEXES_ONLY BOOLEAN NOT NULL:=TRUE; --Nicht neu aufbauenen, nur analysieren
V_PCT_DEL_ROWS NUMBER(2) NOT NULL:=20; --Analysieren aber dieser Anzahl gelöschter Zeielen
V_INDEX_MIN_SIZE_MB NUMBER(5,2) NOT NULL:=1; --Mindest Indexgröße
REC_INDEXSTATS INDEX_STATS%ROWTYPE;
CURSOR C_INDEXES_FOR_REBUILD
IS
SELECT DISTINCT
DI.OWNER,
DI.INDEX_NAME
FROM
DBA_INDEXES DI
INNER JOIN DBA_SEGMENTS DS
ON DI.OWNER=DS.OWNER AND DI.INDEX_NAME=DS.SEGMENT_NAME
WHERE
DI.OWNER =V_OWNER AND
DI.INDEX_TYPE LIKE '%NORMAL%' AND
((DS.BYTES/1024)/1024)>=V_INDEX_MIN_SIZE_MB;

BEGIN

--Alle Objekte ausgeben, die zu analysieren sind
DBMS_OUTPUT.PUT_LINE('REBUILD INDEXES:');
DBMS_OUTPUT.PUT_LINE('=======================');
FOR REC_INDEXES_FOR_REBUILD IN C_INDEXES_FOR_REBUILD
LOOP
DECLARE
EX_LOCKED_INDEX EXCEPTION;
PRAGMA EXCEPTION_INIT(EX_LOCKED_INDEX, -04022);
BEGIN
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' VALIDATE STRUCTURE';
SELECT
*
INTO
REC_INDEXSTATS
FROM
INDEX_STATS;
IF (REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100 >=V_PCT_DEL_ROWS
THEN
IF V_SHOW_REBUILD_INDEXES_ONLY=FALSE
THEN
EXECUTE IMMEDIATE 'ALTER INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' REBUILD ONLINE';
END IF;
DBMS_OUTPUT.PUT_LINE(ROUND((REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100,2) || ' ' ||
REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
REC_INDEXES_FOR_REBUILD.INDEX_NAME);
END IF;
EXCEPTION
WHEN EX_LOCKED_INDEX
THEN
DBMS_OUTPUT.PUT_LINE('INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' COULD NOT BE RBUILD. DDL TIMOEOUT');
END;
END LOOP;
END;
/