Generating Execution plan for - Lambertz

Bitmap Conversion - Optimierung der besonderen Art
Mit der Oracle Version 9.2 wurden wieder bedeutende Änderungen am Optimizer
vorgenommen, so Oracle. Für mich gab es nach einem Wochenende eine ziemliche
Überraschung. Die Antwortzeiten der Produktions-Instanz waren um ein vielfaches
schlechter als noch an dem Freitag vor dem Wochenende. Nachweislich wurden an der
Instanz keine Änderungen vorgenommen und über das Wochenende erfolgte nur die übliche
Nachtverarbeitung.
Das tracen einer Session zeigte, daß in einer SQL*Forms Maske ein einziges Statement
sämliche Rechnerzeit beanspruchte.
SELECT PRO_DAT
FROM PROTOKOLL
WHERE PRO_ID = (SELECT
FROM
WHERE
AND
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
MAX(PRO_ID)
PROTOKOLL
PRO_TAE_ID = :a1
PRO_AUA_ID IN (SELECT AUA_ID
FROM AUFTRAGS_ARTEN
WHERE AUA_KEN IN ( '11','225'
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
8.31
0
62445
0
0.00
0.00
0
5
0
-------- ---------- ---------- ---------- ---------0.00
8.31
0
62450
0
)));
rows
---------0
0
1
---------1
Execution Plan
----------------------------------------------------------------------------------1.1 TABLE ACCESS BY INDEX ROWID SACH1.PROTOKOLL
2.1 INDEX UNIQUE SCAN SACH1.PRO_PK
3.1 SORT AGGREGATE
4.1 TABLE ACCESS BY INDEX ROWID SACH1.PROTOKOLL
5.1 NESTED LOOPS
6.1 INLIST ITERATOR
7.1 TABLE ACCESS BY INDEX ROWID SACH1.AUFTRAGS_ARTEN
8.1 INDEX RANGE SCAN SACH1.AUA_UK
6.2 BITMAP CONVERSION TO ROWIDS
7.1 BITMAP AND
8.1 BITMAP CONVERSION FROM ROWIDS
9.1 INDEX RANGE SCAN SACH1.PRO_FK_AUA_I
8.2 BITMAP CONVERSION FROM ROWIDS
9.1 SORT ORDER BY
10.1 INDEX RANGE SCAN SACH1.PRO_PK
-----------------------------------------------------------------------------------
Auffällig an den Statistiken ist, daß in der Zeile „Execute“ cpu und elapsed Zeit ausgewiesen
werden, obwohl ein Select Statement gewöhnlich nur fetch Zeiten produziert. Zudem
existieren kein BitMap Indizes!
Die Untersuchung der beiden Tabellen zeigt, daß die Füllstände sehr unterschiedlich sind:
SQL > select count(*) from PROTOKOLL;
COUNT(*)
---------25675811
1 row selected.
SQL > select count(*) from AUFTRAGS_ARTEN;
COUNT(*)
---------89
1 row selected.
SQL >
Für den Join von Tabellen mit solch unterschiedlicher Füllständen ist ein Hash Join sehr
performant, für mich der beste Zugriff. Das Einbringen des Hint /*+
USE_HASH(PROTOKOLL) */ zeigt bei erneuter Ausführung der SQL*Forms tatsächlich eine
wesentlich verbesserte Laufzeit:
SELECT PRO_DAT
FROM PROTOKOLL
WHERE PRO_ID = (SELECT /*+ USE_HASH(PROTOKOLL) */
MAX(PRO_ID)
FROM PROTOKOLL
WHERE PRO_TAE_ID = :a1
AND PRO_AUA_ID IN (SELECT AUA_ID
FROM AUFTRAGS_ARTEN
WHERE AUA_KEN IN ( '11','225'
call
count
------- -----Parse
1
Execute
0
Fetch
1
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.00
0
0
0
0.00
0.00
0
0
0
0.13
0.14
0
92
0
-------- ---------- ---------- ---------- ---------0.14
0.14
0
92
0
)));
rows
---------0
0
1
---------1
Execution Plan
-------------------------------------------------------------------------------1.1 TABLE ACCESS BY INDEX ROWID SACH1.PROTOKOLL
2.1 INDEX UNIQUE SCAN SACH1.PRO_PK
3.1 SORT AGGREGATE
4.1 HASH JOIN
5.1 TABLE ACCESS BY INDEX ROWID SACH1.PROTOKOLL
6.1 INDEX RANGE SCAN SACH1.PRO_PK
5.2 INLIST ITERATOR
6.1 TABLE ACCESS BY INDEX ROWID SACH1.AUFTRAGS_ARTEN
7.1 INDEX RANGE SCAN SACH1.AUA_UK
--------------------------------------------------------------------------------
In diesen Statistiken werden auch wieder ordnungsgemäß „Fetch“ Zeiten und keine
„Execute“ Zeiten ausgewiesen.
Es konnte ermittelt werden, daß vor dem Wochenende Oracle diesen Zugriff „freiwillig“
gewählt hatte und erst nach dem aktualisieren der Tabellen Statistiken, Oracle einen
Ausführungsplan mit „Bitmap conversion to RowId“ bevorzugte; auch wenn sich die
Füllstände mit der Nachtverarbeitung nicht nennenswert verändert haben.
Nun ist aber eine Änderung an den Sourcen nicht immer möglich. Hier hilft in den meisten
Fällen, die Statistiken auf den beteiligten Indizes zu löschen.
Nicht nur, daß Oracle mit der Zugriffsart „Bitmap conversion to RowId“ scheinbar noch
einige Probleme hat, kann sich das Laufzeitverhalten ohne erkennbaren Grund drastisch
ändern, weil der Optimizer die „Bitmap conversion to RowId“ für wesentlich performanter
hält.