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;
/

 

SET SERVEROUTPUT ON
DECLARE
 V_OWNER VARCHAR2(30) :='HR'; --Hier zu analysierendes Schema angeben
 --Tabellen und Indizes analysieren deren Statistiken veraltet sind
 --aber innerhalb der letzten 24 Stunden analysiert wurden
 CURSOR C_TABLES_FOR_ANALYZE
 IS
  SELECT
   OWNER,
   TABLE_NAME,
   OBJECT_TYPE
  FROM
   DBA_TAB_STATISTICS
  WHERE
   OWNER =V_OWNER AND
   STALE_STATS='YES' AND
   LAST_ANALYZED > SYSDATE-1;
 
 CURSOR C_INDEX_FOR_ANALYZE
 IS
  SELECT
   OWNER,
   INDEX_NAME,
   OBJECT_TYPE
  FROM
   DBA_IND_STATISTICS
  WHERE
   OWNER =V_OWNER AND
   STALE_STATS='YES' AND
   LAST_ANALYZED > SYSDATE-1;

 CURSOR C_STATUS_FOR_ANALYZE
 IS
  SELECT
   OWNER,
   TABLE_NAME,
   OBJECT_TYPE
  FROM
   DBA_TAB_STATISTICS
  WHERE
   OWNER =V_OWNER AND
   STALE_STATS='YES' AND
   LAST_ANALYZED > SYSDATE-1
  UNION ALL
  SELECT
   OWNER,
   INDEX_NAME,
   OBJECT_TYPE
  FROM
   DBA_IND_STATISTICS
  WHERE
   OWNER =V_OWNER AND
   STALE_STATS='YES' AND
   LAST_ANALYZED > SYSDATE-1;
BEGIN
 --Änderungsverfolgung aus der SGA zurücksreiben
 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- Sollte dieses zu Performanceproblemen führen, entfernen.
 
 --Alle Objekte ausgeben, die zu analysieren sind
 DBMS_OUTPUT.PUT_LINE('OBJECTS BEFORE ANALYZE:');
 DBMS_OUTPUT.PUT_LINE('=======================');
 FOR REC_STATUS_FOR_ANALYZE IN C_STATUS_FOR_ANALYZE
 LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(REC_STATUS_FOR_ANALYZE.OBJECT_TYPE,8,' ') || ':' || REC_STATUS_FOR_ANALYZE.TABLE_NAME);
 END LOOP;
 
 DBMS_OUTPUT.PUT_LINE(CHR(10));
 --Alle Tabellen inkl. deren Indizes analysieren, deren Statistiken veraltet sind
 --aber innerhalb der letzten 24 Stunden analysiert wurden
 DBMS_OUTPUT.PUT_LINE('ANALYZED OBJECTS:');
 DBMS_OUTPUT.PUT_LINE('=================');
 FOR REC_TABLES_FOR_ANALYZE IN C_TABLES_FOR_ANALYZE
 LOOP
  DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE: ' || REC_TABLES_FOR_ANALYZE.TABLE_NAME);
  DBMS_STATS.GATHER_TABLE_STATS(
          OWNNAME=> V_OWNER,
          TABNAME=> REC_TABLES_FOR_ANALYZE.TABLE_NAME,
          ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE,
          CASCADE=> DBMS_STATS.AUTO_CASCADE,
          DEGREE=> NULL,
          NO_INVALIDATE=> DBMS_STATS.AUTO_INVALIDATE,
          GRANULARITY=> 'AUTO',
          METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE AUTO');

 END LOOP;
  
 --Alle Indizes analysieren falls sie nich durch die Tabellenanalyse analysiert wurden, deren Statistiken veraltet sind
 --aber innerhalb der letzten 24 Stunden analysiert wurden
 FOR REC_INDEX_FOR_ANALYZE IN C_INDEX_FOR_ANALYZE
 LOOP
  DBMS_OUTPUT.PUT_LINE('ANALYZE INDEX: ' || REC_INDEX_FOR_ANALYZE.INDEX_NAME);
  DBMS_STATS.GATHER_INDEX_STATS(
          OWNNAME=> V_OWNER,
          INDNAME=> REC_INDEX_FOR_ANALYZE.INDEX_NAME ,
          ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE,
          DEGREE=> NULL,
          NO_INVALIDATE=> DBMS_STATS.AUTO_INVALIDATE,
          GRANULARITY=> 'AUTO');
 END LOOP;
 
 DBMS_OUTPUT.PUT_LINE(CHR(10));
 
 --Objekte anzeigen, die keine Analyse erfahren haben
 DBMS_OUTPUT.PUT_LINE('OBJECTS AFTER ANALYZE:');
 DBMS_OUTPUT.PUT_LINE('======================');
 FOR REC_STATUS_FOR_ANALYZE IN C_STATUS_FOR_ANALYZE
 LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(REC_STATUS_FOR_ANALYZE.OBJECT_TYPE,8, ' ') || ':' || REC_STATUS_FOR_ANALYZE.TABLE_NAME);
 END LOOP;
END;
/

SET SERVEROUTPUT ON
DECLARE
 V_OWNER VARCHAR2(30) :='SYSMAN'; --Hier zu analysierendes Schema angeben
 V_SHOW_REBUILD_INDEXES_ONLY BOOLEAN NOT NULL:=FALSE;
 V_PCT_DEL_ROWS NUMBER(2) NOT NULL:=30;
 V_INDEX_MAX_SIZE_MB NUMBER(5,2) NOT NULL:=1;
 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_MAX_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;
/