Werkzeuge zum SQL-Tuning (Oracle)


Ablauf vom Automatic Tuning Optimizer (Quelle: www.doag.org)

Oracle 11g enthält viele Werkzeuge, die im Bedarfsfall eine schnelle Analyse von Performance-Problemen ermöglicht. Einige dieser Werkzeuge arbeiten voll automatisch und werden über den Scheduler gesteuert.

Einige wichtigen Werkzeuge sind:

  • Automatic Tuning Optimizer (ATO)
  • Wait Monitoring
  • Real Time SQL Monitoring
  • Automatic Workload Repository Monitor (AWR)
  • Automatic Database Diagnostic Monitor (ADDM)
  • Automatic SQL Tuning
  • SQL Tuning Sets (STS)
  • SQL Access Advisor
  • SQL Plan Management (SPM)
  • SQL Management Base (SMB)

Automatic Tuning Optimizer (ATO)

Beim Automatic Tuning Optimizer handelt es sich um den Optimizer von Oracle, der Ausführungspläne für die vorgelegten SQL-Anweisungen erzeugt. Der Optimizer kann in normalem Modus oder im Tuning-Modus arbeiten. In dem normalen Modus kompiliert der Optimizer die SQL-Anweisung und erzeugt einen Ausführungsplan. Der normale Modus erzeugt einen angemessenen Plan für die große Mehrheit von SQL-Anweisungen. Unter dem normalen Modus funktioniert der Optimizer mit sehr strengen Zeitvorgaben. Dafür hat er gewöhnlich nur ein Bruchteil einer Sekunde, um einen angemessenen Ausführungsplan zu berechnen und auszuwählen. Im Tuning-Modus wird der Optimizer als Automatic Tuning Optimizer (ATO) bezeichnet. In diesem Modus führt der Optimizer zusätzliche Analysen durch, um zu überprüfen, ob der im normalen Modus erzeugte Ausführungsplan weiter verbessert werden kann. Beim Automatic Tuning Optimizer werden die statistische Analyse, das SQL-Profiling, die Zugriffspfad-Analyse und die SQL-Struktur-Analyse durchgeführt, die sehr zeit-und ressourcenintensiv sind. Der Automatic Tuning Optimizer soll für komplexe und hoch belastende SQL-Anweisungen verwendet werden, die nicht-triviale Auswirkungen auf das gesamte System haben. Unter dem Tuning-Modus kann der Optimizer mehrere Minuten benötigen, um ein einzelne SQL-Anweisung zu tunen. Der Output des Automatic Tuning Optimizers ist nicht ein Ausführungsplan, sondern stellt eine Reihe von Aktionen dar, die zusammen mit ihrem Grundprinzip und erwartetem Vorteil genutzt wird, um einen deutlich besseren Plan zu erzeugen.

Wait Monitoring

Mit Wait Monitoring kann die Anzahl von aufgetretener Waits und Timeouts des Systems beobachtet werden. Waits und Timeouts sind Wartezustände, die in der View v$system gespeichert werden. Im folgenden Beispiel wird gezeigt, wie man mit SQL-Abfrage auf diese Daten zugreifen kann:

 SELECT event, total_waits, total_timeouts, time_waited, wait_class
 FROM v$system_event
 WHERE wait_class != 'Idle' AND time_waited >= 1000;

Wait-Monitoring (Quelle: www.doag.org)

In der oberen SQL-Abfrage wurde Idle-Events ausgeschlossen, weil sie nicht mit der Nutzung von Ressourcen in Verbindung stehen. Zu den Wartezuständen Idle-Events gehört z.B. die Wartezeit auf die Nachricht eines Clients.

Real Time SQL Monitoring

Real Time SQL Monitoring dient der Analyse von aktuellem Performance-Problem. Dieses Werkzeug untersucht SQL-Anweisungen, die gerade ausgeführt werden. Das SQL Monitoring wird automatisch gestartet, wenn eine SQL-Anweisung mehr als fünf Sekunden CPU- oder I/O-Zeit benötigt. Damit Real Time SQL Monitoring aktiviert wird, muss der Parameter STATISTICS_LEVEL auf ALL oder TYPICAL und der Parameter CONTROL_MANAGEMENT_PACK_ACCESS auf DIAGNOSTIC+TUNING gesetzt werden. Die gesammelte Statistiken für das Real Time SQL Monitoring werden in die Views v$sql_plan_monitor und v$sql_monitor gespeichert. Man kann diese Views mit anderen Views, z.B. v$sql, v$sql_plan, v$session, v$active_session_history verknüpft werden, um umfassende Informationen zu erhalten. Mit der Prozedur REPORT_SQL_MONITOR des Packages DBMS_SQLTUNE kann ein Report in den Formaten Text, HTML oder XML? erstellt werden. Im folgenden Beispiel wird die Erzeugung eines Reports in der HTML-Format dargestellt:

 SQL> set long 1000
 SQL> set pages 3000
 SQL> spool c:\temp\report.html

 SQL>
 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (
  type => 'html',
  sql_id => '65xfp4kbpnc5x';
 ) AS sql_monitor FROM DUAL;

 SQL> spool off

Ablauf des Automatic SQL Tuning von Oracle (Quelle: docs.oracle.com)

Automatic Workload Repository Monitor (AWR) und Automatic Database Diagnostic Monitor (ADDM)

Der Automatic Workload Repository Monitor (AWR) speichert statistische Informationen zur Datenbank und deren Performance in einer Form von Performance-Snapshots. Der Automatic Database Diagnostic Monitor (ADDM) nutzt die Performance-Snapshots vom Automatic Workload Repository, um die Analyse von SQL-Anweisungen inklusive I/O und CPU-Nutzung zu ermöglichen. Das Ziel des ADDM ist die Verarbeitungszeiten innerhalb der Datenbank zu minimieren.

Automatic SQL Tuning

Das Automatic SQL Tuning von Oracle besteht aus dem SQL Tuning Advisor und dem Automatic Database Diagnostic Monitor (ADDM). Der ADDM identifiziert in dem Automatic Workload Repository (AWR) die SQL-Anweisungen mit schlechter Performance. Der SQL Tuning Advisor erzeugt dann eine Liste dieser SQL-Anweisungen in der Reihenfolge ihrer Wichtigkeit, die für das Tuning geeignet sind. Für jede einzelne SQL-Anweisung werden Empfehlungen zur Verbesserung der Performance generiert. Während des Optimierungsprozesses werden alle Arten von Empfehlungen berücksichtigt und getestet. Dabei werden Reporte (Automatic SQL Tuning Reports) über die Ergebnisse erzeugt, die während und nach dem Optimierungsprozess angezeigt werden können. Diese Reporte beschreiben alle analysierten SQL-Anweisungen mit den generierten Empfehlungen und den automatisch implementierten SQL-Profilen. Die Implementierung der Empfehlungen muss von Entwicklern vorgenommen werden. Nur SQL Profile können automatisch implementiert werden und das ist auch nur möglich, wenn die Leistungsverbesserung um dreifach größer höher als die aktuelle Leistung der SQL-Anweisung ist. Die SQL Profile sollen überprüft werden, indem die SQL-Anweisungen mit und ohne die SQL Profile ausgeführt werden. Wenn die Performance verbessert wird, dann sollen die SQL Profile implementiert werden. Die SQL Profile, die automatisch implementiert wurden, enthalten den Wert AUTO in der Spalte TYP in der View DBA_SQL_PROFILES.


Ausführungsablauf von SQL Tuning Sets (Quelle: docs.oracle.com)

SQL Tuning Sets (STS)

Bei den SQL Tuning Sets (STS) handelt es sich um manuelles SQL-Tuning?, das über das Package DBMS_SQLTUNE durchgeführt wird. STS spielen eine große Rolle, weil sie übertragen werden können und somit ist das Tuning auch in einer Test-Umgebung möglich.

Ein SQL Tuning Set wird durch die Prozedur CREATE_SQLSET aus dem Package DBMS_SQLTUNE erzeugt. Das muss nur gemacht werden, wenn mehrere SQL-Anweisungen analysiert werden sollen. Um ein SQL Tuning Set zu analysieren, muss dann ein Tuning Task erstellt werden. Dafür benötigt man die Prozedur CREATE_TUNING_TASK aus dem Package DBMS_ADVISOR. Dann muss der Analyse-Task mit der Prozedur DBMS_SQLTUNE.EXECUTE_TUNING_TASK ausgeführt werden. Die Ergebnisse der Analyse werden durch die Prozedur DBMS_SQLTUNE.REPORT_TUNING_TASK angezeigt. Dann können die Empfehlungen entsprechend der Ergebnisse implementiert werden.

SQL Access Advisor

Den SQL Access Advisor gibt es seit Oracle 10g. Er hilft bei der Optimierung? von Zugriffsstrukturen, besonders bei materialisierten Views, Indizes und materialisierten View-Logs. Dieses Werkzeug gibt Empfehlungen zur Verbesserung der Leistung einer Datenbank durch die Partitionierung bestehender Tabellen und Indizes. Um die Empfehlungen umzusetzen, erzeugt der SQL Access Advisor ein Skript, das SQL-Anweisungen analysiert. Dafür benötigt man das Package DBMS_ADVISOR, das aus einer Sammlung von Analyse-, Empfehlungsfunktionen und Prozeduren besteht. Mit den Funktionen kann man eine Task (Aufgabe) erstellen, Arbeitsbelastung (Workload) definieren und Empfehlungen generieren, anzeigen, umsetzen und entfernen.

In der nebenstehenden Abbildung wird gezeigt, wie der SQL Access Advisor eine materialisierte Views für eine bestimmte Auslastung empfiehlt:


Ausführungsablauf von SQL Access Advisor (Quelle: docs.oracle.com)

SQL Plan Management (SPM)

Mit SQL Plan Management (SPM) können Veränderungen von Ausführungsplänen kontrolliert werden. Zuerst werden die Ausführungspläne als Baseline gespeichert. Wenn neue Optimizer-Statistiken vorhanden sind, prüft der Cost-Based-Optimizer, ob ein neuer Ausführungsplan eine bessere Performance verspricht als der in der Baseline gespeichert ist. Die Datenbank speichert eine Historie aller SQL-Ausführungspläne für SQL-Anweisungen, die mehrfach ausgeführt wurden. Die automatische Durchführung erfolgt, wenn der Parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE auf TRUE gesetzt ist. Oracle prüft dann während der Selektionsphase auf Planänderungen. Bei jeder neuen Übersetzung einer SQL-Anweisung prüft der Optimizer auf den besten Ausführungsplan und vergleicht diesen mit dem Ausführungsplan der Baseline. Neue Ausführungspläne werden der Planhistorie hinzugefügt. Erst wenn sichergestellt wurde, dass der Ausführungsplan zu keiner Verschlechterung der Performance führt, wird er eingesetzt. Diese Vorgänge werden in der SQL Management Base vorgenommen.

SQL Management Base (SMB)

Die SQL Management Base (SMB) ist Teil des Data Dictionary und wird im Tablespace SYSAUX gespeichert. In die SMB werden die Planhistorie (SQL Plan History), SQL Plan Baselines, Statement Logs und SQL Profile abgelegt. Ist die SMB nicht verfügbar, so können die Funktionen von SQL Plan Management nicht benutzt werden. Die SQL Management Base verwaltet die Ausführungspläne, die als Baselines gespeichert sind. Die Ausführungspläne von SQL-Abfragen, die eine bessere Performance aufweist und genutzt werden, werden in die SQL Plan Baseline abgelegt. Die anderen Ausführungspläne, die nicht verwendet werden, bleiben in der Planhistorie. Pläne, die länger als 53 Wochen nicht genutzt werden, werden automatisch entfernt.


Komponenten von SQL Management Base (Quelle: optimizermagic.blogspot.de)

Siehe auch: SQL-Tuning?, Optimizer, Ausführungsplan einer SQL-Abfrage, Ausführungsplanoperationen?, Statistiken, Hints

Quellen:

Kategorie: Tuning, W, S