Die erste Frage die sich einen zukünftigen Entwickler stellt, der SQL Tuning betreiben möchte, wie kann ich den Ausführungsplan mit anzeigen lassen und wie kann ich den Plan lesen, interpretieren und  verändern.

Fangen wir also an, das wir die Ausführungspläne für selbst erstellte Statements anschauen. Welche Bedingungen müssen erfüllt sein das man sie sehen kann.

Verweise

Notwendige Berechtigungen

Das ideale ist natürlich, wenn man die DBA-Rolle hat, dann kann sofort loslegen. Machen Wir es erstmal eine Nummer kleiner. Wir gehen davon aus das der Datenbanknutzer den Namen HR besitzt. das ist ein üblicher Datenbankbenutzer der in jeder Oracle- Datenbank mit Beispieldaten angelegt werden kann.Wir installieren erst einmal die Tabelle die für die Erstellung des Plan notwendig ist. Das Fragezeichen im Aufruf des Skriptes wird durch das ORACLE_HOME ersetzt.

SQL> connect hr
Kennwort eingeben:
Connect durchgeführt.
SQL> @?/rdbms/admin/utlxplan.sql

Tabelle wurde erstellt.
SQL>

Nachdem wir die Tabelle für die Ausführungstabelle angelegt habe, legenwir die notwendige Rolle als Administrator an.

SQL> connect / as sysdba
Connect durchgef³hrt.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
FEHLER in Zeile 1:
ORA-01919: Rolle 'PLUSTRACE' nicht vorhanden


SQL> create role plustrace;

Rolle wurde erstellt.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Benutzerzugriff (Grant) wurde erteilt.

SQL> grant select on v_$statname to plustrace;

Benutzerzugriff (Grant) wurde erteilt.

SQL> grant select on v_$mystat to plustrace;

Benutzerzugriff (Grant) wurde erteilt.

SQL> grant plustrace to dba with admin option;

Benutzerzugriff (Grant) wurde erteilt.

SQL>
SQL> set echo off
SQL>

Jezt vergeben wir die gerade angelegte Rollen an unseren HR Datenbanknutzer.

SQL> CONNECT / AS SYSDBA
Connect durchgef³hrt.
SQL> GRANT PLUSTRACE TO HR;

Benutzerzugriff (Grant) wurde erteilt.

SQL>

Ausfürungspläne anzeigen

Wir führen jetzt mehrmals hintereinander einen einfachen SELECT Befehl aus umd beobachtem die Ausgabe.

SQL> connect hr
Kennwort eingeben:
Connect durchgef³hrt.
SQL> SELECT * FROM regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

Wir sehen die “normale” Ausgabe aller Zeilen.

Jetzt verändern wir die Ausgabe indem wir den Befehl SET AUTOTRACE vorher eingeben.

SQL> SET autotrace ON
SQL> SELECT * FROM regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa


Ausführungsplan
----------------------------------------------------------
Plan hash value: 3077898360

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     4 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| REGIONS |     4 |    56 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        745  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Jezt sehen wir viel mehr,man kann die Ausgabe in drei Bereiche unterteilen.

1. Bereich Ausgabe des Ergebnis des SELECT (Zeile 4 – 9)

2. Bereich Ausgabe des Ausführungsplan des Statements (Zeile 12 – 21)

3. Bereich Ausgabe der Statistik für das Statement (Zeile 24 -36)

Diese Ausgabe ist sehr umfanreich und wird häufig nicht benötigt. Wenn man die Performance eines Statements einschätzen möchte, benötigt man nicht die Ausgabe der Ergebnismenge.

Wir minimieren die Ausgabe indem wir die Ergebnismenge ausschließen.

SQL> SET autotrace traceonly
SQL> SELECT * FROM regions;


Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 3077898360

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     4 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| REGIONS |     4 |    56 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        745  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Jetzt sehen wir schöner weniger. Das Interesse liegt jetzt bei den Plan und die Statitik des Statements des Statements. 

Es gibt aber noch einen Nachteil das Statements wird jedesmal ausgeführt und erst wenn das Statement komplett ausgeführt wurde, wird alles angezeigt. Wenn man das Statement mehrmals varieren möchte, kann das schon eine Weile dauern.

Man kann also den Optimizer sagen, machen mir den Plan führe das Statement nicht aus. Dadurch kann sieht man den Plan sofort.

SQL> SET autotrace traceonly explain
SQL> SELECT * FROM regions;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3077898360

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     4 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| REGIONS |     4 |    56 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Jetzt sieht man nur den Ausführungsplan des Statements.

Was Beim probieren völlig ausreicht.

Wenn man den Ausführungsplan nicht sehen will, aber die Statistik kann man folgendes schreiben.

SQL> SET autotrace traceonly statistics
SQL> SELECT * FROM regions;


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        745  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Will man zu normalen Ansicht zurück kommen, dann muss man SET autotrace OFF eingeben.

SQL> SET autotrace OFF
SQL> SELECT * FROM regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

Wir man die einzelnen Anzeigen auswertet werden wir in den nächsten Beiträgen behandeln.