Sammeln von Systemstatistiken. Bei der Berechnung der Kosten für die Zugriffspfade durch den Optimizer sind Informationen über Zugriffszeiten des I/O-Systems und die Geschwindigkeit des Prozessors notwendig. Diese Informationen können über die Prozedur GATHER_SYSTEM_STATS mit dem Package DBMS_STATS gesammelt werden. Sind diese Informationen nicht verfügbar, verwendet der Optimizer Standardwerte, die vom realen System abweichen und somit evtl. falsche Informationen bei der Auswahl eines geeigneten Zugriffspads liefern. Die Sammlung der Systemstatistiken sollte bei Belastung des Systems durchgeführt werden, um entsprechend aussagekräftige Werte zu erhalten. Zusätzlich könnte es sinnvoll sein, die Systemstatistiken zu unterschiedlichen Zeiträumen sammeln zu lassen, falls das System in diesen Zeiträumen unterschiedliche Lastprofile besitzt. Diese könnten beispielsweise Tages- und Nachtbetrieb sein, weil nachts Jobs ausgeführt werden, während tagsüber die Datenbank im reinen OLTP-Betrieb läuft.

Systemstatistiken werden nicht automatisch gesammelt, und es gibt auch keinen Menüpunkt im Database Control für ihre Sammlung. Das Sammeln der Systemstatistiken muss manuell zu repräsentativen Zeiträumen eingeleitet werden.

Die aktuellen Systemstatistiken befinden sich in der Tabelle SYS.AUX_STATS$.

SQL> DESCRIBE SYS.AUX_STATS$

 Name                    Null?    Typ
 ----------------------- -------- --------------
 SNAME                   NOT NULL VARCHAR2(30)
 PNAME                   NOT NULL VARCHAR2(30)
 PVAL1                            NUMBER
 PVAL2                            VARCHAR2(255)

 

SQL> SELECT * FROM  SYS.AUX_STATS$; 

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ----------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-30-2010 13:29
SYSSTATS_INFO   DSTOP                      09-30-2010 13:31
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW      1683,65129
SYSSTATS_MAIN   IOSEEKTIM               10
SYSSTATS_MAIN   IOTFRSPEED            4096
SYSSTATS_MAIN   SREADTIM          78311,25
SYSSTATS_MAIN   MREADTIM         60531,066
SYSSTATS_MAIN   CPUSPEED              1091
SYSSTATS_MAIN   MBRC                     2
......

13 Zeilen ausgewählt.

 

Beschreibung der Systemstatistiken:

STATUS

Status der Sammlung

DSTART

Startzeit der Sammlung

DSTOP

Endzeit der Sammlung

CPUSPEEDNW

CPU-Geschwindigkeit. Wird bei jedem Instanzstart gemessen.

IOSEEKTIM

Latenzzeit für des I/O-Systems in Millisekunden

IOTFRSPEED

Transferrate des I/O-Systems in Millisekunden

SREADTIM

Lesezeit eines einzelnen Blockes in Millisekunden

MREADTIM

Lesezeit von Multiblöcken bei Full-Table- oder Fast-Full-Index-Scans in Millisekunden

CPUSPEED

CPU-Geschwindigkeit.

MBRC

Durchschnittliche Anzahl der Blöcke beim Lesen einer Blocksequenz bei Full-Table- und Fast-Full-Index-Scans

 

Das Sammeln der Systemstatistiken kann entweder durch ein manuelles Starten und Stoppen oder durch Angabe eines Zeitintervalls durchgeführt werden.

Starten und Stoppen des Sammelvorgangs der Systemstatistiken:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START')
PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 Sammeln von Systemstatistiken über ein Intervall von 10 Minuten:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS(INTERVAL=>10,
                     GATHERING_MODE=>'INTERVAL')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Besitzt die Datenbank zu unterschiedlichen Zeiträumen ein anderes Lastprofil, so kann für jeden Zeitraum die entsprechende Systemstatistik gesammelt und geladen werden. Dafür muss eine Statistiktabelle mit der Prozedur CREATE_STAT_TABLE erstellt werden, in der die zu sammelnden Statistiken abgespeichert werden. Beim Start des Sammelvorgangs wird der Prozedur GATHER_SYSTEM_STATS diese Tabelle und ein Bezeichner für die zu speichernde Statistik übergeben. Soll nun eine Statistik geladen werden, so wird die Prozedur IMPORT_SYSTEM_STATS verwendet, indem man wieder die Tabelle und den Bezeichner für die Statistik übergibt.

Im folgenden Beispiel wird eine Tabelle TABSYSSTATS erstellt, in der für das tägliche Lastprofil die Systemstatistik mit dem Bezeichner STATDAY abgelegt wird. Das Gleiche wird für das nächtliche Lastprofil durchgeführt, für das die Statistik den Bezeichner STATNIGHT erhält. Morgens werden nun die Statistiken für den Tag und abends die Statistiken für die Nacht geladen.

 

Erstellen der Statistiktabelle:


SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','TABSYSSTATS','SYSAUX')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 Erstellen der Systemstatistiken für den Tag:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATDAY')
 PL/SQL-Prozedur erfolgreich abgeschlossen.

 

 SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATDAY')

 PL/SQL-Prozedur erfolgreich abgeschlossen.
 

Erstellen der Systemstatistiken für die Nacht:

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP', STATTAB=>'TABSYSSTATS',
                                         STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Laden der Systemstatistiken für den Tag:

SQL> EXEC DBMS_STATS.IMPORT_SYSTEM_STATS(STATTAB=>'TABSYSSTATS',
                                        STATID=>'STATDAY')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Laden der Systemstatistiken für die Nacht:


SQL> EXEC DBMS_STATS.IMPORT_SYSTEM_STATS(STATTAB=>'TABSYSSTATS',
                                       STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Skript zum Testen von Deferred Statistics in Oracle 11g. Ab Oracle 11g können nicht veröffentlichte Statistiken für Objekte gesammelt werden. Nachdem diese Statistiken getestet und für gut befunden wurden, können sie veröffentlicht werden.

CONN HR/hr
SET ECHO OFF
SET LINESIZE 1000
COL PUBLISH_STATE FORMAT A20
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
/

SET ECHO ON
CL SCR

CREATE SEQUENCE SQ_PUBLISHSTATS
/

CREATE TABLE TAB_PUBLISHSTATS
(
    S1 NUMBER,
    S2 VARCHAR2(2000)
)
TABLESPACE USERS
/

EXEC DBMS_STATS.SET_TABLE_PREFS('HR','TAB_PUBLISHSTATS','PUBLISH','TRUE')

BEGIN
    FOR I IN 1..100000
    LOOP
        INSERT INTO TAB_PUBLISHSTATS VALUES(1,'TESTWERT1TESTWERT1TESTWERT1TESTWERT1');
    END LOOP;
    COMMIT;
END;
/

CREATE INDEX IDX_PUBLISHSTATS ON TAB_PUBLISHSTATS(s1)
TABLESPACE USERS
/

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TAB_PUBLISHSTATS', CASCADE=>TRUE)
PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
PAUSE

UPDATE TAB_PUBLISHSTATS SET S1=SQ_PUBLISHSTATS.NEXTVAL
/
COMMIT
/

EXEC DBMS_STATS.SET_TABLE_PREFS('HR','TAB_PUBLISHSTATS','PUBLISH','FALSE')
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TAB_PUBLISHSTATS')

PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
PAUSE

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE
/

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=FALSE
/
PAUSE

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('HR','TAB_PUBLISHSTATS')
PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF

DROP TABLE TAB_PUBLISHSTATS PURGE;
DROP SEQUENCE SQ_PUBLISHSTATS;


 

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