====== 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:
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 [[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
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;
/
* 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 =====
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 [[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