Tipps und Tricks

1
Oracle Optimierung Tuning

• Tipps und Tricks

2
Themen

• Einführung
• Tools sqlplus tkprof
• Datenbank Parameter
• Beispiel Session SQLPLUS
• Beispiel TKPROF
• Explain plan
• Identifikation von Langläufern
• Buffer_gets
• Disk_reads
• Aktive sessions
• Vlongops
• die SQL-Optimizer
• Rule based
• Costbased
• Funtionsweise des optimizers

3
Einführung
Erfahrungen haben gezeigt, dass bei Optimierung
und Tuning Maßnahmen im Oracle Umfeld die
Potentiale zu 80 auf der Applikationsseite und
20 auf der Systemseite liegen. Das zentrale
Problem liegt in der Identifikation der
relevanten SQL-Statements. Üblicherweise belasten
wenige Prozent der Anfragen die Datenbank bis zu
90.
4
Analyse in der Praxis Datenbank Parameter
Notwendige Voraussetzungen die Konfigurationsparam
ter im init/spfile.ora können folgende Werte
annehmen sql_tracetrue Deaktiviert oder
aktiviert die SQL-Trace-Funktion. Bei TRUE
werden Optimierungsinformationen erfasst, mit
denen die Leistung verbessrt werden kann. Weil
die SQL-Trace-Funktion einen System-Overhead
verursacht, sollte TRUE nur benutzt werden,
wenn Optimierungsinformationen erforderlich sind.
Wertebereich TRUE FALSE Standardwert
FALSE time_statisticstrue Erfasst
Betriebssystem-Timing-Informationen, mit denen
die Datenbank und SQL-Anweisungen optimiert
werden können. Um zu verhindern, dass die Zeit
vom Betriebssystem angefordert werden muss, legen
Sie diesen Wert auf Null fest. Ein Wert von TRUE
kann nützlich sein, um den Fortschritt von lange
andauernden Vorgängen anzuzeigen. Wertebereich
TRUE FALSE Standardwert FALSE trace_enabled
true Der Oracle Server schreibt Fehlermeldungen
der Oracle Prozesse in diverse Diagnosefiles.
Dieses Flag sollte immer eingeschaltet
sein. Wertebereich TRUE FALSE Standardwert
TRUE tracefile_identfier Festlegen eines
Kürzels für die TraceAusgabe auf dem Server. Kein
Standardwert background_dump_dest,user_dump_des
t,max_dump_file_size Festlegen der Verzeichnisse
auf dem Server, maximale Größe der traceFiles
5
Analyse Tools in der Praxis Ausführungsplan und
Zeitermittlung
Beispiel Session mit SQL-PLUS SQLgt set autotrace
on SQLgt set timing on SQLgt select count() from
dual COUNT()
----------

1


Abgelaufen 000005.08 Ausführungsplan -------
--------------------------------------------------
- 0 SELECT
STATEMENT OptimizerCHOOSE
1 0 SORT (AGGREGATE)
2
1 TABLE ACCESS (FULL) OF 'DUAL'

Statistiken --------------
0 recursive calls
0 db
block gets
3 consistent gets

0 physical reads
0 redo size

212 bytes sent via SQLNet to
client
271 bytes received via SQLNet from client
2 SQLNet
roundtrips to/from client
0 sorts (memory)

0 sorts (disk)
1 rows processed


6
Analyse Tools in der Praxis tkprof
Beispiel Session declare icount
number begin -- -- vorbereitungen -- execute
immediate 'alter session set tracefile_identifier
mytrc' dbms_application_info.set_action(act
ion_name gt 'start mytrace' )
dbms_session.set_sql_trace (sql_trace gt true)
-- -- start der applikation Package,oderSQL --
select count() into icount from
user_tables a,user_tab_columns b where
a.table_nameb.table_name and a.table_name
like 'B' - -- aufräumen --
dbms_session.set_sql_trace (sql_trace gt
false) dbms_application_info.set_action(action
_name gt '' ) end /
7
Analyse Tools in der Praxis tkprof - auswerten
Beispiel Session Auf dem Datenbank Server wurde
ein Tracefile für diese Session im
udump-Verzeichnis angelegt inst3_ora_16684_MYTRC.t
rc
tkprof inst3_ora_16684_MYTRC.trc
inst3_ora_16684_MYTRC.txt explainusr/pwd_at_inst3
sortfchela
Die von der Datenbank geschriebenen Trace Dateien
sind nur schwer lesbar, deshalb müssen diese mit
dem Programm tkprof auf eine lesbare Form
gebracht werden. Übliche Sortierungen haben sich
bewährt Sortierung nach Ausführungszeit Sortprs
ela,exeela,fchela Sortierung nach Anzahl
Aufrufe Sortprscnt,execnt,fchcnt
8
Identifikation von Langläufern über disk_reads
Annahmen Der Datenbankserver kann 300 Disk/IOs
pro Sekunde ausführen
select executions, to_number(to_char((disk_re
ads /executions) /300,'9999')) Antwortzeit_in_s,
sql_text from vsql where (disk_reads
/executions) /300 gt10 and executions gt 0 order
by 2 desc
9
Identifikation von Langläufern über buffer_gets
Annahmen Der Datenbankserver kann 4000 buffer
gets pro Sekunde ausführen
select executions, to_number(to_char((buffer_
gets /executions) /4000,'9999'))
Antwortzeit_in_s, sql_text from vsql where
(buffer_gets /executions) /4000 gt10 and
executions gt 0 order by 2 desc
10
Identifikation von Langläufern über
vsession_longops
SELECT start_time, sofar
Anzahl_Einheiten_getan, totalwork
Einheiten_zu_tun, units Einheit,
elapsed_seconds, sql_text FROM
vsql,vsession_longops WHERE sql_address
address AND sql_hash_value hash_value AND
start_time gt SYSDATE -10 -- AND sofar ltgt
totalwork ORDER BY start_time DESC
Anmerkungen Das Verhältnis von sofar/totalwork
ergibt den Staz der Verarbeitung des sql
Statements
11
Ermittlung aktiver sessions
SELECT s.status "Status", s.serial
"Serial", s.TYPE "Type", s.username "DB
User", s.osuser "Client User", s.server
"Server", s.machine "Machine", s.module
"Module", s.client_info "Client Info",
s.terminal "Terminal", s.program "Program",
p.program "O.S. Program", s.logon_time
"Connect Time", lockwait "Lock Wait",
si.physical_reads "Physical Reads", si.block_gets
"Block Gets", si.consistent_gets
"Consistent Gets", si.block_changes
"Block Changes", si.consistent_changes
"Consistent Changes", s.process "Process",
p.spid, p.pid, si.SID, s.audsid, s.sql_address
"Address", s.sql_hash_value "Sql Hash",
s.action, SYSDATE - (s.last_call_et /
86400) "Last Call" FROM vsession s,
vprocess p, SYS.v_sess_io si WHERE s.paddr
p.addr() AND si.SID() s.SID AND
(s.username IS NOT NULL) AND (NVL (s.osuser,
'x') ltgt 'SYSTEM') AND (s.TYPE ltgt
'BACKGROUND') ORDER BY 1
12
Die SQL Optimizer

• Jedesmal wenn eine SQL-Anweisung durchgeführt
  wird, muß die Datenbank entscheiden , wie die
  jeweilige Anweisung optimal durchgeführt
• wird. Es gibt in Oracle verschiedene
  Möglichkeiten den Optimizer Modus einzustellen.
  Dazu gibt es in init.ora folgenden Parameter
• optimizer_mode
• Gibt das Verhalten des Optimizers an. Wenn dieser
  Parameter auf RULE eingestellt ist, wird der
  regelbasierte Optimizer benutzt, es sei
• denn, die Abfrage enthält Hinweise.(Hints) Wenn
  dieser Parameter auf CHOOSE eingestellt ist, wird
  der statistikorientierte Optimizer
• benutzt, es sei denn, Tabellen in der Anweisung
  enthalten keine Statistiken. Bei ALL_ROWS oder
  FIRST_ROWS wird immer der
• statistikorientierte Optimizer verwendet.
• Wertebereich RULE CHOOSE FIRST_ROWS
  ALL_ROWS
• Standardwert CHOOSE

Rule oder Choose ? Eindeutige Präferenz für
Choose, weil dieser Optimizer Mode effizienter
ist. Der regelbasierte arbeitet nach Schema F
und berücksichtigt keine Datenverteilungen Die
Zukunft gehört dem kosten basierenden optimizer.
Weiterentwicklung des RBO ist eingestellt Es gibt
viele Features für den CBO. Z.B.
funktionsbasierter Index Das Risiko beim
Hinzufügen neuer Indizes ist deutlich geringer
Vorgehensweise des kostenbasierten Optimizers
Parsen der SQL-Anweisung Erzeugen einer Liste
aller potentiellen Ausführungspläne Berechnen,
schätzen der Kosten jedes Ausführungsplanes unter
Verwendung aller Objekt-Statistiken Auswählen
des Ausführungsplanes mit den niedrigsten
Kosten
13
Die SQL Optimizer
Hinweise Standardmäßig wird der kostenbasierte
Optimizer immer dann verwendet, wenn Statistiken
für die entsprechenden Objekte existieren. Falls
nicht für alle verwendeten Objekte eine Statistik
existiert , so werden diese Objekte im RDBMS
geschätzt. Falls überhaupt keine Statistik
existiert wird der regelbasierte Optimizer
verwendet. es sei denn der Modus wird über Hints
definiert. Der CBO kann die Ausführungspläne
nicht optimal bestimmen , wenn eine Tabelle
analysiert wurde, aber nicht deren Indizes. Der
Einsatz von veralten Statistiken kann schädlich
für die Performance sein. Im Feld last_analyzed
steht das letzte Analyse Datum Das Analysieren
großer Tabellen, kann bei der Option compute sehr
lange dauern. Abhilfe über estimate 3 Der
Befehl truncate löscht keine Statistiken.

14
Optimierung Tuningbeispiele Warum werden
vorhandene Indizes nicht verwendet ??
select ac_name,ac_date,ac_wert from
action where
substr(ac_name,1,7) 'TRANS'
select ac_name,ac_date,ac_wert from
action where ac_name like
'TRANS'
Die Funktion substr deaktiviert den Index da sie
auf eine indexierte Spalte angewendet wird
select ac_name,ac_date,ac_wert from
action where ac_wert gt0
select ac_name,ac_date,ac_wert from
action where ac_wert ! 0
Alle Referenzen von not,!,ltgt deaktivieren Indizes
15
Optimierung Tuningbeispiele Warum werden
vorhandene Indizes nicht verwendet ??
select ac_name,ac_date,ac_wert from
action where ac_name ac_typ
TRANSX
select ac_name,ac_date,ac_wert from
action where ac_name TRANS
and ac_typ X
Die Funktion concatenate deaktiviert den Index
da sie auf eine indexierte Spalte angewendet wird
select ac_name,ac_date,ac_wert from
action where ac_wert lt 300
select ac_name,ac_date,ac_wert from
action where ac_wert 200
lt500
Alle aritmetischen Operationen ,-,/
deaktivieren Indizes
16
Optimierung Tuningbeispiele Warum werden
vorhandene Indizes nicht verwendet ??
select count() from
action where upper(ac_name) 'HUGO'
Die Funktion upper deaktiviert normalerweise den
Index da sie auf eine indexierte Spalte
angewendet wird
Abhilfe Erstelle einen funktionsbasierten Index
create index action_idx on action
(upper(ac_name)) Setze Parameter Query_rewrite_e
nabled in init.ora auf TRUE setzen Analysiere
Tabelle action mit dbms_stats
17
Optimierung Tuningbeispiele Warum werden
vorhandene Indizes nicht verwendet ??
ac_typ char(1) select
ac_name,ac_date,ac_wert from
action where ac_typ 1
select ac_name,ac_date,ac_wert from
action where ac_typ 1
Da ac_typ ein char Wert und die Konstante ein
numerischer Wert , wandelt Oracle den char in
einen numerischen Wert um. Folge davon ist ein
full table scan auf die Tabelle
18
Optimierung Tuningbeispiele Fehlende order
ungeeignete Indizes
Mit Hilfe eines optimalen Index , kann die
Performance erheblich verbessert werden. Ein
Problem mit dem Index kann es geben , wenn der
Index z.B. mehr als 15 einer Tabelle als
Ergebnis zurückliefert. Dann ist oft ein full
table scan schneller.
index
daten
101,Müller 201,Mayer 301,Huber
301,Huber.. 101,Müller,. 401,Gruber. 201,Mayer
301,Huber..
Ein ungeeigneter Index ist, wenn zu jedem Index
Zugriff noch ein physikalischer Datenzugriff
erfolgt. siehe Beispiel oben Die Index Suche
wird extrem wirksam, wenn alle Abfragespalten im
index repräsentiert sind. Kleinere Tabellen
sollten indexiert werden, weil die Eindeutigkeit
über den Primär oder Unique key gewährleistet ist
und der Optimizer einen besseren Ausführungsplan
erstellen kann.
19
Optimierung Tuningbeispiele Indexierung
einer Spalte Mehrspalten Index
select count() from
action where ac_typ 1 and
ac_wert gt 100
Index1 (ac_typ) Index2 (ac_wert)
Jeder Index Eintrag wird für jeden Wert gelesen
IndexGemeinsam (ac_typ,ac_wert)
Nur eimaliges Lesen des Indexes, wesentlich
performanter
20
Optimierung Tuningbeispiele Nested Loop
sort-merge hash-join im Execution Plan
Nested loop / use_nl /
Sind gut für Online-Transaktionssysteme, Daten
werden schnellst möglichst zurückgeliefert Sollte
n benutzt werden wenn weniger als 10 der Zeilen
zurückkommen
Hash join / use_hash /
Das Gesamtergebnis wird bestimmt , bevor eine
einzige Zeile an den Benutzer zurückgeliefert
wird Es wird eine hash Tabelle im Speicher
aufgebaut die auf der kleinsten Tabelle basiert,
danach wird die andere Tabelle verarbeitet
Sort merge / use_merge /
Ein merge Join ist dann schneller , wenn alle
Spalten in der where Klausel durch einen
Index vorsortiert werden.
21
Optimierung Tuningbeispiele Was ist
schneller in oder exists ?
Wann sollte man die Konstrukte Not in, not exists
, in , exists verwenden ? Unter Umständen ist ein
ein not/in schneller als ein not/exists Es
kommt auf die Bedingungen an. In den meisten
Fällen ist es besser Exists statt in zu
verwenden. Folgende Beispiele sind Ausnahmen wo
exists eine schlechtere Performance hat.
select .. from emp e where exists (select
x from dept d where
e.deptnod.deptno and d.cat AUS)
select from dept d,emp e where
e.deptnod.deptno and d.cat AUS
Ein Join ist schneller als eine Unterabfrage
delete from emp e where exists (select x from
dept d where
e.deptnod.deptno and d.cat AUS)
delete from emp where (deptno,id) in ( select
deptno,id from temp )
Das in Konstrukt is hier nur schneller wenn die
emp sehr groß und die temp Tabelle sehr klein ist
! z.B. emp hat mehere Millionen und temp hat
wenige tausend Zeilen.
delete from emp e where exists (select /
HASH_SJ / x from dept d where
e.deptnod.deptno and d.cat AUS)
Um die Ausführung von exists zu beschleunigen
kann man die Hints hash_sj und merge_sj
verwenden.
22
Optimierung Tuningbeispiele Unnötige
Sortiervorgänge
Die folgenden Operationen benötigen eine
Sortierung Create index Distinct Group
by Order by Intersect Minus Unions Unindexed
table joins Sortiervorgang kann verbessert
werden Sort_area_size erhöhen , initial und next
für tablespace temp um ein vielfaches von
sort_area_size zuordnen.
select ac_name,ac_date,ac_wert from
action where ac_typ
1 union select ac_name,ac_date,ac_wert
from action2 where ac_typ
X
select ac_name,ac_date,ac_wert from
action where ac_typ 1 union
all select ac_name,ac_date,ac_wert from
action2 where ac_typ X
Verwenden von union all statt union Duplikate
werden nicht entfernt !!!
23
Optimierung Tuningbeispiele Unnötige
Sortiervorgänge
Verwenden eines Indexes, um eine Sortierung zu
vermeiden. Index auf ac_name und ac_date
select ac_name,ac_date,ac_wert from
action order by ac_name
select ac_name,ac_date,ac_wert from
action where ac_name gt chr(1)
Die Dummy where Klausel erzwingt den Index
Zugriff, so daß die Zeilen sortiert
zurückgeliefert werden
24
Optimierung Tuningbeispiele Zuviele Indices
Bei Aktualisierungen und Batch-Prozessen können
sich viele Indices auf die Performance negativ
auswirken.
Anahl der Inserts Anzahl Indices Laufzeit in Sekunden
256 0 1,101
512 0 1,161
256 5 3,936
256 10 12,558
512 10 22,132
25
Optimierung Tuningbeispiele aktuelles Projekt
Beispiel
insert into nehmerkredite (
periode, sv_id, paragraph, kredite_id, gebernr,
gebernr_pz, schuldnernr, schuldnernr_pz,
POS_110_gk) select periode, sv_id,
'14', id, gebernr, gebernr_pz,
schuldnernr_tk, schuldnernr_tk_pz, POS_110_GK
from kredite k1 where
k1.periode '200403' and k1.sv_id
1 and (k1.schuldnernr_tk gt
'0999999' or k1.schuldnernr_tk lt '0100000')
and satzart '1' and
paragraph '14' and rowid
(select min(rowid)
from kredite k2 where k2.periode
'200403' and k2.sv_id
1 and k2.gebernr
k1.gebernr and
k2.schuldnernr_tk k1.schuldnernr_tk
and satzart '1'
and paragraph '14')
insert / append / into nehmerkredite
( periode, sv_id, paragraph,
kredite_id, gebernr, gebernr_pz, schuldnernr,
schuldnernr_pz, POS_110_gk)
select periode, sv_id, paragraph, min(id) ,
gebernr, gebernr_pz, schuldnernr_tk,
schuldnernr_tk_pz, POS_110_GK from
kredite k1 where k1.periode
'200403' and k1.sv_id 1
and (k1.schuldnernr_tk gt '0999999' or
k1.schuldnernr_tk lt '0100000')
and satzart '1' and paragraph
'14' group by periode, sv_id,
paragraph, gebernr, gebernr_pz,
schuldnernr_tk, schuldnernr_tk_pz, POS_110_GK

421-gt48 Sekunden, faktor 10 schneller
26
Zusammenfassung - Ausblick
Analyse der Datenbank Zugriffe
Optimierung Tracen ausgewählter Sitzungen und
kritischer Pfade Analysen des Execution
Pläne Beachtung der Optimierungsregeln geeignete
Indizes verwenden evtl umschreiben des
SQL-Codes Zeitmessungen über sqlplus,tkprof,explai
n plan Verwendung von BIND Variablen
Vermeidung von wiederholtem parsen evtl Cursor
Sharing in den initOra Parameter auf similar
anpassen Große Arrays wenn möglich über nocopy
referenzieren String Verarbeitung , zu große
Arrays vermeiden evtl compilieren des
sqlCodes Cachen von häufigen Zugriffen innerhalb
von pl-sql
Parallelisierung Falls möglich Anfragen und Jobs
parallel verarbeiten über dbms_jobs oder eigenes
Framework asynchron/synchron Schon in der
Architektur und Design Phase der
Applikation/Datenmodell beachten Skalierbarkeit
wird verbessert. Sehr gute Erfahrungen im Projekt
Bankenaufsicht
27
Zusammenfassung - Ausblick
Rebuild Index Bei häufigen deletes,inserts,update
s ist es notwendig die Indexe zu reorganisieren
Partitionierung bei großen Datenmengen wird
dringend empfohlen Oracles Partitioning Option
zu verwenden Range Partition List Partition
oder kombiniert Größter Vorteil sind local
partition Indexe Das Antwort Zeitverhalten bleibt
konstant und steigt nicht mit der
Datenmenge Rebuild und Statistik über
subPartitions möglich
Statistik Absolut wichtig sind Tabellen Index
Statistiken beim kostenbasierten Optimizer Mit
der Option for all indexed columns wir die
IndexStatistik automatisch mitgemacht Ab Oracle 9
Package dbms_stats verwenden nicht analyze
verwenden
28
Beispiel indexRebuild und
Statistik bei einer einfachen Tabelle
Hat die Tabelle und der Index Statistik Daten
? select owner,table_name,last_analyzed from
dba_tables where table_name'C_BRANCHEN' and
owneruser select owner,table_name,index_name,last
_analyzed from dba_indexes where
table_name'C_BRANCHEN' and owneruser
Index rebuild Generierung der Statistik begin
execute immediate 'alter index
HABL.C_BRANCHEN_PK
rebuild parallel nologging compute
statistics tablespace HABL_INDEX_S_01'
dbms_session.set_nls('NLS_NUMERIC_CHARACTERS',
'''.,''') -- nur notwendig weil BUG in
dbms_stats Package dbms_session.set_nls('N
LS_TERRITORY', '''america''') -- nur
notwendig weil BUG in dbms_stats Package
dbms_stats.gather_table_stats(ownname gt
'HABL',
tabname gt
'C_BRANCHEN',
cascade gt TRUE,
degree
gt DBMS_STATS.DEFAULT_DEGREE
,
method_opt gt 'FOR ALL INDEXED
COLUMNS',
estimate_percent gt
DBMS_STATS.AUTO_SAMPLE_SIZE) end
Analysiere die Indizes
Grad der Parallelisierung
Löschen der Statistik Daten ? begin
dbms_stats.delete_table_stats(ownname gt
'HABL',
tabname gt 'C_BRANCHEN') end
29
Table/Index Monitoring

• Einschalten des Monitoring
• Alter table c_branchen monitoring
• Alter index idx_br monitoring usage
• Alter index idx_br nomonitoring usage
• Überprüfen was hat sich in den Tabellen/Indexen
  verändert ?
• Select from user_tab_modifications
• Select from vobject_usage
• Schema Statistik ausführen
• begin
• dbms_stats.gather_schema_stats(ownname
  gt 'HABL',
• cascade gt TRUE,
• 
  degree gt
  DBMS_STATS.DEFAULT_DEGREE,
• 
  options gt 'GATHER STALE',

30
Kopieren und Sichern von Statistiken

• Generiere Tabelle zum Speichern der Statistiken
• begin
• sys.dbms_stats.create_stat_table(OWNNAME
  gt'HABL' ,STATTAB gt 'HABL_STAT')
• end
• Erzeugte stat Table füllen
• begin
• sys.dbms_stats.export_schema_stats(OWNNAME
  gt'HABL' ,STATTAB gt 'HABL_STAT' , STATID gt
  'Statistik vom 14.02.2005')
• end
• Tabelle exportieren
• exp user/pwd_at_inst filestats tableshabl_stat
• Tabelle importieren

31
Rückkehr zu gesicherten Statistiken

• Sicherung erzeugen
• begin
• sys.dbms_stats.export_schema_stats(OWNNAME
  gt'HABL' ,STATTAB gt 'HABL_STAT' , STATID gt
  'Statistik vom 14.02.2005')
• end
• Man sammelt neue Statistiken
• begin
• dbms_stats.gather_schema_stats(ownname
  gt 'HABL',
• cascade gt TRUE,
• 
  degree gt
  DBMS_STATS.DEFAULT_DEGREE,
• estimate_percent gt
  DBMS_STATS.AUTO_SAMPLE_SIZE)
• end
• Rückkehr zur alten Statistik
• begin