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.

Ü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

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');

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')
--

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' );

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;

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

• https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

Best Practices for Gathering Optimizer Statistics

• http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf

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.

Blogs:

• https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto
• https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt
• http://www.oracleangels.com/2011/01/automatic-statistics-gathering-job.html

Vorträge:

• http://www.centrexcc.com/Active%20Statistics.ppt.pdf

Doku:

• http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm