Oracle 07.03.2011, 13:31 Uhr

Ausführungspläne und Informationsabfragen

Ändern sich Applikationen oder die Datenbankumgebung durch Einspielen von Patches, Tuning-Maßnahmen usw., besteht häufig die Notwendigkeit, Ausführungspläne zu analysieren. Die Oracle-Datenbank liefert schon seit langer Zeit unterschiedliche Methoden und Instrumente, um die Ausführungen von SQL-Statements zu überwachen und die zugehörigen Ausführungspläne zu generieren.
Eine graphische Implementierung findet sich in den beiden Werkzeugen SQL Developer und Oracle Enterprise Manager. So werden Ausführungspläne im Enterprise Manager nach Bedarf über die speziellen Links im Bereich Performance – wie SQL Monitoring, SQL Tuning Set, Top Activity oder SQL Performance Analyzer – angezeigt. Das SQL Monitoring ist ein wichtiges Hilfsmittel, um Statements zu analysieren, die parallel laufen, den Monitor Hint benutzen oder mindestens 5 Sekunden CPU- beziehungsweise I/O-Zeit benötigen. Zur Verwendung dieser Feature ist allerdings die Lizensierung des Tuning Packs notwendig. Wollen Sie ausführliche Informationen über die Ausführungspläne erhalten, obwohl das Tuning Pack (noch) nicht im Einsatz ist, oder wollen Sie unabhängig von graphischen Implementierungen der Oracle-Werkzeuge sein, sollten Sie das Package DBMS_XPLAN verwenden. Einfache Aufrufe von DBMS_XPLAN sind schon in den vorgefertigten Skripten utlxplp.sql (für parallele Ausführungen) und utlxpls.sql (für serielle Ausführungen) im Verzeichnis $ORACLE_HOME/rdbms/admin zu finden.

Zugriffsrechte

Bevor das Package allerdings zum Einsatz kommen kann, sollten Sie sicher stellen, dass ausreichende Zugriffsrechte vorhanden sind. SELECT-Rechte auf die Plan-Tabelle oder auf verschiedene Data Dictionary Views sind dazu nötig. Zur Vereinfachung kann auch die SELECT_CATALOG_ROLE-Rolle vergeben werden, die diese Privilegien beinhaltet. Das Package DBMS_XPLAN besteht hauptsächlich aus 6 Funktionen, die formatierte Ausführungspläne aus unterschiedlichen Quellen wie Plan-Tabelle, Cursor Cache, AWR-Reports etc. anzeigen.

Aktuelle Cursor

Mit der Funktion DISPLAY_CURSOR können beispielsweise Ausführungspläne von aktuellen Cursorn angezeigt werden. Dies hat den Vorteil, dass vorher kein EXPLAIN PLAN auszuführen ist, sondern die Informationen direkt aus dem aktuellen Cursor-Cache genutzt werden. Mit den Argumenten SQL_ID und CHILD_ID kann dabei ein bestimmtes Statement ausgewählt werden. Wird kein Wert wie im nachfolgenden Listing angegeben, wird das zuletzt ausgeführte Statement verwendet. In den folgenden Beispielen dient das folgende Statement als Cursor:
SELECT * FROM customers
 WHERE UPPER(CUST_LAST_NAME) = 'EVE';
Nehmen Sie die Standardwerte als Eingabewerte, erhalten Sie die Ausgabe entsprechend dem folgenden Listing:
SQL> set heading off
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

SQL_ID 0m8upu88z9nf6, child number 0
 -----------------------------------------------------
SELECT * FROM customers WHERE
UPPER(CUST_LAST_NAME) = 'EVE'

Plan hash value: 2008213504
 -----------------------------------------------------
| Id | Operation     | Name   
|Rows| Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------
|  0 | SELECT STATEMENT  |      |   |    |   406 (100)|     |
|*  1|  TABLE ACCESS FULL| CUSTOMERS
| 283| 84334 |  406  (1)|  00:00:01|
-----------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------
  1 - filter(UPPER("CUST_LAST_NAME")='EVE')


Note
-----
  - dynamic sampling used for this statement (level=2)
22 rows selected.
 Beachten Sie den mit Note gekennzeichneten Bereich in der Ausgabe, der nützliche Informationen über die Statistiken, die Verwendung des SQL-Plan-Managements, den Einsatz von Auto DOP (kurz für Degree of Parallelism)  usw. liefert. Im Beispiel gibt es beispielsweise keine Statistiken, daher wird die Information "dynamic sampling used for this statement (level=2)" als Hinweis in der Note ausgegeben. Das gleiche Ergebnis liefert auch die folgende Abfrage:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR
 ( sql_id=>'0m8upu88z9nf6', format=> 'typical'))
Das Argument FORMAT beeinflusst dabei den Umfang und den Inhalt der Ausgabe und besitzt vier Standardwerte – BASIC, TYPICAL(Default), SERIAL, ALL – die jeweils durch zusätzliche Parameter beeinflussbar sind. Um die Vielfalt der Ausgabemöglichkeiten zu nutzen, lohnt sich in jedem Fall ein Blick in das Handbuch PL/SQL Packages and Types Reference oder in die Ausgaben der DBA-Community (siehe Textkasten Oracle-Dokumentation) zu werfen.
Mit der Funktion DISPLAY_PLAN ist es darüber hinaus möglich, einen in einer Plan Table (hier PLAN_TABLE) gespeicherten Ausführungsplan in verschiedenen Formaten auszugeben So kann zwischen den unterschiedlichen Formattypen HTML, TEXT oder XML ausgewählt werden. Die Ausgabe erfolgt dabei als CLOB. Zusätzlich kann ein Filterprädikat wie zum Beispiel die Spalte PLAN_ID mitgegeben werden, um den entsprechenden Plan aus der Plan-Tabelle zu filtern. Bild 2 zeigt die Anwendung im HTML-Format mit der Filterbedingung PLAN_ID=166.  (Ulrike Schwinn/am)



Das könnte Sie auch interessieren