Oracle Tuning und Optimierung Tutorial: Ausführungsplan (explain plan)
Erstellen und Anzeigen eines Oracle Ausführungsplans

Prinzip

Für jeden SQL-Befehl erstellt der Optimierer einen Ausführungsplan vor der Ausführung. Es gibt zahlreiche Faktoren, die den Ausführungsplan beeinflussen, z.B. der Optimierer-Modus, die SQL-Gestaltung, die Analysedaten (bei kostenbasiertem Optimierer), Hints und die DB-Parameter. Es ist möglich, sich zu Testzwecken, manuell einen Ausführungsplan anzeigen zu lassen, um ggf. Schwachstellen zu finden.

Manuelles Erstellen eines Ausführungsplanes

• Zugriff auf Tabelle plan_table muß verfügbar sein (Skript utlxplan.sql legt die Tabelle an)

• in SQL*Plus: set autotrace traceonly

• mit Befehl "explain plan" und anschließendem Selektieren aus der Tabelle plan_table (Datei explain.sql)

Beispiel

select *
from auftrag_pos ap, auftrag a
where a.auftrag_nr = ap.auftrag_nr;

delete plan_table;

explain plan
set statement_id = 'MY_STATE'
for
select *
from auftrag_pos ap, auftrag a
where a.auftrag_nr = ap.auftrag_nr;

select lpad(' ',2*level)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME QUERY_PLAN
from PLAN_TABLE WHERE STATEMENT_ID = 'MY_STATE'
connect by prior id = PARENT_ID AND STATEMENT_ID = 'MY_STATE'
start with id = 1;

QUERY_PLAN
--------------------------------------------------
NESTED LOOPS
    TABLE ACCESS FULL AUFTRAG
    TABLE ACCESS BY INDEX ROWID AUFTRAG_POS
        INDEX RANGE SCAN PK_AUFTRAG_POS

Der Ausführungsplan zeigt, dass in einer verschachtelten Schleife zunächst die Tabelle "AUFTRAG" vollständig durchlaufen wird. Für jede gefundene Zeile, wird in "AUFTRAG_POS" gesucht, indem der Index "PK_AUFTRAG_POS" durchsucht wird. Mit den gefundenen Rowids erfolgt der Zugriff auf die Daten in "AUFTRAG_POS".

Die Tabelle "AUFTRAG" wird hier auch als Driving Table bezeichnet. Auch "AUFTRAG_POS" ist in unserem Beispiel als Driving Table verwendbar. Nach Möglichkeit sollte die Driving Table nur wenige Zeilen enthalten.

Der Zugriff über "TABLE ACCESS FULL" ist besonders unangenehm. Die gesamte Tabelle wir portionsweise in den Cache geladen. Dies führt zu langsamen I/O-Operationen. Häufig genügt das Anlegen eines Index um solche Befehle deutlich zu beschleunigen. Wie man solche SQL-Befehle findet siehe Ungünstige SQL-Befehle.