Datenbanken Online Lexikon TH Köln, Campus Gummersbach
Aktuelle Änderungen - Suchen:

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