Exceptionfault
Erfahrenes Mitglied
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.
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.
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:
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:
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.
Das erzeugte Textfile enthält nun alle Inforamtionen die wir brauchen um die Qualität des Statements zu analysieren.
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:
Wie das Tracefile zeigt ist der neue Plan nun, nennen wir ihn Suboptimal
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.
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.
Code:
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.
Code:
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:
Code:
SQL> conn system/manager
Connect durchgef³hrt.
SQL> show parameter user_dump
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
user_dump_dest string c:\oracle\admin\DB920\udumpEs 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:
Code:
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.
Code:
tkprof bodev_ora_3644.trc explain1.txt sys=noDas erzeugte Textfile enthält nun alle Inforamtionen die wir brauchen um die Qualität des Statements zu analysieren.
Code:
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:
Code:
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
Code:
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.
