SQL Plan Management in der Praxis

SQL Plan Management in der Praxis
Wilhelm Breßer
Herrmann & Lenz Services GmbH
Burscheid
Schlüsselworte:
Execution Plan, SPM, Cost Based Optimizer, Plan Stability
Ausführungsplan, kostenbasierter Optimizer, stabile Ausführungspläne
Einleitung
Die Stabilität und Performanz von Ausführungsplänen für SQL-Befehle ist ein kritischer
Faktor im täglichen Business. Leider kommt es immer wieder vor, dass Ausführungspläne
und damit die Performanz von SQL-Befehlen sich aus verschiedenen Gründen unerwartet
ändern. Lösungsansätze der Vergangenheit haben hier bisher nicht wirklich geholfen. Was
tatsächlich benötigt wird, ist die Möglichkeit, performante Ausführungspläne sicherzustellen.
Dies würde bedeuten, dass ein veränderter Ausführungsplan nur gespeichert bzw. angewendet
werden dürfte, wenn er eine verbesserte Performanz erwarten lässt. Oracle bietet zur
Erreichung dieses Zieles seit Version 11g (11.1.0.6.0) das sogenannte SQL Plan Management.
Diese Präsentation zeigt, wie SQL Plan Management grundsätzlich funktioniert und geht auch
auf bereits in der Praxis gesammelte Erfahrungen ein. Die Präsentation wird durch
verschiedene vorgeführte Beispiele ergänzt
Historie
In der Vergangenheit bot Oracle im Wesentlichen folgende Möglichkeiten, um stabile
Ausführungspläne sicherzustellen:
• Stored Outlines – Version 8i (Feature der Enterprise Edition)
• Sperren von Objektstatistiken (Version 9i)
• SQL Profile – Version 10g
Stored Outlines schreiben einen Ausführungsplan für ein bestimmtes SQL-Statement fest,
werden in der Datenbank gespeichert, sind mit vielen Problemen behaftet (zahlreiche Bugs)
und funktionieren auch nicht, wenn Cursor Sharing mit den Einstellungen SIMILAR oder
FORCE eingesetzt wird. Ein weiterer Nachteil besteht darin, dass immer derselbe
Ausführungsplan benutzt wird, auch wenn der Cost Based Optimizer in der Lage wäre,
aufgrund der Objekt- und Systemstatistiken einen besseren Plan zu generieren
Das Sperren von Objektstatistiken ist nicht völlig problemlos, da selbst gesperrte Statistiken
unter bestimmten Voraussetzungen überschrieben werden können. Außerdem wird damit
verhindert, dass der Optimizer durch verbesserte Objektstatistiken einen performanteren
Ausführungsplan erzeugen könnte.
Mit Release 10g wurden der SQL Tuning Advisor und ein SQL Access Advisor eingeführt.
Durch Nutzung des SQL Tuning Advisor ist es möglich, sogenannte SQL Profile zu erstellen.
Diese liefern dem Cost Based Optimizer Informationen, die ihm im "normalen" Modus nicht
zur Verfügung stehen. Die zusätzlichen Daten können vom Optimizer genutzt werden, um
performante Ausführungspläne zu generieren. Eine Planstabilität wird hiermit jedoch nicht
erreicht, da in den SQL Profilen keine Ausführungspläne gespeichert werden.
Der SQL Access Advisor wird vom SQL Tuning Advisor benutzt, kann aber auch separat
gestartet werden, um eine Optimierung von Zugriffspfaden zu erreichen (Indizes,
Materialized Views etc.).
SQL Tuning Advisor und SQL Access Advisor stehen sowohl im Enterprise Manager als
auch auf der Kommandozeile in SQL*PLUS zur Verfügung. Im Enterprise Manager setzt
deren Nutzung die Lizensierung von Database Diagnostic Pack und Tuning Pack voraus. Auf
der Kommandozeile kann das Package DBMS_SQLTUNE benutzt werden. Seit 10g Release 2
stellt Oracle im ORACLE_HOME unter /rdbms/admin die Skripte sqltrpt.sql und
dbmssqlt.sql zur Verfügung, was die Arbeit mit dem SQL Tuning Advisor deutlich
vereinfacht.
Problematik
Verschlechterte Ausführungspläne können zu massiven Performanz-Einbrüchen führen.
Bekannte Probleme sind beispielsweise die Änderung von Ausführungsplänen nach der
Aktualisierung von Objektstatistiken, inperformante Ausführungspläne nach einer Änderung
von Objekt-Metadaten oder schlechte Performanz nach Änderungen an der Umgebung
(Migration etc.). Ansätze der Vergangenheit, wie Stored Outlines, das Sperren von
Objektstatistiken oder auch SQL Profile haben diese Schwierigkeiten bisher nur unzureichend
gelöst.
Alle vorstehend aufgeführten Verfahren wirken postaktiv; d.h. sie kommen in Regel erst zur
Anwendung, wenn die Problematik eines verschlechterten Ausführungsplanes bereits
aufgetreten ist!
Oracle verspricht mit Release 11g nun, Möglichkeiten bereitzustellen, mit denen es möglich
ist, proaktive Maßnahmen zu treffen, damit die beschriebenen Probleme gar nicht erst
auftreten können.
Neuerungen in Release 11g
Mit Release 11g hat Oracle folgende neue Werkzeuge eingeführt:
• Pending Statistics
• Extended Column Statistics
• SQL Performance Analyzer (Bestandteil der Real Application Testing Option)
• Automatic SQL Tuning
• SQL Plan Management als Feature der Enterprise Edition
Dieser Vortrag befasst sich ausschließlich mit dem SQL Plan Management. Die abgebildeten
Beispiele wurden mit Enterprise Manager Database Control 11g und einer Datenbank Release
11.2.0.1.0 auf RHEL5 in einer virtuellen Umgebung erstellt.
Wie funktioniert SQL PLAN Management?
SQL Plan Management, kurz SPM, ist sowohl auf der Kommandozeile in SQL*PLUS als
auch über Enterprise Manager verfügbar und benutzt folgende ServerInitialisierungsparameter:
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES – Default: FALSE
• OPTIMIZER_USE_SQL_PLAN_BASELINES – Default: TRUE
Beide Parameter können zur Laufzeit mit ALTER SYSTEM bzw. ALTER SESSION
geändert werden.
Weist der Parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES die Ausprägung
TRUE auf, werden grundsätzlich SQL Plan Baselines und eine SQL Plan Historie erstellt.
Dieses Verfahren wird auch als "Auto Capture" bezeichnet. Damit dies geschieht, müssen
aber folgende Bedingungen erfüllt sein:
• Der Cost Based Optimizer muss benutzt werden
• Das SQL-Statement muss mindestens zweimal ausgeführt oder geparst werden
• Der SQL-Befehl darf nicht die Tabelle DUAL benutzen
• Es darf sich nicht um rekursives SQL handeln
Neben den SQL-Befehlen, die in einem Log protokolliert werden, erfolgt die Sammlung der
jeweiligen Ausführungspläne samt Statistiken in der SQL Management Base, die ein
Repository darstellt und im Tablespace SYSAUX abgelegt ist. SQL-Befehle und SQL-Log
bilden eine sogenannte SQL Plan Historie, in der außerdem weitere vom Cost Based
Optimizer für die Erstellung des Ausführungsplanes verwendeten Informationen, wie
beispielsweise Bindevariablen und die beim Parsen benutzten Umgebungsvariablen,
aufgezeichnet werden. Aufgrund der Informationen im SQL-Log erkennt der Optimizer, ob es
sich um ein wiederholtes Statement handelt.
SPM kennt verschiedene Stati für SQL Plan Baselines, die auch in der nachstehenden
Abbildung zu sehen sind:
Abb. 1: Stati im SQL Plan Management
Diese haben folgende Bedeutungen:
• ENABLED - die SQL Historie zu diesem SQL-Befehl ist aktiv, der SQL
Ausführungsplan kann also zu einer SQL Plan Baseline mit den Stati "Accepted" oder
"Fixed" werden bzw. zu einer existierenden Baseline hinzugefügt und dann vom Cost
Based Optimzer berücksichtigt werden.
• ACCEPTED – die SQL Plan Baseline kann vom Optimizer verwendet werden. Nur die
in der SQL Management Base mit diesem Status oder dem Status "FIXED",
vorhandenen Ausführungspläne werden als SQL Plan Baseline bezeichnet.
• FIXED – die SQL Plan Baseline soll vom Optimizer immer für das entsprechende
SQL-Statement benutzt werden. Eine Baseline mit diesem Status hat Vorrang vor
Baselines mit dem Status "ACCEPTED".
• AUTO PURGE – die SQL Plan Baseline bzw. SQL Historie wird nach der
eingestellten Retention Policy (Default: 53 Wochen) automatisch gelöscht, sofern sie
nicht benutzt wird.
Der erste zu einem SQL-Befehl mit "Auto Capture" gesammelte SQL Ausführungsplan erhält
immer die Stati "ENABLED" und "ACCEPTED", wird also automatisch zur SQL Plan
Baseline. Alle weiteren Ausführungspläne bekommen nur den Status "ENABLED".
Die Details zu einer SQL Plan Baseline bzw. einem SQL Ausführungsplan kann man sich
anzeigen lassen, indem man das entsprechende Element auswählt (siehe "Select" in
Abbildung 1) und dann den Hyperlink zu "Name" oder "SQL Text" anklickt. Das Ergebnis
sieht dann beispielsweise wie folgt aus:
Details zu SQL-Plan-Baseline
Abb. 2: Details einer SQL Plan Baseline
Neben "Auto Capture" besteht noch die Möglichkeit, SQL Plan Baselines mittels eines "Bulk
Loading" in die SQL Management Base zu laden. Dieses Verfahren wird später im Vortrag
beschrieben.
Wie arbeitet "Auto Capture"?
Bei der ersten Ausführung eines SQL-Befehls wird dieser zunächst geparst, in die SQL Plan
Historie eingetragen und ausgeführt. Zusätzlich wird, wenn der Initialisierungsparameter
OPTIMIZER_USE_SQL_PLAN_BASELINES den Wert TRUE aufweist, was dem Default
entspricht und der Befehl zum zweiten Mal ausgeführt wird, geprüft, ob in den existierenden
SQL Plan Baselines ein passender Ausführungsplan vorhanden ist. Falls ja, wird dieser
benutzt. Ist kein passender Plan vorhanden, wird ein solcher erstellt, als SQL Plan Baseline in
der SQL Management Base abgelegt und das Statement ausgeführt. Im Einzelnen stellt sich
der Ablauf wie nachstehend abgebildet dar:
Abb. 3: Ablauf "Auto Capture"
Wird zu einem SQL-Befehl bei erneuter Ausführung ein weiterer Ausführungsplan gefunden,
erfolgt dessen Speicherung in der SQL Plan Historie. Allerdings erhält dieser nicht den Status
"ACCEPTED", sondern nur den Status "ENABLED". Für die Ausführung des Statements wird
der in der SQL Plan Baseline gespeicherte Ausführungsplan verwendet.
Wenn ein Ausführungsplan aus einer SQL Plan Baseline benutzt werden soll, muss dieser den
Status "ACCEPTED" oder "FIXED" haben. Befinden sich mehrere Ausführungspläne in einer
Baseline, entscheidet der Cost Based Optimizer sich für den Plan mit den niedrigsten Kosten.
Statusänderungen eines SQL Ausführungsplanes
SQL Ausführungspläne, die nur den Status "ENABLED" haben, werden nicht automatisch
"ACCEPTED" und somit für den Cost Based Optimizer (CBO) als SQL Plan Baseline
verfügbar gemacht. Bevor der CBO einen SQL Ausführungsplan nutzen kann, muss dieser
verifiziert werden. Dieser Vorgang wird auch als "Evolving" bezeichnet. Eine Verifizierung
ist nur möglich, wenn der SQL Ausführungsplan den Status "ENABLED" hat.
Ein nicht akzeptierter SQL Ausführungsplan kann "UNVERIFIED", "VERIFIED" oder
"REJECTED" sein. Diese Stati haben folgende Bedeutungen:
• UNVERIFIED – Der Ausführungsplan wurde neu gefunden, aber noch nicht
verifiziert oder abgelehnt
• VERIFIED – Ein neuer Ausführungsplan wurde verifiziert und akzeptiert
• REJECTED – Ein neuer Ausführungsplan wurde verifiziert, aber abgelehnt, da bereits
eine Baseline mit einem performanteren Ausführungsplan in der SQL Management
Base existiert.
Das Verifizieren eines SQL Ausführungsplanes läuft folgendermaßen ab:
Abb. 4: Verifikation eines SQL Ausführungsplanes – Schritt 1
Der erste Schritt besteht darin, den noch nicht akzeptierten Ausführungsplan auszuwählen und
auf "EVOLVE" zu klicken, wodurch das nachstehend abgebildete Fenster geöffnet wird.
Abb. 5: Verifikation eines SQL Ausführungsplanes – Schritt 2
Wird hier "Yes" bei "Verify Performance" ausgewählt, erfolgt keine Ausführung des SQLStatements unter Nutzung des in der SQL Historie hinterlegten Ausführungsplanes, sondern
lediglich ein Vergleich der zu den einzelnen Statementausführungen gespeicherten
Statistiken, wie beispielsweise Buffer Gets, Disk Reads und CPU Time.
Mit "Time Limit" lässt sich festlegen, ob Oracle selbst bestimmt, wie lange der Vorgang
dauern soll ("Auto"), ob keine Zeitbegrenzung vorgegeben wird ("Unlimited") oder der
Vorgang eine vorbestimmte Maximaldauer haben soll ("minutes").
Durch "Action" kann bestimmt werden, ob der SQL Ausführungsplan automatisch den Status
"ACCEPTED" erhalten soll, falls er eine bessere Performance verspricht, als Pläne in bereits
vorhandenen Baselines ("Report and Accept"), oder ob lediglich das Ergebnis des
Verifikationsvorganges angezeigt werden soll ("Report only").
Durch einen Klick auf "OK" wird der Vorgang gestartet. Das Ergebnis der Verifikation wird
in einem Bericht angezeigt.
Abb. 6: Verifikation eines SQL Ausführungsplanes – Bericht
In diesem Beispiel ist die Performance des verifizierten SQL Ausführungsplanes um den
Faktor 8,94 schlechter, als die der Referenz-Baseline. Es ist, auch anhand der Dokumentation,
nicht nachvollziehbar, wie der Faktor 8,94 ermittelt wird. Verifikationen anderer
Ausführungspläne lieferten ebenfalls nicht nachvollziehbare Ergebnisse. Auch wenn in Schritt
2 als "Action" "Report and Accept" gewählt worden wäre, was im Report als "COMMIT =
YES" stehen würde, hätte Oracle diesen Plan aufgrund des Ergebnisses nicht auf den Status
"ACCEPTED" gesetzt und somit zu einer SQL Plan Baseline gemacht.
Falls man versucht, einen Ausführungsplan zu verifizieren, der "DISABLED" ist ("Enabled
= NO), wird der Report mit dem Hinweis "It is a disabled plan." erzeugt und
keine Verifikation durchgeführt.
Arbeiten mit fixierten SQL Ausführungsplänen
SQL Plan Management ermöglicht es, SQL Ausführungspläne als SQL Plan Baseline zu
"fixieren". Dies hat folgende Auswirkungen:
• Es wird nur der gefixte Ausführungsplan benutzt.
• Existieren innerhalb der Baseline mehrere derartige Ausführungspläne, wird der mit
den niedrigsten Kosten benutzt.
• Befinden sich innerhalb der Baseline auch nicht gefixte Ausführungspläne, werden
diese nicht mehr berücksichtigt; dies gilt auch, wenn ein solcher Plan niedrigere
Kosten aufweisen sollte, als ein gefixter Plan.
• Falls ein gefixter Plan nicht benutzt werden kann, weil beispielsweise ein darin
enthaltener Index "UNUSABLE" ist, benutzt der Cost Based Optimizer den mit den
geringsten Kosten in der SQL Plan Baseline vorhandenen, nicht gefixten Plan.
Ein SQL Ausführungsplan kann wie nachstehend beschrieben als "FIXED" Baseline
gespeichert werden:
Abb. 7: "Fixen" eines SQL Ausführungsplans
Zunächst ist der gewünschte Ausführungsplan auszuwählen. Dann ist in der Drop Down Liste
"Fixed – Yes" zu selektieren. Mit "Go" wird der Vorgang gestartet.
Abb. 8: Ergebnis der Fixierung eines SQL Ausführungsplans
Bei dem gefixten SQL Ausführungsplan kann es sich um einen bereits akzeptierten
(ACCEPTED = YES) oder einen nicht akzeptierten (ACCEPTED = NO) Plan handeln. Der
Plan muss lediglich "ENABLED" sein. Wird ein nicht akzeptierter SQL Ausführungsplan
"fixiert", wird dieser jedoch vom Cost Based Optimizer noch nicht berücksichtigt. Der Status
dieses Plans muss erst durch eine Verifikation auf "ACCEPTED" gesetzt werden.
Bulk Loading
Neben "Auto Capture" besteht noch die Möglichkeit, SQL Plan Baselines mittels eines "Bulk
Loading" in die SQL Management Base zu laden. Dabei können SQL Tuning Sets, Cursor aus
dem Cursor Cache und SQL Plan Baselines aus einer Staging Tabelle benutzt werden.
Lässt man sich bei diesen Verfahren die Details zu einer Baseline anzeigen, steht im
Gegensatz zur Methode "Auto Capture" als "Origin" "MANUAL-LOAD" anstelle von "AUTOCAPTURE" (siehe Abbildung 2).
Laden unter Nutzung von SQL Tuning Sets
Dieses Verfahren erfordert, zunächst ein SQL Tuning Set (STS) zu definieren und die
gewünschten Ausführungspläne in das STS zu laden.
Anschließend kann das Tuning Set mit Enterprise Manager Database Control oder auf der
Kommandozeile mit SQL*PLUS geladen werden. Dadurch erfolgt automatisch die Erstellung
einer SQL Plan Baseline bzw. das Hinzufügen zu einer solchen. SQL Tuning Sets, die
geladen werden, erhalten somit auch die Stati "ENABLED" und "ACCEPTED" und können
dadurch vom Cost Based Optimizer berücksichtigt werden.
Laden unter Nutzung des Cursor Cache
Bei dieser Methode ist zunächst die SQL_ID des entsprechenden SQL-Statements zu
ermitteln. Diese kann beispielsweise aus der Tabelle V$SQL entnommen werden.
Anschließend kann der Ausführungsplan auf der Kommandozeile mit SQL*PLUS oder mit
Enterprise Manager Database Control geladen werden. Dies bewirkt die automatische
Erstellung einer SQL Plan Baseline bzw. das Hinzufügen zu einer solchen. SQL
Ausführungspläne, die geladen werden, erhalten somit ebenfalls die Stati "ENABLED" und
"ACCEPTED" und können dadurch vom Cost Based Optimizer berücksichtigt werden.
Laden von Ausführungsplänen aus anderen Datenbanken
Oracle bietet die Möglichkeit, Ausführungspläne aus anderen Datenbanken (ab Release 10g)
unter Nutzung einer sogenannten Staging-Tabelle zu laden und für SPM zu nutzen.
Dies erfordert das Anlegen einer solchen Tabelle. Dafür steht mit Release 11g im Package
DBMS_SPM die Prozedur CREATE_STGTAB_BASELINE zur Verfügung. In Release 10g ist
hierfür die Prozedur CREATE_STGTAB_SQLSET des Packages DBMS_SQLTUNE verfügbar.
Sind diese Tabellen vorhanden, ist der weitere Ablauf wie folgt:
• Erstellung von SQL Tuning Sets und Laden der gewünschten Ausführungspläne in
diese STS
• Befüllen der Staging-Tabelle mit den STS im Quellsystem mittels
DBMS_SQLTUNE.PACK_STGTAB_SQLSET
• Export der Staging-Tabelle aus der Quell-Datenbank mittels traditionellem Export
oder Datapump Export
• Transport des Export-Files zum Zielsystem
• Import des Export-Files in die Staging-Tabelle der Ziel-Datenbank mittels Datapump
Import oder traditionellem Import
• Entpacken der Staging-Tabelle der Zieldatenbank unter Verwendung der Prozedur
UNPACK_STGTAB_SQLSET des Packages DBMS_SQLTUNE.
Damit sind die SQL Tuning Sets der Quell-Datenbank in der Zieldatenbank verfügbar und
können mit dem oben zur Nutzung von SQL Tuning Sets beschrieben Verfahren
weiterverarbeitet werden. Auch hier erfolgt somit automatisch die Erstellung einer SQL Plan
Baseline, bzw. das Hinzufügen zu einer Solchen. SQL Tuning Sets, die geladen werden,
erhalten somit auch die Stati "ENABLED" und "ACCEPTED" und können dadurch vom Cost
Based Optimizer berücksichtigt werden.
Laden von SQL Ausführungsplänen aus dem AWR
Bei dieser Vorgehensweise wird zunächst auf Basis des Automatic Workload Repository ein
SQL Tuning Set gebildet und mit SQL Ausführungsplänen befüllt. Dabei kann eine Filterung
über Snap-Ids und SQL-Ids erfolgen. Das weitere Vorgehen ist identisch mit dem vorab zur
Nutzung von SQL Tuning Sets beschrieben Verfahren. Hier erfolgt auch die automatische
Erstellung einer SQL Plan Baseline, bzw. das Hinzufügen der Ausführungspläne zu einer
solchen, was wiederum bewirkt, dass diese die Stati "ENABLED" und "ACCEPTED" erhalten.
Migration von Stored Outlines
SPM soll die seit Release 8i verfügbaren Stored Outlines ersetzen. Daher besteht die
Möglichkeit, solche zu SQL Plan Baselines zu migrieren. Dabei sind zwei verschiedene
Herangehensweisen möglich:
• Die Stored Outlines sollen zu SQL Plan Baselines mit den Stati "ENABLED" und
"ACCEPTED" migriert werden.
• Die Stored Outlines sollen so migriert werden, dass die SQL Plan Baseline den Status
"FIXED" erhält und somit im Grunde das Verhalten der Stored Outline beibehalten
wird, da garantiert ist, dass immer derselbe SQL-Ausführungsplan zur Anwendung
kommt, sofern kein kostengünstigerer als "FIXED" zur Baseline hinzugefügt wird.
SQL Profile
Oracle bietet seit Release 10g die Möglichkeit, sogenannte SQL Profile anzulegen. Werden
diese erzeugt, erfolgt die Berechnung und Speicherung einer Reihe von Kardinalitäten auf
Basis des abgesetzten SQL-Statements. Diese werden beim nächsten Hard Parse vom Cost
Based Optimizer benutzt, um einen möglichst optimalen Ausführungsplan zu ermitteln.
Sind das Database Diagnostic Pack und das Tuning Pack lizensiert und aktiviert (11g
Initialisierungsparameter CONTROL_MANAGEMENT_PACK_ACCESS), wird der SQL Tuning
Advisor in Release 11g im Rahmen des "AUTO TASK FRAMEWORK" automatisch während
der definierten "MAINTENANCE WINDOW" ausgeführt. Dabei werden auf Basis der
Automatic Workload Repository Snapshots SQL-Statements mit hohem Ressourcenverbrauch
ermittelt. Findet der SQL Tuning Advisor bei einem Lauf einen besseren SQL
Ausführungsplan, als den momentan in der SQL Plan Baseline enthaltenen, empfiehlt er die
Erstellung eines SQL Profils und erzeugt dieses selbständig, sofern das hierfür benötigte
Feature aktiviert ist (per Default ist die automatische Implementierung deaktiviert). Der
entsprechende SQL Ausführungsplan wird automatisch als "ENABLED" und "ACCEPTED"
zur Baseline hinzugefügt.
Wird der SQL Tuning Advisor manuell gestartet, ein SQL-Profil vorgeschlagen und dieses
implementiert, erfolgt ebenfalls das Hinzufügen des jeweiligen Ausführungsplanes zu einer
existierenden SQL Plan Baseline mit den Stati "ENABLED" und "ACCEPTED".
Navigation innerhalb der Enterprise Manager Database Control
Innerhalb der Enterprise Manager Database Control ist die Navigation zum SQL Plan
Management wie folgt:
Startseite -> Server -> SQL Plan Control (im Bereich "Query
Optimizer") -> SQL Plan Baseline
Monitoring des SQL Plan Management
Folgende Views stehen hierfür zur Verfügung:
• DBA_SQL_PLAN_BASELINES
• DBA_SQL_MANAGEMENT_CONFIG
Das Monitoring kann beispielsweise wie nachstehend abgebildet aussehen:
select
sql_handle, plan_name, origin,
fixed, autopurge, sql_text
from dba_sql_plan_baselines
where sql_text like '%WIB%';
enabled,
accepted,
Abb. 9: Monitoring SQL Plan Management
Für die Speicherung von SQL Plan Baselines und der SQL Historie sind per Default im
Tablespace SYSAUX 10% vorgesehen. Dies lässt sich mit folgendem Befehl abfragen:
select parameter_value
from dba_sql_management_config
where parameter_name = 'SPACE_BUDGET_PERCENT';
Ändern lässt sich diese Einstellung mit der Prozedur CONFIGURE des Package DBMS_SPM.
Werte zwischen "1" und "50" sind zulässig. Wird der eingestellte Schwellwert überschritten,
erfolgt ein Eintrag in das Alertlog der Instanz.
Mit dieser Prozedur kann ebenfalls die Retention Periode für Baselines konfiguriert werden,
die per Default 53 Wochen beträgt. Erlaubt sind hier Einstellungen zwischen "5" und "523".
Für die im Kontext des SPM nutzbaren SQL Tuning Sets und SQL Profile sind die
nachstehenden Tabellen und Views vorhanden:
• DBA_SQLSET
• DBA_SQLSET_BINDS
• DBA_SQLSET_PLANS
• DBA_SQLSET_STATEMENTS
• DBA_SQLTUNE_BINDS
• DBA_SQLTUNE_PLANS
•
•
•
•
DBA_SQLTUNE_RATIONALE_PLAN
DBA_SQLTUNE_STATISTICS
DBA_STAT_EXTENSIONS
DBA_SQL_PROFILES
Arbeiten auf der Kommandozeile
Zur Arbeit mit SQL Plan Management in SQL*PLUS liefert Oracle das Package DBMS_SPM.
Dieses bietet folgende Prozeduren:
• ALTER_SQL_PLAN_BASELINE
• CONFIGURE
• CREATE_STGTAB_BASELINE
• DROP_SQL_PLAN_BASELINE
• EVOLVE_SQL_PLAN_BASELINE
• LOAD_PLANS_FROM_CURSOR_CACHE
• LOAD_PLANS_FROM_SQLSET
• MIGRATE_STORED_OUTLINE
• PACK_STGTAB_BASELINE
• UNPACK_STGTAB_BASELINE
Für die in diesem Kontext nutzbaren SQL Tuning Sets und SQL Profile ist seit Release 10g
das Package DBMS_SQLTUNE verfügbar.
Testergebnisse
• Grundsätzlich funktioniert das SQL Plan Management wie dokumentiert.
• Beim Verifizieren von Ausführungsplänen verhält Oracle sich offenbar
unterschiedlich in Release 1 und Release 2.
• Ein Ausführungsplan, der besser ist, als der aktuell benutzte, wird durch eine
Verifikation nicht unbedingt "ACCEPTED", weil es offenbar, entgegen der
Dokumentation, nicht reicht, dass er niedrigere oder mindestens identische Kosten wie
die Baseline aufweist.
• SQL Plan Management unterstützt "Adaptive Cursor Sharing" nur eingeschränkt
(siehe nachstehende Tabelle 1). Sofern es funktioniert, müssen die für verschiedene
Werte der Bindevariablen ermittelten Ausführungspläne allerdings (ohne
Performanceanalyse) verifiziert werden. Dies gilt jedoch nur für den zweiten
ermittelten SQL Ausführungsplan. Sind bereits 2 SQL Plan Baselines vorhanden,
werden für weitere durch den Cost Based Optimizer generierte SQL
Ausführungspläne automatisch zusätzliche SQL Plan Baselines mit den Stati
"ENABLED" und "ACCEPTED" erzeugt oder Ausführungspläne zu Baselines
hinzugefügt.
CURSOR_SHARING
EXACT
SIMILAR
FORCE
Systemgenerierte
Bindevariablen
-/√
√
Benutzerdefinierte
Bindevariablen
x
x
x
Tabelle 1: Testergebnisse mit Adaptive Cursor Sharing
Für die vorstehenden, mit 'x' gekennzeichneten Kombinationen funktionierte das
Adaptive Cursor Sharing nicht. Es wurde, unabhängig von den Werten der
Bindevariablen, immer der zuletzt akzeptierte SQL Ausführungsplan benutzt.
Zusammenfassung
• Wird das SQL Plan Management permanent und systemweit mit der Einstellung
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE benutzt, erzeugt dies
eine enorm große Zahl von Ausführungsplänen. Das erfordert deren regelmäßige
Kontrolle, da diese grundsätzlich nicht automatisch verifiziert werden. Daher sollte
dieses Feature nur zeitlich begrenzt und auf Session-Ebene eingesetzt werden.
• Laut Dokumentation sind der entscheidende Faktor für die Akzeptanz von
Ausführungsplänen als Baseline dessen Kosten (Cost). Es ist jedoch nicht transparent,
wie diese in die Berechnung der Akzeptanz eingehen.
• Kosten sind von CPU-Nutzung und I/O abhängig. Aufgrund verschiedener Faktoren
(z.B. Anzahl erforderlicher Plattenzugriffe) können diese jedoch zu verschiedenen
Zeitpunkten variieren.
• Erfahrungsgemäß sind niedrige Kosten für einen Ausführungsplan nicht zwangsläufig
mit einer performanten Ausführung bzw. Lieferung des gewünschten Ergebnisses von
SQL-Befehlen verbunden. So sind auch beispielsweise Rahmenbedingungen wie
Systemlast, Speicherauslastung und Netzwerk zu berücksichtigen.
• Eine sehr wichtige Rolle für die Erstellung eines SQL Ausführungsplanes bilden die
vom Cost Based Optimizer berechneten und vor allem auf Objektstatistiken
basierenden Kardinalitäten der einzelnen Prädikate. SPM ist somit indirekt auf gute
Objektstatistiken angewiesen.
• Ist der Server Result Cache konfiguriert, wird er auch vom SQL Plan Management
benutzt. Sobald Ergebnisse im Cache ungültig werden, kann eine darauf basierende
SQL Plan Baseline nicht mehr benutzt werden. Dies kann zu einer
Performanceverschlechterung führen.
• Sind Objekte, die von einer Baseline benutzt werden, nicht verfügbar (z.B. gelöschter
oder nicht benutzbarer Index), kommt die Baseline nicht mehr zum Einsatz. Auch dies
kann zu einer Verschlechterung der Performance führen.
• Wird ein SQL-Befehl mit anderen Parametern ausgeführt, als denen, die bei der
Erstellung der SQL Plan Baseline benutzt wurden (z.B. geänderter
OPTIMIZER_MODE), kann die Baseline für die erneute Statementausführung nicht
benutzt werden. Auch dies kann zu einer Verschlechterung der Performance führen.
•
•
•
Wenn neue Funktionalitäten einer Applikation, beispielsweise im Rahmen eines
Releasewechsels implementiert werden sollen, kann es hilfreich sein, deren
Performance im Vorfeld unter Nutzung von SPM zu testen.
SPM kann im Rahmen von Migrationen hilfreich sein, um die Performance von SQLStatements im Zielsystem im Vorfeld zu überprüfen. Dies mag vor allem interessant
sein, wenn die Real Application Testing Option nicht zur Verfügung steht.
Die Defaulteinstellung auf den Wert "TRUE" des Initialisierungsparameters
OPTIMIZER_USE_SQL_PLAN_BASELINES kann Sinn machen, da SPM
grundsätzlich funktioniert. Allerdings sollte hier unbedingt eine Abwägung aufgrund
der vorstehend genannten Punkte erfolgen.
Quellen
Metalink-Notes:
456518.1
SQL Plan Management
456019.1
How to transport a SQL Tuning Set
787692.1
Loading Hinted Execution Planes into SQL Plan Baseline
788853.1
SQL Plan Baseline not always created
789520.1
SQL Plan Management Tracing
789888.1
How to load SQL Plans into SPM from AWR
790039.1
How to drop plans from SPM Repository
801033.1
How to move 10gR2 Execution Plans and load into 11g SPM
Bugs:
8922968
SPM not used if plans loaded from AWR Snapshots
Oracle Dokumentation: Ist teilweise noch auf dem Stand 11g Release 1.
Kontaktadresse:
Wilhelm Breßer
Herrmann & Lenz Services GmbH
Höhestraße 37
D-51399 Burscheid
Telefon:
Fax:
E-Mail
Internet:
+49(0)2174-6712-0
+49(0)2174-6712-22
[email protected]
http://www.hl-services.de