====== 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: [[http://www.twg-it.de/freedownloads/2012/DOAG_Nuenberg_2012_Gluecksspiel_Systemstatistiken.pdf|DOAG_Nuenberg_2012_Gluecksspiel_Systemstatistiken]].


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


Überwachen mit:
<code plsql>
column sname format a20
column pname format a20
column pval2 format a20
select
 sname
 , pname
 , pval1
 , pval2
 from
sys.aux_stats$;

</code>

Mit dem PL/SQL Package dbms_stats wird eine erste Workload Statistik angelegt.
<code plsql>
Set timing on
exec dbms_stats.gather_system_stats();
</code>
siehe auch [[http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#g43900|Statistik 10g]]


=== B) Workload Statisik anlegen für eine laufende DB Installation ===
Start während der laufenden Arbeitszeit
<code plsql>
execute dbms_stats.gather_system_stats('Start');   
</code>
Stoppen ca. 1h später
<code plsql>
execute dbms_stats.gather_system_stats('Stop');
</code>


=== C) Statistiken für das Data Dictionary anlegen ===

Für eine komplett neue Statistik der DB bei Bedarf zuvor löschen
<code plsql> 
Set timing on
Exec DBMS_STATS.DELETE_DATABASE_STATS;
Exec DBMS_STATS.DELETE_DICTIONARY_STATS;
Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS;
</code>

Neue Statisik anlegen \\
\\
Data Dictionary analysieren\\
degree Parameter = Anzahl Prozessoren der Maschine

<code plsql>
Set timing on
exec dbms_stats.gather_fixed_objects_stats(null);
exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_percent  => 100, degree  => 24,options  => 'GATHER')
-- 

</code>


=== D) Statistiken für die einzelnen Schemas ===

Zum Schluss über allen noch nicht analysierten Objekte 

<code plsql>
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' );
</code>

----

===== Statistik exportieren / importieren =====

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

<code sql>
-- 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

</code>
Export der Tabelle in einen Dump file
<code bash>
exp "'sys as sysdba'" file=prod_stats.dmp tables=EXPSTATSTABLE rows=yes 
</code>

Importieren der Tabelle im Zielsystem

<code bash>
imp "'sys as sysdba'" file=prod_stats.dmp tables=EXPSTATSTABLE ignore=yes
</code>


Löschen der bestehenden Statistik bei Bedarf
<code sql>
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' );  
</code>


Importieren der Statisik 
<code sql>
-- 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;   

</code>





===== 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.

<code sql>

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



  * 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



===== Script für die Überwachung =====

<code plsql 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
</code>

siehe auch [[http://orapowershell.codeplex.com/SourceControl/latest#sql/statistic.sql|statistic.sql]] für die aktuellste Version.

 {{tag>sql script}}

==== Quellen ====

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