Oracle 12c: Datenbank Tuning und Monitoring
Mit Oracle 12c wurde der Real-Time ADDM eingeführt. Damit gibt es einen innovativen Weg, die Ursachen und Probleme zu analysieren, wenn die Oracle Datenbank hängt oder nicht antwortet. Der „Automatic Database Diagnostic Monitor (ADDM)" kann im normalen und im Diagnose Modus gefahren werden. In Abhängigkeit des Connection Modus werden unterschiedliche Ergebnisse erzielt.
Im Folgenden wird aufgezeigt, wie der ADDM ein Set vordefinierter Kriterien durchläuft, nach denen die aktuelle Performance der Datenbank analysiert wird. Sobald ein Problem identifiziert und analysiert worden ist, hilft der Real-Time ADDM mit dem Emergency Monitoring Feature, die Ursachen und Lösungen zu finden.
Die Oracle Datenbank 12c hat viele automatische Performance Diagnose Empfehlungen. Dies sind unter anderem der Automatische Datenbank Diagnose Monitor (ADDM), der Real-Time ADDM und der Vergleichszeitraum Compare Period ADDM. Der ADDM Problem Report zeigt bei der Analyse festgestellte Probleme der letzten Stunde an und macht Vorschläge zur Behebung. Der Spot ADDM ist ein neues Advisory, das automatisch angestoßen ("angetriggert") wird, wenn die Datenbank anfängt, Performanceprobleme zu bekommen und versucht, die Ursache des Problems zu identifizieren. Das Ergebnis wird in das Automatic Workload Repository (AWR) gespeichert. In Oracle 12c werden Daten aus dem Real-Time SQL Monitoring, Datenbank Operations Monitoring und dem Real-Time ADDM Reports ebenfalls in das AWR der Datenbank abgespeichert. Dadurch hat der Administrator die Möglichkeit, die Performance der Ausführung der Datenbankabfragen nachträglich zu überprüfen. Mit dem Real-Time SQL Monitoring kann die Performance einer speziellen SQL Abfrage über die Zeit untersucht werden.
Der ADDM analysiert Performanceprobleme direkt nach dem Auftreten des Events. Das spart Zeit und Ressourcen, indem der ADDM das Problem reproduziert.
Der SQL-Monitor-, ASH- und AWR-Report zeigen jetzt Statistiken von In-Memory-Operationen. Was bedeutet das?
Der erweiterte Real-Time ADDM meldet proaktive Performanceprobleme, indem er alle 3 Sekunden Performancedaten aus der SGA ausliest. Es werden dabei auch die Ash Daten der letzten 10 Minuten analysiert. Dadurch kann der Administrator proaktiv gewarnt werden und Ursachenforschung betreiben.
ADDM und AWR sind standardmäßig vorhanden. Mit dem Diagnostic + Tuningpack werden die gefunden Ergebnisse in dem Work Load Repository abgespeichert. Dafür müssen folgende Datenbankparameter gesetzt sein.
timed_statistics = true statistics_level = [typical|all Control_management_pack_access=diagnostic/diagnostic+tuning
Systemstatistiken müssen vorhanden sein. Der ADDM benötigt für die I/O Statistiken – z. B.: average read time – die hardwarespezifischen Antwortzeiten für das Lesen eines Datenbankblocks. Normale Werte liegen zwischen 5.000 und 20.000 Mikrosekunden. Mit folgendem Script werden die Systemstatistiken gesammelt und die Antwortzeit eines Lesevorgangs auf 8.000 Mikrosekunden gesetzt.
SQL>dbms_stats.gather_system_stats(gathering_modIe=>'start'); SQL>dbms_stats.gather_system_stats(gather_mode=>'stop'); SQL>Execute dbms_advisor.set_default_task_parameter( ‚ADDM‘, ‚DBIO_EXPECTED‘, 8000)
Per Default werden die allgemeinen Datenbankstatistiken als Snapshot in einem Intervall von 60 Minuten durchgeführt. Diese werden in dem Automatic Workload Repository (AWR) des Users SYS in dem Tablespace SYSAUX gespeichert und 8 Tage lang vorgehalten. Die Auswertung dieser Daten werden von dem ADDM unmittelbar nach jedem AWR-Snapshot ermittelt und analysiert. Nach diesen 8 Tagen werden die Snapshot Statistiken im AWR über die Maintenance Tasks, die schon bei der Installation der Datenbank aktiviert werden, automatisch gelöscht.
Der AWR in der Oracle Datenbank 12c speichert das Real-Time_SQl_Monitoring, das Monitoring der Datenbankoperationen und Real-Time ADDM Reports in der Datenbank ab. Damit können nachträglich Änderungen in der Ausführung der SQL-Abfragen untersucht werden.
Wie sieht dabei die Speicherplatzbelegung aus?
Die aktuelle Speicherplatzbelegung der Statistikdaten im SYSAUX-Tablespace wird mit der folgenden SQL-Prozedur nach den einzelnen Komponenten aufgeschlüsselt und als Report abgespeichert:
SQL>$ORACLE_HOME/rdbms/admin/awrinfo.sql.
Wo werden AWR-, ASH- und ADDM-Performancedaten gespeichert und wer kann sie sehen?
| Feature | Speicherort | Sichtbar in Root | Sichtbar in PDB | PDB Daten werden entfernt wenn unplugged |
|---|---|---|---|---|
| ASH | Root | Ja | Nur PDB-Daten | Nein |
| ADDM-Daten | Root | Ja | Nein | Nein |
| AWR-Daten | Root | Ja | Nur PDB-Daten | Nein |
| Optimizer-Statistikdaten | Root/PDB | Ja | Auf PDB-Daten eingeschränkt | Ja |
| Segment-Advisordaten | Root/PDB | Ja | Auf PDB-Daten eingeschränkt | Ja |
Die Informationen zu den Top 25-Sessions werden über das Diagnosewerkzeug Active Session History (ASH) im Sekundenbereich ermittelt. Statistiken und Reports zu den Statistiken können manuell zu jeder Zeit erstellt werden.
Der Prozess Manageability Monitor (MMON) sammelt Snapshots und speichert sie im SYSAUX-Tablespace des Users SYS ab. Die hier gesammelten Daten sind über DBA-Views ersichtlich. Die Komponente ADDM interpretiert diese Daten zyklisch und macht Vorschläge, die über Advisory-Views oder über GRID Control (OEM) ausgewertet werden können. Der Manageability Monitor Lite Process (MMNL) unterstützt den MMON-Prozess, wenn der ASH-Buffer voll ist oder ein AWR-Snapshot gezogen wird, indem er die Daten aus dem ASH-Buffer in die AWR-Tabellen schreibt. Der Default Parameter des AWR-Snapshot-Intervalls kann von einer Stunde auf 10 Minuten reduziert werden. Die ADDM-Analyse läuft jeweils anschließend an.
Real-Time ADDM
Der Real-Time ADDM analysiert die Performance ähnlich wie der reguläre ADDM, der nach jedem AWR-Snapshot angestoßen wird und die AWR-Snapshots analysiert. Er liefert Diagnose-Ergebnisse und macht Vorschläge, um die Performance zu verbessern. Der Real-Time ADDM benutzt nicht die AWR-Snapshots sondern die aktuellen Aktivitäten aus den SGA Daten. Der Anmelde-Modus an die Datenbank mit SYSDBA-Privileg ist abhängig von dem Status der Instanz. Es wird automatisch der Diagnostic Modus gewählt, wenn die Anmeldung an der Instanz nicht mehr möglich ist. Der Normale Modus wird gewählt, wenn eine Anmeldung an der Instanz keine Probleme bereitet. Die Sammlung der aktuellen Performance Daten aller Datenbank-Instanzen aus der SGA werden analysiert. Die Daten werden für die Bereiche untersucht, die blockiert oder lahmgelegt worden sind, weil sie starke Zugriffskonflikte auf lokale oder globale Ressourcen aufweisen. Bereiche mit ungewöhnlich hoher Datenbankaktivität werden aufgezeigt und Analyseergebnisse geliefert.
Nachdem die Analyse beendet ist, können die Real-Time ADDM Findings abgefragt werden. In 12c werden die Pluggable Datenbanken (PDBs) vom ADDM mit den Analyseergebnissen einzeln aufgeführt. Der Real-Time ADDM findet heraus, dass eine Session beendet (killed) werden muss, weil ein Logon Trigger eine Tabelle locken will, die bereits gelockt ist. Real-Time ADDM identifiziert Langläuferabfragen, die fast 100% der Ressourcen benötigten. Die Tabelle v$longops wird dafür ausgelesen. In Abb.2 sind weitere ADDM-Kriterien aufgeführt. Im Oracle Enterprise Manager (OEM) – in der zentralen Advisory-Seite – können die ADDM Analyse Tasks angesehen werden, eine neue Analyse gestartet, sowie ein aktueller oder historischer Bericht mit den Analyseergebnissen und Empfehlungen angesehen werden.
ADDM Reports starten
SQL> @?/rdbms/admin/addmrpt.sql
ADDM Reports ansehen:
SQL> DBMS_ADDM.GET_REPORT function: SQL> execute DBMS_ADDM.GET_REPORT ( task_name IN VARCHAR2 RETURN CLOB); SQL> SET LONG 1000000 PAGESIZE 0; SQL> SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL; SQL> Select DBMS_ADDM.REAL_TIME_ADDM_REPORT() from dual; REAL_TIME_ADDM_REPORT -------------------------------------------------------- <report db_version="12.1.0.1.0" inst_count="1" cpu_cores="4" hyperthread="N" con
Ziel der ADDM Analyse ist es, bei gleichen Ressourcen von CPU und Hardware die Datenbankzeit zu reduzieren, um mehr User-Anfragen in der gleichen Zeit unterstützen zu können und den Durchsatz der Datenbank zu erhöhen. Abb.2 zeigt die Bereiche, die vom ADDM untersucht werden.
Dabei werden Top-Down die Hauptkonsumenten der Ressourcen analysiert. Bei dem ersten gefundenen Symptom wird nach der Ursache geforscht. ADDM benutzt dabei die DB-Time, um Performance-Engpässe zu finden. In den oben angezeigten Bereichen (Abb.3) werden in einem ADDM Report die System-Ressourcen angezeigt, die signifikante Zeit verbraucht haben. Danach wird absteigend nach der Zeit sortiert. Die DB-Time-Statistik wird nach der Time Model Statistik bemessen. Unter DB-Time versteht man die Gesamtzeit aller User-Prozesse, die in Ihrer Datenbankabfrage entweder aktiv arbeiten oder aktiv warten. Sie ist ein Indikator für den Total Instance Workload.
Die Statistik der aktiven Sessions wird einmal pro Sekunde gesammelt. Der Durchschnitt aller aktiven Sessions in der Datenbank zum Zeitpunkt X wird folgendermaßen berechnet:
Average Active Sessions=Total DB-Time/ Wall Clock Elapsed Time
Die DB-Time beinhaltet nur Vordergrund-Sessions inklusive CPU-Zeit, IO-Zeit und Wartezeit. Die Idle Wait Time wird dabei ausgeschlossen.
Real-Time ADDM Connection Modi
In Abhängigkeit des Status der Datenbank benutzt ADDM zwei unterschiedliche Connection-Modi im Enterprise Manager. Die normale JDBC-Connection wird benutzt, um umfangreiche Performanceanalysen durchzuführen. Wenn die Datenbank steht und eine normale Anmeldung nicht möglich ist, wird der Diagnose Anmelde-Modus mit einer latchlosen Verbindung durchgeführt. In diesem Modus werden extreme Hang-Situationen analysiert, ohne exklusiven Zugriff auf geschützte Ressourcen und Strukturen, wenn keine JDBC-Anmeldung mehr möglich ist. Vor 12c musste die Datenbank oder der Rechner durchgestartet werden. Jetzt schlägt ADDM Lösungen ohne Neustart der Datenbank vor und unterstützt bei der Ursachenforschung. Die Analyse-Informationen stammen in Echtzeit aus der SGA und beinhalten blockierende Sessions, Deadlocks, Hangs, Shared Pool-Connections, Objekt-Locks, Top-Aktivitäten und weitere Ausnahmesituationen.
Real-Time ADDM-Trigger oder Spot-ADDM
Ab Oracle 12c ermittelt der Real-Time ADDM proaktiv Performanceprobleme. Der Manageability Monitor (MMON) sammelt automatisch alle 3 Sekunden Performancestatistiken ohne Lock und Latches aus der SGA und benutzt die In-Memory Data. Es erfolgt eine Diagnose über Performancepeaks in der Datenbank. Der MMON Slave-Prozess kreiert den ADDM-Report und speichert ihn in das AWR-Repository ab. Die Metadaten des Reports stehen in den DBA_HIST_REPORT- und ADDM-Views. Der MMON-Prozess prüft die Statistiken und "triggert" eine Real-Time ADDM-Analyse an, wenn die in Abb.3 gezeigten Vorfälle oder Probleme auftreten.
Real-Time ADDM-Trigger-Kontrolle
Damit der Automatic Trigger nicht zu viel Systemressourcen verbraucht, prüft der Real-Time ADDM das Zeitdelta zwischen 2 Real-Time ADDM-Reports. Ist das Intervall kleiner als 5 Minuten, so wird verhindert, dass ein neuer Report erzeugt wird. In einer RAC-Umgebung zieht die Oracle RAC-Kontrolle, indem der Real-Time ADDM-Report nur auf einer Instance läuft. Dabei generiert der MMON-slave process ein Lock. Es findet eine Prüfung statt, ob der ADDM-Report schon auf einer anderen Instance läuft. Ist die Prüfung negativ, wird der Report generiert.
Repeated Trigger
Repeated Trigger sind Automatische Trigger für Performanceprobleme, die angestoßen werden, wenn die Anzahl der aufgetretenen Probleme 100% oder höher im Vergleich zu dem vorherigen Report innerhalb der letzten 45 Minuten liegt. Zum Beispiel gibt es aktuell TOP 8 aktive SQL. Innerhalb der nächsten 45 Minuten sind 16 Top aktive Sessions aufgetreten. Oder es tritt ein neues Problem auf, das nicht in den letzten 45 Minuten aufgetreten ist. Wenn beispielsweise ein erneuter Dead-Lock auftritt, wird ein neuer Report generiert.
Compare ADDM
Als Vorbereitung der Performanceanalyse sollten sogenannte Baseline AWR-Snapshots erstellt werden, die das zu untersuchende System bei normalem Zustand oder Augenblicken der guten Performance widerspiegeln. Baselines können mit dem Oracle Enterprise Manager erstellt werden oder mit dem AWR Baseline Package. Oracle empfiehlt, in Zeiten guter Performance eine Baseline als Referenz zu ziehen, damit diese in Zeiten schlechter Performance herangezogen werden kann.
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE start_snap_id => 270, end_snap_id => 280, baseline_name => 'peak baseline', dbid => 3310949047, expiration => 30);
Der Vergleichs-ADDM wird berechnet, indem die o. g. Baseline-Periode mit dem gewählten Zeitraum verglichen wird. Es können auch zwei beliebige Zeiträume miteinander verglichen werden. Dabei wird folgende Vorgehensweise priorisiert:
Es wird nach Systemänderungen im Bereich Datenbankkonfiguration, Workload- Änderung und Sql-Änderung gesucht, die das Performanceproblem hervorgerufen haben könnten. Eine ADDM-Analyse wird für die beiden Zeiträume gestartet. Dann wird der Unterschied der beiden Perioden gemessen. Schließlich werden die Ergebnisse nach vordefinierten internen Regeln verglichen. Eine Vergrößerung des Datenbankparameters SGA_TARGET kann den I/O-Durchsatz erhöhen und somit den WORKLOAD. Es werden init.ora- Parameteränderungen herausgefunden und Änderungen vorgeschlagen. Änderungen in der Hardware und Software können angezeigt werden. Danach werden Vorschläge gemacht.
Compare ADDM im Oracle Enterprise Manager Cloud Control 12c
Wir gehen auf die Performanceseite, wählen AWR, ADDM über Vergleichszeitraum, ADDM Analyse. Hier können jetzt 2 Zeiträume oder ein aktueller Zeitraum zum Basiszeitraum verglichen werden.
Es folgt zum Abschluss ein Beispielbericht mit einer ADDM-Empfehlung:
ADDM-Bericht für Task 'ADDM:1181675085_1_988' --------------------------------------------- Analysezeitraum --------------- AWR-Snapshot-Bereich von 987 bis 988. Zeitraum beginnt um 05.11.14 13:00:50 Zeitraum endet um 05.11.14 14:00:51 Analyseziel ----------- Datenbank 'TEST' mit DB-ID 123456789 Datenbankversion 11.2.0.4.0. ADDM hat Instanz TEST mit der Nummer 1 auf test-db analysiert. Aktivität während des Analysezeitraums -------------------------------------- Gesamte Datenbankzeit betrug 2412 Sekunden. Die durchschnittliche Anzahl von aktiven Sessions betrug ,67. Zusammenfassung der Ergebnisse ------------------------------ Beschreibung Aktive Sessions Empfehlungen Prozent der Aktivität -------------------------------------------- --------------------- ------------ 1 Hard Parse aufgrund von literaler Auslastung ,22 | 32,86 1 2 Top SQL-Anweisungen ,07 | 10,43 3 3 Soft Parse ,02 | 3,51 2 4 PL/SQL-Ausführung ,02 | 3,04 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Ergebnisse und Empfehlungen --------------------------- Ergebnis 1: Hard Parse aufgrund von literaler Auslastung Auswirkung ist ,22 aktive Sessions, 32,86\% der Gesamtaktivität. ---------------------------------------------------------------- SQL-Anweisungen wurden wegen der Verwendung von Literalen nicht gemeinsam benutzt. Dies hat zu zusätzlichen Hard Parses geführt, die wesentliche Datenbankzeit belegt haben. Empfehlung 1: Anwendungsanalyse Geschätzter Vorteil beträgt ,22 aktive Sessions, 32,86\% der Gesamtaktivität. --------------------------------------------------------------------------- -- Aktion Überprüfen Sie die Anwendungslogik auf die mögliche Benutzung von Bindevariablen anstelle von Literalen. Aktion Alternativ können Sie den Parameter "cursor_sharing" auf "force" festlegen. Begründung Es wurde ermittelt. dass mindestens 5 SQL-Anweisungen mit FORCE_MATCHING_SIGNATURE 12961165857754784729 und PLAN_HASH_VALUE 3674228654 Literale benutzt haben. Ein Beispiel ist die SQL-Anweisung mit SQL_ID "abap1443sp1g5" . Beziehungsobjekt SQL-Anweisung mit SQL_ID abap1443sp1g5. Begründung Es wurde ermittelt. dass mindestens 5 SQL-Anweisungen mit FORCE_MATCHING_SIGNATURE 12961165857754784729 und PLAN_HASH_VALUE 3674228654 Literale benutzt haben. Ein Beispiel ist die SQL-Anweisung mit SQL_ID "cjup292c5xyxn" . Beziehungsobjekt SQL-Anweisung mit SQL_ID cjup292c5xyxn. Symptome, die zu dem Ergebnis geführt haben: -------------------------------------------- Hard Parsing von SQL-Anweisungen hat wesentliche Datenbankzeit belegt. Auswirkung ist ,22 aktive Sessions, 33,21\% der Gesamtaktivität.
