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