Ausführungspläne mit DBMS

DBA Community - Februar 2011

Ausführungspläne mit DBMS_XPLAN

von Ulrike Schwinn, ORACLE Deutschland GmbH

Ändern sich Applikationen oder die Umgebung durch Einspielen von Patches, Änderungen an Initialisierungsparametern, Änderungen an den Storageparametern oder Zugriffsstrukturen besteht häufig die Notwendigkeit, Ausführungspläne zu generieren und zu analysieren. Die Oracle Datenbank liefert schon seit langer Zeit unterschiedliche Methoden und Instrumente, um die Ausführungen von SQL Statements zu monitoren und die zugehörigen Ausführungspläne zu generieren. Auch eine graphische Implementierung findet sich beispielsweise in den beiden Werkzeugen SQL Developer und Enterprise Manager. So werden Ausführungspläne im Enterprise Manager nach Bedarf über die speziellen Links im Bereich Performance (siehe SQL Monitoring, SQL Tuning Set, Top Activity und SQL Performance Analyzer) angezeigt.

Das SQL Monitoring ist dabei ein wichtiges Hilfsmittel, um Statements zu analysieren, die parallel laufen, den MONITOR Hint besitzen oder mindestens 5 Sekunden CPU bzw. I/O Zeit benötigen. Eine Implementierung findet sich im Enterprise Manager und im SQL Developer Release 3.0 Early Adopter. Der folgende Ausschnitt zeigt das SQL Monitoring im Enterprise Manager.

Für eine größere Ansicht auf das Bild klicken.

Der folgende Ausschnitt zeigt die Implementierung im aktuellsten SQL Developer 3.0 Early Adopter Release.

Für eine größere Ansicht auf das Bild klicken.

Wichtiger Hinweis: Zur Verwendung dieses Features ist die Lizensierung des Tuning Packs notwendig.

Möchte man ausführliche Informationen über die Ausführungspläne erhalten, obwohl das Tuning Pack (noch) nicht im Einsatz ist, oder möchte man unabhängig von graphischen Implementierungen der Oracle Werkzeuge sein, sollte man das Package DBMS_XPLAN verwenden. Einfache Aufrufe von DBMS_XPLAN sind übrigens in den vorgefertigten Skripten utlxplp.sql (parallele Ausführung) und utlxpls.sql (serielle Ausführung) im Verzeichnis $ORACLE_HOME/rdbms/admin zu finden.

Der folgende Tipp zeigt häufig verwendete Funktionen von DBMS_XPLAN und darüberhinaus Funktionalitäten, die weniger bekannt aber sehr hilfreich sein können. In allen Beispielen wird Oracle Database 11g Release 2 verwendet.

Die Funktionen im Überblick
Bevor das Package DBMS_XPLAN zum Einsatz kommen kann, sollte man sicher stellen, dass ausreichende Zugriffsrechte vorhanden sind. SELECT-Rechte auf verschiedene Data Dictionary Views (siehe Handbucheintrag) sind dazu nötig. Zur Vereinfachung kann die SELECT_CATALOG_ROLE Rolle vergeben werden, die diese Privilegien beinhaltet.

DBMS_XPLAN besteht hauptsächlich aus 6 Funktionen, die jeweils formatierte Ausführungspläne aus folgenden unterschiedlichen Quellen anzeigen:

DISPLAY: Informationen stammen aus einer Plan Table.
DISPLAY_PLAN: Informationensquelle wie bei DISPLAY; zusätzlich ist die Ausgabe in unterschiedlichen Formaten wie HTML möglich.
DISPLAY_AWR: Informationen stammen aus AWR Reports.
DISPLAY_CURSOR: Informationen stammen aus einem aktuell ausgeführten Cursor, der sich im Cursor Cache befindet.
DISPLAY_SQL_PLAN_BASELINE: Informationen stammen aus SQL Plan Baselines.
DISPLAY_SQLSET: Informationen sind im SQL Tuning Set gespeichert.

Bei den Funktionen handelt es sich um TABLE Funktionen, die bis auf die Funktion DISPLAY_PLAN eine Collection - eine Ansammlung von Zeilen - zurückliefert. Die Verwendung erfolgt in der FROM Klausel der Abfrage nach dem Schlüsselwort TABLE. Alle Funktionen haben ein Eingabe-Argument für SQL_ID oder SQL_HANDLE und ein Argument für FORMAT, das eine umfangreiche Ausgabe ermöglicht. Im Folgenden liegt der Fokus auf der häufig verwendeten Funktion DISPLAY_CURSOR und der Funktion DISPLAY_PLAN.

Die TABLE Funktion DISPLAY_CURSOR
Mit dieser Funktion können Ausführungspläne von aktuellen Cursorn angezeigt werden. Mit den Argumenten SQL_ID und CHILD_ID kann dabei ein bestimmtes Statement aus dem Cursor Cache ausgewählt werden. Wird kein Wert angegeben, wird das zuletzt ausgeführte Statement verwendet. Das Argument FORMAT beeinflusst den Umfang und den Inhalt der Ausgabe und besitzt 4 Standardwerte - BASIC, TYPICAL(Default), SERIAL, ALL. Folgende Beispiele illustrieren die Verwendung. Als Cursor dient dabei das folgende Statement.

SELECT * FROM  Customers WHERE UPPER(CUST_LAST_NAME) = 'EVERETT';

Nimmt man die Standardwerte als Eingabewerte, erhält man folgende Ausgabe.

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) = 'EVERETT'

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")='EVERETT')

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.

Beachten Sie bitte den Bereich "Note", der nützliche Informationen über die Statistiken, die Verwendung des SQL Plan Management, den Einsatz von Auto DOP usw. liefert. In unserem Fall gibt es beispielsweise keine Statistiken. Das gleiche Ergebnis liefert übrigens folgende Abfrage:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR ( sql_id=>'0m8upu88z9nf6', format=> 'typical'))

Nutzt man hingegen ALL als Formatierungswert, werden zusätzlich Informationen zu Projektionen und die Aliaswerte ausgegeben.

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR ( sql_id=>'g1y8d58zm2kxm', format=> 'all'))

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

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 |
-------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / CUSTOMERS@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("CUST_LAST_NAME")='EVERETT')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUSTOMERS"."CUST_ID"[NUMBER,22],
       "CUSTOMERS"."CUST_FIRST_NAME"[VARCHAR2,20],
       "CUST_LAST_NAME"[VARCHAR2,40], "CUSTOMERS"."CUST_GENDER"[CHARACTER,1],
       "CUSTOMERS"."CUST_YEAR_OF_BIRTH"[NUMBER,22],
       "CUSTOMERS"."CUST_MARITAL_STATUS"[VARCHAR2,20],
       "CUSTOMERS"."CUST_STREET_ADDRESS"[VARCHAR2,40],
       "CUSTOMERS"."CUST_POSTAL_CODE"[VARCHAR2,10],
       "CUSTOMERS"."CUST_CITY"[VARCHAR2,30],
       "CUSTOMERS"."CUST_CITY_ID"[NUMBER,22],
       "CUSTOMERS"."CUST_STATE_PROVINCE"[VARCHAR2,40],
       "CUSTOMERS"."CUST_STATE_PROVINCE_ID"[NUMBER,22],
       "CUSTOMERS"."COUNTRY_ID"[NUMBER,22],
       "CUSTOMERS"."CUST_MAIN_PHONE_NUMBER"[VARCHAR2,25],
       "CUSTOMERS"."CUST_INCOME_LEVEL"[VARCHAR2,30],
       "CUSTOMERS"."CUST_CREDIT_LIMIT"[NUMBER,22],
       "CUSTOMERS"."CUST_EMAIL"[VARCHAR2,30],
       "CUSTOMERS"."CUST_TOTAL"[VARCHAR2,14],
       "CUSTOMERS"."CUST_TOTAL_ID"[NUMBER,22],
       "CUSTOMERS"."CUST_SRC_ID"[NUMBER,22],
       "CUSTOMERS"."CUST_EFF_FROM"[DATE,7], "CUSTOMERS"."CUST_EFF_TO"[DATE,7],
       "CUSTOMERS"."CUST_VALID"[VARCHAR2,1]

Note
-----
   - dynamic sampling used for this statement (level=2)

Die Formatausgabe kann durch zusätzliche Parameter beeinflusst - erweitert oder eingeschränkt - werden. Interessante Erweiterungen bieten beispielsweise die Werte IOSTATS LAST oder IOSTATS ALL. In der Ausgabe werden zusätzliche Spalten wie A-Rows (actual rows) und E-Rows (estimated rows) zur Verfügung gestellt, die Hinweise auf die Güte der Optimizer-Schätzungen geben können. So deutet eine große Differenz der Werte in diesen Spalten (hier 8114-1081) auf eine ungenaue Abschätzung des Optimizers hin.

In unserem Beispiel sind die Statistiken veraltet, daher erklärt sich die Differenz. Um diese Funktion nutzen zu können, ist die zusätzliche Angabe des Hints GATHER_PLAN_STATISTICS oder der Parameterwert ALL für STATISTICS_LEVEL notwendig. Das Schlüsselwort LAST zeigt dabei an, dass sich die Statistiken auf die letzte Cursor-Ausführung beziehen.

-- Ausführung der Abfrage mit Hint gather_plan_statistics
SQL> select /*+ gather_plan_statistics */ cust_id from sales where amount_sold>1500;
...

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(format=>'IOSTATS ALL'));
SQL_ID  a1a4juu3z65qr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ cust_id from sales where
amount_sold>1500

Plan hash value: 1550251865

---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |   8114 |00:00:00.05 |    2256 |
|   1 |  PARTITION RANGE ALL|       |      1 |   1081 |   8114 |00:00:00.05 |    2256 |
|*  2 |   TABLE ACCESS FULL | SALES |     28 |   1081 |   8114 |00:00:00.04 |    2256 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AMOUNT_SOLD">1500)

Die Spalten E-Rows und A-Rows können Sie auch in den SQL Monitoring Screenshots im oberen Abschnitt dieses Tipps finden. A-Rows wird dort mit aktuelle Zeilen oder actual_rows und E-Rows mit geschätzte Zeilen oder estimated_rows bezeichnet.

Die TABLE Funktion DISPLAY_PLAN
Mit der Funktion DISPLAY_PLAN ist es möglich, einen in einer Plan Table (hier PLAN_TABLE) gespeicherten Ausführungsplan auszugeben. Im Unterschied zu DISPLAY können unterschiedliche Ausgabetypen wie HTML, TEXT oder XML ausgewählt werden. Die Ausgabe erfolgt dabei als CLOB. Zusätzlich kann ein Filterprädikat mitgegeben werden, um den entsprechenden Plan aus der Plan Table zu filtern. Die Spalte PLAN_ID der Plan Table stellt ein gutes Beispiel für ein geeignetes Filterkriterium dar. Folgendes Beispiel zeigt die Anwendung im TEXT Format mit der Filterbedingung PLAN_ID=53.

SQL> set longchunksize 200
SQL> set heading off
SQL> set long 10000
SQL> select dbms_xplan.display_plan(table_name=>'PLAN_TABLE',format=>'TYPICAL',-
     filter_preds=>'plan_id=53',type=>'TEXT') from dual;

 Plan Hash Value  : 1163973071

-------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows   | Bytes    | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           | 739491 | 17747784 |  506 | 00:00:01 |
|   1 |   PX COORDINATOR          |           |        |          |      |          |
|   2 |    PX SEND QC (RANDOM)    | :TQ10001  | 739491 | 17747784 |  506 | 00:00:01 |
| * 3 |     HASH JOIN             |           | 739491 | 17747784 |  506 | 00:00:01 |
|   4 |      PX RECEIVE           |           |  55501 |   610511 |  232 | 00:00:01 |
|   5 |       PX SEND BROADCAST   | :TQ10000  |  55501 |   610511 |  232 | 00:00:01 |
|   6 |        PX BLOCK ITERATOR  |           |  55501 |   610511 |  232 | 00:00:01 |
|   7 |         TABLE ACCESS FULL | CUSTOMERS |  55501 |   610511 |  232 | 00:00:01 |
|   8 |      PX BLOCK ITERATOR    |           | 739491 |  9613383 |  271 | 00:00:01 |
|   9 |       TABLE ACCESS FULL   | SALES     | 739491 |  9613383 |  271 | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("C"."CUST_ID"="S"."CUST_ID")


Note
-----
- dynamic sampling used for this statement

Nutzt man den Ausgabetyp HTML, sieht das Ergebnis im Browser folgendermassen aus.

Für eine größere Ansicht auf das Bild klicken.

Weitere Tipps und Hinweise
Ausführungspläne generieren und interpretieren ist ein wichtiger Bestandteil des Tuningprozesses. Die Nutzung weiterer Werkzeuge und Advisories kann darüberhinaus den Prozess abkürzen oder gar überflüssig machen. Folgende Liste gibt einen kurzen Überblick über die veröffentlichten Tipps zu diesem Thema:

Mehr zu diesem Thema werden Sie in den nächsten Ausgaben finden ...

Zurück zur Community-Seite