Tutorial – EXPLAIN PLAN eines SQL Statements erstellen und anzeigen
Um einen EXPLAIN PLAN – Ausführungsplan eines SQL Statement unter Oracle erstellen und anzeigen zu lassen sind einige Arbeitschritte erforderlich. In diesen kleinen Tutorial gehe ich auf die Voraussetzungen, Durchführung und Auswertung in Bezug auf EXPLAIN PLANs ein.
Was ist ein EXPLAIN PLAN – Ausführungsplan
Kurz und knapp: Wie wird mein SELECT, UPDATE, INSERT oder DELETE Statement von der Oracle Datenbank verarbeitet, d.h. welche Arbeitsschritte wird die Datenbank durchführen damit ich mein gewünschtes Ergebnis erhalte.
Hier die offizielle Beschreibung aus der Oracle Dokumentation: Oracle Doku 11.2
PLAN_TABLE erstellen
Als Erstes muss eine Tabelle – PLAN_TABLE – erstellt werden. In dieser Tabelle werden alle notwendigen Informationen beim Erstellen eines EXPLAIN PLANs abgelegt. Dazu stellt Oracle im Verzeichnis $ORACLE_HOME/rdbms/admin/ das SQLPlus Script utlxplan.sql zur Verfügung.
Starten Sie SQLPlus und verbinden Sie sich mit der gewünschten DB und User. Führen Sie dann folgenden Befehl aus:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Tabelle wurde erstellt. /* Alternativ kann auch die Tabelle einen anderen Namen haben, dazu ist jedoch im späteren Verlauf beim Erstellen der Explain Plans eine zusätzliche Information mit anzugegeben. */ create table BESSERDICH_PLAN_TABLE ( statement_id varchar2(30), plan_id number, timestamp date, remarks varchar2(4000), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_alias varchar2(65), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, depth numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000), projection varchar2(4000), time numeric, qblock_name varchar2(30), other_xml clob ); |
EXPLAIN PLAN – Ausführungsplan erstellen
Bei der Erstellung des EXPLAIN PLANs wird das eigentlich SQL Statement – SELECT, UPDATE, INSERT oder DELETE – nicht ausgeführt.
Es werden die von Oracle berechneten Arbeitsschritte inklusive der jeweiligen Kosten auf Basis des Optimizers sowie der Statistiken berechnet und in die von uns angegebene PLAN_TABLE eingefügt.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Explain Plan wird in der Tabelle PLAN_TABLE erstellt SQL> EXPLAIN PLAN FOR SELECT * FROM besserdich_01 WHERE ID = 4 / EXPLAIN PLAN ausgeführt. -- Explain Plan wird in der Tabelle PLAN_TABLE -- mit der StatementID = meineStatID1 erstellt SQL> EXPLAIN PLAN SET STATEMENT_ID = 'meineStatID1' FOR SELECT * FROM besserdich_01 WHERE ID = 4 / EXPLAIN PLAN ausgeführt. -- Explain Plan wird in der Tabelle BESSERDICH_PLAN_TABLE -- mit der StatementID = meineStatID2 erstellt SQL> EXPLAIN PLAN SET STATEMENT_ID = 'meineStatID2' INTO BESSERDICH_PLAN_TABLE FOR SELECT * FROM besserdich_01 WHERE ID = 4 / EXPLAIN PLAN ausgeführt. |
Wollen Sie die Tabelleneinträge in der PLAN_TABLE auch in andere Sessions sehen, müssen Sie die Transaktion erst mit einem COMMIT festschreiben.
Ich empfehle das jeder Datenbankbenutzer seine eigene PLAN_TABLE besitzt und nicht z.B. eine öffentlichen vom DB-USER sys.
EXPLAIN PLAN – Ausführungsplan anzeigen
Nachdem der EXPLAIN PLAN, wie oben beschrieben, erstellt wurde, kann mit der eigentlichen Analyse begonnen werden. Es gibt wie immer verschiedene Möglichkeiten sich das Ergebnis in verschiedensten Formen anzeigen zu lassen. Hier ein paar Beispiele.
1 2 3 4 5 |
-- von Oracle bereitgestelltes Script SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql -- hier steht jedoch nicht mehr drin als folgendes SQL-Statement SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')); |
Möchte man z.B. auf eine bestimmte StatementID zugreifen und oder auf eine eigene PLAN_TABLE, bietet die die Function DISPLAY noch zusätzliche Parameter an.
Hier ein paar Beispiele:
1 2 3 4 5 6 7 8 9 10 11 |
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'meineStatID1','TYPICAL')) / SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'meineStatID1','ALL')) / SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('BESSERDICH_PLAN_TABLE', 'meineStatID2','COST')) / |
SCHREIBEN SIE EINEN KOMMENTAR