Inhaltsverzeichnis

Statistiken anlegen und überwachen

1. Systemweite Statistik

In der DOAG News 01/2013 (S.52) ist ein sehr gut gemachter Artikel von Thorsten W. Grebe über die Problematik mit den Oracle Systemstatistiken zu finden: “Glücksspiel Systemstatistiken - Das Märchen vom typischen Workload“.

Unter diesen Link ist sein Vortrag zu dem Thema auf der DOAG Konferenz: DOAG_Nuenberg_2012_Gluecksspiel_Systemstatistiken.

A) NoWorkload Statisik anlegen für eine neue DB Installation

Überwachen mit: 

column sname format a20
column pname format a20
column pval2 format a20
SELECT
 sname
 , pname
 , pval1
 , pval2
 FROM
sys.aux_stats$;

Mit dem PL/SQL Package dbms_stats wird eine erste Workload Statistik angelegt. 

SET timing ON
exec DBMS_STATS.gather_system_stats();

siehe auch Statistik 10g

B) Workload Statisik anlegen für eine laufende DB Installation

Start während der laufenden Arbeitszeit 

EXECUTE DBMS_STATS.gather_system_stats('Start');

Stoppen ca. 1h später 

EXECUTE DBMS_STATS.gather_system_stats('Stop');

C) Statistiken für das Data Dictionary anlegen

Für eine komplett neue Statistik der DB bei Bedarf zuvor löschen 

SET timing ON
Exec DBMS_STATS.DELETE_DATABASE_STATS;
Exec DBMS_STATS.DELETE_DICTIONARY_STATS;
Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS;

Neue Statisik anlegen

Data Dictionary analysieren
degree Parameter = Anzahl Prozessoren der Maschine 

SET timing ON
exec DBMS_STATS.gather_fixed_objects_stats(NULL);
exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_percent  => 100, degree  => 24,options  => 'GATHER')
--

D) Statistiken für die einzelnen Schemas

Zum Schluss über allen noch nicht analysierten Objekte 

CONNECT / AS sysdba
-- prüfen was wann analysiert wurde
SELECT TO_CHAR(LAST_ANALYZED,'dd.mm hh24'),owner,COUNT(*) FROM dba_tables GROUP BY owner,TO_CHAR(LAST_ANALYZED,'dd.mm hh24') ORDER BY 1 DESC;
SET timing ON
-- ein Schema
Exec DBMS_STATS.GATHER_SCHEMA_STATS ( ownname => 'MEIN_USER',degree=>24,options=>'GATHER AUTO' );
-- Alles in der DB das nicht analysiert wurde
Exec DBMS_STATS.GATHER_DATABASE_STATS (degree=>24,options=>'GATHER AUTO' );

Statistik exportieren / importieren

Ziel: Aus der Produktion die gleiche Statisik in das Testsystem kopieren 

-- prüfen of die Tabelle schon da ist/war
 
sqlplus / AS sysdba
 
sqlplus>SELECT owner,TABLE_NAME FROM dba_tables WHERE TABLE_NAME='EXPSTATSTABLE'
 
-- falls existiert löschen 
 
EXEC dbms_stats.drop_stat_table(ownname => 'SYS', stattab => 'EXPSTATSTABLE');
 
-- Tabelle für die Statistiken anlegen
EXEC dbms_stats.create_stat_table  (ownname => 'SYS', stattab => 'EXPSTATSTABLE', tblspace => 'sysaux'); 
 
 
-- Kopieren der Statistik der DB in die Tabelle
 
-- Exportieren der Statistiken der ganzen Datenbank
EXEC dbms_stats.EXPORT_DATABASE_STATS (statown => 'SYS'   , stattab => 'EXPSTATSTABLE',statid =>'SB5MEGAPATCH' );
-- oder alternativ die eines Anwendres
-- Exportieren der Statistiken des Eigentümers der Schema Objekte
-- exec dbms_stats.export_schema_stats(ownname => 'OPERATOR' , stattab =>'EXPSTATSTABLE' ,statid =>'OB5MEGAPATCH',statown => 'sys');
 
--  prüfen ob Einträge vorhanden sind ( 11g >> 92000! für eine leere Datenbank! )
SELECT COUNT(*) FROM EXPSTATSTABLE;
 
--
exit

Export der Tabelle in einen Dump file 

exp "'sys as sysdba'" file=prod_stats.dmp tables=EXPSTATSTABLE rows=yes

Importieren der Tabelle im Zielsystem 

imp "'sys as sysdba'" file=prod_stats.dmp tables=EXPSTATSTABLE ignore=yes

Löschen der bestehenden Statistik bei Bedarf 

sqlplus / AS sysdba
EXEC dbms_stats.delete_database_stats(no_invalidate =>TRUE ,stattype => 'ALL',force => TRUE);
 
-- oder alternativ nur für einen Anwender
-- exec dbms_stats.delete_schema_stats ( ownname => 'OPERATOR' );

Importieren der Statisik 

-- import DB statistic
EXEC dbms_stats.import_database_stats(stattab =>'EXPSTATSTABLE' ,statid =>'SB5MEGAPATCH',statown =>'SYS' ,no_invalidate => TRUE,force => TRUE); 
 
-- import schema statistic bei Bedarf
-- exec dbms_stats.import_schema_stats ( ownname => 'OPERATOR', stattab =>'EXPSTATSTABLE' , statid =>'OB5MEGAPATCH', statown  => 'SYS',no_invalidate => TRUE,force => TRUE); )
 
 
-- evtl. gecached Information aus der SGA entfernen
ALTER system FLUSH shared_pool;

Histogramme auf Tabellen Spalten vermeiden

Wenn keine Histogramme auf den Tabellen Spalten gewünscht sind mit method_opt ⇒ 'FOR ALL COLUMNS SIZE 1' die Erstellung deaktivieren.

Beispiel für eine Tabelle, in 10g muss die Statistik aber komplett gelöscht werden (10g) , ab 11g kann mit dbms_stats.delete_column_stats direkt das Histogramm einer Spalte gelöscht werden. 

BEGIN
 
dbms_stats.delete_table_stats(
      ownname=>'SCOTT'
    , tabname=>'EMP'
    , no_invalidate => FALSE);
);
 
dbms_stats.gather_table_stats ( 
      ownname          => 'SCOTT' 
     ,tabname          => 'EMP'
     ,estimate_percent => dbms_stats.auto_sample_size 
     ,method_opt       => 'FOR ALL COLUMNS SIZE 1' 
     ,cascade          => TRUE 
     ,degree           => 8 
);
 
END;
/

Best Practices for Gathering Optimizer Statistics

Script für die Überwachung

stat.sql
SET linesize 130
 
ttitle left  "Workload Statistik Values" skip 2
 
column SNAME format a20
column pname format a15
column PVAL2 format a20
 
SELECT SNAME,
 PNAME,
 PVAL1,
 PVAL2 FROM sys.aux_stats$
/
 
ttitle left  "LAST ANALYZED Tables Overview" skip 2
 
SELECT TO_CHAR(LAST_ANALYZED,'dd.mm hh24')
       ,owner,COUNT(*)  
  FROM dba_tables 
 GROUP BY owner,TO_CHAR(LAST_ANALYZED,'dd.mm hh24') 
 ORDER BY 1 DESC;
 
ttitle off

siehe auch statistic.sql für die aktuellste Version.

,

Quellen

Blogs:

Vorträge:

Doku: