Tutorial – EXPLAIN PLAN eines SQL Statements erstellen und anzeigen

von Tobias Redmann

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:

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

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.

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

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

-- 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'));

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

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'))
/

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

Sie sind hier: