Eine Trace Datei - Lambertz

CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Inhaltsverzeichnis
1.
2.
Eine Trace Datei _______________________________________________________ 6
1.1.
Session Trace __________________________________________________________ 7
1.2.
Trace einer beliebigen Session ____________________________________________ 7
1.3.
Probleme beim Erstellen einer Trace Datei__________________________________ 8
1.4.
Autotrace______________________________________________________________ 9
Trace Datei und TKProf Ausgabe ________________________________________ 11
2.1.
Das Trace File im Überblick _____________________________________________ 11
2.1.1.
Beschreibung der Ergebnisse in einer Trace Datei __________________________________ 15
2.2.
Erstellen einer TKProf Datei ____________________________________________ 17
2.3.
Interpretieren einer TKProf Datei ________________________________________ 19
2.3.1.
2.3.2.
3.
Darstellung der Ergebnisse in einer TKProf Datei __________________________________ 20
Was bildet die Differenz zwischen CPU- und elapsed time____________________________ 30
So arbeitet Oracle _____________________________________________________ 33
3.1.
Auflösung eines Statement ______________________________________________ 33
3.2.
Der Index_____________________________________________________________ 41
3.2.1.
3.2.2.
3.3.
Ungünstiger CLUSTERING_INDEX ____________________________________________ 41
Index Selektivität ____________________________________________________________ 43
Verschiedene Indextypen________________________________________________ 48
3.3.1.
3.3.2.
3.3.3.
3.4.
B*tree Index _______________________________________________________________ 48
Bitmap Index _______________________________________________________________ 53
Hash Index_________________________________________________________________ 53
Wann Oracle einen Index im CHOOSE Mode nicht nutzt ____________________ 53
3.4.1.
3.4.2.
3.4.3.
3.4.4.
3.4.5.
3.4.6.
3.4.7.
3.4.8.
3.4.9.
3.4.10.
3.4.11.
3.4.12.
3.4.13.
3.4.14.
4.
Zu große Anzahl von gelöscht markierten Indexeinträgen ____________________________
Geringes Datenvolumen der Tabelle _____________________________________________
Einschränkung mit != oder <> __________________________________________________
Unbekannte Selektivität durch Bereichsabfragen ___________________________________
Unbekannte Selektivität durch Einsatz von Bindevariablen ___________________________
Unterbrechung der Reihenfolge von Indexattributen_________________________________
Füllgrad des Index-Key _______________________________________________________
Fehlende Statistiken__________________________________________________________
Veraltete Statistiken__________________________________________________________
Abfrage mit IS NULL ______________________________________________________
Abfrage IS NOT NULL ____________________________________________________
Mehrere Indizes __________________________________________________________
Ungünstiger Clustering_Index _______________________________________________
LIKE in der WHERE Klausel________________________________________________
53
54
54
54
55
55
57
59
59
59
60
60
61
61
Erklärung eines Explain Plan ___________________________________________ 63
4.1.
Operationen eines Explain Plan __________________________________________ 66
______________________________________________________________________________________________________
Oracle Trace
Seite 2
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
4.1.1.
4.1.2.
4.1.3.
4.1.4.
4.1.5.
4.1.6.
4.1.7.
4.1.8.
4.1.9.
4.1.10.
4.1.11.
4.1.12.
4.1.13.
4.1.14.
4.1.15.
4.1.16.
4.1.17.
4.1.18.
4.1.19.
4.1.20.
4.1.21.
4.1.22.
4.1.23.
4.1.24.
4.1.25.
4.1.26.
4.1.27.
4.1.28.
4.1.29.
4.1.30.
4.2.
TABLE ACCESS (FULL)_____________________________________________________
TABLE ACCESS (CLUSTER) _________________________________________________
TABLE ACCESS (HASH) ____________________________________________________
TABLE ACCESS (BY ROWID)________________________________________________
INDEX (RANGE SCAN) _____________________________________________________
INDEX (UNIQUE SCAN) ____________________________________________________
INDEX (FAST FULL SCAN) __________________________________________________
INDEX (FULL SCAN) _______________________________________________________
INDEX (FULL SCAN (MIN/MAX)) ____________________________________________
CONNECT BY ___________________________________________________________
FILTER_________________________________________________________________
MERGE JOIN____________________________________________________________
MERGE JOIN (OUTER) ___________________________________________________
MERGE JOIN (CARTESIAN) _______________________________________________
SORT (ORDER BY) ______________________________________________________
SORT (JOIN) ____________________________________________________________
SORT (AGGREGATE) ____________________________________________________
SORT (UNIQUE) _________________________________________________________
SORT (GROUP BY) ______________________________________________________
NESTED LOOPS (OUTER)_________________________________________________
NESTED LOOPS (ANTI) __________________________________________________
NESTED LOOPS (SEMI) __________________________________________________
CONCATENATION ______________________________________________________
INTERSECTION _________________________________________________________
MINUS _________________________________________________________________
UNION _________________________________________________________________
UNION-ALL_____________________________________________________________
VIEW __________________________________________________________________
NESTED LOOPS _________________________________________________________
HASH JOIN _____________________________________________________________
66
66
67
73
74
75
75
77
77
77
77
78
78
78
80
80
80
80
80
81
81
81
81
81
81
81
81
81
82
84
INDEX RANGE SCAN _________________________________________________ 85
5.
Bedeutung von Tabellen und Index Statistiken _____________________________ 88
6.
Glossar______________________________________________________________ 89
6.1.
Recursive calls ________________________________________________________ 89
6.2.
DB_BLOCK_SIZE_____________________________________________________ 89
6.3.
DB_FILE_MULTIBLOCK_READ_COUNT _______________________________ 89
6.4.
Chained rows _________________________________________________________ 90
6.5.
Cost _________________________________________________________________ 91
6.6.
DB Block gets _________________________________________________________ 91
6.7.
DB Block changes______________________________________________________ 91
6.8.
Datenblöcke im Überblick_______________________________________________ 92
6.9.
Extents _______________________________________________________________ 93
6.10.
High Water Mark______________________________________________________ 93
6.11.
User calls _____________________________________________________________ 93
______________________________________________________________________________________________________
Oracle Trace
Seite 3
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.12.
Execute counts ________________________________________________________ 93
6.13.
Distinct keys __________________________________________________________ 94
6.14.
Cardinalität___________________________________________________________ 94
6.15.
Rule _________________________________________________________________ 95
6.16.
Choose _______________________________________________________________ 97
6.17.
Treibende Tabelle_____________________________________________________ 100
6.18.
Der Shared Pool Memory ______________________________________________ 105
6.19.
Die SQL Area ________________________________________________________ 106
6.20.
Library cache ________________________________________________________ 106
6.21.
db file scattered read __________________________________________________ 107
6.22.
db file sequential read _________________________________________________ 107
6.23.
Buffer busy waits _____________________________________________________ 107
6.24.
Free buffer waits _____________________________________________________ 108
6.25.
Latch free ___________________________________________________________ 108
6.26.
Log buffer space ______________________________________________________ 109
6.27.
Log file switch________________________________________________________ 109
6.28.
Parse _______________________________________________________________ 109
6.28.1.
6.28.2.
7.
Hard Parse______________________________________________________________ 114
Soft Parse ______________________________________________________________ 117
Index ______________________________________________________________ 118
______________________________________________________________________________________________________
Oracle Trace
Seite 4
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
1.
Eine Trace Datei
Auch wenn eine Oracleinstanz nur mittelmäßig konfiguriert wurde, sind diese
Performance-Verluste geringer als die, welche durch SQL Statements auslöst
werden können. Eine Vielzahl an Mißständen kann die Ursache sein. Ohne
Hilfsmittel ist es fast ummöglich sie zu finden. Ein Hilfsmittel ist es, während
das oder die Statements abgearbeitet werden von Oracle eine Tracedatei
erstellen zu lassen
In der Trace Datei werden alle Statements protokolliert, welche direkt
angegebenen oder durch den Aufruf von Modulen (Packages, Proceduren
oder Function) verarbeitet wurden, aber auch solche, welche implizit durch
Oracle zur Ausführung gelangten. In einer solchen Datei werden zu jedem
Statement zusätzlich Statistiken „eingefangen“, die für das Tuning von
höchster Bedeutung sind.
Trace Dateien werden immer im „user_dump_dest“ Verzeichnis der Oracle
Instanz erzeugt. Das physikalische Verzeichnis, welches sich hinter dem Alias
„user_dump_dest“ verbirgt, kann in der initSID.ora Datei direkt eingesehen
werden, oder aber mit einem Select Statemenet ermittelt werden, wie es
nachfolgend beschrieben ist.
SELECT NAME, VALUE
FROM SYS.V_$SYSTEM_PARAMETER
WHERE name = 'user_dump_dest'
Die Oracle Datenbank besitzt gewöhnlich für dieses Verzeichnis ein Schreibund Leserecht. Der User, welcher die Trace Datei später auswerten möchte
verfügt nicht automatisch über diese Rechte! Dies ist gerade für
Bertriebsysteme wie Unix zu berücksichtigen.
Im Dateinamen einer Tracedatei ist die Session ID enthalten, mit der das
Betriebssystem die Session eindeutig verwaltet hat.
So ergibt sich als Beispiel eine Datei mit dem Namen „ora_488.trc“ im
„user_dump_dest“ Verzeichnis. Das bedeutet, daß die Session (hier
SQL*Plus) vom Betriebsystem unter der Nummer 488 geführt wurde.
Nur wenn der initSID.ora Parameter TIMED_STATISTICS auf TRUE gesetzt
oder die Session entsprechend geändert wurde (ALTER SESSION SET
TIMED_STATISTICS=TRUE), entstehen in der Trace Datei zeitliche
Statistiken. Ausführungsstatistiken sind in jedem Fall enthalten.
Das Erzeugen von zeitlichen Statistiken, also Timed Statistics, führt zu
Verlusten zwischen 1% und 5% der CPU Leistung. Zu unterscheiden ist, ob
______________________________________________________________________________________________________
Oracle Trace
Seite 5
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
TIMED_STATISTICS für die gesamte Instanz gilt oder auf eine Session
beschränkt ist.
In einer Trace Datei können nicht mehrere Sessions protokolliert werden.
Es gibt verschiedene Möglichkeiten eine Trace Datei zu erzeugen. Welche
das im Einzelnen sind und worin sie sich unterscheiden wird nachfolgend
beschrieben. Eine Trace Datei wird erst angelegt, wenn mindestens ein
Statement bearbeitet wurde und nicht zu dem Zeitpunkt zu dem der Trace
aktiviert wurde.
1.1.
Session Trace
Ein „session trace“ ist nur für eine Session möglich, wie sie zum Beispiel
entsteht, wenn SQL*Plus gestartet wird. Ein solches Programm muß über die
Möglichkeit verfügen, die Oracle Session zu verändern (ALTER SESSION...)
ALTER SESSION SET SQL_TRACE = TRUE;
Der Trace kann jedoch nur eingeschaltet werden, wenn der Oracle User, der
mit der Datenbank verbunden ist, über die Berechtigung „ALTER SESSION“
verfügt.
Erst wenn der Tracemodus beendet wurde, wird die Tracedatei wieder
geschlossen. Entweder durch ein ALTER SESSION Statement wie
nachfolgend beschrieben oder durch das Beenden der Session (Disconnect
oder Programmende).
ALTER SESSION SET SQL_TRACE = FALSE;
Wird innerhalb der gleichen Session mehrmals ein Trace ein- und
abgeschaltet, werden alle Trace Ergebnisse in der selben Datei gespeichert!!
1.2.
Trace einer beliebigen Session
Wenn das Einschalten eines „Session trace“ nicht möglich ist (z.B. weil die
Verarbeitung schon läuft oder USER X einen Trace für USER Y erstellen
möchte), bietet Oracle die Möglichkeit, den Trace mit hilfe einer Procedure
eines Package zu starten. Der Aufruf lautet:
______________________________________________________________________________________________________
Oracle Trace
Seite 6
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
sys.dbms_system.set_sql_trace_in_session( SID, serial#, What );
Es werden jedoch SID und SERIAL# benötigt, um die Procedure ausführen zu
können, die mit folgenden Script ermittelt werden können. Unter dem Einsatz
von CUT und PASTE erleichert die Prompt Ausgabe den Aufruf.
REM
ACCEPT UserName PROMPT 'Name des Oracle Users: ';
SELECT osuser, username, status, sid, serial#, Terminal, program
FROM v$session
WHERE osuser like UPPER( '&UserName' );
PROMPT EXEC sys.dbms_system.set_sql_trace_in_session( SID, serial#, What );
Zu jeder SID, SERIAL# erfolgt noch die Angabe, ob der Trace gestartet
(TRUE) oder beendet (FALSE) werden soll.
Wird für die gleiche Session (SID und SERIAL#) mehrmals ein Trace ein- und
abgeschaltet, werden alle Ergebnisse in der selben Datei gespeichert.
Die Art ein Trace zu erzeugen ist auch dann erforderlich, wenn nicht eine
gesamte Session, sondern nur ein Ausschnitt der Verarbeitung überwacht
werden soll – zum Beispiel aus Zeit oder Plattenplatz Gründen.
Der User, welche eine fremde Transaktion oder Session tracen möchte, muß
über das Ausführungsrecht der Package DBMS_SYSTEM verfügen und zwar
als direkten Grant; nicht über eine Role.
1.3.
Probleme beim Erstellen einer Trace Datei
•
Werden Trace Dateien aus dem „user_dump_dest“ Verzeichnis gelöscht,
ohne daß die Datenbank neu gestartet wird, werden keine neuen
Tracefiles erzeugt.
•
Ein EXPLAIN PLAN wird z.B. in einer TKProf Ausgabe Datei nicht erstellt,
obwohl die Angabe „explain=..“ für die Erstellung einer TKProf
Ausgabedatei angegeben wurde, wenn die Tabelle PLAN_TABLE für den
User, mit dem der Ausführungsplan erstellt werden soll, keine
Berechtigung auf diese Tabelle hat, die Tabelle nicht existiert, oder ein
anderes Format besitzt, als es von Oracle für die Datenbankversion
vorausgesetzt wird.
•
Werden die Anzahl Rows in einer Trace- oder einer TKProf Datei nicht
angezeigt, kann es daran liegen, daß ein Trace erzeugt wurde für ein
Statement, welche schon über einen längeren Zeitraum lief. Auch ein
ALTER SESSION... kann dazu führen, daß für vereinzelte Statements
keine Rows angezeigt werden. Werden in mehreren Trace Dateien die
______________________________________________________________________________________________________
Oracle Trace
Seite 7
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Rows nicht angezeigt, hat sich die Instanz „verschluckt“ und es hilft nur ein
reboot.
1.4.
•
Wurde der Trace noch nicht geschlossen (zum Beispiel ALTER SESSION
SET SQL_TRACE = FALSE), werden für die TKProf Datei keine Rows
angezeigt.
•
Laufzeit Statistiken (alle zeitlichen Statistiken) werden nicht ermittelt, wenn
die überwachte Session im Modus TIMED_STATISTICS=FALSE arbeitet.
•
Eine Trace Datei entsteht nicht zu dem Zeitpunkt, zu dem der Trace
eingeschaltet wurde, sondern erst, nachdem das erste Statement
ausgeführt wurde.
•
Wird innerhalb einer Session ein Trace mehrmals ein und abgeschaltet,
werden die Ergebnisse des Trace in die selbe Datei eingetragen. Es
entsteht keine neue Trace Datei.
Autotrace
Autotrace ist eine Low Cost Version an Tuning Informationen zu gelangen
unter SQL*Plus. Wesentlicher Unterschied liegt in den fehlenden Statistiken
über das Statement. So werden werden nicht die Ausführungsdauer oder die
bearbeiteten Rows angezeigt, welche für die Ergebnismenge bearbeitet
werden mußten. Ein EXPLAIN PLAN und die Ausführungsstatistiken wie
CONSISTENT GETS (gleichbedeutend den Query Werten einer TKProf
Datei) sind aber vorhanden.
Autotrace wird gestartet in SQL*Plus durch die Eingabe
SET AUTOTRACE ON
und bleibt solange aktiv, bis der Modus mit
SET AUTOTRACE OFF
wieder beendet oder die SQL*Plus Session geschlossen wird.
Autotrace bewirkt, daß alle eingegebenen Statemets getraced werden und die
Trace Ergebnisse direkt nach der Ausführung angezeigt werden.
Autotrace liefert nur dann die gwünschten Ergebnisse wenn die Tabelle
SYS.PLAN_TABLE existiert und der User über Schreib- und Leserechte für
diese Tabelle verfügt. Weitere Berechtigungen, die erforderlich sind, stellt
Oracle mit dem Script PLUSTRACE.SQL bereit. Hierbei handelt es sich um
die Einrichtung einer Berechtigungsrolle „plustrace“ wie folgt:
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
______________________________________________________________________________________________________
Oracle Trace
Seite 8
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
grant plustrace to xxxx with admin option;
Nachfolgend ein Beispiel für den Einsatz von AUTOTRACE:
SQLL > set autotrace on
SQLL > select * from dual;
D
X
1 row selected.
real: 581
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'DUAL'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
175 bytes sent via SQL*Net to client
256 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL > set autotrace off;
______________________________________________________________________________________________________
Oracle Trace
Seite 9
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
2.
Trace Datei und TKProf Ausgabe
Der Inhalt einer Trace Datei ist nicht zuletzt wegen der Dateigröße sehr
schwierig lesbar. Aus diesem Grunde liefert Oracle ein Tool aus, das eine
Zusammenfassung aller Informationen aus einer Trace Datei erstellt und in
einer beliebig zu benennenden Ausgabe Datei speichert.
Aber die Beschreibung der einzelnen Informationen direkt in der Trace Datei
ist nicht weniger von Bedeutung als die TKProf Ausgabe. Z.B. sind nur in
einer Trace Datei alle Informationen über Ablaufreihenfolgen erkennbar,
welche Cursor von welchen abhängig waren, wie groß die Arrays waren die
für einen FETCH eines oder mehrerer Statements benutzt wurde und in
welcher Häufigkeit der Aufruf des gleichen Statement durch Oracle erfolgt
sind.
2.1.
Das Trace File im Überblick
Mit den ersten Zeilen werden die Randbedingungen beschreiben die vorlagen,
als die Trace Datei erstellt wurde.
Angaben über den Namen der Tracedatei, über Oracle und die Maschine, auf
der die Oracle Instance läuft und die Beschreibung der Instanz selbst.
Die Zeile mit der Session ID
*** SESSION ID:(9.21)
spiegelt die SID und SERIAL# wieder, wie sie auch in der V$SESSON View
enthalten sind. Anhand dieser Session ID kann die Trace Datei eindeutig der
Session zugeordnet werden, für die ein Trace erzeugt wurde.
Nachdem der Name der Application angezeigt wurde, ist der erste Cursor mit
PARSING IN CURSOR #1 .... ausgewiesen. Einige Zeilen weiter -diese
werden zu einem späteren Zeitpunkt beschrieben- erscheint das Statement
für diesen Cursor, dessen Ende durch END OF STMT angezeigt wird. Mit
PARSING IN CURSOR #2 wird der zweite Cursor beschrieben der geöffnet
wurde. Das an dieser Stelle die 2 und nicht noch einmal die 1 eingetragen ist,
weist darauf hin, daß der erste Cursor noch nicht geschlossen war, als der
zweite geöffnet wurde. D.h., immer dann, wenn zum vorherigen Cursor die
laufende Nummer um eins erhöht wurde, ist der Cursor mit niedriger Nummer
noch nicht geschlossen. Dieses Verhalten kann man recht gut für die beiden
Einträge PARSING IN CURSOR #8 in nachfolgenden Tracefile erkennen.
Die Höchste Nummer eines Cursor in der gesamten Trace Datei beschreibt,
wieviele Cursor maximal zeitglich geöffnet waren.
______________________________________________________________________________________________________
Oracle Trace
Seite 10
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
PARSING IN CURSOR #2 zeigt im folgen Auszug einen Cursor, der von
Oracle selbst erzeugt wurde.
Dump file /oracle/815/admin/ORCL/udump/ora_488.trc
Oracle8i Enterprise Edition Release 8.1.5.0.2 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
ORACLE_HOME = /oracle/815
System name:
Linux
Node name:
lxnotebook
Release:
2.2.13
Version:
#3 Tue Mar 14 15:07:16 MET 2000
Machine:
i686
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 488, image: oracle@lxnotebook (TNS V1-V3)
*** 2000.10.31.08.19.10.887
*** SESSION ID:(9.21) 2000.10.31.08.19.10.886
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=52 dep=0 uid=21 oct=47 lid=21 tim=2808754576 hv=4201917273
ad='24442580'
begin dbms_output.get_lines(:lines, :numlines); end;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=2808754576
EXEC #1:c=1,e=41,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=2808754617
*** 2000.10.31.08.19.28.354
=====================
PARSING IN CURSOR #2 len=275 dep=1 uid=0 oct=3 lid=0 tim=2808756323 hv=3559516504
ad='2453335c'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$ from obj$ where
owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4
is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:6 or subname is
null and :6 is null)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756323
EXEC #2:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756329
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756329
=====================
PARSING IN CURSOR #3 len=350 dep=1 uid=0 oct=3 lid=0 tim=2808756329 hv=2216582187
ad='245324b0'
select
ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$,flags,pctfree$,
pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,
samplesize,cols,property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nv
l(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,nvl(spare3,0) from tab$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756329
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756329
FETCH #3:c=0,e=1,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756330
=====================
PARSING IN CURSOR #4 len=685 dep=1 uid=0 oct=3 lid=0 tim=2808756331 hv=199702406
ad='24531ee4'
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,
i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccn
t,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,spare2,decode(i.pctthres$,null,null,
mod(trunc(i.pctthres$/256),256)) from ind$ i, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#, min(to_number(bitand(defer,4))) valid#
from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.bo#=:1
END OF STMT
PARSE #4:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756331
______________________________________________________________________________________________________
Oracle Trace
Seite 11
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
EXEC #4:c=1,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
=====================
PARSING IN CURSOR #5 len=250 dep=1 uid=0 oct=3 lid=0 tim=2808756336 hv=906438690
ad='2452b92c'
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,127/*MAXSB1MINAL*/),0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property
, charsetid,charsetform,spare1,spare2 from col$ where obj#=:1 order by intcol#
END OF STMT
PARSE #5:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756336
EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756336
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
=====================
PARSING IN CURSOR #6 len=69 dep=1 uid=0 oct=3 lid=0 tim=2808756336 hv=114078687
ad='24543e10'
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
END OF STMT
PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756336
EXEC #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
FETCH #6:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756336
=====================
PARSING IN CURSOR #7 len=132 dep=1 uid=0 oct=3 lid=0 tim=2808756336 hv=1639440790
ad='24543658'
select
con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(d
efer,0),mtime from cdef$ where obj#=:1
END OF STMT
PARSE #7:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756337
EXEC #7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756337
FETCH #7:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756337
=====================
PARSING IN CURSOR #8 len=190 dep=1 uid=0 oct=3 lid=0 tim=2808756337 hv=4059714361
ad='2454e914'
select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(
groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where ts#=:1 and file#=:2 and
block#=:3
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756337
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756337
FETCH #8:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756337
STAT #8 id=1 cnt=1 pid=0 pos=0 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #8 id=2 cnt=1 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #8 len=128 dep=1 uid=0 oct=3 lid=0 tim=2808756339 hv=2554239735
ad='2441ba94'
select u.name,o.name from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and
t.obj#=o.obj# and o.owner#=u.user# order by o.obj#
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808756339
EXEC #8:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756340
FETCH #8:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756340
STAT #8 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT ORDER BY '
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS '
STAT #8 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #8 id=4 cnt=1 pid=3 pos=1 obj=74 op='TABLE ACCESS BY INDEX ROWID TRIGGER$ '
STAT #8 id=5 cnt=1 pid=4 pos=1 obj=119 op='INDEX RANGE SCAN '
STAT #8 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ '
STAT #8 id=7 cnt=0 pid=6 pos=1 obj=33 op='INDEX UNIQUE SCAN '
______________________________________________________________________________________________________
Oracle Trace
Seite 12
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
STAT #8 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ '
STAT #8 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN '
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756340
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756340
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756340
FETCH #3:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756340
=====================
PARSING IN CURSOR #9 len=190 dep=2 uid=0 oct=3 lid=0 tim=2808756340 hv=4059714361
ad='2454e914'
select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(
groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where ts#=:1 and file#=:2 and
block#=:3
END OF STMT
PARSE #9:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756340
EXEC #9:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756340
FETCH #9:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756340
STAT #8 id=1 cnt=1 pid=0 pos=0 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #8 id=2 cnt=1 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
=====================
PARSING IN CURSOR #9 len=56 dep=2 uid=0 oct=3 lid=0 tim=2808756342 hv=4195740643
ad='24523c70'
select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
END OF STMT
PARSE #9:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=2808756342
EXEC #9:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
EXEC #9:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #9:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756342
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756343
=====================
PARSING IN CURSOR #10 len=116 dep=2 uid=0 oct=3 lid=0 tim=2808756343 hv=189272129
ad='2452e4dc'
select
o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from
obj$ o where o.obj#=:1
END OF STMT
PARSE #10:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=2808756343
EXEC #10:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756343
FETCH #10:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
______________________________________________________________________________________________________
Oracle Trace
Seite 13
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
EXEC #10:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=2808756343
FETCH #10:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=2808756343
=====================
PARSING IN CURSOR #8 len=198 dep=1 uid=0 oct=3 lid=0 tim=2808756343 hv=1854821847
ad='24416dbc'
select bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum,
maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$
where obj#=:1 and intcol#=:2
END OF STMT
PARSE #8:c=1,e=3,p=1,cr=36,cu=1,mis=1,r=0,dep=1,og=0,tim=2808756343
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808756343
FETCH #8:c=0,e=1,p=3,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2808756344
=====================
PARSING IN CURSOR #1 len=63 dep=0 uid=21 oct=6 lid=21 tim=2808756344 hv=2216434206
ad='2443fc10'
update LAMBERTZ.ADRESSEN set name='Meier' where name = 'Meier'
END OF STMT
PARSE #1:c=5,e=21,p=5,cr=58,cu=1,mis=1,r=0,dep=0,og=4,tim=2808756344
*** 2000.10.31.08.19.46.237
EXEC #1:c=440,e=1767,p=15276,cr=15275,cu=1958,mis=0,r=1903,dep=0,og=4,tim=2808758111
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE ADRESSEN '
STAT #1 id=2 cnt=1904 pid=1 pos=1 obj=3711 op='TABLE ACCESS FULL ADRESSEN '
=====================
PARSING IN CURSOR #1 len=52 dep=0 uid=21 oct=47 lid=21 tim=2808758187 hv=4201917273
ad='24442580'
begin dbms_output.get_lines(:lines, :numlines); end;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2808758187
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=2808758187
=====================
PARSING IN CURSOR #1 len=9 dep=0 uid=21 oct=45 lid=21 tim=2808758252 hv=3749013834
ad='2440cf1c'
rollback
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2808758252
XCTEND rlbk=1, rd_only=0
=====================
PARSING IN CURSOR #2 len=275 dep=1 uid=0 oct=3 lid=0 tim=2808758259 hv=570441826
ad='24581ca4'
select
name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dflinit,dflincr,dflextp
ct,dflminext, dflminlen, owner#,scnwrp,scnbas, NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0),
dflogging, bitmapped, inc#, flags, plugged, NVL(spare1,0), NVL(spare2,0) from ts$ where
ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2808758259
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2808758259
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2808758259
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=16 op='TABLE ACCESS CLUSTER TS$ '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
EXEC #1:c=9,e=142,p=0,cr=22,cu=3830,mis=0,r=0,dep=0,og=4,tim=2808758394
*** 2000.10.31.08.20.02.044
2.1.1.
Beschreibung der Ergebnisse in einer Trace Datei
Wie im vorherigen Kapitel zu sehen war sind in einer Trace Datei eine Kürzel
und Kennungen benutzt worden, die nachfolgend beschrieben werden.
PARSE
- Statistiken zum erfolgten Parsen
______________________________________________________________________________________________________
Oracle Trace
Seite 14
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
EXEC
- Statistiken zu einem INSERT, UPDATE oder DELETE
FETCH
- Statistiken zu einem SELECT
Zu jedem einzelnen Vorgang werden aufgelistet:
c
CPU Einsatz in 1/100 Sekunden (CPU)
e
Gesamte Verarbeitungzeit in 1/100 Sekunden der entsprechenden
Operation (elapsed)
p
Physikalische Disk Zugriffe auf dem Speichermedium (disk)
cr
Anzahl Consistent Gets (Query).
cu
Anzahl Current Blocks (Current).
r
Anzahl Rows, die eine Operation als Result geliefert hat. Für jedes
FETCH werden z.B die Rows angegeben, die mit einem Fetch gelesen
werden (Rows).
Der Zähler eines Cursors (PARSING IN CURSOR #1) in einem *.trc File wird
immer doppelt vergeben, wenn der Cursor geschlossen wurde bevor ein
neuer erstellt wird. Für die wiederholte Ausführung folgender Statements
select count(*) from adr_name;
select count(*) from adr_name;
Select count(*) from adr_name;
zeigt sich das entsprechende Trace (*.trc) File:
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=21 oct=3 lid=21 tim=2827937218 hv=3851129330
ad='24421058'
select count(*) from adr_name
END OF STMT
PARSE #1:c=6,e=27,p=2,cr=40,cu=0,mis=1,r=0,dep=0,og=4,tim=2827937218
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2827937223
FETCH #1:c=15,e=121,p=946,cr=945,cu=4,mis=0,r=1,dep=0,og=4,tim=2827937344
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=280073 pid=1 pos=1 obj=3717 op='TABLE ACCESS FULL ADR_NAME '
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=21 oct=3 lid=21 tim=2827937612 hv=3851129330
ad='24421058'
select count(*) from adr_name
END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2827937612
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2827937616
FETCH #1:c=19,e=36,p=0,cr=945,cu=4,mis=0,r=1,dep=0,og=4,tim=2827937652
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=280073 pid=1 pos=1 obj=3717 op='TABLE ACCESS FULL ADR_NAME '
______________________________________________________________________________________________________
Oracle Trace
Seite 15
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=21 oct=3 lid=21 tim=2827938903 hv=2776197289
ad='243fcd50'
Select count(*) from adr_name
END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2827938903
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2827938907
FETCH #1:c=18,e=18,p=0,cr=945,cu=4,mis=0,r=1,dep=0,og=4,tim=2827938925
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=280073 pid=1 pos=1 obj=3717 op='TABLE ACCESS FULL ADR_NAME'
=====================
2.2.
Erstellen einer TKProf Datei
Die Übesetzung einer *trc Datei mit dem Tool TKProf erfolgt:
tkprof ora_488.trc ora_488.out explain=user/passwort@SID sys=no
Es handelt sich um die *trc aus dem vorherigen Kapitel. Somit können
Analogien recht gut erkannt werden. Die TKProf Datei hat folgendes
Aussehen:
TKPROF: Release 8.1.5.0.2 - Production on Tue Oct 31 08:25:18 2000
(c) Copyright 1999 Oracle Corporation.
All rights reserved.
Trace file: ora_488.trc
Sort options: default
********************************************************************************
count
= number of times OCI procedure was executed
cpu
= cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk
= number of physical reads of buffers from disk
query
= number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows
= number of rows processed by the fetch or execute call
********************************************************************************
begin dbms_output.get_lines(:lines, :numlines); end;
call
count
------- -----Parse
2
Execute
2
Fetch
0
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.01
0.41
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.01
0.41
0
0
0
rows
---------0
2
0
---------2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
********************************************************************************
update LAMBERTZ.ADRESSEN set name='Meier'
where
______________________________________________________________________________________________________
Oracle Trace
Seite 16
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.02
0
58
0
4.40
17.67
15276
15275
1958
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------4.40
17.69
15276
15333
1958
rows
---------0
1903
0
---------1903
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------1
1904
Row Source Operation
--------------------------------------------------UPDATE ADRESSEN
TABLE ACCESS FULL ADRESSEN
Rows
------0
1
1904
Execution Plan
--------------------------------------------------UPDATE STATEMENT
GOAL: CHOOSE
UPDATE OF 'ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADRESSEN'
********************************************************************************
rollback
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.09
1.42
0
20
3830
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.09
1.42
0
20
3830
rows
---------0
0
0
---------0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
********************************************************************************
begin sys.dbms_system.set_sql_trace_in_session( 9,21 ,false ); end;
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
rows
---------0
1
0
---------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call
count
------- -----Parse
5
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.02
0
58
0
rows
---------0
______________________________________________________________________________________________________
Oracle Trace
Seite 17
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Execute
5
Fetch
0
------- -----total
10
4.50
19.50
15276
15295
5788
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------4.50
19.52
15276
15353
5788
1906
0
---------1906
Misses in library cache during parse: 4
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call
count
------- -----Parse
13
Execute
19
Fetch
54
------- -----total
86
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.03
0.05
1
30
1
0.02
0.12
0
0
0
0.00
0.02
4
60
0
-------- ---------- ---------- ---------- ---------0.05
0.19
5
90
1
rows
---------0
0
45
---------45
Misses in library cache during parse: 12
5 user SQL statements in session.
13 internal SQL statements in session.
18 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: ora_488.trc
Trace file compatibility: 7.03.02
Sort options: default
1
5
13
18
16
1
207
2.3.
session in tracefile.
user SQL statements in trace file.
internal SQL statements in trace file.
SQL statements in trace file.
unique SQL statements in trace file.
SQL statements EXPLAINed using schema:
LAMBERTZ.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
Interpretieren einer TKProf Datei
Auch wenn es sich bei der TKProf Ausgabe Datei um eine Zusammenfassung
der Trace Ergebnisse der *trc Datei handelt werden noch recht viele
Einzelinformationen geliefert.
Trace file: ora_488.trc
Sort options: default
********************************************************************************
count
= number of times OCI procedure was executed
cpu
= cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk
= number of physical reads of buffers from disk
query
= number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows
= number of rows processed by the fetch or execute call
********************************************************************************
______________________________________________________________________________________________________
Oracle Trace
Seite 18
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Im Vorspann der Ausgabe werden die ursprüngliche Trace Datei genannt und
das Sortierkriterium, mit welchem die Datei erstellt wurde.
2.3.1.
Darstellung der Ergebnisse in einer TKProf Datei
Nachdem ein Statement oder der Aufruf einer PL/SQL Routine aufgeführt
wurde, erfolgt die Darstellung der Statistik zu dieser SQL Ausführung in Form
einer Matrix.
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.02
0
58
0
4.40
17.67
15276
15275
1958
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------4.40
17.69
15276
15333
1958
rows
---------0
1903
0
---------1903
Die Matrix besteht im Wesentlichen aus den Zeilen „Parse“, „Execute“, „Fetch“
und „total“. Die Spalten zeigen die eigentlichen Statistiken der jeweiligen
Operation. „Execute“ Werte fallen nur bei INSERT, UPDATE oder DELETE an
und SELECT Statements verursachen „Fetch“ Werte.
Grundsätzlich werden drei Arten Operationen unterschieden und protokolliert:
Parse
entstehen immer bei der Ausführung eines Statement. Die
genaue Beschreinbung zum Prase ist dem Anhang zu
entnehmen.
Execute
Werte zeigen die Anzahl der Ausführungen des Statements. Für
SELECT Statements kann die Anzahl „Execute“ von der „Fetch“
abweichen. Für explizite Aufrufe ist die Anzahl Parse und
Execute identisch.
************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
cpu
elapsed
disk
query
current
------- --------- ------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.60
77.51
14084
3007636
4
rows
------0
0
1784
______________________________________________________________________________________________________
Oracle Trace
Seite 19
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
------- ------ ------- --------- ------- ---------- ---------- ------total
597
51.61
77.52
14084
3007636
4
1784
************************************************************************
Für das Beispiel wurde das Statement einmalig in SQL*Plus
abgesetzt. Die wiederholte Ausführung des selben Statements,
führt zur Verdopplung der „count“ Ergebnisse.
************************************************************************
call
count
------- -----Parse
2
Execute
2
Fetch
1190
------- -----total
1194
cpu
elapsed
disk
query
current
------- --------- ------- ---------- ---------...
...
...
------- --------- ------- ---------- ---------...
rows
-------
-------
************************************************************************
Das folgende Beispiel zeigt
Statements aus einem Cursor.
den
Aufruf
eines
SELECT
************************************************************************
declare
v_plz
NUMBER( 7 );
begin
for cur_rec in( SELECT plz_id
FROM adr_adressen
WHERE rownum < 21 )loop
SELECT plz
INTO v_plz
FROM adr_plz
WHERE id=cur_rec.plz_id;
end loop;
end;
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ------- ------- ---------0.01
0.01
0
0
0
0.00
0.01
0
0
0
0.00
0.00
0
0
0
-------- ---------- ------- ------- ---------0.01
0.02
0
0
0
rows
-------0
1
0
-------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
************************************************************************
SELECT PLZ_ID
FROM
ADR_ADRESSEN
call
count
------- -----Parse
1
WHERE ROWNUM < 21
cpu
elapsed
disk
query
current
------ --------- ------- -------- ---------0.00
0.00
0
0
0
rows
-------0
______________________________________________________________________________________________________
Oracle Trace
Seite 20
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Execute
1
Fetch
21
------- -----total
23
0.00
0.00
0
0
0
0.00
0.00
0
20
4
------ --------- ------- -------- ---------0.00
0.00
0
20
4
0
20
-------20
************************************************************************
SELECT PLZ
FROM
ADR_PLZ WHERE ID = :b1
call
count
------- -----Parse
1
Execute
20
Fetch
20
------- -----total
41
cpu
elapsed
disk
query
current
-------- ---------- ------- ------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.03
0.06
2
40
80
-------- ---------- ------- ------- ---------0.03
0.06
2
40
80
rows
-------0
0
20
-------20
************************************************************************
Angaben größer 0 (außer der Spalte count) sind auf das
Speichern von Zwischenergebnissen in der SGA zurück zu
führen. Es werden in der Execute Zeile keine Werte für „disk“,
„query“, „current“ oder „rows“ angezeigt. Deutlich zu sehen, im
folgenden Beispiel für den Einsatz der Funktion MAX.
************************************************************************
SELECT MAX( name )
FROM
adr_name
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- ------- ------- ---------0.00
0.00
0
0
0
0.00
0.01
0
0
0
0.00
0.08
3
3
0
-------- ---------- ------- ------- ---------0.00
0.09
3
3
0
rows
------0
0
1
------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------1
1
Row Source Operation
--------------------------------------------------SORT AGGREGATE
INDEX FULL SCAN (MIN/MAX) (object id 7063)
************************************************************************
Ein weiteres Beispiel zeigt, daß Oracle für die Ausführung eines
Statement mit einem SubSelect (nicht correlated) eine temporäre
Tabelle erstellt.
______________________________________________________________________________________________________
Oracle Trace
Seite 21
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
************************************************************************
SELECT *
FROM adr_adressen
WHERE EXISTS( SELECT '#' FROM adr_plz )
call
count
cpu
elapsed
disk
query
current
rows
------- ------ ------- ---------- ------- -------- ---------- -------Parse
1
0.00
0.00
0
0
0
0
Execute
1
0.00
0.01
0
1
4
0
Fetch
32
0.01
0.00
0
32
4
96
------- ------ ------- ---------- ------- -------- ---------- -------total
34
0.01
0.01
0
33
8
96
************************************************************************
Fetch
Werte entstehen ausschließlich für SELECT Anforderungen.
Also nicht für die Ausführung eines UPDATE, DELETE oder
INSERT.
Zu jeder einzelnen Operation werden in der Matrix in Spalten aufgelistet:
Count
Aufzählung, wie häufig eine entsprechende Operation ausgeführt
wurde
CPU
CPU Einsatz in Sekunden.
Elapsed
Gesamte Verarbeitungzeit der
(parse, execute, fetch oder total).
Disk
Physikalische Zugriffe auf das Speichermedium (Festplatte).
Query
besitzt die gleiche Bedeutung wie CONSISTENT GETS. Also die
Bereitstellung von konsistenten Datenblöcken für die
Bearbeitung eines Statements. Die Daten stammen aus dem
DB_BUFFER (oder der Festplatte, wenn die Daten schon
geflasht wurden) oder als Rollback-Information aus dem Buffer
oder von der Festplatte.
entsprechenden
Operation
Query Werte für eine „Parse“ Operation sind auf das Lesen von
Data Dictonary Informationen zurückzuführen.
Für ein SubSELECT (nicht correlated) innerhalb einer WHERE
Klausel (vorausgesetzt ORACLE hat das Statement beim Parse
nicht in einen Join überführt) weisen die Spalten „Query“ und
„Current“ Werte aus (also nicht nur „fetch“ oder „execute“).
Ursache ist, daß ORACLE die Ergebnisse des SubSELECT in
eine temporäre Tabelle speichert. Hiermit verbunden sind
RECURSIVE CALLS.
______________________________________________________________________________________________________
Oracle Trace
Seite 22
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
************************************************************************
SELECT *
FROM adr_adressen
WHERE EXISTS( SELECT '#' FROM adr_plz )
call
count
------- -----Parse
1
Execute
1
Fetch
32
------- -----total
34
cpu
elapsed
disk
query
current
------- ---------- ------- -------- ---------0.00
0.00
0
0
0
0.00
0.00
0
1
4
0.01
0.00
0
32
4
------- ---------- ------- -------- ---------0.01
0.00
0
33
8
rows
-------0
0
96
-------96
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
Row Source Operation
------- --------------------------------------------------96 FILTER
96
TABLE ACCESS FULL ADR_ADRESSEN
1
TABLE ACCESS FULL ADR_PLZ
************************************************************************
Es gibt Beispiele die zeigen, daß nicht zwingend CONSISTENT
GETS für ein Statement entstehen.
************************************************************************
UPDATE ADR_ADRESSEN
SET name = ´Mayer´
WHERE rowid = :a
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
1
1
Fetch
0
0.00
0.00
0
0
0
0
------- ------ ------ ---------- ------- ------- ---------- -------total
2
0.00
0.00
0
0
1
1
************************************************************************
Für das UPDATE mit der WHERE Klausel auf eine Rowid
werden keine „query“ Werte ausgewiesen!
Auch für INSERT Operationen werden Query Werte
ausgewiesen, die von Oracle für die Suche des nächsten freien
Blocks anfallen. Selbst für Tabellen mit mehreren Millionen
Datensätzen fällt dieser Wert kleiner 100 aus. Größere Werte
weisen auf eine erhöhte Anzahl Extens (>10) hin und ein Reorg
der entsprechenden Tabelle sollte angestrebt werden.
Current
werden beschrieben als CONSISTENT GETS Blöcke, die in den
CURRENT Mode (lokal gekapselter Speicher) übernommen
wurden, weil Oracle an diesen Blöcken Veränderungen
______________________________________________________________________________________________________
Oracle Trace
Seite 23
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
vorgenommen hat oder neue Blöcke erzeugt wurden. CURRENT
Werte treten z.B. auf, wenn durch Datenänderungen Korrekturen
an der FREELIST in einem Block erforderlich wurden, oder
durch die Reorganisation eines Index (bearbeiten der Header-,
Leaf- oder Branch Blöcke eines B*Tree Index - ausgelöst durch
INSERTs, UPDATEs oder DELETEs), usw.
Current Werte treten aber auch auf, wenn viele Daten geändert
oder gelöscht wurden, wodurch das gesamte Datenvolumen
innerhalb eines Blocks den definierten PCTUSED unterschreitet.
Es erfolgt ein Eintrag in die FREELIST, daß dieser Block wieder
für die Aufnahme neuer Daten zur Verfügung steht.
Für Änderungen einer Tabelle ohne Indizes liegt der „Current“
Wert bei einem Update unterhalb dem „Query“ Wert. Tabellen
mit Indizes weisen grundsätzlich einen größeren „Current“ Wert
gegenüber dem „Query“ Wert aus. Aber auch hier gibt es
Ausnahmen. UPDATEs, für die keine Zeile zum Aktualisieren
gefunden wurde, beträgt der Wert für „Execute“ 0. Somit ist auch
ohne einen Execute Plan mit der Angabe von Rows zu
erkennen, daß keine Zeile aktualisiert wurde. Für INSERTs
hingegen werden keine „Current“ Werte ausgewiesen, wenn für
den neuen Wert, der in die Tabelle eingetragen werden soll, das
Einsortieren in den Index entfällt und statt dessen die neue
Information einfach angehängt wird.
CURRENT Werte treten nicht zwingend auf.
Rows
Anzahl Rows, welche für die einzelne Operation (Parse, Execute
oder Fetch) als Ergebnis geliefert wurden.
Fällt für die Zeile „Fetch“ die Angabe „count“ kleiner aus als der
Wert für die ermittelten „Rows“, handelt es sich um einen „array
fetch“. Array fetch hat den Vorteil, daß mehere Rows zu einem
Paket zusammengafasst und nicht einzeln übertragen werden.
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
------- --------- ------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.60
77.51
14084
3007636
4
------- --------- ------- ---------- ---------51.61
77.52
14084
3007636
4
rows
------0
0
1784
------1784
Ein Array Fetch kann mit dem OCI (Oracle call interface)
programmiert werden. Oracle Tools wie SQL*Plus oder
SQL*Forms unterstützen Array fetch von hause aus.
______________________________________________________________________________________________________
Oracle Trace
Seite 24
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Ausgwiesene Rows für einen Explain Plan zeigen die Anzahl
Datensätze, welche aus den einzelnen Operationen
hervorgingen.
Für
NESTED
LOOPS
oder
ähnliche
Gruppenfunktionen beschreibt die Angabe die Rows, welche aus
der Mischoperation für die Weiterverarbeitung geliefert wurden.
Hierzu ein Beispiel.
______________________________________________________________________________________________________
Oracle Trace
Seite 25
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
------- --------- ------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.60
77.51
14084
3007636
4
------- --------- ------- ---------- ---------51.61
77.52
14084
3007636
4
rows
------0
0
1784
------1784
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1493661
1495444
2987320
3568
3568
1784
3568
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: FIRST_ROWS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
************************************************************************
1785 Row’s bilden die Ergebnismenge aus dem ersten NESTED
LOOP für die Bedingung „name.id = adr.name_id“. Auch wenn
für die einzelnen Tabellen jeweils rund 1.5 Mio Rows gelesen
wurden und den Index fast 3 Mio. Solche Zugriffe sind zu
vermeiden.
Innerhalb eines Cursors spiegelt die Row Angabe die Anzahl
Datensätze wieder, welche für einen Fetch ermittelt wurden. Im
folgenden Beispiel wird die Anzahl Rows in der Statistik mit 3
ausgewiesen und im Explain Plan wurde nur eine Row ermittelt,
obwohl das Statement nur einmalig ausgeführt wurde.
______________________________________________________________________________________________________
Oracle Trace
Seite 26
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
******************************************************************************
-- Hier der Cursor in einem PL/SQL Block
begin
for i in( SELECT anrede FROM adr_anrede )loop
INSERT into dual values ( '1' );
end loop;
end;
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.01
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
rows
------0
1
0
------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
******************************************************************************
SELECT ANREDE
FROM
ADR_ANREDE
call
count
------- -----Parse
1
Execute
1
Fetch
3
------- -----total
5
cpu
elapsed
disk
query
current
-------- ---------- ---------- --------- --------0.00
0.03
0
3
0
0.00
0.00
0
0
0
0.00
0.04
2
3
9
-------- ---------- ---------- --------- --------0.00
0.07
2
6
9
rows
--------0
0
3
--------3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
(recursive depth: 1)
Rows
------0
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
INSERT INTO DUAL
VALUES
( '1' )
call
count
------- -----Parse
1
Execute
3
Fetch
0
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- -------- --------- ---------0.00
0.00
0
0
0
0.00
0.00
0
2
7
0.00
0.00
0
0
0
-------- ---------- -------- --------- ---------0.00
0.00
0
2
7
rows
-------0
3
0
-------3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
(recursive depth: 1)
Rows
------0
Execution Plan
--------------------------------------------------INSERT STATEMENT
GOAL: CHOOSE
******************************************************************************
______________________________________________________________________________________________________
Oracle Trace
Seite 27
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Die Abweichungen sind sehr deutlich zu erkennen, wenn das
Statement direkt in SQL*Plus ausgeführt wird.
******************************************************************************
SELECT ANREDE
from
ADR_ANREDE
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
2
4
-------- ---------- -------- ---------- ---------0.00
0.00
0
2
4
rows
-------0
0
3
-------3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
3
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
Es wurden mit einem ARRAY Fetch 3 Datensätze als Ergebnis
geliefert. Auch die Anzahl Rows des Explain Plan wird nun mit 3 Rows
ausgewiesen.
Für dieses Beispiel ist die verfälsche Anzahl Rows des Explain Plan
noch ersichtlich. Schwieriger wird es, wenn eine PL/SQL Procedure,
Funktion aufgerufen wird.
Grundsätzlich gilt:
• Für die ausgewiesenen Statistiken eines Statements handelt es sich für die
wiederholte Ausführung um eine Zusammenfassung von Einzelergebnissen.
Dann sind die Ergebnisse durch die Anzahl der Ausführungen zu teilen, um
die Durchschnittswerte für eine Ausführung zu erhalten. Disk Angaben sind
hiervon ausgenommen.
•
Die Spalteninformationen zu den Fetch, Execute und Parse Angaben lassen
eine erste Einschätzung zu, ob die Verarbeitung eines Statements
überwiegend im Memory erfolgen konnte.
Logical reads – physical reads
(Query + Current) - Disk
______________________________________________________________________________________________________
Oracle Trace
Seite 28
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
------------------------------------------------------------- = --------------------------------------------------
Logical reads
<= 99 %
(Query + Current)
Stellt sich ein Wert kleiner als 99% ein, liegt die Ursache entweder an der zu
großen Anzahl Extents oder daran, daß der „Library Cache“ nicht ausreichend
groß dimensioniert ist. Wenn die Maschine nicht über ausreichend Memory
verfügt um den „Library Cache“ vergrößern zu können, wird man mit diesem
Mißstand leben müssen. Wenn die Anzahl Disk I/O überschaubar bleibt,
relativ zu dem „Query“ Wert, ist hier kein Handlungsbedarf. Außer, die
Device’s sind extrem langsam.
•
Für die Anzahl Rows im EXPLAIN PLAN handelt es sich nicht um eine
Zusammenfassung, sondern um die tatsächliche Anzahl Rows, welche für
eine einmalige Ausführung ermittelt wurden.
•
Für eine „0.00“ Zeitangabe liegt die Verabeitungsdauer unter 1 / 100 Sekunde
und wird nicht mehr erfaßt.
•
Häufig zu erkennen ist, daß die „elapsed“ Zeit geringer als die „CPU“ Zeit
ausfällt. Ursache sind Rundungsdifferenzen bei der Summenbildung aus dem
Trace File. Fakt ist, daß „elapsed“ immer über der „CPU“ Zeit liegt. Häufig
anzutreffen, wenn eine große Anzahl „Executes“ oder „Fetch“ ausgewiesen
ist.
2.3.2.
Was bildet die Differenz zwischen CPU- und elapsed time
Warum ein Statement mal schnell und mal langsam die Ergebnisse liefert,
kann sicherlich an der gesamten Situation einer Datenbank Instanz liegen. So
kann eine lang andauernde fremde Batchverarbeitung die eigene
Verarbeitung ziemlich behindern. Aber auch Statements aus einer fremden
Transaktion, welche große Datenmengen bearbeiten, behindern die eigene
Transaktion. Solche „Behinderungen“ lassen sich meist nicht vermeiden.
Anders verhält es sich, wenn für eine Foreign Key Tabelle ein Index für diesen
Key fehlt. Dann erfolgt ein TABLE LOCK auf die Foreign Key Tabelle in dem
Moment, für den ein UPDATE auf eine Datensatz der Primary Tabelle
durchgeführt wird.
Im Normalfall sperrt Oracle die Datensätze der Foreign Key Tabelle, welche
dem Key der Primary Tabelle entsprechen, über den Foreign Key Index um
die Datenintegrität zu gewährleisten. Fehlt nun der Foreign Key Index auf der
Foreign Key Tabelle, kann Oracle die Sätze nicht eindeutig identifizieren,
welche dem Primary Key entsprechen und sperrt (sicherheitshalber) die
gesamte Tabelle. Dieser Zustand wird nur zum Problem, wenn zeitgleich
______________________________________________________________________________________________________
Oracle Trace
Seite 29
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
mehere Anwender verschiedene Datensätze der Primary Tabelle ändern oder
löschen wollen. Dann entsteht ein Wartezustand auf die Foreign Key Tabelle
bis die Tabelle wieder freigegeben wird.
Hier einmal grafisch die Lock-Situation:
Primary Key Tabelle
Foreign Key Tabelle
Sperrung
der
gesamten
Tabelle
Primary
Key
Primary
Key
Foreign
Key
4711
Noname
1
4711
...
5813
Mueller
8
5813
...
Warten auf
Sperre
Anders, wenn zu dem Foreign Key Constraint ein Index vorhanden ist. Dann
sperrt Oracle nur die Sätze, welche dem Primary Key entsprechen.
Primary Key Tabelle
Foreign Key Tabelle
Primary
Key
4711
Noname
5813
Mueller
Satzsperre
durch
Index
Primary
Key
Foreign
Key
1
4711
...
8
5813
...
______________________________________________________________________________________________________
Oracle Trace
Seite 30
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Weitere Ursachen für einen großen Unterschied zwischen „elapsed“ und
„CPU“ können sein:
Überforderte CPU
Überfordertes Netzwerk
Überforderte oder schlecht ausgelastete Festplatten (üngünstiges
Striping, Tablespace ungünstig verteilt oder langsame Platten)
Table locking (Konkurierende DML’s wie oben gezeigt)
Schlecht gewählter DB_FILE_MULTIBLOCK_READ_COUNT Faktor,
wodurch eine zu geringe Anzahl Datenblöcke mit einem Lesezugriff in
den Memory gelesen werden. Wartezeiten im Bereich I/O sind die
Folge. Aber auch eine zu große Anzahl kann dazu führen, daß der
verfügbare Memory nicht ausreicht die Datenmengen aufzunehmen.
Übermäßiges „flashen“ ist die Folge.
Zu geringe Anzahl FREELISTs (Konkurierende DML’s)
Zu geringe Anzahl INITTRANS einer Tabelle (Konkurierende DML’s)
Block contention (Konkurierende DML’s) durch einen Parallelbetrieb,
um die Gesamtlaufzeit einer Operation auf mehere Transaktionen
aufzuteilen.
Bereichsabgrenzungen
bestimmter
Daten
(z.B.
Vertragsnummer) werden zum Steuern der einzelnen Transaktionen
eingesetzt. Gerade im Zugriff der Indexblöcke oder Datenblöcke
entstehen Lockings dadurch, daß die Transaktion A an dem Block X
Änderungen vornehmen muß (durch Insert, delete oder update), der
von der anderen Transaktion gelesen werden soll. Genau zu dem
Zeitpunkt hält Oracle eine Blocksperre und der Select muß warten.
Latch waits
______________________________________________________________________________________________________
Oracle Trace
Seite 31
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
3.
So arbeitet Oracle
Am besten zu verstehen sind EXPLAIN PLAN’s und Traceergebnisse, wenn
bekannt ist, wie Oracle arbeitet.
Grundsätzlich muß berücksichtigt werden, ob die Verarbeitug im RULE oder
dem CHOOSE Mode erfolgt.
3.1.
Auflösung eines Statement
Nun soll einmal untersucht werden, wie Oracle ein Statement auflöst.
Das folgende Statement hat die Aufgabe alle PLZ und ORTe auszugeben für
alle Adressen, die dem Namen „Meier“ entsprechen.
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier';
Nun, die selektierten Attribute plz.PLZ und ort.ORT sind nicht in der Tabelle
NAME enthalten, für die eine Einschränkung auf das Attribut „name.name“
vorliegt. Die folgende Darstellung zeigt die Abhängigkeiten der einzelnen
Tabellen und verdeutlicht, wie mit dem bekannten Namen eine zugehörige
PLZ und ORT ermittelt werden könnte.
Name.name -> name.id -> adr.name_id -> adr.ort_id -> ort.id -> ort.ort
Name.name -> name.id -> adr.name_id -> adr.plz_id -> plz.id -> plz.plz
Wird unterstellt, daß Oracle auch dieses Vorgehen wählt, erfolgt im ersten
Schritt die Ermittlung einer Teilmenge aus der Tabelle ADR_NAME, die der
Bedingung „NAME = ‘Meier‘ “ entspricht (im Beispiel Statement entspricht das
genau einem Datensatz).
Ein DESCRIBE zeigt, daß die Tabelle ADR_NAME außer dem eigentlichen
Namen noch eine ID beinhaltet. Somit ist für jeden gefundenen Namen dieser
Tabelle auch die ID bekannt. In der Tabelle ADR_ADRESSEN existiert die
gleiche ID unter dem Attributnamen NAME_ID.
______________________________________________________________________________________________________
Oracle Trace
Seite 32
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
SQL > desc adr_name;
Name
Null?
------------------------------- -------ID
NAME
Type
---NUMBER(7)
VARCHAR2(90)
Somit ist die Bildung einer weiteren Teilmenge aus der Tabelle
ADR_ADRESSEN möglich, welche der Bedingung „adr.name_id = name.id“
entspricht. Aus der Tabellendefinition ist ersichtlich, daß mit jeden Datensatz
der Tabelle ADR_ADRESSEN auch eine PLZ_ID und ORT_ID geliefert wird.
SQL > desc adr_adressen;
Name
Null?
------------------------------- -------ID
ANREDE_ID
VORNAME
NAME_ID
STRASSE
PLZ_ID
ORT_ID
TELEFON
VORWAHL_ID
RUFNUMMER
Type
---NUMBER(7)
NUMBER(7)
VARCHAR2(25)
NUMBER(7)
VARCHAR2(60)
NUMBER(7)
NUMBER(7)
VARCHAR2(15)
NUMBER(7)
VARCHAR2(30)
Sie referenzieren auf die ID’s der Tabellen ADR_PLZ und ADR_ORT, zu der
jeweils die tatsächliche PLZ und der ORT gespeichert sind und bestimmen die
Ergebnismenge.
Soweit zur Theorie. Nachfolgend wird das Statement in verschiedenen Modi
ausgeführt um zu sehen, wie sich Oracle tatsächlich verhält.
Zu Beginn die Ausführung im RULE Mode, indem der entsprechende Hint
eingesetzt wird.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ RULE */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
127.60
165.63
11389
7483264
4
-------- ---------- ---------- ---------- ---------127.60
165.64
11389
7483264
4
rows
---------0
0
1784
---------1784
______________________________________________________________________________________________________
Oracle Trace
Seite 33
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1493661
1493661
1493661
2987320
2987320
2987320
2987320
1784
2987320
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
(UNIQUE)
******************************************************************************
Execution Plan
-----------------------------------------------------------------------------1.1 NESTED LOOPS
2.1 NESTED LOOPS
3.1 NESTED LOOPS
4.1 TABLE ACCESS FULL LAMBERTZ.ADR_ADRESSEN
4.2 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_PLZ
5.1 INDEX UNIQUE SCAN LAMBERTZ.ADR_PLZ_ID_IDX
3.2 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_ORT
4.1 INDEX UNIQUE SCAN LAMBERTZ.ADR_ORT_ID_IDX
2.2 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_NAME
3.1 INDEX UNIQUE SCAN LAMBERTZ.ADR_NAME_ID_IDX
NESTED LOOPS<---NESTED LOOPS<---NESTED LOOPS<---TABLE ACCESS
^
^
^
FULL
|
|
|
ADR_ADRESSEN
|
|
|
|
|
|
|
|
+---------------TABLE ACCESS<---INDEX
|
|
BY INDEX ROW
UNIQUE SCAN
|
|
ADR_PLZ
ADR_PLZ_ID_I
|
|
|
|
|
+---------------TABLE ACCESS<---INDEX
|
BY INDEX ROW
UNIQUE SCAN
|
ADR_ORT
ADR_ORT_ID_I
|
|
+---------------TABLE ACCESS<---INDEX
BY INDEX ROW
UNIQUE SCAN
ADR_NAME
ADR_NAME_ID_
Der Ausführungsplan zeigt ein anderes Vorgehen als erwartet.
______________________________________________________________________________________________________
Oracle Trace
Seite 34
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Die Ursache liegt im RULE Mode. RULE Mode heißt regelbasiert und eine
jener Regeln ist, die Bearbeitung des Statement mit der Tabelle zu beginnen,
die in der FROM Anordnung als letztes eingetragen ist.
Somit wurde in dem Statement ADR_PLZ als treibende Tabelle bestimmt. Für
diese Tabelle liegt aber keine Einschränkung vor. Somit werden alle
Ergebnisse der Tabelle ADR_ADRESSEN mit denen aus der Tabelle
ADR_PLZ verglichen.
Ob der Zugriff auf die Tabelle ADR_PLZ mit dem Index ADR_PLZ_ID_IDX
einen Vorteil für die Laufzeit darstellt, kann erst einmal nicht angenommen
werden.
Dieser Zugriff ist als schlecht anzusehen. Es wurden knapp 7,5 Mio
CONSISTENT GETS (query) benötigt, die Ergebnismenge zu bestimmen!
Wird nun entsprechend der Regel das Statement umgestellt und die treibene
Tabelle ADR_NAME an das Ende der FROM Klausel eingetragen und die
Einschränkung für diese Tabelle zu Beginn der WHERE Klausel, zeigt sich die
Ausführung wie folgt.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ RULE */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_Ort ort
ADR_plz plz
ADR_NAME name
name.name = 'Meier'
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
51.39
64.80
10056
3007636
4
-------- ---------- ---------- ---------- ---------51.39
64.80
10056
3007636
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1493661
1495444
2987320
3568
3568
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
______________________________________________________________________________________________________
Oracle Trace
Seite 35
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
1784
3568
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
******************************************************************************
Somit hat sich die Annahme von Beginn bestätigt, wie die Bearbeitung eines
Statements durch Oracle erfolgt.
Wurde der Hint RULE des ursprünglichen Statements gegen FIRST_ROWS
ersetzt, zeigt die TKProf Ausgabe, daß aufgrund der Statistiken, über welche
Oracle zu diesen Tabellen verfügt, das das Statement vor der Bearbeitung (im
Parsing) so umgestellt wird, daß die Tabelle ADR_NAME zur treibenden
Tabelle wurde.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.48
64.83
10056
3007636
4
-------- ---------- ---------- ---------- ---------51.49
64.84
10056
3007636
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1493661
1495444
2987320
3568
3568
1784
3568
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: FIRST_ROWS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
______________________________________________________________________________________________________
Oracle Trace
Seite 36
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Somit kann angenommen werden, daß Oracle ein Statement im CHOOSE
Mode vor der Ausführung den Regeln entsprechend umformatiert und erst
dann das Result Set bildet!
Das optimalste Vorgehen zeigt Oracle ohne Angabe eines Hint durch den
Einsatz mehrerer HASH JOIN.
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.00
0
0
0
0.00
0.00
0
0
0
4.04
10.37
10056
13183
12
-------- ---------- ---------- ---------- ---------4.05
10.37
10056
13183
12
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
584
1784
296
1784
1
2
1493660
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_PLZ'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ORT'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_NAME_IDX'
(NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
******************************************************************************
In der folgenden erweiterten Darstellung des Ausführungsplan ist zu
erkennen, daß ADR_NAME auch an dieser Stelle wieder zur treibenden
Tabelle eingesetzt wurde.
SELECT STATEMENT
OPIMIZER=CHOOSE cost= 3068
Execution Plan
------------------------------------------------------------1.1 HASH JOIN
2.1 TABLE ACCESS FULL LAMBERTZ.ADR_PLZ
2.2 HASH JOIN
3.1 TABLE ACCESS FULL LAMBERTZ.ADR_ORT
3.2 HASH JOIN
Cost
--------3068
1
3066
1
3064
______________________________________________________________________________________________________
Oracle Trace
Seite 37
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
4.1 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_NAME
5.1 INDEX RANGE SCAN LAMBERTZ.ADR_NAME_IDX
4.2 TABLE ACCESS FULL LAMBERTZ.ADR_ADRESSEN
4
3
3016
HASH JOIN<------TABLE ACCESS
^
FULL
|
ADR_PLZ
|
|
+---------------HASH JOIN<------TABLE ACCESS
^
FULL
|
ADR_ORT
|
|
+---------------HASH JOIN<------TABLE ACCESS<---INDEX
^
BY INDEX ROW
RANGE SCAN
|
ADR_NAME
ADR_NAME_IDX
|
|
+---------------TABLE ACCESS
FULL
ADR_ADRESSEN
Die treibende Tabelle ADR_NAME verfügt über eine qualifizierte
Einschränkung (name.name = 'Meier‘) und wird per Index angesprochen.
Basierend auf diesem Datensatz werden alle weiteren Teilmengen über einen
HASH JOIN verarbeitet.
Liegt in der WHERE Klausel keine Einschränkung für auch nur eine Tabelle
vor (ungeachtet der Join Verknüpfung), muß dennoch eine als treibende
Tabelle bestimmt werden. Das ist dann die Tabelle mit den geringsten
Datensätzen (laut Statistik. Wenn falsche Statistiken vorliegen, kann es zu
einer Fehleinschätzung kommen). Diese Tabelle wird ggf. auch mit einem
Index gelesen. Z.B. wenn für das Statement der Hint FIRST_ROWS
angegeben wurde mit der Forderung, möglichst schnell die ersten Ergebnisse
zu liefern.
SELECT
FROM
,
,
,
WHERE
AND
AND
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=2989450 Optimizer=HINT: FIRST_ROWS
1
0
NESTED LOOPS
2
1
NESTED LOOPS
3
2
NESTED LOOPS
4
3
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
5
3
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_ORT'
______________________________________________________________________________________________________
Oracle Trace
Seite 38
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
6
7
8
9
5
2
7
1
INDEX (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
INDEX (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
SQL > select count(*) from adr_adressen;
COUNT(*)
---------1493660
1 row selected.
real: 13868
SQL > select count(*) from adr_plz;
COUNT(*)
---------584
1 row selected.
real: 140
SQL > select count(*) from adr_ort;
COUNT(*)
---------296
1 row selected.
real: 140
SQL > select count(*) from adr_name;
COUNT(*)
---------280073
Die Tabelle Ort ist mit 296 Rows die Kleinste aller beteiligten.
Fehlt eine Einschränkung in der WHERE Klausel, um mit
Zwischenergebnissen die nächsten Teilergebnisse des Join zu bestimmen,
entsteht ein Kartesiches Produkt. Für das oben eingesetzte Statement wurden
alle Join Verknüpfungen entfernt, so das nur noch eine Einschränkung auf die
Tabelle ADR_NAME vorhanden ist. Der EXPLAIn PLAN zeigt die
Auswirkungen.
SELECT
FROM
,
,
,
WHERE
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_Ort ort
ADR_plz plz
ADR_NAME name
name.name = 'Meier';
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=885769941 Optimizer=HINT: FIRST_ROWS
1
0
MERGE JOIN (CARTESIAN)
______________________________________________________________________________________________________
Oracle Trace
Seite 39
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
2
3
4
5
6
7
8
9
10
1
2
3
3
5
2
7
1
9
MERGE JOIN (CARTESIAN)
MERGE JOIN (CARTESIAN)
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_ORT'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_PLZ'
3.2. Der Index
Informationen einer Tabelle können sequentiell, also alle hintereinander, oder
geziehlt per ROWID angesprochen werden. Das Heraussuchen eines
Datensatz aus einer Tabelle über die ROWID ist die schnellste Art an die
Information in einer Datentablle zu gelangen.
Die Vorstellung, ein Index Zugriff ist immer gut, ist nicht richtig. Denn ein Index
ist im Prinzip nichts anderes als eine Tabelle, in der Attribute vorgehalten
werden, die der zugehörigen Tabelle entsprechen und zusätzlich einen
Verweis auf den Datensatz der Tabelle, die Rowid.
Das ein Index von seiner Struktur her schneller die gesuchten Daten findet ist
sicherlich richtig, da ein Index nach einem anderen Muster Daten ablegt und
auch wiederfindet. Ein Zeitverlust ergibt sich je nach Ergebnismenge des
Index durch den Abgleich mit den Daten der Tabelle.
Für Indizes werden an Blöcken mindestens gelesen:
B*Tree Index
1-3 - RANGE SCAN, UNIQUE SCAN
N - FAST FULL SCAN. Die Anzahl Datenblöcke wird
gesteuert durch den Parameter
DB_FILE_MULTIBLOCK_READ_COUNT der initSID.ora
Datei
3.2.1.
Bitmap Index
1
Hash Cluster
1
Ungünstiger CLUSTERING_INDEX
Der CLUSTERING_INDEX ist eine Maßzahl für die Verteilung der sortierten
Indexdaten zu der entsprechenden Daten der Tabelle. Im besten Fall entspricht
der CLUSTERING_INDEX der Anzahl LEAF BLOCKS des Indizes, wogegen das
schlechteste Ergebnis gleich der Anzahl Datensätze der Tabelle entspricht.
______________________________________________________________________________________________________
Oracle Trace
Seite 40
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für folgende Darstellung liegt ein sehr guter CLUSTERING_INDEX Wert vor,
denn die Datensätze liegen fast alle in der gleichen Reihenfolge, wie sie im
Index enthalten sind.
Für das nachfolgende Beispiel müssen für den Index Range Scan die
Datenblöcke zum Index quer durch die Tabelle ermittelt werden und fordert
reichlich Memoryoperationen oder gar I/O.
______________________________________________________________________________________________________
Oracle Trace
Seite 41
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Meist ist das sortierte Speichern durch reorganisieren der Tabelle nicht möglich
und der Zustand kann nicht geändert werden.
3.2.2.
Index Selektivität
Liegt ein Datenmodell vor, für das möglicherweise die Attribute der einzelnen
Indizes in der falschen Reihenfolge eingesetzt wurden und das selektivste
Attribut ist nicht an Position 1 eingetragen, fallen für die Bearbeitung eines
Statements übermäßig viele CONSITENT GETS an. Wenn es fachlich möglich
ist, sollte das selektivste Attribut eines Indizes an erster Position eingetragen
sein.
Welche Auswirkung sich ergeben für einen Verstoß dieser Anforderung, zeigt
folgendes Beispiel.
______________________________________________________________________________________________________
Oracle Trace
Seite 42
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Betrachten wir folgendes SELECT Statement:
SELECT
FROM
WHERE
AND
AND
*
VERTRIEBSREGIONEN
ADR_ID = 142
VERTRIEB_REGION_ID BETWEEN 110 AND 190
VERTRIEB_MITARBEITER_ID = 1
In der Tabelle VERTRIEBSREGIONEN sind ca. 760.000 Sätze gespeichert und
es liegt ein Primary Key Index vor wie folgt:
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM user_ind_columns
WHERE table_name = ´VERTRIEBSREGIONEN´;
INDEX_NAME
---------VTR_PK
VTR_PK
COLUMN_NAME
-----------------VERTRIEB_REGION_ID
ADR_ID
COLUMN_POSITION
--------------1
2
Die Selektivität (DISTINCT_KEYS, also Anzahl Datensätze pro Index-Key) der
einzelnen Indexattribute erhält man:
SELECT COUNT( DISTINCT( vertrieb_region_ID )) "DISTINCT_KEYS"
FROM vertriebsregionen;
DISTINCT_KEYS
------------4
SELECT COUNT( DISTINCT( adr_ID )) "DISTINCT_KEYS"
FROM vertriebsregionen;
DISTINCT_KEYS
------------127030
D.h., auf eine VERTRIEB_REGION_ID entfallen ca. 190.000 Datensätze
(gesamte Anzahl Datensätze / DISTINCT_KEYS), wogegen ca. sechs der
760.000 Sätzen umfassenenden Tabelle einer ADR_ID zugeordnet sind.
Der Wert DISTINCT_KEYS für
SELECT DISTINCT_KEYS,
NUM_ROWS
FROM SYS.ALL_INDEXES
WHERE index_name = 'VTR_PK'
AND table_name = 'VERTRIEBSREGIONEN';
______________________________________________________________________________________________________
Oracle Trace
Seite 43
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
liefert die Selektivität für alle Attribute des Index, also VERTRIEBS_REGION_ID
und ADR_ID in Kombination. Diese Angabe ist abhängig vom
Änderungsverhalten der Tabelle seit dem letzten ANALYSE.
Nun führen wir das SELECT Statement aus und erkennen am EXPLAIN PLAN,
daß der Primary Key Index VTR_PK von dem Oracle Optimizer nicht genutzt
wird.
Explain Plan
-------------------------------1.0 SELECT STATEMENT Mode=CHOOSE Cost (max) = 814
2.1 TABLE ACCESS FULL VERTRIEBSREGIONEN
Die Ausführungszeit beträgt 3,120 Sekunden.
Der FULL TABLE ACCESS wird vom Oracle Optimizer gewählt, weil sich für das
erste IndexAttribut VERTRIEB_REGION_ID eine nicht abschätzbare Anzahl
Indexsätze der Bereichsabfrage ergeben. Nämlich ca. 190.000 Index Einträge
für jede VERTRIEBS_REGION_ID. Da maximal sechs ID's möglich sind ist ein
FULL TABLE ACCESS keine schlechte Wahl.
Ersetzt man
VERTRIEB_REGION_ID BETWEEN 110 AND 190
gegen
VERTRIEB_REGION_ID = 190
zeigt der Ausführungsplan sich wie folgt:
Explain Plan
-------------------------------1.0 SELECT STATEMENT Mode=CHOOSE Cost (max) = 3
2.1 TABLE ACCESS BY ROWID VERTRIEBSREGIONEN
3.1 INDEX RANGE SCAN VTR_PK
Oracle signalisiert mit COST=3 einen guten Ausführungspfad.
Ausführungszeit liegt jedoch nicht weit unter 3 Sekunden.
Die
Dieser Ausführungspfad wurde vom Oracle Optimizer gewählt, weil das erste
Attribut des Index auf Gleichheit abgefragt wurde und somit die Anzahl der zu
lesenden Index Einträge eine begrenzte Menge darstellt. Da diese "begrenzte"
Anzahl jedoch ca. 190.000 Indexsätze umfaßt, also 1/6 des gesamten
Indexbestandes, ist dieser Zugriffspfad ohne Vorteil.
______________________________________________________________________________________________________
Oracle Trace
Seite 44
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Diese Betrachtung dient ausschließlich der Anschauung und zeigt deutlich, daß
ein Indexzugriff nicht immer Geschwindigkeitsvorteile zur Folge haben muß. Dies
wird noch einmal mit Einsatz eines Hint für die Abfrage deutlich.
SELECT /*+ RULE */ *
FROM VERTRIEBSREGIONEN
WHERE ADR_ID = 142
AND VERTRIEB_REGION_ID BETWEEN 110 AND 190
AND VERTRIEB_MITARBEITER_ID = 1
Für den erzwungenen RULE Zugriff zeigt der folgende Ausführungsplan, daß der
Index genutzt wird.
Explain Plan
-------------------------------1.0 SELECT STATEMENT Mode=HINT: RULE Cost (max) = 0
2.1 TABLE ACCESS BY ROWID VERTRIEBSREGIONEN
3.1 INDEX RANGE SCAN VTR_PK
Aber auch hier liegt die Ausführungszeit bei 3 Sekunden.
Warum aber nutzt der Optimizer im CHOOSE Modus den Index VTR_PK nicht?
Betrachten wir hierzu noch einmal die zu Beginn ermittelten Distinct Keys der
einzelnen Index-Attribute.
INDEX-ATTRIBUT
---------------------VERTRIEB_REGION_ID
ADR_ID
DISTINCT_KEYS
------------4
127030
SAETZE pro DISTINCT_KEY ca.
--------------------------190000
6
Da ein Index immer sortiert vorliegt (aufsteigend in der Reihenfolge der
IndexAttribute), müssen wenigstens 190.000 aufeinander folgende Indexsätze
gelesen werden. Aus dieser Ergebnismenge werden die Sätze mit
übereinstimmender ADR_ID ausgewählt, um schließlich mit der ROWID den
eigentlichen Datensatz aus der Tabelle zu selktieren.
Da für einen Index das erste Attribut (hier die VERTRIEB_REGION_ID) mit dem
nächsten (der ADR_ID) in direkter Verbindung steht, kann durch Vertauschen
der Attribute in ihrer Reihenfolge (COLUMN_POSITION) die Anzahl der IndexSätze, die gelesen werden müssen, drastisch reduziert werden.
Mit diesem Ziel wurde folgender Index angelegt.
______________________________________________________________________________________________________
Oracle Trace
Seite 45
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
CREATE INDEX VTR_IDX1
ON VERTREIBSREGIONEN( ADR_ID, VERTRIEB_REGION_ID );
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM user_ind_columns
WHERE index_name = ´VTR_IDX1´;
INDEX_NAME
---------VTR_IDX1
VTR_IDX1
COLUMN_NAME
-----------------ADR_ID
VETRIEB_REGION_ID
COLUMN_POSITION
--------------1
2
Die Überprüfung des Index zeigt, dass die Positionen der Attribute für den neuen
Index VTR_IDX1 gegenüber dem VTR_PK Index vertauscht sind.
Für die wiederholte Ausführung des anfänglichen Statement
SELECT *
FROM VERTRIEBSREGIONEN
WHERE ADR_ID = 142
AND VERTRIEB_REGION_ID BETWEEN 110 AND 190
AND VERTRIEB_MITARBEITER_ID = 1
zeigt sich der Explain Plan wie folgt.
Explain Plan
----------------------------------------------------------1.0 SELECT STATEMENT Mode=CHOOSE Cost (max) = 3
2.1 TABLE ACCESS BY ROWID VERTRIEBSREGIONEN
3.1 INDEX RANGE SCAN VTR_IDX1
Der Indexzugriff erfolgt und die Laufzeit sank auf 0,038 Sekunden.
Veränderungen am Statement, mit denen eine Reduzierung der Kosten (COST)
verbunden war oder der Indexeinsatz im RULE Mode trugen nicht zur
Laufzeitverbesserung bei. Erst der Index, für den das erste Attribut eine sehr
hohe Selektivität aufwies konnte die Laufzeit von ca. 3 Sekunden auf 0,038
Sekunden verbessert werden.
______________________________________________________________________________________________________
Oracle Trace
Seite 46
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
3.3. Verschiedene Indextypen
3.3.1.
B*tree Index
Das B*tree (Binärbaum) Verfahren ist eine sehr verbreitete Art und Weise,
Daten eines Index zu verwalten. Innerhalb eines B*tree Index liegen die
Informationen sortiert vor, in Form eines Binärbaums. Somit muß für eine
SELECT Anforderung nicht der gesamte Index gelesen werden, um sicher zu
stellen, daß kein Eintrag übersehen wurde, sondern nur bis zu dem Punkt, an
dem sich der Suchbegriff ändert. Hier ein Beispiel, in dem vorausgestzt wird,
daß für das Attribut „name“ ein Index vorhanden ist:
SELECT * FROM adressen WHERE name = ‘Maier‘;
D.h., alle ROWIDs zu ‚Maier‘ sind gefunden, wenn z.B. ‚Meier‘ im Index
gefunden wurde. Die ist deutlich zu erkennen in folgendem EXPLAIN PLAN.
Zwei Sätze für einen Index Zugriff.
Rows
------0
1784
584
1784
296
1784
1
2
1493660
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_PLZ'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ORT'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_NAME_IDX'
(NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
Für den Index ADR_NAME_IDX werden zwei Sätze gelesen und für die
Tabelle ADR_NAME nur einer.
Es gilt die Attributreihenfolge zu beachten, wie sie bei der Anlage des Index
bestimmt wurde.
SQL > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM all_ind_columns
WHERE index_name = 'ADRESSEN_IDX01';
TABLE_NAME
-----------------------------ADRESSEN
ADRESSEN
COLUMN_NAME
COLUMN_POSITION
-------------- --------------PLZ
1
VORWAHL
2
______________________________________________________________________________________________________
Oracle Trace
Seite 47
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
PLZ wird als erstes Attribut für diesen Index ausgewiesen und VORWAHL als
zweites. In einer WHERE Klausel muß mindestens das erste Attribut
abgefragt werden. Hier sind <> oder NULL Abfragen falsch.
Nachfolgender EXPLAIN PLAN zeigt, daß der Index benutzt wird.
SQL > SELECT plz
FROM adressen
WHERE plz = 7774;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=7 Optimizer=CHOOSE
1
0
SORT (AGGREGATE)
2
1
INDEX (FULL SCAN) OF 'ADRESSEN_IDX01'
Wogegen folgendes Statement zu einem FULL TABLE ACCESS oder einem
INDEX FAST FULL SCAN führt.
SQL > SELECT plz
FROM adressen
WHERE vorwahl = 7774;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1053 Optimizer=CHOOSE
1
0
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Für einen Tabellen Zugriff über einen B*Tree Index werden 2-4 Datenblöcke
gelesen. Drei für den Index (ein Header-, ein Branch- und ein Leaf Block, in
dem die RowID zu dem Datensatz der Tabelle enthalten ist) und einer für die
Tabelle.
Folgendes Beispiel zeigt diesen Zusammenhang. Mit dem ersten SELECT
wird erst einmal die RowID zu einem Datensatz der Tabelle ADR_ADRESSEN
ermittelt. Aus der TKProf Ausgabe für das SELECT mit der Einschränkung der
zuvor ermittelten RowID ist ersichtlich, daß genau ein CONSISTENT GETS
(query) für die Bildung des Ergebnisses benötigt wird. Im Gegensatz dazu wird
der Index Zugriff mit vier CONSISTENT GETS für den Indexzugriff
ausgewiesen. Die drei „disk“ Zugriffe spiegeln die Indexblöcke wieder, denn
der Datenblock der Tabelle wurde zuvor in der SGA gespeichert.
SQL > SELECT rowid FROM adr_adressen WHERE id =1;
ROWID
-----------------AAAA6AAAFAAAFYMAAA
______________________________________________________________________________________________________
Oracle Trace
Seite 48
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
1 row selected.
SQL >
******************************************************************************
select *
from
adr_anrede where rowid = 'AAAA6AAAFAAAFYMAAA'
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
1
0
-------- ---------- ---------- ---------- ---------0.01
0.00
0
1
0
rows
------0
0
1
------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY USER ROWID) OF 'ADR_ANREDE'
******************************************************************************
select *
from
adr_adressen where id = 1
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.11
3
4
0
-------- ---------- ---------- ---------- ---------0.00
0.11
3
4
0
rows
-----0
0
1
-----1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ADRESSEN'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ADRESSEN_ID_IDX'
(UNIQUE)
******************************************************************************
Nur mit einem ROWNUM=1 kann ein SCAN vermieden werden.
******************************************************************************
SELECT
FROM
WHERE
AND
/*+ RULE */ *
adr_plz
id=10
rownum =1
call
count
------- ------
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ----------
rows
-------
______________________________________________________________________________________________________
Oracle Trace
Seite 49
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Parse
1
Execute
1
Fetch
1
------- -----total
3
0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
3
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
3
0
0
0
1
------1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
1
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
COUNT (STOPKEY)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
Die Anzahl der Datenblöcke, die mindestens für einen Index gelesen werden
müssen, ist abhängig vom B*Level. Dieser Level spiegelt die
Ausprägungshöhe eines Index wieder. Der B*Level 0, also die Leaf-Blocks, ist
immer vorhanden!
Mit dem folgenden Select Statement werden unterschiedliche Indizes und
deren B*Level ausgegeben.
SQL > SELECT
2
INDEX_NAME,
3
TABLE_NAME,
4
BLEVEL
5
FROM SYS.ALL_INDEXES
6 WHERE INDEX_NAME IN( 'ADR_ANREDE_ID_IDX', 'ADR_PLZ_ID_IDX', 'ADR_ADRESSEN_ID_IDX' );
INDEX_NAME
-----------------------------ADR_ANREDE_ID_IDX
ADR_PLZ_ID_IDX
TABLE_NAME
BLEVEL
------------------------------ ---------ADR_ANREDE
0
ADR_PLZ
1
______________________________________________________________________________________________________
Oracle Trace
Seite 50
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
ADR_ADRESSEN_ID_IDX
ADR_ADRESSEN
2
Die TKProf Ausgabe zeigt, daß mit steigender Anzahl für den B*Level, die
Anzahl Indexblöcke steigt, die mindestens verarbeitet werden.
SELECT /*+ FIRST_ROWS */ *
FROM
adr_anrede where id=1
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
------ ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
2
0
------ ---------- ---------- ---------- ---------0.00
0.01
0
2
0
rows
-------0
0
1
-------1
Es wurden zwei Datenblöcke als Query ausgewiesen. Einer für den Index und
einer für die Tabelle. Im folgenden Select werden in Summe drei Query
Blöcke ausgewiesen, entsprechend der höheren Anzahl B*Level.
SELECT
FROM
WHERE
AND
/*+ FIRST_ROWS */ *
adr_plz
id=10
rownum =1
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
3
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
3
0
rows
------0
0
1
------1
SELECT /*+ FIRST_ROWS */ *
from
adr_adressen where id = 4711
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.02
0.01
0
0
0
0.00
0.01
0
0
0
0.01
0.14
4
4
0
-------- ---------- -------- ---------- ---------0.03
0.16
4
4
0
rows
-------0
0
1
-------1
______________________________________________________________________________________________________
Oracle Trace
Seite 51
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
3.3.2.
Bitmap Index
Ein Bitmap Index ist außerodentlich gut geeignet für Attribute mit geringer
Selektivität. Und im Gegensatz zu einem B*Tree Index muß immer nur ein
Indexblock gelesen werden. Durch die komprimierte Form sind in einem
Bitmap Indexblock wesentlich mehr Referenzen zu Datensätzen einer
Tabelle enthalten als in einem B*Tree Index, was jedoch für sehr
dynamische Tabellen zu ungewünschten Locking Situationen führen kann.
Für einen Bitmap werden für jede mögliche Ausprägung eines Attributs eine
eingene Spalte im Index genutzt und der logische Zustand eingetragen
Aus dem ursprünglichen Attribut einer Tabelle
Anrede
Herr
Frau
Firma
Herr
1
0
0
Frau
0
1
0
Firma
0
0
1
Herr
1
0
0
Herr
1
0
0
Kombiniert mit weiteren Attributen der Tabelle entsteht ein Index mit sehr
hoher Selektivität.
3.3.3.
Hash Index
3.4.
Wann Oracle einen Index im CHOOSE Mode nicht nutzt
Häufig ist zu erkennen, daß ORACLE einen Index nicht nutzt, obwohl es
augenscheinlich sinnvoll erscheint. Im RULE Mode hingegen benutzt Oracle
grundsätzlich einen Index, wenn einer existiert.
3.4.1.
Zu große Anzahl von gelöscht markierten Indexeinträgen
Wird ein Datensatz in einer Tabelle gelöscht, erfolgt im Index keine
Löschung des referenzierten Eintrags, sondern er wird als gelöscht markiert.
______________________________________________________________________________________________________
Oracle Trace
Seite 52
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für Oracle ist diese Methode wesentlich schneller, den B*Tree auf dem
aktuellen Stand zu halten, als durch eine Reorganisation des Index.
3.4.2.
Geringes Datenvolumen der Tabelle
Für Tabellen mit geringem Datenvolumen können alle Datensätze mit einer
einzigen Leseoperation in den Speicher gelesen werden. In
Tabellenstatistiken ist die Anzahl gesamter Datenblöcke beschrieben,
welche Tabelle benötigt werden. Ist die Anzahl Datenblöcke kleiner oder
gleich der mit DB_FILE_MULTIBLOCK_READ_COUNT*DB_BLOCK_SIZE
bestimmten Anzahl, wird die Tabelle im FULL TABLE ACCESS gelesen und
nicht mit einem Index.
Zu beachten ist die HIGH WATER MARK einer Tabelle. Es kann
vorkommen, das laut Statistik eine einziger Zugriff ausreicht die gesamte
Tabelle in den Speicher zu laden. Eine Tabelle wird im FULL TABLE
ACCESS jedoch immer bis zur HIGH WATER MARK gelesen und im
schlechtesten Fall liegt die Anzahl Blöcke, die tatsächlich gelesen werden
müssen, um ein vielfaches höher als die Statistiken es ausgewiesen haben.
3.4.3.
Einschränkung mit != oder <>
Einschränkungen in der WHERE Klausel mit != oder <> führen unweigerlich
zum FULL TABLE ACCESS. Zu ungenau ist die Einschränkung auf die
Ergebnismenge. Selbst ein Hint bewirkt selten das Gegenteil.
3.4.4.
Unbekannte Selektivität durch Bereichsabfragen
Für den Fall, daß in der WHERE Klausel eines Statement ein Bereich für ein
Attribut angegeben wurde („>“, „<“ oder BETWEEN) erfolgt häufig ein FULL
TABLE ACCESS. Ursache liegt darin, daß ORACLE die Größt- und
Kleistwerte aus den Statistiken kennt und daraus ermittelt, wie hoch die
Wahrscheinlichkeit ist, daß für die Einschränkung nicht mehr als ca. 20% der
Daten selektiert werden müssen und für den Fall erfolgt ein FULL TABLE
ACCESS Zugriff (ich gebe zu, daß die Angabe von 20% nicht belegt ist,
jedoch liegen verschiedenste Vergleiche vor, die aussagen, daß mehr als
20% Daten einer Tabelle schneller per FULL TABLE ACCESS ermittelt
werden können, als über einen Index).
Der Einsatz von Bindevariablen zeigt sich an dieser Stelle problematisch.
______________________________________________________________________________________________________
Oracle Trace
Seite 53
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
3.4.5.
Unbekannte Selektivität durch Einsatz von Bindevariablen
Für folgendes Beispiel wird Oracle den Indexzugriff bevorzugen, wenn ein
entsprechender Index vorhanden ist.
SELECT *
FROM emp
WHERE empno = :a
Im Gegenteil dazu das folgende Statement.
SELECT *
FROM emp
WHERE empno > :a
Oracle führt in den Statistiken den kleinsten und größten Wert des indizierten
Attributes einer Tabelle. Durch den Einsatz der Bindevariablen kann nicht
mehr bestimmt werden wie viele Daten womöglich gelesen werden müssen
und der FULL TABLE ACCESS erhält den Vorrang vor einem Index Zugriff.
D.h., wenn für die Tabelle „emp“ zehn Datensätze gespeichert sind und das
Attribut „empno“ die Werte 1 bis 10 trägt, ist es ein Unterschied, ob die
Bindevariable den Wert 2 oder 9 beinhaltet. Für den ersten Fall müssen
sieben Datensätze gelesen werden wogegen im zweiten Fall gerade mal ein
Datensatz gelesen werden müßte.
3.4.6.
Unterbrechung der Reihenfolge von Indexattributen
Für das Anlegen eines Index spielt die Angabe der Reihenfolge der Attribute
zu diesem Index eine große Rolle.
Die Tabelle ADR_ADRESSEN erhält folgenden Index:
CREATE INDEX adr_adressen_idx01
ON adr_adressen( id, vorname )
/
Auch wenn in der WHERE Klausel des folgenden Select Statements nur
eines der drei Index Attribute abgefragt wird, erfolgt der Zugriff über den
Index.
______________________________________________________________________________________________________
Oracle Trace
Seite 54
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
SELECT * FROM adr_adressen WHERE id =24244;
Execution Plan
----------------------------------------------------------OPIMIZER=CHOOSE cost= 3
1.1 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_ADRESSEN
2.1 INDEX UNIQUE SCAN LAMBERTZ.ADR_ADRESSEN_ID_IDX
Ursache ist, daß es sich um das erste Attribut des Index handelt. Anders für
folgendes Statement:
SELECT * FROM adr_adressen WHERE vorname = 'Heinz';
Execution Plan
----------------------------------------------------------OPIMIZER=CHOOSE cost= 3016
1.1 TABLE ACCESS FULL LAMBERTZ.ADR_ADRESSEN
Der Zugriff erfolgt im FULL TABLE ACCESS, weil das erste Attribut des
Index nicht abgefragt wurde.
Wird das erste Attribut temporär gefüllt mit einer Angabe, die keine wirkliche
Einschränkung darstellt, läßt sich Oracle oft zu einem Index Zugriff
„hinreißen“.
SELECT
FROM
WHERE
AND
*
adr_adressen
vorname = 'Heinz'
id > 0;
Von dieser Lösung ist abzuraten, denn eine Laufzeitverbesserung wird sich
nicht einstellen.
Grundsätzlich werden nur die Attribute eines Index genutzt, für die keine
Unterbrechung in der Reihenfolge vorliegt, mit der sie angelegt wurden.
Immer wenn die Reihenfolge unterbrochen wird, erfolgt der Indexzugriff mit
den Attributen bis zu der Unterbrechung. Hierzu ein Beispiel:
CREATE INDEX Tabelle_idx01
ON Tabelle( attr1, attr2, attr3, attr4
/
SELECT
WHERE
AND
AND
)
* FROM Tabelle
attr1 = 1234
attr2 = 4711
attr4 = 4321;
Der Index Tabelle_idx01 für die Tabelle TABELLE wird nur mit den Attributen
„attr1“ und „attr2“ angesprochen. Dies hat meistens Nachteile für die Laufzeit
______________________________________________________________________________________________________
Oracle Trace
Seite 55
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
zur Folge gegenüber einem Zugriff über alle Attribute des Indizes. Nicht
aber, wenn die ersten Attribute sehr eindeutig sind.
3.4.7.
Füllgrad des Index-Key
Eine Tabelle mit zwei Indizes liegt vor.
CREATE TABLE adr_idx_tst
(
anrede
vorname
name
strasse
plz
ort
telefon
vorwahl
rufnummer
)
/
VARCHAR2(10),
VARCHAR2(25),
VARCHAR2(90),
VARCHAR2(60),
NUMBER(5),
VARCHAR2(60),
VARCHAR2(15),
NUMBER(10),
VARCHAR2(30)
CREATE INDEX adr_idx_tst_idx01
ON adr_idx_tst
( name,
vorname )
/
CREATE INDEX adr_idx_tst_idx02
ON adr_idx_tst
( name,
vorname,
strasse,
plz )
/
Nun erfolgt ein SELECT in dem drei Tabellenattribute auf Gleichheit
abgefragt werden. Die ersten beiden Attribute sind in beiden Indizes
vorhanden und zwei weitere nur in einem Index. Der SELECT erfolgt ohne
Statistiken, also im RULE Mode (COST wird mit 0 ausgewiesen).
SELECT
FROM
WHERE
AND
AND
*
adr_idx_tst
name = 'Asche'
vorname = 'Jurgen'
strasse = 'Maximilianstr. 2'
Execution Plan
-------------------------------------------------------------OPIMIZER=CHOOSE cost= 0
1.1 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_IDX_TST
2.1 INDEX RANGE SCAN LAMBERTZ.ADR_IDX_TST_IDX01
______________________________________________________________________________________________________
Oracle Trace
Seite 56
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Im RULE Mode entscheidet sich der Oracle Optimizer für den Index
„ADR_IDX_TST_IDX01“, weil alle Attribute des Index in derWHERE Klausel
abgefragt werden. Mit dem Index können aber nur die Sätze ermittelt
werden, welche der Gleichheit von „name“ und „vorname“ entsprechen. Das
sind jedoch mehr Datensätze, als mit dem dritten Attribut aus der WHERE
Klausel gewünscht sind.
Oracle löst das, indem alle Datensätze aus dem Index ermittelt werden,
welche der Bedingung für die Indexattribute entsprechen (drei Indexsätze)
und selektiert jedoch nur einen Datensatz aus der Tabelle aufgrund der
kompletten WHERE Bedingung.
******************************************************************************
SELECT *
FROM adr_idx_tst
WHERE name = 'Barth'
AND vorname = 'Dr. Christian'
AND strasse = 'Im Grund 16'
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
4
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
4
0
rows
---------0
0
1
---------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
3
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_IDX_TST'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_IDX_TST_IDX01'
(NON-UNIQUE)
******************************************************************************
D.h., wenn mehere Indizes vom gleichen Typ (UNIQUE oder nicht) vorliegen,
wird im RULE Mode der genutzt, für den entweder alle oder die meisten
Attribute in der WHERE Klausel erscheinen.
Für den CHOOSE MODE gilt das nicht grundsätzlich.
Auch wenn alle Attribute aus dem Index „adr_idx_tst_idx02“ Einsatz in der
WHERE Klausel finden, erfolgt der Zugriff über den Index
„adr_idx_tst_idx01“. Das liegt daran, daß für diesen Index mehr als 1/3 Drittel
der Tabellenattribute indiziert sind.
SELECT *
FROM adr_idx_tst
WHERE name = 'Asche'
______________________________________________________________________________________________________
Oracle Trace
Seite 57
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
AND vorname = 'Jurgen'
AND strasse = 'Maximilianstr. 2'
AND PLZ = 78267;
Execution Plan
----------------------------------------------------------OPIMIZER=CHOOSE cost= 2
1.1 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_IDX_TST
2.1 INDEX RANGE SCAN LAMBERTZ.ADR_IDX_TST_IDX01
3.4.8.
Fehlende Statistiken
Ein Grund, daß im CHOOSE Mode ein vorhandener Index nicht zum Einsatz
gelangt, können fehlende Statistiken auf nicht allen Tabellen des Statements
sein. Voraussetzung ist, das für wenigstens eine Tabelle oder Index
Statistikwerte vorliegen (Statistiken weisen die Anzahl Rows einer Tabelle
aus, geben Aussage, wie selektiv ein Index ist, usw.). Ohne jegliche
Statistiken erfolt ein RULE Zugriff und der arbeitet vorrangig mit Indizes.
Mit hilfe von Statistiken setzt Oracle das Statement in eine Form um, welche
den RULE Regeln entspricht! Erst dann wird es ausgeführt.
3.4.9.
Veraltete Statistiken
Für Tabellen, in denen im gleichen Verhältnis Datensätze gelöscht, sowie
eingefügt werden, veralten Statistiken kaum. Auch für Tabellen, an denen
kaum Änderungen erfolgen, müssen die Statistiken recht selten neu erzeugt
werden.
Jedoch sind für Tabellen mit extremen Wachstum (z.B. Data Warehouse) die
Statistiken schnell veraltet. Dies muß nicht zwingend zu einem
Laufzeitproblem führen.
Tabellen, die einer extremen Schwankung im Datenvolumen unterliegen,
können nicht beliebig mit neuen Statistiken versehen werden. Erst recht
nicht, wenn das gesamte Datenvolumen recht groß ist, weil die Erstellung
zeitkritisch ist. Für den Fall muß überlegt werden, ob der Zugriff auf diese
Tabellen nicht durchweg regelbasiert erfolgen soll. Aber dann müssen die
Statements unbedingt den Regeln entsprechen!!
3.4.10. Abfrage mit IS NULL
______________________________________________________________________________________________________
Oracle Trace
Seite 58
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Außer für einen Bitmap Index werden NULL Werte für Attribute einer Tabelle
nicht indiziert. D.h., wenn ein B*tree oder Hash Index zum Einsatz gelangen,
führt die Abfrage IS NULL unweigerlich zum FULL TABLE ACCESS.
3.4.11. Abfrage IS NOT NULL
IS NOT NULL ist keine wirkliche Einschränkung in der WHERE Klausel.
Auch wenn für das Attribut, welches auf IS NOT NULL abgefragt wird ein
Index vorliegt der genutzt werden kann, heißt IS NOT NULL nur, daß alle
Indexeinträge der Bedingung entsprechen. Dann ist ein FULL TABLE
ACCESS statistisch einfach erfolgreicher. Das weiß auch Oracle!
3.4.12. Mehrere Indizes
Sind für eine Tabelle mehere Indizes vorhanden, die sich laut Definition
unterscheiden, gilt folgende Hirarchie
Unique Key
Einfacher Index
Auch wenn der Unique Key Index aus nur einem Attribut besteht und der
einfache Index aus meheren Attributen, welche alle in der WHERE Klausel
vorhanden sind.
CREATE INDEX IDX_01 ON test_Tabelle( ID1, ID2, CD );
CREATE UNIQUE INDEX IDX_UK ON test_Tabelle( CD );
SELECT
FROM
WHERE
AND
AND
Bezeichnung, Kennung, aend_dat, aend_user
Test_Tabelle
id1 = 50206
id2 = 50156
cd = 'ABCDE'
Execution Plan
-----------------------------------------------1.1 TABLE ACCESS BY INDEX ROWID TEST_TABELLE
2.1 INDEX UNIQUE SCAN IDX_UK
Hinzu kommt, daß Oracle einen Index höher bewertet, für den alle Attribute
in der WHERE Klausel enthalten sind.
______________________________________________________________________________________________________
Oracle Trace
Seite 59
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
3.4.13. Ungünstiger Clustering_Index
Liegt der Clustering Faktor für einen Index nahe an der Gesamtanzahl
Datensätze für eine Datentabelle, nutzt Oracle nur selten den Index.
3.4.14. LIKE in der WHERE Klausel
Folgende Tabellen werden eingesetzt, um den LIKE Parameter zu bewerten.
SQL > DESC adr_adressen;
Name
Null?
------------------------------- -------ID
ANREDE_ID
VORNAME
NAME_ID
STRASSE
PLZ_ID
ORT_ID
TELEFON
VORWAHL_ID
RUFNUMMER
Type
---NUMBER(7)
NUMBER(7)
VARCHAR2(25)
NUMBER(7)
VARCHAR2(60)
NUMBER(7)
NUMBER(7)
VARCHAR2(15)
NUMBER(7)
VARCHAR2(30)
SQL > DESC adr_name;
Name
Null?
------------------------------- -------ID
NAME
Type
---NUMBER(7)
VARCHAR2(90)
Für die aufgelisteten Tabellen sind folgende Indizes vorhanden:
SELECT table_name, index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name IN( 'ADR_ADRESSEN', 'ADR_NAME' );
TABLE_NAME
-------------ADR_ADRESSEN
ADR_NAME
ADR_NAME
INDEX_NAME
--------------------ADR_ADRESSEN_ID_IDX
ADR_NAME_IDX
ADR_NAME_ID_IDX
COLUMN_NAME
------------ID
NAME
ID
COLUMN_POSITION
--------------1
1
1
______________________________________________________________________________________________________
Oracle Trace
Seite 60
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für die erste Afrage erfolgt die Einschränkung mit ‘%Meyer%‘
SELECT *
FROM adr_adressen,
adr_name
WHERE adr_name.name like '%Meyer%'
AND adr_adressen.name_id = adr_name.id
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3447 Optimizer=CHOOSE
1
0
HASH JOIN
2
1
TABLE ACCESS (FULL) OF 'ADR_NAME'
3
1
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
Der Ausführungsplan zeigt einen FULL TABLE ACCESS auf die Tabelle
ADR_ADRESSEN, weil durch den führenden und abschließenden
Platzhalter ‘%‘ die Ergebnismenge beliebig groß werden kann. D.h., ein
RANGE SCAN über den gesamten Index wäre die Folge.
Anders dagegen, wenn die Einschränkung erfolgt ohne führenden
Platzhalter. Für den Fall ist die Ergebnismenge kalkulierbar und der Index
ein Vorteil.
SELECT *
FROM adr_adressen,
adr_name
WHERE adr_name.name like 'Meyer%'
AND adr_adressen.name_id = adr_name.id
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3024 Optimizer=CHOOSE
1
0
HASH JOIN
2
1
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
3
2
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
4
1
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
______________________________________________________________________________________________________
Oracle Trace
Seite 61
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
4. Erklärung eines Explain Plan
Ein EXPLAIN PLAN ist die dokumentierte Vorgehensweise für die Bearbeitung
eines Statements. Für die Erstellung eines EXPLAN PLAN muß das Statement
nicht ausgeführt werden, sondern es reicht eine Anforderung an Oracle die
Vorgehensweise in einer Tabelle PLAN_TABLE zu speichern. Für einen Trace
gilt das nicht. Dort erfolgt vor der Ermittlung des EXPLAIN PLAN die eigentliche
Ausführung des Statements um Angaben über die Anzahl der tatsächlich
gelesenen Rows oder Laufzeiten der einzelnen Statements treffen zu können.
Nur wenn ein EXPLAIN PLAN ausdrücklich mit dem SQL Kommando „EXPLAIN
PLAN SET ..“ erstellt wird, kann der Name der Tabelle frei gewählt werden.
Für das implizite Erstellen eines EXPLAIN PLAN, z.B. mit AUTOTRACE unter
SQLPLUS oder der Übersetzung eines *trc Files mit dem Tool „tkprof..“, wird
eine Tabelle von Oracle vorausgesetzt, die den Namen PLAN_TABLE trägt. Sie
kann entweder als Synonym oder als Tabelle des Users vorliegen.
In jedem Fall muß sie unter ORACLE 8.0x der nachfolgenden Form
entsprechen:
CREATE TABLE plan_table(
statement_id
varchar2(30),
timestamp
date,
remarks
varchar2(80),
operation
varchar2(30),
options
varchar2(30),
object_node
varchar2(128),
object_owner
varchar2(30),
object_name
varchar2(30),
object_instance numeric,
object_type
varchar2(30),
optimizer
varchar2(255),
search_columns number,
id
numeric,
parent_id
numeric,
position
numeric,
cost
numeric,
cardinality
numeric,
bytes
numeric,
other_tag
varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id
numeric,
other
long
)
/
grant select, insert, update, delete on plan_table to public;
create public synonym plan_table for sys.plan_table;
______________________________________________________________________________________________________
Oracle Trace
Seite 62
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Weicht die Struktur ab, wird an der sonst üblichen Stelle (AUTOTRACE oder
Trace) eine Fehlermeldung erscheinen.
Explizit erstellt man einen EXPLAIN PLAN mit einem SQL Kommando:
EXPLAIN PLAN SET statement_id='LC' INTO my_plan_table for @&File_Name
Dieser Aufruf führt dazu, daß ORACLE einen EXPLAIN PLAN erstellt und in der
angegebenen Tabelle my_plan_table speichert. Die Statement_id ist nicht
festgelegt. Hier lautet sie 'LC'. Die ID hat die Aufgabe, alle Einträge zu einem
EXPLAIN PLAN eindeutig zu identifizieren. Zu beachten ist, wenn noch veraltete
Werte in der Tabelle mit der gleichen ID enthalten sind, die Darstellung des
EXPLAIN entwas bizarr wirken kann, weil die neuen Einträge nicht von den alten
zu unterscheiden sind.
@&File_Name ist eine Variable und gibt den Namen der Datei an, in der das
Statement enthalten ist, für das ein EXPLAIN PLAN erstellt werden soll. Zu
beachten ist, das in dieser Datei nichts außer dem einen Statement enthalten
sein darf und dieses mit einem „;“ abgeschlossen sein muß!
Für die Auswertung wird ein SELECT Statement eingesetzt, welches die
PLAN_TABLE Ergebnisse aufbereitet darstellt.
Generating Execution plan for
1 SELECT anr.ANREDE
2
, name.Name
3
, adr.VORNAME
4
, adr.STRASSE
5
, plz.plz
6
, ort.ort
7
, adr.TELEFON
8
, vhl.vorwahl
9
, adr.RUFNUMMER
10 FROM LAMBERTZ.ADR_ADRESSEN adr
11
, LAMBERTZ.ADR_anrede anr
12
, LAMBERTZ.ADR_name name
13
, LAMBERTZ.ADR_Ort ort
14
, LAMBERTZ.ADR_plz plz
15
, LAMBERTZ.ADR_Vorwahl vhl
16 WHERE anr.ID = adr.ANREDE_ID
17
AND vhl.ID = adr.VORWAHL_ID
18
AND name.ID = adr.NAME_ID
19
AND plz.ID = adr.PLZ_ID
20
AND ort.ID = adr.ORT_ID
21*
AND name.name = 'Meier';
SELECT STATEMENT
OPIMIZER=CHOOSE cost= 3028
Execution Plan
Cost
Distincts Bytes
------------------------------------------------------------ ---------- ---------- ------1.1 HASH JOIN
2.1 TABLE ACCESS FULL LAMBERTZ.ADR_VORWAHL
2.2 HASH JOIN
3.1 TABLE ACCESS FULL LAMBERTZ.ADR_PLZ
3.2 HASH JOIN
3028
1
3026
1
3024
572
164
349
164
213
108680
4264
57236
4264
29394
______________________________________________________________________________________________________
Oracle Trace
Seite 63
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
4.1 TABLE ACCESS FULL LAMBERTZ.ADR_ORT
4.2 HASH JOIN
5.1 TABLE ACCESS FULL LAMBERTZ.ADR_ANREDE
5.2 NESTED LOOPS
6.1 TABLE ACCESS BY INDEX ROWID LAMBERTZ.ADR_NAME
7.1 INDEX RANGE SCAN LAMBERTZ.ADR_NAME_IDX
6.2 TABLE ACCESS FULL LAMBERTZ.ADR_ADRESSEN
1
3022
1
3020
4
3
3016
82
260
4
260
1
1
1493217
3690
24180
40
21580
24
88099803
Diese Darstellung ist deshalb sehr gut, weil nicht nur der EXPLAIN PLAN zu
erkennen ist, sondern auch die COST der einzelnen Operationen, die
DISTINCTS und die geschätzten BYTES, die an die nächst höhere Operation
geliefert werden.
Nun, die erste Operation, die ORACLE durchführt, ist die mit der höchsten
Nummer (hier die 7) mit kleinstem Dezimalanteil (7.1). Die treibende Tabelle ist
streng genommen der Index ADR_NAME_IDX, jedoch gilt die Tabelle zu dem
Index als treibend und nicht der Index selbst. Die Position der treibenden Tabelle
befindet sich nicht zwingend am Ende des EXPLAIN PLAN. Je nach Komplexität
kann sie in der Mitte oder gar zu Beginn des PLAN liegen.
Die Angaben COST, DISTINCTS, BYTES basieren auf den Statistiken, die von
ORACLE nach einem ANALYZE angelegt wurden. Liegen keine Statistiken auf
allen beteiligten Tabellen des Statements vor und nur dann erfolgt der Zugriff im
RULE Mode (auch wenn CHOOSE angezeigt wird). Wurde mit einem RULE Hint
gearbeitet, werden diese Werte nicht angezeigt.
Die Anzahl Byte für einen Datensatz ergeben sich aus:
Bytes = AVG Row Len * Anzahl Rows.
AVG Row Len = Bytes / Anzahl Rows (88099803 / 1493217 = 59).
Auch wenn diese Zahlen nicht bis ins Letzte stimmig sind, ist es hilfreich für die
Einschätzung der erforderlichen Ressourcen, die für eine Teiloperation benötigt
werden.
Der TABLE ACCESS FULL auf die Tabelle ADR_ADRESSEN ist demnach
wesentlich kritischer als für die Tabelle ADR_ANREDE. Jetzt ist auch sofort klar,
warum ein FULL TABLE ACCESS nicht immer kritisch ist!!
Auch die COSTen summieren sich von der höchsten bis zur kleinsten
Positionsnummer (von 3016 auf 3028). In dem Beispiel ist aufgrund der COST
Angababen zu erkennen, daß der FULL TABLE ACCESS auf die Tabelle
ADR_ADRESSEN die meisten Reccoursen benötigt.
Alle Zahlenwerte einer Zeile gelten für die Aktion, in der sie zu finden sind. So
liefert z.B. der Index an Position 7.1 einen Satz und folglich kann nur ein Satz
aus der Tabelle zu Position 6.1 selektiert werden. Gesamt gesehen wurden zu
diesem Zeitpunkt schon 2 Sätze gelesen. Auch wenn der INDEX hier im RANGE
SCAN angegeben wurde, handelt es sich um einen UNIQUE INDEX, der nur
nicht als solcher deklariert ist (siehe Kapitel INDEX RANGE SCAN).
______________________________________________________________________________________________________
Oracle Trace
Seite 64
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für den FULL TABLE ACCESS werden ca. 1493217 Datensätze gelesen.
Position 6.1 und 6.2 sind Datenlieferant für den NESTED LOOP der Position 5.2.
D.h., der NEESTED LOOP reduziert die Daten (1493217 +1) auf 260
Datensätze. Position 5.1 und 5.2 liefern zusammen 264 Datensätze, welche mit
hilfe des HASH JOIN wieder auf 260 Datensätze reduziert werden, usw.
Werden die Ergebnisse der Tabelle PLAN_TABLE nun grafisch dargestellt, sind
dies Zusammenhänge sofort deutlich.
HASH JOIN<--TABLE ACCESS
^
FULL
|
ADR_VORWAHL
|
|
+-----------HASH JOIN<--TABLE ACCESS
^
FULL
|
ADR_PLZ
|
|
+-----------HASH JOIN<--TABLE ACCESS
^
FULL
|
ADR_ORT
|
|
+-----------HASH JOIN<--TABLE ACCESS
^
FULL
|
ADR_ANREDE
|
|
+-----------NESTED LOOPS<--TABLE ACCESS<---INDEX
^
BY INDEX ROW
RANGE SCAN
|
ADR_NAME
ADR_NAME_IDX
|
|
+--------------TABLE ACCESS
FULL
ADR_ADRESSEN
4.1.
Operationen eines Explain Plan
Für die Darstellung eines Explain Plan, existieren Begriffe, deren Bedeutung
nun gezeigt werden soll.
4.1.1.
TABLE ACCESS (FULL)
Der Zugriff auf die Tabelle erfolgt indem alle Daten der Tabelle in den Buffer
eingelesen werden um hieraus die Ergebnismenge zu bilden. Es werden alle
Datenblöcke der Tabelle gelesen bis zur HIGH WATER MARK.
______________________________________________________________________________________________________
Oracle Trace
Seite 65
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
4.1.2.
TABLE ACCESS (CLUSTER)
Einlesen der Daten aus einer Tabelle mit einem Cluster Key Index.
4.1.3.
TABLE ACCESS (HASH)
Ist
für
die
Datenbank
Instanz
der
initSID.ora
Parameter
HASH_JOIN_ENABLED=TRUE gesetzt, findet man in einem Explain Plan
häufig einen TABLE ACCESS (HASH), wenn nicht dieser Zugriff durch einen
Hint übersteuert wurde. Es handelt sich um einen HASH JOIN Index, der von
Oracle zur Laufzeit in der SGA angelegt wird, für die Daten der Tabelle,
welche einmalig im TABLE ACCESS (FULL) gelesen wurde. Angezeigt wird
nur der TABLE ACCESS (HASH)
Zu einem HASH JOIN gehört mindestens eine Tabelle, die gegen eine
weitere Tabelle oder eine Zwischenmenge gelesen wird, z.B. aus einem
NESTED LOOP. Sidn zwei Tabellen an dem HASH JOIN beteiligt, wird
gewöhnlich die größere der beiden Tabellen mit einem HASH Index
versehen. DB_BUFFER Daten einer Tabelle die im FULL TABLE ACCESS
gelesen wurde, sind in der LRU Liste weit vorne zu finden, was heißt, daß
diese Daten sehr schnell wieder aus dem Speicher entfernt werden, wenn es
erforderlich werden sollte, der HASH INDEX jedoch nicht.
SELECT anr.ANREDE
, name.Name
, adr.VORNAME
, adr.STRASSE
, plz.plz
, ort.ort
, adr.TELEFON
, vhl.vorwahl
, adr.RUFNUMMER
FROM LAMBERTZ.ADR_ADRESSEN adr
, LAMBERTZ.ADR_anrede anr
, LAMBERTZ.ADR_name name
, LAMBERTZ.ADR_Ort ort
, LAMBERTZ.ADR_plz plz
, LAMBERTZ.ADR_Vorwahl vhl
WHERE anr.ID = adr.ANREDE_ID
AND vhl.ID = adr.VORWAHL_ID
AND name.ID = adr.NAME_ID
AND plz.ID = adr.PLZ_ID
AND ort.ID = adr.ORT_ID
AND name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.02
0
0
0
0.00
0.00
0
0
0
4.34
12.42
12595
13187
20
-------- ---------- ---------- ---------- ---------4.35
12.44
12595
13187
20
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
______________________________________________________________________________________________________
Oracle Trace
Seite 66
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
624
1784
584
1784
296
1784
4
1784
2
2
1784
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_VORWAHL'
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_PLZ'
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_ORT'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF
'ADR_NAME_IDX' (NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ADRESSEN'
******************************************************************************
Außer zwei Tabellen werden alle im HASH JOIN gelesen. Also einmal FULL
TABLE ACCESS und organisieren der Daten mit einem HASH INDEX im
Speicher. Gerade wenn Tabellen zu Beginn eines EXPLAIN PLAN zu finden
sind, zeigt der HASH Zugriff Vorteile. Denn jeder Datensatz aus der tieferen
Ebene muß mit dem aus der nächst höheren Ebene verglichen werden. Und
wenn dieser zu vergleichende Datensatz mit hilfe eines Index ermittelt
werden kann, sind hier Geschwindigkeitsvorteile zu erkennen. Aber nur,
wenn die SGA ausreichend groß dimensioniert ist!
Fazit ist, daß trotz des Mehraufwandes -Indexbildung zur Laufzeit- dieser
Zugriff schneller ist als ein klassischer Index Zugriff, wie das nachfolgende
Beispiel zeigt. Zu Beginn der Zugriff mit HASH_JOIN_ENABLED = FALSE.
******************************************************************************
SELECT anr.ANREDE
, name.Name
, adr.VORNAME
, adr.STRASSE
, plz.plz
, ort.ort
, adr.TELEFON
, vhl.vorwahl
, adr.RUFNUMMER
FROM LAMBERTZ.ADR_ADRESSEN adr
, LAMBERTZ.ADR_anrede anr
, LAMBERTZ.ADR_name name
, LAMBERTZ.ADR_Ort ort
, LAMBERTZ.ADR_plz plz
, LAMBERTZ.ADR_Vorwahl vhl
WHERE anr.ID = adr.ANREDE_ID
AND vhl.ID = adr.VORWAHL_ID
AND name.ID = adr.NAME_ID
AND plz.ID = adr.PLZ_ID
AND ort.ID = adr.ORT_ID
AND name.name = 'Meier'
______________________________________________________________________________________________________
Oracle Trace
Seite 67
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.07
0.14
3
297
3
0.00
0.00
0
0
0
4.12
13.12
12602
12600
20
-------- ---------- ---------- ---------- ---------4.19
13.26
12605
12897
23
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1784
1785
1784
1785
1784
1785
1784
2
2
1784
1784
4
1784
296
1784
584
1784
624
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID)
OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF
'ADR_NAME_IDX' (NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ADRESSEN'
SORT (JOIN)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ANREDE'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_ORT'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_PLZ'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_VORWAHL'
******************************************************************************
Somit ist der Indexzugriff an dieser Stelle aufwendiger, als im DB_BUFFER
zur Laufzeit einen Index über die komplette Tabelle zu erzeugen.
Für einen TABLE ACCESS (HASH) sollten einige Parameter in der
initSID.ora Datei gesetzt sein:
•
HASH_AREA_SIZE = 2 * SORT_AREA_SIZE
•
HASH_MULTIBLOCK_IO_COUNT
Der DB_FILE_MULTIBLOCK_READ_COUNT Parameter spielt an der Stelle
keine Rolle.
______________________________________________________________________________________________________
Oracle Trace
Seite 68
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Nun also der Zugriff mit einigen
(HASH_JOIN_ENABLED = TRUE)
TABLE
ACCESS
(HASH)
******************************************************************************
SELECT anr.ANREDE
, name.Name
, adr.VORNAME
, adr.STRASSE
, plz.plz
, ort.ort
, adr.TELEFON
, vhl.vorwahl
, adr.RUFNUMMER
FROM LAMBERTZ.ADR_ADRESSEN adr
, LAMBERTZ.ADR_anrede anr
, LAMBERTZ.ADR_name name
, LAMBERTZ.ADR_Ort ort
, LAMBERTZ.ADR_plz plz
, LAMBERTZ.ADR_Vorwahl vhl
WHERE anr.ID = adr.ANREDE_ID
AND vhl.ID = adr.VORWAHL_ID
AND name.ID = adr.NAME_ID
AND plz.ID = adr.PLZ_ID
AND ort.ID = adr.ORT_ID
AND name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.07
0.17
3
295
3
0.00
0.00
0
0
0
4.19
11.87
12602
13187
20
-------- ---------- ---------- ---------- ---------4.26
12.04
12605
13482
23
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
624
1784
584
1784
296
1784
4
1784
2
2
1784
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_VORWAHL'
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_PLZ'
HASH JOIN
TABLE ACCESS (FULL) OF 'ADR_ORT'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF
'ADR_NAME_IDX' (NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ADRESSEN'
******************************************************************************
Der Hash Zugriff ist schneller.
______________________________________________________________________________________________________
Oracle Trace
Seite 69
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Mit dem Einsatz des Hints FIRST_ROWS werden von Oracle grundsätzlich
keine HASH JOINS eingesetzt, weil für den HASH erst einmal die Tabellen
komplett gelesen werden müssen um im Anschluß den Hash Index zu
Erzeugen, mit dem Oracle ab diesem Zeitpunkt auf die Konsistenten Daten
der Tabelle im DB_BUFFER zugreifen wird. Unter dem Aspect, daß
möglichst schnell die ersten Daten geliefert werden sollen, ist für den
FIRST_ROWS ein HASH Zugriff nicht geeignet.
Im folgenden Beispiel wurde der iniSID.ora Parameter gesetzt auf
HASH_JOIN_ENABLED = TRUE. Das Statement mit dem Hint
FIRST_ROWS eingesetzt.
***************************************************************************************
SELECT /*+ FIRST_ROWS */ anr.ANREDE
, name.Name
, adr.VORNAME
, adr.STRASSE
, plz.plz
, ort.ort
, adr.TELEFON
, vhl.vorwahl
, adr.RUFNUMMER
FROM LAMBERTZ.ADR_ADRESSEN adr
, LAMBERTZ.ADR_anrede anr
, LAMBERTZ.ADR_name name
, LAMBERTZ.ADR_Ort ort
, LAMBERTZ.ADR_plz plz
, LAMBERTZ.ADR_Vorwahl vhl
WHERE anr.ID = adr.ANREDE_ID
AND vhl.ID = adr.VORWAHL_ID
AND name.ID = adr.NAME_ID
AND plz.ID = adr.PLZ_ID
AND ort.ID = adr.ORT_ID
AND name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.02
0.01
0
0
0
0.00
0.00
0
0
0
4.53
12.84
12609
26269
4
-------- ---------- ---------- ---------- ---------4.55
12.85
12609
26269
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1785
1785
2
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: FIRST_ROWS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF
'ADR_NAME_IDX' (NON-UNIQUE)
______________________________________________________________________________________________________
Oracle Trace
Seite 70
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
1785
3568
3568
3568
3568
3568
3568
1784
3568
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ADRESSEN'
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_ORT'
INDEX (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_ANREDE'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_ANREDE_ID_IDX' (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX' (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_VORWAHL'
INDEX (UNIQUE SCAN) OF 'ADR_VORWAHL_ID_IDX' (UNIQUE)
******************************************************************************
Erstaunlich für den FIRST_ROWS Zugriff ist, daß die „elapsed time“ ähnlich
ausfällt wie der HASH JOIN Zugriff, die Anzahl CONSISTENT GETS sich
jedoch fast verdoppelt haben. Die CURRENT dagegen am geringsten
ausfallen.
Für Oracle Instanzen mit einer CPU, deren Kapazität durch weitere Oracle
Instanzen oder sonstige Prozesse belastet sind, die eine hohe Anforderung
an die CPU stellen, ist die HASH JOIN Methode langsamer, als ein Index
Zugriff. Ursache ist die hohe CPU Anforderung.
HASH JOIN Zugriffe bieten gerade dann einen zeitlichen Vorteil, wenn eine
große Tabelle mit mehreren kleinen Tabellen gejoint wurde, wie folgender
Vergleich zeigt.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.07
0
0
0
0.00
0.03
0
0
0
4.72
11.08
9872
13183
12
-------- ---------- ---------- ---------- ---------4.72
11.18
9872
13183
12
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
584
1784
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_PLZ'
HASH JOIN
______________________________________________________________________________________________________
Oracle Trace
Seite 71
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
296
1784
1
2
1493660
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ORT'
HASH JOIN
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_NAME_IDX'
(NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
******************************************************************************
SELECT /*+ FIRST_ROWS */
plz.plz, ort.ort
FROM ADR_ADRESSEN adr
, ADR_NAME name
, ADR_Ort ort
, ADR_plz plz
WHERE name.ID
= adr.NAME_ID
AND plz.ID
= adr.PLZ_ID
AND ort.ID
= adr.ORT_ID
AND name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.73
65.65
9872
3007636
4
-------- ---------- ---------- ---------- ---------51.74
65.66
9872
3007636
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1493661
1495444
2987320
3568
3568
1784
3568
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: FIRST_ROWS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
Das gleiche Statement im HASH JOIN benötigt wesentlich weniger
„consistent gets“ als der FIRST_ROWS Zugriff und zudem sechs mal
schneller.
______________________________________________________________________________________________________
Oracle Trace
Seite 72
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
4.1.4.
TABLE ACCESS (BY ROWID)
Es erfolte ein Tabellenzugriff mit einer RowId als Einschränkung. Dies ist der
schnellste Zugriff auf Daten einer Tabelle. Gewöhnlich erscheint diese
Ausführungsbeschreibung durch den Einsatz eines Index, nicht aber, wenn
in der WHERE Klausel eine Einschränkung über die RowID erfolgt.
4.1.5.
INDEX (RANGE SCAN)
Der wohl häufigste Zugriff über einen Index ist wohl der RANGE SCAN. Wie
der Name schon vermuten läßt, wird ein Bereich des Index gelesen, um die
Ergebnismenge im Anschluß aus der Datentabelle per ROWID zu ermitteln.
Ein RANGE SCAN findet Einsatz, wenn alle Attribute eines Index in der
Abfrage eingesetzt sind, aber auch wenn nur ein Teil der Attribute
(entsprechend der definierten Reihenfolge) in der WHERE Klausel
erscheinen. D.h., wenn ein Index aus den Attributen A, B, C besteht und nur
die Attribute A, B in der WHERE Klausel abgefragt werden. Hierzu ein paar
Beispiele:
Für die Tabelle ADRESSEN liegt folgender Index vor:
SQL > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM all_ind_columns
WHERE index_name = 'ADRESSEN_IDX01';
TABLE_NAME
-----------------------------ADRESSEN
ADRESSEN
COLUMN_NAME
COLUMN_POSITION
-------------- --------------PLZ
1
VORWAHL
2
Der folgende SELECT liefert einen RANGE SCAN ohne weiter auf die
Datentabelle zugreifen zu müssen. Grund ist, daß alle Attribute des Index (in
dem Fall „plz“) im Index enthalten sind.
SQL > SELECT
FROM
WHERE
AND
plz
adressen
vorwahl = 7774
plz = 78267;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3 Optimizer=CHOOSE
1
0
INDEX (RANGE SCAN) OF 'ADRESSEN_IDX01'
Dieses Ergebnis liefert Ähnlichkeiten zu einem FAST FULL SCAN.
Wesentlicher Unterschied ist, daß hier alle Attribute des Index in der
WHERE Klausel zu finden sind.
______________________________________________________________________________________________________
Oracle Trace
Seite 73
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
SQL > SELECT plz
FROM adressen
WHERE plz = 78267;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=18 Optimizer=CHOOSE
1
0
INDEX (RANGE SCAN) OF 'ADRESSEN_IDX01'
Der Ausführungsplan gleicht dem zuvor ermittelten. Jedoch werden die
COSTen um ein sechsfaches höher ausgewiesen. Grund liegt darin, daß der
RANGE SCAN um ein vielfaches größer sein kann und somit auch die
Ergebnismenge, gegenüber der Einschränkung mit allen Attributen des
Index. Bedenkt man, daß die Tabelle ADRESSEN 1,6 Millionen Datensätze
beinhaltet, ist die Bewertung mit COST=3 sehr gering. Ein idealer Zugriff ist
demnach, wenn alle Attribute auf Gleichheit in der WHERE Klausel
abgefragt werden.
Somit ist die Annahme gerechtfertigt, daß zu einem erhöhten COST Wert ein
nicht optimaler Zugriff gehört. Diese Verbindung gilt nur, wenn das SELECT
ohne HINT gestartet wurde!
4.1.6.
INDEX (UNIQUE SCAN)
Dieser Zugriff wird ausgewiesen, wenn ein Index genau eine Rowid für die
Tabelle ermittelt hat. Das ist in der Regel der Fall für einen UNIQUE Index.
SELECT /*+ FIRST_ROWS */
PLZ
FROM ADR_PLZ
WHERE PLZ=1055;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1 Optimizer=HINT: FIRST_ROWS
1
0
INDEX (UNIQUE SCAN) OF 'ADR_PLZ_IDX' (UNIQUE)
4.1.7.
INDEX (FAST FULL SCAN)
Mit einem FAST FULL SCAN werden die Indexblöcke, welche zur
Ergebnisermiitlung gelesen werden nicht einzeln gelesen, sondern in
Abhängigkeit des DB_FILE _MULTIBLOCK_READ_COUNT Parameter, wie
er in der initSID.ora gesetzt ist. Der Optimizer benutzt diesen Index Zugriff
gewöhnlich nur, wenn alle selektierten Attribute und die in der WHERE
Klausel im Index enthalten sind.
______________________________________________________________________________________________________
Oracle Trace
Seite 74
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Ein netter Nebeneffekt ist, das nicht alle Attribute in der WHERE Klausel
erscheinen müssen, entsprechend der Reihenfolge der Attribute, damit der
Index eingesetzt wird. Dies ist ein abweichendes Verhalten zu einem
„gewöhnlichen“ Index. Zunächst einmal ein Beispiel:
Für die Tabelle ADRESSEN liegt folgender Index vor:
SQL > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM all_ind_columns
WHERE index_name = 'ADRESSEN_IDX01';
TABLE_NAME
-----------------------------ADRESSEN
ADRESSEN
COLUMN_NAME
COLUMN_POSITION
-------------- --------------PLZ
1
VORWAHL
2
Nachfolgender EXPLAIN PLAN zeigt, daß der Index im FAST FULL SCAN
benutzt wird, obwohl das erste Attribut nicht in der WHERE Bedingung
enthalten ist.
SQL > SELECT plz
FROM adressen
WHERE vorwahl = 7774;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1053 Optimizer=CHOOSE
1
0
SORT (AGGREGATE)
2
1
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Auch nachfolgendes SELECT Statement nutzt den FAST FULL SCAN mit
gleichen COST Werten, denn ein Attribut auf Ungleichheit abgefragt ist, als
wenn es gar nicht in der WHERE Bedingung enhalten wäre.
SQL > SELECT
FROM
WHERE
AND
plz
adressen
vorwahl = 7774
plz != 78267;
Execution Plan
-------------------------------------------------------0
SELECT STATEMENT Cost=1053 Optimizer=CHOOSE
1
0
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Folgendes Statement führt unweigerlich zum FULL TABLE ACCESS, weil
das selektierte Attribute „name“ nicht im Index enthalten ist:
SQL > SELECT name, plz
FROM adressen
WHERE vorwahl = 7774;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3664 Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'ADRESSEN'
______________________________________________________________________________________________________
Oracle Trace
Seite 75
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Viele Tests zeigen jedoch, daß ein FULL TABLE ACCESS meist bessere
Antwortzeiten liefert, wenn die SGA großzügig dimensioniert ist und die
Tabelle nicht gerade zu einem Dataware house gehört.
4.1.8.
INDEX (FULL SCAN)
Für ein SELECT ohne Einschränkung auf eine Tabelle wählt Oracle immer
ein FULL TABLE ACCESS und verzichtet auf einen INDEX( FULL SCAN).
Auch dann, wenn alle Index Attribute selektiert wurden. Ursache liegt darin,
daß eine sequentielle Suche in in einer Tabelle einfacher und somit schneller
erfolgen kann, als einen Index, der als B*Tree organisiert ist, vollständig zu
lesen und anschließend die Daten der Tabelle.
SQL > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM all_ind_columns
WHERE index_name = 'ADRESSEN_IDX01';
TABLE_NAME
-----------------------------ADRESSEN
ADRESSEN
COLUMN_NAME
COLUMN_POSITION
-------------- --------------PLZ
1
VORWAHL
2
Nachfolgender EXPLAIN PLAN zeigt, daß der Index nicht benutzt wird.
SQL > SELECT plz, vorwahl
FROM adressen;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3664 Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'ADRESSEN'
4.1.9.
INDEX (FULL SCAN (MIN/MAX))
Es wurde für die Ermittlung eines Min oder Max Wertes ein Index eingesetzt.
Nicht zu erkennen ist, ob alle Attribute des Index für die Ermittlung eingesetzt
wurden. An dieser Stelle hilft nur ein händische Prüfung.
4.1.10. CONNECT BY
Für den Einsatz einer Hirarchieabfrage wird diese Ausführung angezeigt.
______________________________________________________________________________________________________
Oracle Trace
Seite 76
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
4.1.11. FILTER
Innerhalb einer Verarbeitung entstehen Filteroperationen, wenn zwei
Ergebnismengen zu einer zusammengeführt werden, indem die Ergebnisse,
welche nicht der WHERE Einschränkung entsprechen, eleminiert werden.
4.1.12. MERGE JOIN
Innerhalb einer Verarbeitung werden zwei Teilmengen gebildet, welche per
MERGE JOIN zu einer zusammengeführt werden.
4.1.13. MERGE JOIN (OUTER)
Innerhalb einer Verarbeitung werden zwei Teilmengen gebildet, welche per
MERGE JOIN zu einer zusammengeführt werden. Jedoch sind diese
Teilmengen entstanden aus einem Join, der als OUTER JOIN formuliert
wurde.
4.1.14. MERGE JOIN (CARTESIAN)
Kartesische Produkte entstehen immer dann wenn jeder Datensatz einer
Tabelle gegen jeden Datensatz einer anderen Tabelle gejoint werden.
Ein kartesisches Produkt behindert die Verarbeitung eines Statements. Wie
aber entsteht es. Eine einfache Form ist folgendes Statement:
SELECT emp.* FROM emp, dept
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=5 Optimizer=CHOOSE
1
0
MERGE JOIN (CARTESIAN)
2
1
INDEX (FAST FULL SCAN) OF 'DEPT_PRIMARY_KEY'
3
1
SORT (JOIN)
4
3
TABLE ACCESS (FULL) OF 'EMP'
Die Ursache liegt in der fehlenden oder unzureichenden Einschränkung des
Statements in der WHERE Klausel. Nachfolgend ein Statement mit
durchgängiger Einschränkung in der Where Klausel.
SELECT /*+ FIRST_ROWS */ ort.ort
FROM ADR_ADRESSEN adr
, ADR_Ort ort
______________________________________________________________________________________________________
Oracle Trace
Seite 77
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
,
,
WHERE
AND
AND
AND
ADR_plz plz
ADR_NAME name
name.name = 'Meier'
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1497273 Optimizer=HINT: FIRST_ROWS
1
0
NESTED LOOPS
2
1
NESTED LOOPS
3
2
NESTED LOOPS
4
3
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
5
3
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
6
5
INDEX (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
7
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_ORT'
8
7
INDEX (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
9
1
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_PLZ'
10
9
INDEX (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
Die Bearbeitung des Statements erfolgt in folgenden Schritten:
adr.name -> adr.name_id -> name.id -> adr.ort_id -> ort.id -> ort.ort
Werden nun Teile aus der WHERE Klausel entfernt, fehlen die einzelnen
Verbindungen von einer zur anderen Tabelle. Diese Lücken werden
geschlossen, indem Oracle alle Daten einer Tabelle liest, für die keine
Eischränkung vorliegt und „merge join“ diese zu einem Ergebnis.
SELECT
FROM
,
,
,
WHERE
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_Ort ort
ADR_plz plz
ADR_NAME name
name.name = 'Meier';
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=885769941 Optimizer=HINT: FIRST_ROWS
1
0
MERGE JOIN (CARTESIAN)
2
1
MERGE JOIN (CARTESIAN)
3
2
MERGE JOIN (CARTESIAN)
4
3
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
5
3
SORT (JOIN)
6
5
TABLE ACCESS (FULL) OF 'ADR_ORT'
7
2
SORT (JOIN)
8
7
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
9
1
SORT (JOIN)
10
9
TABLE ACCESS (FULL) OF 'ADR_PLZ'
Im gezeigten Beispiel ist gut zu erkennen, daß für jede fehlende Bedingung
in der WHERE Klausel ein Merge Join (cartesian) entsteht.
______________________________________________________________________________________________________
Oracle Trace
Seite 78
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Aber Vorsicht. Oracle erkennt eine CARTESIAN nicht immer !!!
4.1.15. SORT (ORDER BY)
In einem Statement wurde eine ORDER BY Klausel angegeben. Dies
spiegelt sich in einem Ausführungsplan wieder.
4.1.16. SORT (JOIN)
Bevor ein MERGE JOIN ausgeführt werden kann erfolgt die Sortierung der
einzelnen Ergebnismengen in einem SORT (JOIN).
4.1.17. SORT (AGGREGATE)
In einem Statement wurde eine GROUP BY Klausel angegeben. Für die
Ermittlung des Result Set benutzt Oracle selbst jedoch eine GROUP BY
Funktion.
4.1.18. SORT (UNIQUE)
Innerhalb der Bildung einer Ergebnismenge erfolgt die Eleminierung
doppelter Einträge per SORT (UNIQUE). Meist zu finden für den Einsatz
einer DISTINCT Funktion in einem SELECT Statement.
4.1.19. SORT (GROUP BY)
In einem Statement wurde eine GROUP BY Klausel angegeben. Innerhalb
der Gruppierung erfolgt eine.
SQL > SELECT
FROM
,
,
,
WHERE
AND
AND
AND
GROUP
plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_Ort ort
ADR_plz plz
ADR_NAME name
name.name = 'Meier'
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
BY plz.plz, ort.ort;
______________________________________________________________________________________________________
Oracle Trace
Seite 79
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3080 Optimizer=HINT: FIRST_ROWS
1
0
SORT (GROUP BY)
2
1
HASH JOIN
3
2
TABLE ACCESS (FULL) OF 'ADR_PLZ'
4
2
HASH JOIN
5
4
TABLE ACCESS (FULL) OF 'ADR_ORT'
6
4
HASH JOIN
7
6
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
8
7
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
9
6
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
4.1.20. NESTED LOOPS (OUTER)
Für den verwendeten Join handelt es sich um einen OuterJoin
4.1.21. NESTED LOOPS (ANTI)
Für den verwendeten Join handelt es sich um einen AntiJoin
4.1.22. NESTED LOOPS (SEMI)
Für den verwendeten Join handelt es sich um einen SemiJoin
4.1.23. CONCATENATION
Im SELECT liegt eine CONCATENATION Verknüpfung vor.
4.1.24. INTERSECTION
Im SELECT liegt eine INTERSECTION Verknüpfung vor.
4.1.25. MINUS
Im SELECT liegt eine MINUS Verknüpfung vor.
4.1.26. UNION
Im SELECT liegt eine UNION Verknüpfung vor.
4.1.27. UNION-ALL
Im SELECT liegt eine UNION-ALL Verknüpfung vor.
______________________________________________________________________________________________________
Oracle Trace
Seite 80
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
4.1.28. VIEW
Entweder befindet sich eine VIEW in der Abfrage oder Oracle bildetete zur
Laufzeit eine temporäre Tabelle und weist diese als VIEW aus.
***************************************************************************************
SELECT a.name, b.maximal
FROM (SELECT MAX( plz ) maximal FROM adr_plz ) b, adressen a
call
count
------- -----Parse
1
Execute
1
Fetch
70
------- -----total
72
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.01
0
0
0
0.01
0.00
0
74
8
-------- ---------- ---------- ---------- ---------0.01
0.01
0
74
8
rows
---------0
0
210
---------210
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
36
1
1
1
36
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
MERGE JOIN (CARTESIAN)
VIEW
SORT (AGGREGATE)
INDEX
GOAL: ANALYZED (FULL SCAN (MIN/MAX)) OF
'ADR_PLZ_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADRESSEN'
******************************************************************************
4.1.29. NESTED LOOPS
Zu einem NESTED LOOPS gehören mindestens zwei Tabellen
(Teilmengen), wovon die Eine, mit hilfe eines Index, die Ergebnismenge
gegen die zweite Tabelle vergleicht.
SELECT anr.ANREDE
, name.Name
, adr.VORNAME
, adr.STRASSE
, plz.plz
, ort.ort
, adr.TELEFON
, vhl.vorwahl
, adr.RUFNUMMER
FROM LAMBERTZ.ADR_ADRESSEN adr
, LAMBERTZ.ADR_anrede anr
, LAMBERTZ.ADR_name name
, LAMBERTZ.ADR_Ort ort
, LAMBERTZ.ADR_plz plz
, LAMBERTZ.ADR_Vorwahl vhl
WHERE anr.ID = adr.ANREDE_ID
AND vhl.ID = adr.VORWAHL_ID
AND name.ID = adr.NAME_ID
AND plz.ID = adr.PLZ_ID
______________________________________________________________________________________________________
Oracle Trace
Seite 81
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
AND ort.ID = adr.ORT_ID
AND name.name = 'Meier'
Rows
------0
1784
1785
1784
1785
1784
1785
1784
1785
-> 1784
2
2
1784
1784
4
1784
296
1784
584
1784
624
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID)
OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF
'ADR_NAME_IDX' (NON-UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ADRESSEN'
SORT (JOIN)
TABLE ACCESS
GOAL: ANALYZED (FULL) OF
'ADR_ANREDE'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_ORT'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_PLZ'
SORT (JOIN)
TABLE ACCESS (FULL) OF 'ADR_VORWAHL'
Bearbeitet wird als erstes die Tabelle ADR_NAME indem der Index
ADR_NAME_IDX eine Auswahl an RowID’s bestimmt hat, die der Bedingung
<name.name=‘Meier‘> entspricht und aus der Tabelle selektiert werden per
ROWID. Jeder Satz aus der Tabelle ADR_NAME wird als Einschränkung
(zwei Sätze) genutzt um die Ergebnismenge der Tabelle ADR_ADRESSEN
zu bestimmen (1784 Sätze). Diese Anzahl entspricht dem Ergebnis des
NESTED LOOPS.
In diesem Fall wird der TABLE ACCESS FULL nur einmal! (auch wenn zwei
Rows für die Tabelle ADR_NAME im Plan ausgewiesen werden) ausgeführt
und liefert jeweils 1784 Ergebnissätze an die. Hier der Beweis.
SELECT * FROM LAMBERTZ.ADR_name name WHERE name.name = 'Meier';
ID NAME
---------- -------------------------------------------------------------155936 Meier
1 row selected.
SQL >
****************************************************************************
SELECT *
FROM
adr_name WHERE name = 'Meier'
______________________________________________________________________________________________________
Oracle Trace
Seite 82
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- -------- -------- ---------0.01
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
4
0
-------- ---------- -------- -------- ---------0.01
0.00
0
4
0
rows
-------0
0
1
-------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_NAME_IDX'
(NON-UNIQUE)
****************************************************************************
Oracle liefert genau einen Satz. Somit stellt -vom Sinn her- u.a Statement
den zweiten Tabellenzugriff in der NESTED LOOPS dar.
SELECT
,
,
,
FROM
,
WHERE
adr.VORNAME
adr.STRASSE
adr.TELEFON
adr.RUFNUMMER
LAMBERTZ.ADR_ADRESSEN adr
LAMBERTZ.ADR_name name
adr.NAME_ID = 155936
Das Result Set liefert auch genau die im Trace angegeben 1784 Datensätze.
In diesem Zusammenhang ist auch ersichtlich, warum Oracle diese Tabelle
als treibende Tabelle ausgewählt hat. Auch wenn ADR_ANREDE mit
maximal 3 Einträgen als führende Tabelle wesentlich effektiver wäre.
Sind zwei NESTED LOOPS in einander verschachtelt, werden erst alle
Ergebnisse der inneren Schleife gebildet und als Ergebnismenge mit dem
äußeren Loop -gewöhnlich eine weitere Tabelle- verglichen und
ausgewertet.
4.1.30. HASH JOIN
Als Gegenteil zu einem NESTED LOOP ist ein HASH JOIN Zugriff zu sehen.
Für HASH JOINs werden Tabellen im FULL TABLE gelesen oder zuvor
ermittelte Ergebnisse nach dem HASH Algorithmus im Memory verarbeitet.
______________________________________________________________________________________________________
Oracle Trace
Seite 83
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
4.2.
INDEX RANGE SCAN
In fast allen EXPLAIN PLAN Ausgaben wird ein Index Zugriff als RANGE
SCAN angegeben. RANGE SCANs sind im gewöhnlich nur langsam, wenn
viele Datensätze des Index gelesen werden müssen.
Ein RANGE SCAN wird auch angezeigt, nur ein Treffer aus der Tabelle
geliefert wird. Hier ein Beispiel:
***************************************************************************************
SELECT *
FROM ADR_name name
WHERE name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- ------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
4
0
-------- ---------- ------- ---------- ---------0.00
0.00
0
4
0
rows
------0
0
1
------1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (RANGE SCAN) OF 'ADR_NAME_IDX'
(NON-UNIQUE)
***************************************************************************************
Selbst für einen UNIQUE INDEX erfolgen wenigstens zwei Index Zugriffe für
einen Wert aus der Tabelle:
***************************************************************************************
SELECT *
FROM adr_anrede
WHERE anrede = 'Herrn'
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- ------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
0.01
0.06
1
2
0
-------- ---------- ------- ---------- ---------0.02
0.07
1
2
0
rows
-------0
0
1
-------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
______________________________________________________________________________________________________
Oracle Trace
Seite 84
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Rows
------0
1
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ANREDE'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ANREDE_IDX'
(UNIQUE)
***************************************************************************************
Ein weiteres Beispiel zeigt das gleiche Ergebnis
******************************************************************************
SELECT /*+ RULE */
FROM adr_plz
WHERE id=10
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
*
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.02
2
3
0
-------- ---------- ---------- ---------- ---------0.00
0.02
2
3
0
rows
---------0
0
1
---------1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT
GOAL: HINT: RULE
1
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
2
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
An diesem Trace ist deutlich zu erkennen, daß ORACLE aus dem Index 2
Sätze liest und einen Datensatz aus der Tabelle. Nur mit einem ROWNUM=1
wird nur ein Staz aus dem Index gelesen.
***************************************************************************************
SELECT /*+ RULE */ *
FROM adr_plz
WHERE id=10
AND rownum =1
call
count
------- -----Parse
1
Execute
1
Fetch
1
------- -----total
3
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
3
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
3
0
rows
---------0
0
1
---------1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
COUNT (STOPKEY)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
______________________________________________________________________________________________________
Oracle Trace
Seite 85
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
1
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
***************************************************************************************
Es wird deutlich, wie Oracle einen B*Tree Index auswertet. Erst wird die ID
gelesen, welche der WHERE Bedingung entspricht und mit der
darauffolgenden Row wird sichergestellt, daß ein Wechel des Kriteriums
vorliegt. Durch die Sortierung eines Index ist sichergestellt, das nicht an einer
anderen Stelle im Index ein Eintrag der WHERE Bedingung entsprechen
würde.
______________________________________________________________________________________________________
Oracle Trace
Seite 86
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
5. Bedeutung von Tabellen und Index Statistiken
Bei der Auswertung einer Trace oder TKProf Datei bleiben für die Interpretation
immer Grauzonen. Statistiken helfen diese zu erkennen.
Eine gute Möglichkeit bieten die Oracle Utilities UTLBSTAT.SQL und
UTLESTAT.SQL. Mit UTLBSTAT.SQL werden alle relevanten Daten in
temporäre Tabellen eingetragen und mit den Werten abgeglichen, die mit
UTLESTAT.SQL ermittelt werden. Diese Scripte bieten den Vorteil, daß
Statistiken für einen beliebigen Zeitraum erstellt werden können. Alle V$
Tabellen, in denen Statistiken angezeigt werden, beziehen sich entweder auf
eine Session oder auf Gesamtergebnisse seit dem letzten Start der Instanz.
Führt man UTLBSTAT.SQL in SQL*Plus aus und direkt danach UTLESTAT.SQL
werden Egebnisse aufgelistet, obwohl zwischenzeitlich keine anderen
Operationen auf der Datenbank vorgenommen wurden. Das liegt an SQL*Plus.
Verwendet man ein anderes Programm, indem die Differenzbildung
vorgenommen wird, sind diese Verfälschungen nicht mehr vorhanden.
______________________________________________________________________________________________________
Oracle Trace
Seite 87
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.
Glossar
6.1.
Recursive calls
werden z.B. in SQL*Plus angezeigt wenn AUTOTRACE eingeschaltet wurde.
Recursive calls geben eine Aussage, wie oft Statements implizit ausgeführt
wurden (entweder von Oracle oder Statements aus einer Procedure). Bei
solchen Statements handelt es sich DLL Kommandos, die genau wie alle
anderen Statements geparsed werden müssen, z.B. durch die Einbindung
eines weiteren Extents. Aber auch, wenn ein Cursor für ein Statement von
Oracle während der Verarbeitung aus dem Cache entfernt und immer wieder
zu Ausführung geladen werden muß. Auch der Zugriff auf das Data Dictonary
äußert sich als recursive call. Die Anzahl der recursive calls sollte nach
Möglichkeit 0 sein, wenn keine Proceduren, Funktionen eingesetzt wurden.
Trigger für Datentabellen produzieren auch Recursive Calls.
6.2.
DB_BLOCK_SIZE
Ist die Blockgröße, in der Oracle seine Daten innerhalb eines Extents und im
Speicher verwaltet. Die Blockgröße sollte mit der des Betriebsystems
übereinstimmen, da sonst ein Datenüberhang entsteht. Die Blockgröße kann
nur beim Erstellen der Datenbank festgelegt werden und ist danach nicht
mehr änderbar!
6.3.
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_MULTIBLOCK_READ_COUNT ist ein initSID.ora Parameter und
beschreibt die Anzahl Datenblöcke, die für einen einzigen Lesezugriff
zusammenhängend gelesen werden können. Diese Angabe wirkt nur für FULL
TABLE ACCESS Zugriffe oder dem Index Zugriff FAST FULL SCAN. Alle
anderen Zugriffe basieren immer auf einem Block. Beinhaltet die Tabelle
weiniger
Blöcke
als
die
durch
den
Parameter
DB_FILE_MULTIBLOCK_READ_COUNT festgelegte Anzahl, werden die
verfügbaren in einem I/O Zugriff eingelesen.
Für diesen Parameter gibt es keine allgemeingültigen Richtwert. Wurde er zu
gering gewählt, werden wenige Datenblöcke mit einem Lesezugriff in den
Memory gelesen und jede weitere Anforderung führt zu einem I/O und damit
im schlechtesten Fall zu Wartezeiten. Aber auch eine zu großzügige Definition
des Parameters DB_FILE_MULTIBLOCK_READ_COUNT kann dazu führen,
______________________________________________________________________________________________________
Oracle Trace
Seite 88
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
daß der verfügbare Memory nicht ausreicht, die für einen I/O gelieferten
Datenmengen aufzunehmen. Übermäßiges „flash out“ ist die Folge.
Ein Hinweis, ob dieser Parameter richtig oder falsch gewählt wurde, ist zu
erkennen an dem Wert DB_FILE_SEQUENTIAL_READ in der View
V$SYSTEM_EVENT (SELECT * FROM V$SYSTEM_EVENT WHERE name
= ‘ db file sequential read‘).
Er besagt, wie lange und oft Oracle warten mußte, einen angeforderten Block
aus dem Datafile zu erhalten. Mit einem größeren Wert für
DB_FILE_MULTIBLOCK_READ_COUNT werden also mehere Blöcke direkt
in einem Lesevorgang in den DB_BUFFER übertragen.
Der Wert für DB_FILE_SEQUENTIAL_READ sollte möglichst 0 sein. In jedem
Fall sollte er kleiner werden, wenn DB_FILE_MULTIBLOCK_READ_COUNT
vergrößert wird.
6.4.
Chained rows
treten auf, wenn bei einem INSERT oder UPDATE der verfügbare Speicher
nicht ausreicht, um die veränderten Daten im gleichen Block zu speichern. Die
Blockgröße ist abhängig von der Definition DB_BLOCK_SIZE in der
initSID.ora Datei. CHAINED ROWS können häufig vermieden werden, wenn
der Wert PCTFREE einer Tabelle vergrößert wird. Ist ein Datensatz jedoch
größer als das gesamte Block, entsteht immer ein CHAINED ROW.
Beispiel: DB_BLOCK_SIZE ist für die Datenbank mit 4096 (Byte) eingestellt
und für eine Tabelle wurde PCTUSED mit 40 und PCTFREE mit 10 festgelegt.
Somit stehen maximal ca.3686 Bytes an Speicherplatz für einen Block zur
Verfügung. Nun werden drei Records mit jeweils 1200 Byte in einem Block
gespeichert. Erfolgt ein UPDATE am ersten Datensatz, der um 390 Byte
verlängert wird, ist der PCTFREE ist mit 410 Bytes groß genug um diese
Änderung im gleichen Block einzutragen. Für das gleiche UPDATE an den
beiden anderen Datensätzen reicht der reservierte Platz nicht mehr aus. Zwei
CHAINED ROWS sind entstanden.
Wäre PCTFREE mit 30 (1229 Bytes) bestimmt worden, hätte ORACLE nicht
drei, sondern nur zwei Datensätze der Größe 1200 Bytes in dieses Block
eingetragen und die Satzerweiterung von gesamt 780 Bytes in dem PCTFREE
aufnehmen können.
Unter den gleichen Bedigungen wie in vorherigem Beispiel soll nun ein
Datensatz mit der Gesamtlänge von 9870 Byte eingetragen werden. Auch
wenn PCTFREE auf 0 gesetzt wäre, reicht der verfügbare Speicherplatz in
einem Block nicht aus den Datensatz zu speichern.
______________________________________________________________________________________________________
Oracle Trace
Seite 89
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.5.
Cost
Diese Maßzahl ist ein theoretischer Wert. Jeder Zugriffsmethode ist ein Wert
zugeordnet. Der schnellste Zugriff ist der auf eine Zeile per ROWID, wogegen
der langsamste ein TABLE ACCESS FULL ist. Auch ohne genaue Werte zu
kennen gilt (theoretisch):
geringe Kosten
-
guter Zugriffspfad
hohe Kosten
-
schlechter Zugriffspfad
An diesen Kosten orientiert sich auch der Oracle Optimizer für die
Bestimmung des Explain Plan.
Jedoch gibt es unzählige Beispiele, wo trotz sehr hoher Kosten eine
schnellere Abarbeitung in der Datenbank erfolgt. Also Vorsicht!!
6.6.
DB Block gets
zeigt die Anzahl Blöcke, die als CONSISTENT GETS in den CURRENT Mode
übertragen wurden.
6.7.
DB Block changes
beschreibt die Anzahl dirty Blocks, welche durch INSERT, DELETE oder
UPDATE in der SGA (DB_BUFFER) verändert und ins Rollback geschrieben
wurden.
______________________________________________________________________________________________________
Oracle Trace
Seite 90
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
6.8.
Datenblöcke im Überblick
In der folgenden Grafik werden die Zusammenhänge deutlich, so wie sie in
den oberen Kapiteln beschrieben wurden. Es handelt sich immer um
Datenblöcke von der Größe, wie sie mit dem Parameter DB_BLOCK_SIZE in
der initSID.ora festgelegt wurden.
Physikalisches Device
Physikalisches Device
Tabellen
Indizes
Rollback
Consistent Change
Rollbackdaten
DB_BUFFER
Consistent Gets
DB BLOCK GETS
DB BLOCK CHANGES
Current Blocks
Result Set
______________________________________________________________________________________________________
Oracle Trace
Seite 91
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.9.
Extents
Die Anzahl der EXTENTs spiegelt sich in der HIGH WATER MARK wieder
und umgekehrt. Die Anzahl EXTENTs einer Tabelle verringert sich nicht durch
das Löschen von Datensätzen in der Tabelle, weil Oracle sich an der HIGH
WATER MARK orientiert. EXTENTs können nicht explizit gelöscht werden,
sondern verfallen durch ein TRUNC auf die entsprechende Tabelle. Die
Anzahl der EXTENTs ergibt sich aus dem größten Datenvolumen, was zu
irgend einem Zeitpunkt den Füllstand der Tabelle ausgemacht hat, der HIGH
WATER MARK.
6.10. High Water Mark
ist eine Maßzahl für den letzten Datenblock einer Tabelle im letzten Extent in
der Datenbank, der zu irgend einem Zeitpunkt einmal Daten beinhaltet hat.
Diese Marke wird selbst durch Löschen aller Datensätzen nicht herunter
gesetzt, sondern bleibt bis zu einem TRUNCATE oder DROP der Tabelle
bestehen. Bei einen FULL TABLE ACCESS Zugriff auf Daten einer Tabelle
werden alle Blöcke bis zur HIGH WATER MARK eingelesen; auch wenn sich
im Extremfall nicht ein Datensatz in der Tabelle befindet. Die HIGH WATER
MARK erhöht sich auch, wenn Daten in die Tabelle eingefügt werden und per
ROLLBACK wieder entfernt wurden.
Parallele Inserts in Tabellen produzieren ggf. eine sehr hoch gelegene HIGH
WATER MARK. Ein FULL TABLE ACCESS arbeitet die gesamte Tabelle bis
zur HIGH WATER MARK ab, auch wenn nicht ein Datensatz in der Tabelle
vorliegt. Änderungen an der HIGH WATER MARK finden auch statt wenn
Daten per Rollback wieder aus der Tabelle entfernt wurden!
6.11. User calls
sind Anforderungen durch Oracle dem User Sources bereitzustellen. User
Calls beinhalten Parse und Execute Anforderungen.
______________________________________________________________________________________________________
Oracle Trace
Seite 92
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
6.12. Execute counts
Gesamtanzahl aller SQL Aufrufen (User und recursive calls).
6.13. Distinct keys
Wird immer nur für einen Index angegeben und zeigt die Anzahl Datensätze
an, die mit einem Index Key erreicht werden können. Der Wert beträgt für
einen primary Key die Anzahl Rows der zugehörigen Tabelle. Im
schlechtesten Fall liegt der Wert bei eins und würde aussagen, daß für einen
Index Key alle Sätze der Tabelle angesprochen würden. Gewöhnlich wird der
Wert des DISTINCT KEY jedoch berechnet. Z.B. sind in der Tabelle
ADR_ADRESSEN 245591 NAME_ID’s vor:
SQL > select count( distinct name_id ) from adr_adressen;
COUNT(DISTINCT NAME_ID)
---------------------245591
1 row selected.
Diese Werte werden von Oracle ermittelt, wenn ein Index mit Statistikwerten
durch ANALYZE bestimmt wurden. In der View ALL_INDEXES existiert eine
Spalte mit dem Namen DISTINCT_KEYS, in dem der Wert gespeichert ist.
Abweichungen zwischen dem errechneten Wert und dem Wert, wie er in der
View ALL_INDEXES eingetragen ist, entstehen durch die Art, wie die
Statistikwerte ermittelt wurden. Nur mit der ANALYZE Option COMPUTE
liegen keine Abweichungen vor.
Oracle diese Arbeit leisten zu lassen, hat u.a. den Vorteil, daß
DISTINCT_KEYS für Indizes ermittelt werden, die aus mehreren Attributen
bestehen.
6.14. Cardinalität
Errechnet sich aus:
______________________________________________________________________________________________________
Oracle Trace
Seite 93
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Gesamte Anzahl Datensätze einer Tabelle
--------------------------------------------------------------------------------------
DISTINCT_KEYS
Also im besten Fall ergibt sich der Wert 1 und im schlechtesten fall die Anzahl
der Datensätze der Tabelle.
6.15. Rule
Die gesamte Vorgehensweise die Oracle benutzt, ein Result Set zu ermitteln,
basiert auf einem Regelwerk. In diesem Regelwerk wird beschrieben welche
Operationen als schnell und unkritisch gelten und welche nicht von Vorteil
sind.
Priorität Zugriffsmethode
1
Tabellenzugriff Zugriff per RowID
2
Tabellenzugriff mit einem Unique Index (ein Attribut), für den
das IndexAttribut in der WHERE Klausel auf = abgefragt wird.
3
Tabellenzugriff mit einem Unique Index (konkateniert), für den
alle IndexAttribute in der WHERE Klausel auf = abgefragt
werden.
4
für den alle IndexAttribute in der WHERE Klausel auf =
abgefragt gegen einen Cluster Key des selben Clusters
5
Tabellenzugriff mit einem Cluster-Index für den alle
IndexAttribute in der WHERE Klausel auf = abgefragt werden.
6
IndexSchlüssel eines Hash Clusters
Tabellenzugriff mit einem NonUnique Index (konkatieniert), für
den alle IndexAttribute in der WHERE Klausel auf = abgefragt
werden.
7
Tabellenzugriff mit einem NonUnique Index für ein Attribut.
8
B*tree Index mit mehreren Attributen einer Tabelle
(zusammengesetzter Index).
Tabellenzugriff mit einem uncompressed Index (konkatiniert),
für den alle IndexAttribute in der WHERE Klausel auf >= oder
<= abgefragt werden.
9
Tabellenzugriff mit einem compressed Index (konkatiniert), für
den alle IndexAttribute in der WHERE Klausel auf >= oder <=
______________________________________________________________________________________________________
Oracle Trace
Seite 94
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
den alle IndexAttribute in der WHERE Klausel auf >= oder <=
abgefragt werden.
10
Bereichssuche in einem abgeschlossenen Intervall über
Spalten mit einem Index.
Tabellenzugriff mit einem uncompressed Index (konkatiniert),
für den nicht alle IndexAttribute in der WHERE Klausel
abgefragt werden.
11
Bereichsuche in einem nicht abgeschlossenen Intervall über
Spalten mit einem Index
Tabellenzugriff mit einem compressed Index (konkatiniert), für
den nicht alle IndexAttribute in der WHERE Klausel abgefragt
werden.
12
Tabellenzugriff mit einem Unique Index für BETWEEN oder
LIKE Abfragen
13
Tabellenzugriff mit einem NonUnique Index für BETWEEN oder
LIKE Abfragen
14
Tabellenzugriff mit einem Unique Index für > oder < Abfragen
15
Tabellenzugriff mit einem NonUnique Index für > oder <
Abfragen
16
Sort / Merge Operationen
17
Max oder Min Ermittlung auf Spalten einer Tabelle, für die ein
Index vorhanden ist.
18
ORDER BY auf Spalten eines Index, GROUP BY auf Spalten
eines Index
19
Full Table Access, ORDER BY auf Spalten ohne Index,
GROUP BY auf Spalten ohne Index
Für einen regelbasierten Zugriff muß das eingentliche Statement auch einem
Regelwerk entsprechen.
Die treibende Tabelle muß als letzte Tabelle in der FROM Klausel aufgezeigt
werden und das Attribut, mit welchen Oracle die erste Teilmenge bestimmen
soll, ist an erster Stelle der WHERE Klausel anzugeben (siehe auch Kapitel
„Wie erfolgt die Auflösung eines Statement“ ).
SELECT
FROM
WHERE
AND
*
Tablle1, Tabelle2(treibene Tabelle)
Tabelle2.bez = ‘Weiss nicht‘
Tabelle2.Tabelle1_id =Tabelle1.id
______________________________________________________________________________________________________
Oracle Trace
Seite 95
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.16. Choose
Seit der Version 7.xx stellt Oracle zu dem RULE Mode den statistikbasierten
Zugriff zur Verfügung. Er kann gewählt werden per Hint, ALTER SESSION..,
oder über eine Einstellung in der initSID.ora und die gesamte Datenbank
arbeitet im CHOOSE Mode. CHOOSE bedeutet, daß der statistikbasierte
Zugriff gewählt wird, wenn für die am Statement beteiligten Tabellen und
Indizes Statistikwerte erzeugt wurden.
Wenn keine Statistiken für die Tabellen und Indizes vorliegen erfolgt der
Zugriff im RULE Mode, auch wenn die Datenbank für den CHOOSE Mode
eingestellt ist. So oder ähnlich wird die Frage beantwortet, ob die Ausführung
eines Statement regel- oder statistikbasiert ohne Statistikwerte erfolgt.
Betrachten wir folgende Ausgabgssituation:
SELECT
FROM
WHERE
OR
index_name, column_name, column_position
user_ind_columns
table_name = 'ADR_ADRESSEN'
table_name = 'ADR_NAME';
TABLE_NAME
-------------ADR_ADRESSEN
ADR_NAME
ADR_NAME
INDEX_NAME
--------------------ADR_ADRESSEN_ID_IDX
ADR_NAME_IDX
ADR_NAME_ID_IDX
COLUMN_NAME
----------------ID
NAME
ID
COLUMN_POSITION
--------------1
1
1
SQL > SELECT count(*)
FROM adr_adressen;
COUNT(*)
---------1493660
1 row selected.
SQL > SELECT count(*)
FROM adr_name;
COUNT(*)
---------280073
1 row selected.
SQL >
Wenn also im CHOOSE Mode ein RULE Zugriff erfolgt, sollte man das am
Explain Plan erkennen können. Für einen RULE Zugriff werden keine
COSTen angezeigt und die Anzahl Buffer Gets sind dann gleich.
______________________________________________________________________________________________________
Oracle Trace
Seite 96
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für das erste Statement wurde sichergestellt, daß auf beiden Tabellen
Statistikwerte vorliegen. Die Ausführung erfolgt ohne Hint.
Mit Statistikwerten:
SQL > select *
from adr_adressen,
adr_name
where adr_name.name like 'Meyer%'
and adr_adressen.name_id = adr_name.id
and rownum < 11;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3024 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
HASH JOIN
3
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
4
3
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
5
2
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
89 consistent gets
Um erkennen zu können, wann ein RULE Zugriff im CHOSSE Mode erfolgt,
wird das folgende Statement per Hint zu einem RULE Zugiff gezwungen.
Rule Zugriff mit einem Hint erzwungen:
SQL > select /*+ RULE */ *
from adr_adressen,
adr_name
where adr_name.name like 'Meyer%'
and adr_adressen.name_id = adr_name.id
and rownum < 11;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=HINT: RULE
1
0
COUNT (STOPKEY)
2
1
NESTED LOOPS
3
2
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
4
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
5
4
INDEX (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
20844 consistent gets
______________________________________________________________________________________________________
Oracle Trace
Seite 97
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Für die nachfolgenden Statements wurden nach einander die Statistikwerte
einzelner Tabellen gelöscht.
ohne Statistikwerte für die Tabelle ADR_NAME und deren
Indizes
SQL > select *
from
,
where
and
and
adr_adressen
adr_name
adr_name.name like 'Meyer%'
adr_adressen.name_id = adr_name.id
rownum < 11;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3201 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
HASH JOIN
3
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
4
3
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
5
2
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
89 consistent gets
ohne Statistikwerte für die Tabelle ADR_Adressen und deren
Indizes
SQL > select *
from
,
where
and
and
adr_adressen
adr_name
adr_name.name like 'Meyer%'
adr_adressen.name_id = adr_name.id
rownum < 11;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=3024 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
HASH JOIN
3
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
4
3
INDEX (RANGE SCAN) OF 'ADR_NAME_IDX'
5
2
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
89 consistent gets
______________________________________________________________________________________________________
Oracle Trace
Seite 98
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für das letzte Statement wurden alle Statistiken für die Tabellen und Indizes
entfernt. Wenn nun die Annahme stimmt, zeigt sich der Zugriff exakt gleich,
wie er für den Einsatz des Hint RULE zu erkennen war.
Entfernen aller Statistikwerte auf Tabellen und Indizes
SQL > select *
from
,
where
and
and
adr_adressen
adr_name
adr_name.name like '%Meyer%'
adr_adressen.name_id = adr_name.id
rownum < 11;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
NESTED LOOPS
3
2
TABLE ACCESS (FULL) OF 'ADR_ADRESSEN'
4
2
TABLE ACCESS (BY INDEX ROWID) OF 'ADR_NAME'
5
4
INDEX (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
20844 consistent gets
Tatsächlich ist der Ausführungsplan identisch dem RULE Zugriff und die
COSTen werden nicht ausgewiesen. Auch die Anzahl CONSISTENT GETS
sind identisch. Wenn auch im gezeigten Beispiel mit Teilstatistiken gute
Ergebnisse erzielt wurden, ist davon auszugehen, daß es auch gegenteilige
Ergebnisse gibt.
6.17. Treibende Tabelle
Als treibende Tabelle wird die Tabelle in einem Join bezeichnet, welche die
ersten Ergebnisse liefert, um eine Weiterverarbeitung vornehmen zu können.
Am einfachsten ist dies zu erkennen für einen RULE Zugriff. Denn laut
Definition benutzt Oracle die Tabelle als treibend, welche in der FROM
Klausel als letztes eingetragen ist.
******************************************************************************
SELECT
FROM
,
,
/*+ RULE */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
______________________________________________________________________________________________________
Oracle Trace
Seite 99
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
,
WHERE
AND
AND
AND
ADR_plz plz
name.ID
=
plz.ID
=
ort.ID
=
name.name =
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
adr.NAME_ID
adr.PLZ_ID
adr.ORT_ID
'Meier'
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
127.60
165.63
11389
7483264
4
-------- ---------- ---------- ---------- ---------127.60
165.64
11389
7483264
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1493661
1493661
1493661
2987320
2987320
2987320
2987320
1784
2987320
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_NAME_ID_IDX'
(UNIQUE)
******************************************************************************
Wie unsinnig die Anordung der FROM Klausel für dieses Statement war, zeigt
eine weitere TKProf Ausgabe für das gleiche Statement mit umgestellter
FROM Klausel. Besser ist die Tabelle ADR_NAME als Führende zu
bestimmen.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ RULE */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_Ort ort
ADR_plz plz
ADR_NAME name
name.name = 'Meier'
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
51.39
64.80
10056
3007636
4
-------- ---------- ---------- ---------- ---------51.39
64.80
10056
3007636
4
rows
---------0
0
1784
---------1784
______________________________________________________________________________________________________
Oracle Trace
Seite 100
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
1784
1785
1785
1493661
1495444
2987320
3568
3568
1784
3568
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
******************************************************************************
Somit ist auch die Anzahl Rows, die bearbeitet wurden, wesenlich geringer als
im Beispiel zu Beginn.
Die treibende Tabelle sollte in jedem Fall jene sein, welche die beste
Einschränkung in der WHERE Klausel enthält.
Im CHOOSE Mode löst Oracle die Frage nach der treibenden Tabelle selbst,
wie der folgende FIRST_ROWS Zugriff zeigt.
******************************************************************************
SELECT
FROM
,
,
,
WHERE
AND
AND
AND
/*+ FIRST_ROWS */ plz.plz, ort.ort
ADR_ADRESSEN adr
ADR_NAME name
ADR_Ort ort
ADR_plz plz
name.ID
= adr.NAME_ID
plz.ID
= adr.PLZ_ID
ort.ID
= adr.ORT_ID
name.name = 'Meier'
call
count
------- -----Parse
1
Execute
1
Fetch
595
------- -----total
597
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.01
0
0
0
0.00
0.00
0
0
0
51.60
77.51
14084
3007636
4
-------- ---------- ---------- ---------- ---------51.61
77.52
14084
3007636
4
rows
---------0
0
1784
---------1784
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (LAMBERTZ)
Rows
------0
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: FIRST_ROWS
______________________________________________________________________________________________________
Oracle Trace
Seite 101
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
1784
1785
1785
1493661
1495444
2987320
3568
3568
1784
3568
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ADRESSEN'
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF
'ADR_NAME'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF
'ADR_NAME_ID_IDX' (UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_ORT'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_ORT_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
Nun gilt noch zu klären, warum aus der Tabelle ADR_NAME 1495444 Rows
gelesen werden, obwohl eine solche Anzahl nicht in dieser Tabelle enthalten
ist.
SQL > SELECT table_name, num_rows
FROM sys.dba_tables
WHERE owner = 'LAMBERTZ'
AND Table_Name IN( 'ADR_ADRESSEN', 'ADR_ANREDE', 'ADR_NAME', 'ADR_PLZ' );
TABLE_NAME
NUM_ROWS
------------------------------ ---------ADR_ADRESSEN
1493217
ADR_ANREDE
4
ADR_NAME
280073
ADR_PLZ
584
3 rows selected.
real: 230
SQL >
Grund ist, daß eigentlich die Tabelle ADR_ADRESSEN als treibende
eingesetzt wurde. Das liegt am NESTED LOOP. Hier der Beweis.
Zu Beginn werden für die Tabelle ADR_ADRESSEN 1493661 Rows im FULL
TABLE ACCESS ermittelt. Jede dieser Row‘s wird auf die Gleichheit name.id
= adr.name_id gegen die Tabelle ADR_NAME geprüft dadurch, daß zwei
Indexeinträge (siehe Indexe) aus dem Index ADR_NAME_ID_IDX zu einer
Ergebniszeile der Tabelle führt. In Summe sind das 2987320 Row‘s aus dem
Index. Eine Row der Tabelle wird ohne Indexzugriff gelesen.
Dieses Vorgehen ist insofern besser weil weniger Datensätze bewertet
werden müssen, wenn jeder Satz der Tabelle ADR_ADRESSEN gegen die
indizierte Tabelle ADR_NAME verglichen wird und nicht umgekehrt.
Trotz der kleinen Unschärfe kann nach wie vor davon ausgegangen werden,
daß ADR_NAME die treibende Tabelle ist.
______________________________________________________________________________________________________
Oracle Trace
Seite 102
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Für einen CHOOSE Zugriff ohne Einschränkung auf eine der gejointen
Tabellen fällt die Auswahl als treibende Tabelle immer auf die mit der
geringsten Anzahl Datensätze. Das liegt daran, daß Oracle mit jedem Satz
aus dieser Tabelle die Ergebnisse der nächsten Tabelle bestimmt. Somit ist
das Datenvolumen was sich für die Weiterverarbeitung ergibt am geringsten.
______________________________________________________________________________________________________
Oracle Trace
Seite 103
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.18. Der Shared Pool Memory
Der Shared Pool ist ein Teil der SGA. Die Größe des Shared Pool kann durch
den Parameter SHARED_POOL_SIZE in der initSID.ora beeinflußt werden.
Jedoch nicht einzelne Teile innerhalb des Shared Pool.
Shared Pool
Library Cache
Dictionary Cache
Shared SQL Area
Row cache
Library, API, ...
Trigger, Functions,
Procedures,
Packages
Control Stuctures
Locks, Librarys, Cache
handles, LRU, MRU, ....
Reuseable Runtime
Memory
Sorts, ...
Control Structures
- Character Set conversion
Memory
- Network Securety
Attributes
Die einzelnen Größen (ohne die Control Strukturen)können ermittelt werden
mit dem SELECT:
SELECT NAME
SUM( BYTES )
FROM SYS.V_$SGASTAT
WHERE name IN( 'sql area',
'library cache',
'free memory',
'dictionary cache',
'PL/SQL DIANA',
'PL/SQL MPCODE' )
GROUP BY name
______________________________________________________________________________________________________
Oracle Trace
Seite 104
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
6.19. Die SQL Area
Die SQL Area ist ein Speicherbereich in der SGA (system global area), der
eine Historie genutzter Statements oder PL/SQL Routinen enthält, wobei eine
Trennung für SQL- und PL/SQL Statements gegeben ist. In diesem SHARED
MEMORY POOL landen alle PL/SQL Routinen sowie SELECT, INSERT,
UPDATE und DELETE Anweisungen, wenn sie sich von denen
unterscheiden, die schon im Pool vorliegen.
Für viele Zugriffe auf die Datenbank ist die Zeit für das eigentliche UPDATE,
INSERT, SELECT oder DELETE der Daten der geringere Anteil verglichen mit
den Zeiten, die für das Kontrollieren der Statements und bestimmen des
Zugriffspfad benötigt werden, wenn die CPU Leistung begrenzt verfügbar ist.
Genau an dieser Stelle wird der SHARED MEMORY POOL genutzt, indem
auf gespeicherte Informationen zu einem identischen Statement zurück
gegriffen wird.
Für die Erkennung, ob es sich bei dem Statement um eins handelt, das schon
einmal verwendet wurde, findet ein 1:1 Vergleich der Statements statt. D.h.
SELECT * FROM EMP ist nicht gleich SELECT * FROM Emp. Nur wenn kein
Unterschied in der Schreibweise zu einem zuvor ausgeführten Statement
besteht, wird der Parse Vorgang vorzeitig beendet und auf die Informationen
des Shared Memory Pool verwiesen. Erkennbar ist dies, wenn z.B. ein
SELECT Statement zweimal hintereinander in SQL*Plus ausgeführt wird. Der
zweite Lauf liefert die Ergebnismenge schneller als der Erste.
Auch der Ausführungsplan für den Aufruf einer View wird im Shared Memory
Pool abgelegt und dem Statement mit der View zugeordnet - nicht dem
Statement, was sich hinter der View verbirgt!
Vorgehalten werden die Informationen im Shared Memory Pool nach dem
FIFO Prinzip. Also das Statement, welches als erstes in den Shared Memory
eingelagert wurde, wird als erstes wieder ausgelagert.
6.20. Library cache
Der Library Chache ist ein Speicherbereich in der SGA, in dem die
ausgeführten Packages, Funktionen, Proceduren oder Trigger vorgehalten
werden. Der gesamte Quellcode eines solchen PL/SQL Moduls muß
wenigstens einmal zur Ausführung gelangt sein. Der Library Cache wird
ähnlich verwaltet wie die SQL Area und entfernt Module wieder aus diesem
Speicher wenn sie über einen längeren Zeitraum nicht genutzt wurden und der
vorhandene Library Chache nicht ausreicht, um ein neues Modul
aufzunehmen.
______________________________________________________________________________________________________
Oracle Trace
Seite 105
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.21. db file scattered read
Dieses Wait Event zeigt an, daß für den User Process Daten in die SGA
gelesen werden sollen aber für die Lesezugriffe das Device nicht schnell
genug die Daten liefert. Nur für einen Full Table Acces oder Full Index Access
entstehen diese Art Wartezustände, weil soviel Datenblöcke am Stück
angefordert
werden,
wie
durch
initSID.ora
Parameter
„DB_FILE_MULTIBLOCK_READ_COUNT“ beschrieben sind und weil die
Daten verteilt über das gesamte Device gelesen werden müssen, entsteht ein
Wait Event.
Dies hat zur Folge, daß kein durchgängiger Speicherbereich in der SGA
belegt werden kann, sondern die Daten fragmentiert werden.
6.22. db file sequential read
Dieses Wait Event zeigt an, daß für den User Process Daten in die SGA
gelesen werden sollen, die Lesezugriffe des Device nicht schnell genug
erfolgen und somit die zusammenhängende Lieferung von Daten nicht
möglich ist. Der Unterschied zum „db file scattered read“ liegt darin, daß die
gelesenen Daten zusammenhängend in dem Speicher abgelegt werden. Das
liegt nicht zuletzt daran, daß nur ein Datenblock angefordert wurde. Und einen
Block zusammenhängend in den Speicher zu übertragen ist nicht wirklich
schwierig. Single Block I/O findet gewöhnlich nur für den Einsatz von Indexen
statt.
6.23. Buffer busy waits
Dieser Wartezustand entsteht, wenn ein Datenblock im Buffer cache von
mehreren Prozessen beansprucht wird. Ein solcher Zustand wird auch „Block
contention“ genannt. „buffer busy waits“ entstehen für einen DATA BLOCK,
SEGMENT HEADER, UNDO HEADER und UNDO BLOCK. Welcher Typ
welche Wartezustände verursacht kann ermittelt werden mit folgendem
Statement:
SELECT class, count
FROM V$WAITSTAT
WHERE count > 0
ORDER BY count DESC;
SEGMENT HEADER Waits entstehen meist für eine nicht ausreichende
Anzahl FREELISTs der Tabelle oder eines Indexes. Automatisches Segment
Speicher Management, wie es für einen LOCALLY MANAGED TABLESPACE
üblich ist, verhindern diese Probleme.
DATA BLOCK Waits haben als Ursache:
Unselektive Index Zugriffe
______________________________________________________________________________________________________
Oracle Trace
Seite 106
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
Wenn mehere Prozesse den selben Block eines Indexes oder
einer Datentabelle mit neuen Daten versorgen wollen. Letzlich
wartet der Prozess auf die Zuweisung einer Speichersperre
(Latch).
UNDO HEADER ist eine Wartesituation auf den SEGMENT HEADER eines
Rollbacksegment. Wir nicht das automatische Speichermanagement für die
Rollback Segmente genutzt, sollte explizit mehr Rollback Segment zugeordnet
werden.
UNDO BLOCK ist die Wartesituation auf einen Block in einem
Rollbacksegment. Wir nicht das automatische Speichermanagement für die
Rollback Segmente genutzt, sollte explizit mehr Rollback Segment zugeordnet
werden.
6.24. Free buffer waits
Wenn der Server Prozess keinen Platz im Speicher findet, um Daten zu
plazieren, wird der DataBaseWriter aufgefordert den Speicher freizuräumen
durch schreiben aller „dirty blocks“ auf das Device. Erst wenn der
Schreibvorgang abgeschlossen ist, werden die „dirty blocks“ aus dem
Speicher gelöscht und stehen der Anfrage des Server Prozess bereit.
Der DBWR ist langsam wenn:
Das Device zu langsam ist und die Schreibzugriffe nicht schnell genug
leisten kann
Der Buffer Cache zu klein inst und der DBWR nur mit dem schreiben von
„dirty blocks“ beschäftigt ist. Diese Situation wäre aber auch an einem zu
geringen „Buffer cache hit ratio“ Wert zu erkennen.
Der Buffer Cache zu groß ist und ein einziger DBWR Prozess nicht schnell
genug schreiben von „dirty blocks“ bewerkstelligen kann.
6.25. Latch free
Wenn der Server Prozess nicht direkt eine Speichersperre bewirken kann,
sondern eine wiederholte Latch Anforderung starten muß, entsteht ein Wait.
Mit hilfe eines Staments können solche Waits ausfindig gemacht werden:
SELECT n.name, SUM(w.p3) Sleeps
FROM V$SESSION_WAIT w, V$LATCHNAME n
WHERE w.event = 'latch free'
AND w.p2 = n.latch#
GROUP BY n.name;
______________________________________________________________________________________________________
Oracle Trace
Seite 107
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
6.26. Log buffer space
Dieser Wartezustand entsteht, wenn der Serverprozess auf freien Speicher im
Log Buffer wartet. Wenn der LOG BUFFER schneller beschrieben wird, als
der LGWR diese Information auf das Device schreiben kann.
Wenn für das I/O kein bottleneck besteht, hilft nur das Vergrößern des LOG
BUFFER
6.27. Log file switch
Wenn der LGWR nicht schnell genug auf das
umzuschalten kann, entsteht dieser Event. Dies
Anzahl LogWriter Prozesse nicht ausreicht das
bearbeiten. Aber auch zu kleine REDO LOG
Wartezuständen.
nächste Online Redo Log
ist z.B. der Fall, wenn die
Datenvolumen zeitnah zu
FILES führen zu solchen
6.28. Parse
Jedes Statement, das in irgendeiner Form zur Ausführung gelagen soll, muß
geparst werden. Der Parser trifft alle Vorbereitungen (ähnlich dem Parser
einer Programmiersprache), um das Statement ausführen zu können.
In den Fällen, in denen die Parse Zeiten wesentlich mehr Zeit in Anspruch
nehmen als die eigentliche Verarbeitung, kann die Möglichkeit des Pinnens in
Anspruch genommen werden. Es können Packages, Proceduren oder
Funktionen und einzelne Cursor gepinnt werden. Gerade dann, wenn die CPU
Leistung relativ gering ausfällt. Aber pinnen darf nur kontrolliert erfolgen, weil
jedes gepinnte Element den Speicher der SGA dauerhaft belegt.
Statements sollten Bindevariablen beinhalten, weil sonst jedes Statement in
der SGA gespeichert wird; auch wenn sich die Statements nur für eine Zahl in
der WHERE Klausel unterscheiden!
Im folgenden Beispiel erfolgt die wiederholte Ausführung des selben
Statements.
SQL > select * from ADR_ANREDE;
1 Firma
2 Frau
3 Herrn
SQL > /
1 Firma
2 Frau
3 Herrn
SQL > /
1 Firma
______________________________________________________________________________________________________
Oracle Trace
Seite 108
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
2 Frau
3 Herrn
SQL >
******************************************************************************
select *
from
ADR_ANREDE
call
count
------- -----Parse
3
Execute
3
Fetch
6
------- -----total
12
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.01
0.02
0
0
0
0.00
0.00
0
0
0
0.00
0.06
2
6
12
-------- ---------- ---------- ---------- ---------0.01
0.08
2
6
12
rows
---------0
0
9
---------9
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT
GOAL: CHOOSE
3
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
Die ausgewiesene Anzahl Parsings besteht aus einem vollständigen Parse
und zwei parsings, die der Wiedererkennung vorzeitig beendet wurden.
Das gleiche Statement mit veränderter Schreibweise benötigt keinen Disk
Zugriff. Somit konnten die Daten aus dem DB_BUFFER bezogen werden,
obwohl eine andere Schreibweise für das gleiche Statement vorlag!
******************************************************************************
select *
from
adr_anrede
call
count
------- -----Parse
1
Execute
2
Fetch
4
------- -----total
7
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
0.01
0.02
0
4
8
-------- ---------- ---------- ---------- ---------0.01
0.03
0
4
8
rows
---------0
0
8
---------8
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
4
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
______________________________________________________________________________________________________
Oracle Trace
Seite 109
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Es wird deutlich, daß Oracle das Statement noch einmal aufbereitet
(Typisches Beispiel ist, daß IN Angaben in eine OR Kette ersetzt). Damit liegt
eine Wiedererkennung vor für das Statement, das schon im LIBRARY CACHE
vorhanden und einem EXPLAIN PLAN zuordnet ist, auch mit veränderter
Schreibweise. Hiermit ist erklärbar, warum keine Disk I/O entstand und die
Daten für dieses Statement aus dem DB_BUFFER entnommen wurden!
Deutlicher ist die Aufbereitung eines Statements durch Oracle zu erkennen in
einem PL/SQL Block.
Der nachfolgende Block wird zweimal ausgeführt und unterscheidet sich nur in
der Schreibweise.
begin
for i in( select anrede from adr_anrede )loop
insert into dual values ( '1' );
end loop;
end;
Der zugehörige TKProf Auszug zeigt, daß
******************************************************************************
SELECT ANREDE
FROM
ADR_ANREDE
call
count
------- -----Parse
2
Execute
2
Fetch
6
------- -----total
10
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.07
0
6
0
0.00
0.00
0
0
0
0.00
0.04
2
6
9
-------- ---------- ---------- ---------- ---------0.00
0.11
2
12
9
rows
---------0
0
6
---------6
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
(recursive depth: 1)
Rows
------0
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
begin
for i in( select anrede from adr_anrede )loop
insert into dual values ( '1' );
end loop;
end;
call
count
cpu
elapsed
disk
query
current
rows
______________________________________________________________________________________________________
Oracle Trace
Seite 110
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
-------- ---------- ---------- ---------- ---------0.01
0.00
0
0
0
0.00
0.01
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.01
0.01
0
0
0
---------0
1
0
---------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
******************************************************************************
INSERT INTO DUAL
VALUES
( '1' )
call
count
------- -----Parse
2
Execute
6
Fetch
0
------- -----total
8
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
2
13
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
2
13
rows
---------0
6
0
---------6
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
(recursive depth: 1)
Rows
------0
Execution Plan
--------------------------------------------------INSERT STATEMENT
GOAL: CHOOSE
******************************************************************************
begin
for i in( SELECT anrede FROM adr_anrede )loop
INSERT into dual values ( '1' );
end loop;
end;
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.01
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
rows
---------0
1
0
---------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
******************************************************************************
Das INSERT und auch das SELECT Statements wurde in Großbuchstaben
innerhalb des Cursors ausgeführt und dadurch wiedererkannt.
Für das SELECT Statement wurde die Anzahl Rows im Ausführungsplan aus
der Tabelle ADR_ADRESSEN mit einer Row ausgewiesen. Das ist für den
FULL TABLE ACCESS auf die Tabelle mit drei Datensätzen falsch. Das zeigt
auch der TKProf für die Ausführung des Statements direkt in SQL*Plus.
******************************************************************************
______________________________________________________________________________________________________
Oracle Trace
Seite 111
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
SELECT ANREDE
from
ADR_ANREDE
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
2
4
-------- ---------- ---------- ---------- ---------0.00
0.00
0
2
4
rows
---------0
0
3
---------3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
3
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: CHOOSE
TABLE ACCESS
GOAL: ANALYZED (FULL) OF 'ADR_ANREDE'
******************************************************************************
Das folgende Problem zeigt, das Statements innerhalb eines Cursores
(LOOP) nur einmalig geparsed werden!!
******************************************************************************
declare
v_count
NUMBER( 7 );
begin
SELECT count(*) INTO v_count FROM adr_anrede;
SELECT count(*) INTO v_count FROM adr_anrede;
SELECT count(*) INTO v_count FROM adr_anrede;
for cur_rec in( SELECT plz_id FROM adr_adressen where rownum < 11 )loop
SELECT count(*) INTO v_count FROM adr_anrede;
end loop;
end;
call
count
------- -----Parse
1
Execute
1
Fetch
0
------- -----total
2
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.02
0.02
0
0
0
0.01
0.00
0
0
0
0.00
0.00
0
0
0
-------- ---------- ---------- ---------- ---------0.03
0.02
0
0
0
rows
---------0
1
0
---------1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (LAMBERTZ)
******************************************************************************
SELECT COUNT(*)
FROM
ADR_ANREDE
call
count
------- -----Parse
4
Execute
13
Fetch
13
------- ------
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.03
0.08
0
0
0
0.00
0.00
0
0
0
0.00
0.04
2
13
52
-------- ---------- ---------- ---------- ----------
rows
---------0
0
13
----------
______________________________________________________________________________________________________
Oracle Trace
Seite 112
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
total
30
0.03
0.12
2
13
52
13
******************************************************************************
SELECT PLZ_ID
FROM
ADR_ADRESSEN
call
count
------- -----Parse
1
Execute
1
Fetch
11
------- -----total
13
WHERE ROWNUM < 11
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.01
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
10
4
-------- ---------- ---------- ---------- ---------0.00
0.01
0
10
4
rows
---------0
0
10
---------10
******************************************************************************
6.28.1. Hard Parse
1. Sind alle Signalworte wie FROM oder ORDER BY in der richtigen
Reihenfolge? Fehlen Signalworte oder sind welche zu viel ? Im Fehlerfall
wird der Parse Vorgang abgebrochen und eine Meldung ausgegeben. Z.
B. führt SELECT FROM TABELLE zu einem Fehler, weil kein Attribut aus
der Tabelle angegeben wurde, das selektiert werden soll.
2. Kontrollieren, ob dieses Statement bereits in der SQL Area vorliegt und
nicht als invalid markiert ist. Verglichen wird der eigentliche Statement
Text bis auf das letzte Bit. Zu beachten ist die Groß- und Kleinschreibung,
Spaces und Zeilenumbrüche. Wenn also ein exakt gleiches Statement
gefunden wurde, wird der Parse Vorgang mit dem Bearbeiten von
Bindevariablen beendet und das Statement ausgeführt. Alle erforderlichen
Informationen werden dann aus dem Shared Memory Pool gelesen. Wird
das Statement nicht in der SQL Area gefunden, handelt es sich um ein
neues Statement und wird in die SQL Area übernommen.
3. Optimieren des Statements. Z.B. wird:
SELECT *
FROM DEPT
WHERE DEPT.EMPNO IN (SELECT EMPNO FROM EMP)
optimiert in
SELECT DEPT.*
FROM DEPT, EMP
WHERE DEPT.EMPNO = EMP.EMPNO
______________________________________________________________________________________________________
Oracle Trace
Seite 113
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Hier ein weiteres Beispiel, wie IN(...) in eine OR Kette zerlegt wird.
Deutlich in der Trace Datei:
******************************************************************************
select /*+ RULE */ *
from
adr_plz where id=10 or id=11 or id=12
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
9
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
9
0
rows
---------0
0
3
---------3
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
3
1
2
1
2
1
2
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
CONCATENATION
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
select /*+ RULE */ *
from
adr_plz where id IN( 10,11,12)
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
9
0
-------- ---------- ---------- ---------- ---------0.00
0.00
0
9
0
rows
---------0
0
3
---------3
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 21 (LAMBERTZ)
Rows
------0
3
1
2
1
Execution Plan
--------------------------------------------------SELECT STATEMENT
GOAL: HINT: RULE
CONCATENATION
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
______________________________________________________________________________________________________
Oracle Trace
Seite 114
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
2
1
2
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
TABLE ACCESS
GOAL: ANALYZED (BY INDEX ROWID) OF 'ADR_PLZ'
INDEX
GOAL: ANALYZED (UNIQUE SCAN) OF 'ADR_PLZ_ID_IDX'
(UNIQUE)
******************************************************************************
Für beide Statements wird der gleiche Explain Plan ausgewiesen.
5. Einfügen des Object Owner in die SQL Anweisung, wenn erforderlich. So
wird aus:
SELECT *
FROM DEPT
das Statement wie folgt:
SELECT *
FROM SCOTT.DEPT
6. Existiert die angegebene Tabelle im Data Dictionary und liegen
Zugriffsrechte für den Gebrauch dieser Tabelle, Modul oder Synonym
vor?
7. Festlegen der Zugriffsart, wenn die Datenbank im CHOOSE Modus
arbeitet. Die CHOOSE Einstellung hat einen COST-basierten Zugriff zur
Folge, wenn Analyse Ergebnisse für die Tabellen vorliegen. Ist dies nicht
der Fall, wird der RULE (regelbasierte) Zugriff gewählt.
8. Wurde die CHOOSE gewählt, erfolgt eine Überprüfung aller beteiligter
Objekte, ob für wenigstens eins dieser Objekte Statistikwerte vorliegen. Ist
dies der Fall, optimiert Oracle das Statement entsprechend der Regeln,
wie sie z.B. für einen RULE Zugriff beachtet werden müssen.
9. Erstellen des Ausführungsplans für dieses Statement, dem EXPLAIN
PLAN und speichern im Shared Memory Pool (mit dem EXPLAIN PLAN
wird festgelegt mit welcher Vorgehensweise der Zugriff auf die Daten
erfolgen wird. Für UPDATE, DELETE oder SELECT wichtig. Der
EXPLAIN PLAN enthält, welche Indizes genutzt, welche Tabellen im FULL
TABLE ACCESS gelesen werden und welche der gejointen Tabellen die
Führungstabelle sein wird). Der EXPLAIN PLAN wird dem ursprünglichen
Statement zugeordnet und im Shared Memory Pool verwaltet.
10. Bearbeiten der Bindevariablen
11. Ausführen des Statement in der Datenbank.
12. Liefern der Ergebnismenge
______________________________________________________________________________________________________
Oracle Trace
Seite 115
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
[email protected]
Hard Parsings sollten immer in einem guten Verhältnus zueinander stehen,
nämlich zu einer erhöhten Anzahl „execute count“ möglichst wenig „parse
count“. Mit folgendem Statement kann Klarheit geschaffen werden:
SELECT
FROM
WHERE
AND
AND
ORDER
ss.sid, sn.name, ss.value
V$SESSTAT ss, V$STATNAME sn
name IN ('parse count (hard)','execute count')
ss.statistic# = sn.statistic#
ss.value > 0
BY sid, value
6.28.2. Soft Parse
Ein Softparse steht in direkter Verbindung mit einem RELOAD. Wenn
nämlich der LIBRARY CACHE nicht ausreicht, erfolgt ein „aged out“. D.h.,
daß die Infomationen (z.B. Explain plan, Zugriffsrechte, usw.) aus dem
LIBRARY CACHE entfernt werden und ausschließlich das Statement in der
SQL Area gehalten wird. Erfolgt nun die wiederholte Anforderung, das
Statement auszuführen, ist ein „soft parse“ Vorgang erforderlich. D.h., nur
die Syntax ist geprüft und alles weitere muß erneut bestimmt werden. Der
Einstieg in den Parse Prozess erfolgt an Position 2 der Hard Parse
Beschreibung.
Reloads können ermittelt werden mit folgendem Statement:
SQL >
SELECT SQL_TEXT,
LOADS,
FIRST_LOAD_TIME,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS,
ROWS_PROCESSED,
OPTIMIZER_MODE
FROM V$SQLAREA
WHERE LOADS > 1
ORDER BY first_load_time asc,
buffer_gets desc,
disk_reads desc;
______________________________________________________________________________________________________
Oracle Trace
Seite 116
CL
ambertz
onsulting
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
eMail: [email protected]
7.
Index
A
H
AUTOTRACE ............................................. 10, 63
HIGH WATER MARK.......................................93
C
P
CHOOSE......................................................... 104
Count ................................................................ 23
CPU ................................................................... 23
Current ............................................................. 24
CURRENT ........................................................ 91
Parse..................................................................20
PLAN_TABLE....................................................63
PLUSTRACE ........................................................9
D
Disk ................................................................... 23
DISTINCT_KEYS....................................... 94, 95
Q
Query .................................................................23
R
Rows ..................................................................25
E
S
Elapsed............................................................. 23
Execute............................................................. 20
SGA ............................................................91, 105
Shared Pool .....................................................105
SHARED_POOL_SIZE ..................................105
F
Fetch ................................................................. 23
______________________________________________________________________________________________________
Oracle Trace
Seite 117