# [Oracle] Kleiner Tuning Workshop



## Exceptionfault (24. Oktober 2004)

Das größte Problem beim Tuning langsamer Anwendungen oder generell der zu Grunde liegenden Datenbank ist in der Regel nicht die Beseitigung der Schwachstelle sondern deren Erkennung. Meiner Erfahrung nach ist die größte Schwachstelle bei langsamen nicht die Datenbank sondern das SQL Statement.

Oracle besitzt einen sog. Optimizer der bei jeder Anfrage versucht die beste Methode für die Datengewinnung zu ermitteln. Wird z.B. ein hoher Prozentsatz der Tabelle abgefragt (ca. > 15%) so wird sich der Optimizer für einen Full Table Scan entscheiden und alle Blöcke der Tabelle lesen. Wird nur ein Datensatz abgefragt, so wird der Optimizer einen Index Zugriff wählen.

Das Ergebnis des Optimizers ist der sog. EXECUTION PLAN. Er legt fest wie und in welcher Reihenfolge Daten geholt werden, welche Indizes genutzt werden und mit welcher Methode z.B. Join Operationen ausgeführt werden. Gerade bei komplexen Statements ist der Optimizer jedoch nicht in der Lage den besten EXECUTION PLAN zu erstellen. Sicher wird er auch zu einem Ergebnis kommen, aber leider nicht immer zum schnellsten oder billigsten (= Ressourcenschonend). 

Wenn wir gerade bei den Kosten sind: Der Optimizer entscheidet sich anhand von berechneten und geschätzen "Kosten" für eine Operation. Er schätzt die Anzahl der erwarteten Zeilen und vergleicht somit unterschiedliche EXECUTION PLÄNE. Das kann natürlich nur gut funktionieren wenn der Optimizer möglichs gute Informationen über die Daten hat. Diese Informationen nennt Oracle "Statistiken". Wer in seiner Datenbank also nicht regelmäßig Statistiken erzeugt sollte sich mal das "Statspack" DBMS_STATS ansehen. 

Hier ein Beispiel wie man Statistiken sammeln könnte. Das ganze sollte regelmäßig etwa als Job ausgeführt werden. Aber vorsicht, auf großen Datenbanken (>GB) dauert das Statistik sammeln möglicherweise etwas länger und kann schonmal ein WE durchlaufen.


```
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
   ownname          => 'schemaeigner',
   method_opt       => 'FOR ALL COLUMNS SIZE SKEWONLY',
   cascade          => true,
   options          => 'GATHER STALE'
);
END;
/
```

Jetzt aber zum eigentlichen Tuning TIP:
Wie gesagt findet der Optimizer nicht immer den besten Plan. Um herauszufinden welchen er überhaupt benutzt gibt es die Möglichkeit eines 

EXPLAIN PLAN FOR statement

Das Problem ist, dass EXPLAIN PLAN zwar den Ausführungsplan zeigt, wir aber dennoch dessen Qualität schlecht abschätzen können.
Eine bessere aber auch aufwendigere Methode ist das SQL Tracing mit TKPROF, ein von Oracle mitgeliefertes Kommandozeilen Tool.

Nehmen wir als Beispiel ein ganz einfaches Statement.


```
SQL> ALTER SESSION SET SQL_TRACE=true;

Session wurde geõndert.

SQL> SELECT EMPNO, ENAME, DNAME
  2  FROM   EMP, DEPT
  3  WHERE  EMP.DEPTNO = DEPT.DEPTNO
  4  AND    ENAME = 'KING';

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7839 KING       ACCOUNTING

SQL> ALTER SESSION SET SQL_TRACE=false;

Session wurde geõndert.
```

In diesem Beispiel wurde gleich das SQL Tracing aktiviert und nach dem Statement wieder deaktiviert. Das Tracing hat nun eine Datei in der USER_DUMP_DEST erzeugt. Wer nicht weiss wo das ist kann dies mit folgendem herausfinden:


```
SQL> conn system/manager
Connect durchgef³hrt.
SQL> show parameter user_dump

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
user_dump_dest             string      c:\oracle\admin\DB920\udump
```

Es ist möglich dass in dem Verzeichnis mehrere Dateien liegen. Schafft ihr es nicht anhand des Datums und der Uhrzeit die richtige Datei herauszufinden gibt es ein einfaches SQL Statement mit dem ihr die richtiger Endungsnummer findet:


```
select SPID 
from v$process 
where addr = (
      select PADDR 
      from v$session 
      where AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
);
```

Die erzeugte Datei können wir so noch nicht wirklich gut lesen, deshalb helfen wir uns mit dem Tool TKPROF weiter. Es erwartet als Parameter mindestens den Namen der Eingabe-, sowie der Ausgabedatei. Der Parameter sys=no ist ganz Sinnvoll um "unwichtige" Systemaktionen auszuklammern.


```
tkprof bodev_ora_3644.trc explain1.txt sys=no
```

Das erzeugte Textfile enthält nun alle Inforamtionen die wir brauchen um die Qualität des Statements zu analysieren.


```
SELECT EMPNO, ENAME, DNAME
FROM   EMP, DEPT
WHERE  EMP.DEPTNO = DEPT.DEPTNO
AND    ENAME = 'KING'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         10          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         10          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS  (cr=10 r=0 w=0 time=147 us)
      1   TABLE ACCESS FULL EMP (cr=8 r=0 w=0 time=97 us)
      1   TABLE ACCESS BY INDEX ROWID DEPT (cr=2 r=0 w=0 time=29 us)
      1    INDEX UNIQUE SCAN PK_DEPT (cr=1 r=0 w=0 time=13 us)(object id 8282)
```

Wir sehen z.B. dass ein NESTED LOOP gemacht wurde. Bei der Anzahl der erwarteten Zeilen und duch Verwendung eines Indizes sichlich die Beste Wahl. In diesem Fall ist der Plan des Optimizers natürlich ideal. Optimierungsbedarf besteht immer dann, wenn die Ausführungszeit unerwartet hoch ist und/oder die Werte disk, query und current total hoch sind. Ich habe schon Statements gesehen, die nach einer Optimierung von 20 Minuten auf unter einer Sekunde optimiert werden konnten. 

Jetzt wissen wir zwar, was der Optimizer gemacht hat, aber wie können wir das Verbessern ?

1.) Umschreiben des SQL Statements. Dazu ist zu sagen, dass hier besonders bei komplexen Statements auf die Logik geachtet werden muss. Häufig liefert ein Umbau des Statements eine andere Ergebnismenge (z.B. bei Joins). Ein Kochrezept wie gute Statements aussehen gibt es nicht, daher sollte auch JEDES Statement eines Programms einmal so oder zumindest mit EXPLAIN PLAN analysiert werden. Ein Beispiel für eine Änderung wäre die Zusammenfassung eines Subselects in einen Join. 

2.) HINTS sind eine gute Möglichkeit dem Optimizer eine andere Ausführungsart des Statements nahe zu legen. Sofern diese hints syntaktisch korrekt sind und z.B. die Indizes auch existieren die wir als HINT verlangen wird der Optimizer diese auch verwenden. HINTS sollten allerdings mit Vorsicht verwendet werden, da beim Umstieg auf andere DB Versionen sich z.B. das Verhalten des optimizers ändern könnte und durch die HINTS genau das Gegenteil erreicht wird, nämlich miese Statements.

Verwenden wir nun zur Demonstration ein Hint auf das schon bekannte Statement:


```
ALTER SESSION SET SQL_TRACE=true;
SELECT /*+ use_hash(emp dept) */
	   EMPNO, ENAME, DNAME
FROM   EMP, DEPT
WHERE  EMP.DEPTNO = DEPT.DEPTNO
AND    ENAME = 'KING';
ALTER SESSION SET SQL_TRACE=false;
```

Wie das Tracefile zeigt ist der neue Plan nun, nennen wir ihn Suboptimal 

```
SELECT /*+ use_hash(emp dept) */
	   EMPNO, ENAME, DNAME
FROM   EMP, DEPT
WHERE  EMP.DEPTNO = DEPT.DEPTNO
AND    ENAME = 'KING'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         15          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         15          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH JOIN  (cr=15 r=0 w=0 time=568 us)
      1   TABLE ACCESS FULL EMP (cr=7 r=0 w=0 time=90 us)
      4   TABLE ACCESS FULL DEPT (cr=8 r=0 w=0 time=83 us)
```

So, ich hoffe es war ein bisschen interessant und lehrreich. Wer noch Fragen hat darf diese natürlich stellen. Das Thema Tuning und Optimizer ist recht komplex, daher kann ich hier längst nicht alle Möglichkeiten und Vorgensweisen ausführen, aber es ist mal ein Anfang.


----------



## mschuetzda (24. Oktober 2004)

Da kann ich einfach nur DANKE sagen.
Wirklich hilfreich, knapp und verständlich beschrieben.   

Lieben Gruß
mschuetzda


----------



## Thomas Darimont (24. Oktober 2004)

WOW!

Das hast du wiedermal gut gemacht, exceptionfault!

Nett wäre es aber noch zu erwähnen, dass es bei Oracle zwei verschiedene "Optimizer" gibt. Nämlich einen sogenannten Rule-Based Optimizer der eben versucht nach ganz bestimmten Regeln die optimalie Ausführungssequenz für einen SQL Befehl zu bestimmen und den Cost-Based Optimizer, welcher sich auf statistische Informationen und verschiedene Heuristiken stützt. Ich glaube mich zu erinnern, dass Oracle standardmäßig den Cost Based Optimizer verwendet und auch empfiehlt bei diesem zu bleiben, da dieser in der Regel die besseren Ergebnisse produziert.

Weiter so!

Gruß Tom


----------



## Exceptionfault (24. Oktober 2004)

Danke für das Lob   



> dass Oracle standardmäßig den Cost Based Optimizer verwendet und auch empfiehlt


Nicht nur das, der Rule Based Optimizer unterstützt viele neue (9i) Features wie Bitmap Join Indizes, Partitionierte Tabellen etc. nicht. In 10g ist er auch nicht mehr supported, d.h. er ist noch drin, um ihn zu aktivieren muss aber glaub ich ein undokumentierter Parameter verwendet werden.


----------



## Exceptionfault (27. Oktober 2004)

So, ich führe hiermit meinen kleinen Tuning Workshop weiter und versuche mal eine etwas andere Richtung des Tunings näher zu erläutern. Nehmen wir mal an wir hätten in unserer Applikation sämtliche SQL Statements nach der oben beschriebenen Methode (SQL_TRACE) mindestens einmal optimiert, und sind uns sicher, dass der Ausführungsplan optimal ist. Die Antwortzeiten sind aber immer noch nicht zufriedenstellend. Wir müssen also annehmen, dass die Konfiguration der Datenbank in irgend einer Weise nicht optimal ist. 

Nun, gleich vorweg. Mit der folgenden Methode sind im Normalfall keine Wunder zu erwarten. Die größten Performance Verbesserungen erreicht man mit SQL Tuning, es sei denn die Datenbank ist wirklich miserabel aufgesetzt. Und selbst dann wäre ein Aufrüsten der Hardware meist effektiver, z.B. mehr RAM, schnellere Platten, Striping etc Aber wir wollen ja mal sehen was es noch für Möglichkeiten gibt, also weiter...

Oracle bietet in der Version 9.2.0.4 ca. 257 Parameter, und das sind NUR die dokumentierten. Gemeinsam mit den undokumentierten Parametern kommen wir auf 840. Wo soll man da optimieren? Versuchen wir also die Problemstellen unserer Datenbank etwas näher einzugrenzen:

Ein Beispiel: Der User Scott legt eine neue Tabelle an. Das Statement ist sicherlich NICHT optimal, aber wir brauchen ja etwas Last um unsere Applikation zu simulieren ;-), daher habe ich die Tabelle ein "klein wenig" vergrößert.


```
SQL> CREATE TABLE test AS 
  2  SELECT e1.*            
  2  FROM EMP E1, EMP E2, EMP E3, EMP E4, EMP E5;

Tabelle wurde angelegt.

...

SQL> INSERT INTO test
  2  SELECT * FROM TEST;

2151296 Zeilen wurden erstellt.
```

Als DBA werden wir nun die Session des Users Scott mal genauer unter die Lupe nehmen. Scott muss solange zur Datenbank connected bleiben, also neue Session aufmachen und folgendes ausführen:


```
SQL> -- SID von Scott feststellen
SQL> SELECT SID FROM V$SESSION WHERE USERNAME = 'SCOTT';

       SID
----------
        10
        
SQL> COL EVENT FOR A30
SQL>
SQL> SELECT EVENT, TIME_WAITED/100, TOTAL_WAITS, AVERAGE_WAIT 
  2  FROM V$SESSION_EVENT 
  3  WHERE SID = 10 
  4  ORDER BY TIME_WAITED DESC;

EVENT                          TIME_WAITED/100 TOTAL_WAITS AVERAGE_WAIT
------------------------------ --------------- ----------- ------------
SQL*Net message from client             928,91        8559           11
rdbms ipc reply                          25,43          29           88
db file scattered read                   18,78        1031            2
log buffer space                          9,68         295            3
log file sync                             1,04           4           26
log file switch completion                 ,59          39            2
control file parallel write                ,53         124            0
db file single write                       ,12          62            0
db file parallel read                      ,11           1           11
db file sequential read                    ,06         124            0
control file sequential read               ,02         623            0
```

Wir sehen hier einen Auszug der Session Statistiken des Users Scott. Vergessen wir die ersten zwei Zeilen, sie zeigen uns sog. Idle Events. Hier ist nicht wirklich was passiert. Als nächstes sehen wir dann aber "db file scattered read". Das ist nichts anderes als Full Table Scans. Die Tabelle zeigt uns, wir wären 18,78 Sekunden schneller mit den Tabellenoperationen gewesen, hätten wir nicht 1031 mal auf einen Full Table Scann warten müssen. In diesem Fall ist also wohl unser I/O Durchsatz der Platte oder des Controlers zu niedrig (Wenn wir davon ausgehen das Statement wäre optimal!) Der nächste Eintrag "log buffer space" würde uns darauf hinweisen, dass der Logbuffer zu klein ist und wir zu oft in die Redo Logs flushen müssen. In meinem Fall ist er 500KB groß, was für Statements dieser Art wohl zu klein ist. Eine Vergrößerung auf 1MB wäre hier z.B. ein Versuch wert.

Es gibt kein Kochrezept um mit dieser Methode eine Performance Verbesserung zu erreichen, und man muss genau abwägen in wie weit die Zahlen zu "erwarten" waren. D.h. bei meinem schlechten Statement war es nicht überraschend, dass es an den DISK Operationen (I/O) hängt. Man sieht aber z.B. auch dass der Eintrag mit den redo logs "log file switch completion" nicht sonderlich ins gewicht fällt. Offenbar sind meine Redo Logs gut Konfiguriert, es treten also keine Probleme durch Log Switches auf.

Achso...
Und wer die Statistiken etwas allgemeiner haben möchte, der kann diese natürlich auch über die komplette DB und nicht nur über eine einzelne Session abrufen:


```
SQL> SELECT EVENT, TIME_WAITED/100, TOTAL_WAITS, AVERAGE_WAIT
  2  FROM V$SYSTEM_EVENT
  3  ORDER BY TIME_WAITED DESC;

EVENT                          TIME_WAITED/100 TOTAL_WAITS AVERAGE_WAIT
------------------------------ --------------- ----------- ------------
rdbms ipc message                    619946,94      113465          546
pmon timer                            92752,62       31946          290
smon timer                            90157,46         357        25254
SQL*Net message from client           11927,06       10063          119
control file parallel write              94,96       30525            0
rdbms ipc reply                          28,23         191           15
db file scattered read                    19,5        1122            2
log buffer space                          9,68         295            3
db file sequential read                   5,19        1036            1
control file heartbeat                     4,1           1          410
log file sequential read                  3,33         266            1
```


----------

