Performanceanalyse und Tuning von Oracle 10g Release 2
Werbung
Performanceanalyse und Tuning
von
Oracle 10g Release 2
Dr. Günter Unbescheid
Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
Prolog
©Database Consult GmbH - Jachenau
09/2006
Folie 2 von xx
Statement 1
Performanceanalyse und Tuning von Oracle 10g Release 2
Unzureichendperformante
performanteSysteme
Systemesind
sind
Unzureichend
normalund
und
normal
ausdiesem
diesemGrunde
Grundekein
keinGrund
Grundzur
zur
aus
Beunruhigung.
Beunruhigung.
Auf Überraschungen sind wir
vorbereitet. – Nur die
alltäglichen Dinge brechen
über uns herein wie
Katastrophen.
Stanislaw Jercy Lec
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 3 von xx
1
Statement 2
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuningist
istdaher
daherein
ein
Tuning
alltäglichesGeschäft,
Geschäft,
alltägliches
dasssich
sichnoch
nochdazu
dazutäglich
täglichändert!
ändert!
dass
Wenn der Wind des Wandels weht, bauen
die einen Schutzmauern, die anderen
bauen Windmühlen
Chinesische Weisheit
©Database Consult GmbH - Jachenau
09/2006
Folie 4 von xx
Statement 3
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuningist
istkein
keinautomatischer
automatischerProzess,
Prozess,
Tuning
sonderneine
einezielgemässe
zielgemässeund
undbewusste
bewusste
sondern
Planung,Umsetzung
Umsetzungund
undKontrolle
Kontrolle
Planung,
Nur wer sein Ziel kennt,findet den
Weg
Lao-Tse
©Database Consult GmbH - Jachenau
09/2006
Folie 5 von xx
Statement 4
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuningerfordert
erfordert
Tuning
Wissen,Geduld,
Geduld,Neugier,
Neugier,Phantasie,
Phantasie,
Wissen,
Konzentrationauf
aufdas
dasWesentliche
Wesentliche
Konzentration
Wenig gedeiht,
Zuviel zerstreut.
Volksmund
09/2006
Phantasie ist wichtiger als
Wissen,denn Wissen ist
begrenzt.
Albert Einstein
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 6 von xx
2
Statement 5
Performanceanalyse und Tuning von Oracle 10g Release 2
DieKenntnis
Kenntnisder
der Features
Featuresmacht
machtnoch
noch
Die
keinenSommer
Sommer(=performantes
(=performantesSystem).
System).
keinen
Wir leben in einer Zeit vollkommener Mittel
und verworrener Ziele.
Albert Einstein
©Database Consult GmbH - Jachenau
09/2006
Folie 7 von xx
Statement 6
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuningmacht
machtschnell(er)
schnell(er)–– ist
istjedoch
jedoch
Tuning
nichtimmer
immerschnell
schnellund
undeinfach.
einfach.
nicht
„Wer kämpft, kann verlieren. Wer nicht kämpft,
hat schon verloren.“
Bertolt Brecht
©Database Consult GmbH - Jachenau
09/2006
Folie 8 von xx
Auf den Punkt...
Performanceanalyse und Tuning von Oracle 10g Release 2
Wissen
Strategie
Werkzeuge
Features
09/2006
Psychologie
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 9 von xx
3
Resumee
Performanceanalyse und Tuning von Oracle 10g Release 2
[Tuning]ist
istwie
wieRudern
Ruderngegen
gegenStrom.
Strom.
[Tuning]
Sobaldman
manaufhört,
aufhört,treibt
treibtman
manzurück.
zurück.
Sobald
Anlehnungan
anBenjamin
BenjaminBritten
Britten
InInAnlehnung
©Database Consult GmbH - Jachenau
09/2006
Folie 10 von xx
Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Gegründet 1996
• Kompetenzen im Umfeld von ORACLE-basierten Systemen
• Tätigkeitsbereiche
–
–
–
–
–
–
–
–
–
Tuning, Installation, Konfiguration
Support, Troubleshooting, DBA-Aufgaben
Datenmodellierung und –design
Datenbankdesign, Systemanalysen
Programmierung: SQL,PL/SQL,JSP, ADF UIX, BC4J
Client-Server und Webapplikationen
Schulungen
Expertise/Gutachten
www.database-consult.de
©Database Consult GmbH - Jachenau
09/2006
Folie 11 von xx
Agenda
– Vom richtigen Umgang: Management des Datenbank-Managements
– Vom richtigen Einstieg: geeignete Analysestrategien
• Konfiguration und Prophylaxe
– Memory-Techniken: SGA- und PGA-Analysen
– Kennzahlen, Metriken und Alerts – Active Workload Repository
– Performance-Berichte, Log- und Trace-Dateien
• Lastprofile und Analysemethoden
– Tracing-Werkzeuge
– Wait Events und CPU-Profile
Performanceanalyse und Tuning von Oracle 10g Release 2
• Konzeption
• Cost Based Optimizer
– Arbeitsweise, Statistiken und Histogramme
– SQL-Profiles und SQL Tuning Advisor
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 12 von xx
4
Rahmenbedingungen
– Nicht alle Features sind in einem Tag darstellbar
– Auswahl nach Gewichtung und Bekanntheitsgrad
• Darstellung des Beobachteten – keine graue Theorie, weil ...
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Technische Details des aktuellen Relesase-Standes 10.2.0.2
• Tests unter SuSe Linux 9.3
• Konzentration auf das Wesentliche
Folie 13 von xx
...brave new softworld ?!...
Performanceanalyse und Tuning von Oracle 10g Release 2
...The
Thefollowing
following...
...isisnot
notaacommitment
commitmentto
to
...
deliverany
anymaterial,
material,code,
code,or
orfunctionality,
functionality,and
and
deliver
shouldnot
notbe
berelied
reliedupon
uponin
inmaking
makingpurchasing
purchasing
should
decisions.The
Thedevelopment,
development,release,
release,and
and
decisions.
timingof
ofany
anyfeatures
featuresor
orfunctionality
functionalitydescribed
described
timing
forOracle’s
Oracle’sproducts
productsremains
remainsat
atthe
the sole
sole
for
discretionof
ofOracle.
Oracle.
discretion
WhitePaper
PaperProlog
Prolog
White
09/2006
©Database Consult GmbH - Jachenau
Folie 14 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 1: Die Konzeption
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 15 von xx
5
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 1: Die Konzeption
Geeignete Analysestrategien
©Database Consult GmbH - Jachenau
09/2006
Folie 16 von xx
Kontext
Performanceanalyse und Tuning von Oracle 10g Release 2
• Tuning ist Teil des gesamten Oracle-Management Prozesses
– Systemdesign und Implementierung während der Entwicklung
– Kontrolle der Ziele während des „Approval“
– Monitoring und Tuning während Nutzung
• Die Wirksamkeit steigt mit der „Design-Nähe“
Monitoring
Tuning
Systemdesign
Approval
©Database Consult GmbH - Jachenau
09/2006
Folie 17 von xx
Strategien
Performanceanalyse und Tuning von Oracle 10g Release 2
• Reaktion bei Abweichung von Zielvorgaben
– Skalierbarkeit
– Antwortzeitverhalten
• Kenntnis des Applikations-Umfeldes (Architektur)
• Exakte Fokussierung auf das Problem
– Monitoring - Lastprofile
• Konzentration auf signifikante Engpässe
– CPU-Zeiten, Wartezeiten
• Optimierung dieser Engpässe
• Kontrolle der Zielvorgaben
• Bei Bedarf: nächste Runde
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 18 von xx
6
Strategie
Performanceanalyse und Tuning
von Oracle 10g Release 2
Methode
Reduktion der Anwortzeiten durch Optimierung
massgeblicher Faktoren
Ermittlung der Faktoren durch Aufzeichnung von
Ressourcenprofilen
Ressourcenprofile listen CPU- und Wartezeiten von
Kontexten
Oracle Wait Events und Zeitmodelle ermöglichen Profile
©Database Consult GmbH - Jachenau
09/2006
Folie 19 von xx
Kontext
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning
von Oracle 10g Release 2
Methode
• Fokus auf
geschäftsrelevante Dialoge
mit kritischem
Antwortzeitverhalten
• Kenntnis des
technologischen Umfelds
• Kenntnis des konkreten
Dialogablaufs – z.B. über
Aktivitätsdiagramme
• Messung zum richtigen
Zeitpunkt in originalem
System
Folie 20 von xx
Fokus
C D S S S C D D S C
Session 2
S D D C D D D C C S
Session 3
D C C C D D S S C D
Session 4
C D S S S S C C D D
Zeit
Grafik nach C. Millsap
C = 32,5 %
D = 37,5 %
S = 30,0 %
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
Session 1
C = 25 %
D = 75 %
S=0%
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 21 von xx
7
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 1: Die Konzeption
Geeignete Analysestrategien
Management des DB-Managements
©Database Consult GmbH - Jachenau
09/2006
Folie 22 von xx
Management des DB-Managements
Problemstellungen
Performanceanalyse und Tuning von Oracle 10g Release 2
Tagesgeschäft
Strategisches
Geschäft
Aufgaben
Neue Technologie
Entwicklung
Lösung/Durchführung
Logging
Infrastruktur
Archiv
Monitoring
Wissensbasis
©Database Consult GmbH - Jachenau
09/2006
Folie 23 von xx
Management des DB-Managements
Datenbank
Enwicklung
Applikation
Netzwerk
Betriebssystem
–
–
–
–
–
• Tuning ist Bestandteil aller
Funktionsbereiche
• Organisationsstrukturen
sollten dies unterstützen!
–
–
–
–
09/2006
Netzwerk
Betriebssystem
Datenbank
Applikation
Entwicklung
Performanceanalyse und Tuning von Oracle 10g Release 2
• Tuning ist Teamarbeit
Ticket-Systeme
Wissensbasis
Gremien/Arbeitskreise
Eskalationsstufen
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 24 von xx
8
Management des DB-Managements
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuning
Lastprofile
aktiv
Monitoring
Reporting
Optimierte Implementierung
Datenbankdesign
Systemanalyse
©Database Consult GmbH - Jachenau
09/2006
proaktiv
„performance
engineering“
Folie 25 von xx
Management des DB-Managements
– baut auf bestehende Systeme
– dort sind Modifikationen teuer oder unmöglich
• Tuning ist nicht per se automatisch verfügbar
– sef-tuning ersetzt kein Systemdesign und „performance engineering“
• Ziele
– Minimierung des Ressourcenverbrauchs zur Sicherstellung des
erwarteten Antwortverhaltens – auch bei Skalierung
– Die Ressource-Minimierung wird – neben dem Funktionsumfang – zum
Motor der Entwicklung.
09/2006
©Database Consult GmbH - Jachenau
Folie 26 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 2: Konfiguration und Prophylaxe
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Tuning ist reaktiv
Folie 27 von xx
9
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 2: Konfiguration und Prophylaxe
Memory-Techniken:
SGA- und PGA-Analysen
©Database Consult GmbH - Jachenau
09/2006
Folie 28 von xx
ASMM
– eingeführt unter 9.0.1, erweitert unter 9.2.0
– dynamische Parameter für Shared Pool, Default Buffer Cache, und
später Large Pool
– Obergrenze (statisch) festgelegt über SGA_MAX_SIZE
– keine automatische Umverteilung der SGA-Komponenten
• Unter 10g
– Möglichkeit der automatischen, Last-abhängigen Umverteilung
bestimmter SGA-Komponenten durch den MMAN Prozess: Shared
Pool, Default Buffer Cache, Large Pool und Java Pool
– Dynamisch gesteuert über (m.H. von SPFILE)
• SGA_TARGET > 0 und <= SGA_MAX_SIZE
Performanceanalyse und Tuning von Oracle 10g Release 2
• Automatic Shared Memory Management (ASMM)
• Historie
• STATISTICS_LEVEL = {TYPICAL|ALL}
©Database Consult GmbH - Jachenau
09/2006
Folie 29 von xx
ASMM
Performanceanalyse und Tuning von Oracle 10g Release 2
• MMAN alle 5 Minuten aktiv
• Anpassung der SGA-Komponenten auf Basis von
– Workload Statistiken
– konsultiert Memory Advisor
• Views DBA_HIST_CACHE_ADVICE,
DBA_HIST_[y]_POOL_ADVICE
(y= JAVA | SHARED | STREAMS),
DBA_HIST_[x]_ADVICE
(x = MTTR | PGA | SGA)
– “what-if” Analysen zur Ermittlung der besten Mem-Verteilung
– Schichtet Mem entsprechend um
– übernimmt nach Startup die zuletzt ermittelten Werte über
entsprechende __-Parameter (z.B. __db_cache_size)
• Explizit gesetzte Parameter geben Minimalgrössen vor
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 30 von xx
10
ASMM
–
–
–
–
–
DB_<KEEP/RECYCLE>_CACHE_SIZE
DB_nK_CACHE_SIZE (non default block size)
LOG_BUFFER
FIXED SGA
STREAMS_POOL_SIZE
• SGA_TARGET = Summe der manuellen und automatischen
Bereiche
• Pro und Con
Performanceanalyse und Tuning von Oracle 10g Release 2
• Nicht automatisch getunt (manuelle Bereiche):
– Gut für (periodisch) wechselnde SGA-Anforderungen
– Überflüssig bei „festen“ Ressourcen-Anforderungen
• SGA_TARGET <= SGA_MAX_SIZE
• Bei SGA_TARGET = 0 altes Memory-Modell (9i)
©Database Consult GmbH - Jachenau
09/2006
Folie 31 von xx
ASMM
– v$sgainfo, v$sga_dynamic_components,
v$sga_dynamic_free_memory, v$sga_resize_ops
NAME
MB
---------------- ------------------------------Fixed SGA Size
1
Redo Buffers
3
Buffer Cache Size
364
Shared Pool Size
136
Large Pool Size
4
Java Pool Size
4
Streams Pool Size
0
Granule Size
4
Maximum SGA Size
512
Startup overhead in Shared Pool
28
Free SGA Memory Available
0
RESIZE
-------No
No
Yes
Yes
Yes
Yes
Yes
No
No
No
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Infos über
Folie 32 von xx
Automatisches PGA Management
– Program Global Area oder Workarea liegt im process private memory
des Server Processes
– enthält globale Variablen, Daten Strukturen und control information für
einen Server Process
• z.B. runtime area eines Cursors, erzeugt bei jedem execute
• Performance hängt auch von Memory in PGA ab
• Manuell beeinflusst durch
– SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, etc.
Performanceanalyse und Tuning von Oracle 10g Release 2
• PGA
• Ab 9i automatisiertes PGA Memory Management
– WORKAREA_SIZE_POLICY=AUTO
– PGA_AGGREGATE_TARGET=<angestrebte Gesamtgroesse>
– zunächst nur für Dedicated Server ab 10g auf für Shared Server
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 34 von xx
11
Automatisches PGA Management
– Mischstrategien sind möglich!
– bei „manual“ gelten die Werte der entsprechenden Init.ora Parameter
• PGA_AGGREGATE_TARGET
– angestrebte Gesamtgrössen für alle Sort- und Hash-Areas
– grössere Anforderungen durch
• massive gleichzeitige Sort-Operationen (Minimalgrösse garantiert)
• Array-Speicher für z.B. PL/SQL Module
• interne Limitierungen sind über folgende Parameter sichtbar
Performanceanalyse und Tuning von Oracle 10g Release 2
• Beide Parameter sind dynamisch
– _pga_max_size – maximaler PGA-Anteil pro Prozess (d. 200 MB)
– _smm_max_size / _smm_min_size – maximaler/minimaler PGAAnteil pro Operation
– unterschiedliche Defaults für serielle und parallele Operationen
09/2006
©Database Consult GmbH - Jachenau
Folie 35 von xx
Automatisches PGA Management
– OLTP Systeme
PAT = (<Total Physical Memory > * 80%) * 20%
– DSS Systeme
PAT = (<Total Physical Memory > * 80%) * 50%
• Tracing von Sort-Operationen etc.
– Event 10032 (dump sort statistics)
ALTER SESSION SET EVENTS
'10032 trace name context forever, level 10';
– Event 10033 (dump sort intermediate run statistics. Level 10)
– Event 10053 (dump the decisions made by the optimizer when parsing
a statement. Level 1)
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Empfehlungen für die erste Einstellung
Folie 36 von xx
Automatisches PGA Management
– v$pgastat - instance-level statistics on the PGA memory usage
– V$SQL_WORKAREA_HISTOGRAM – Histogramm von WA-Grössen und
Executions (one-pass, multi-pass)
– V$SQL_WORKAREA_ACTIVE – aktive WAs (executing) in der Instanz
– V$PGA_TARGET_ADVICE – Auswirkungen auf Cache Hit und
Overallokation bei unterschiedlichen PATs
– V$PGA_TARGET_ADVICE_HISTOGRAM – Auswirkungen auf 1-pass
und multi-pass Operationen
– DBA_HIST_PGASTAT (nur AWR)
– DBA_HIST_PGA_TARGET_ADVICE (nur AWR)
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Monitoring der PGA-Bereiche
Folie 37 von xx
12
v$pgastat
– Für dynamische workareas verfügbarer Bereich
– pga_aggregate_target minus fester Bereich (z.B. Session Info
• global memory bound
– Maximalgröße einer Workarea (>= 1MB)
• total PGA allocated
• total PGA used for auto workareas
– augenblicklich gesamthaft genutzte PGA für dynamische Bereiche
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Statistiken der PGA-Nutzung u.a.:
• aggregate PGA auto target
Folie 38 von xx
v$pgastat
09/2006
164413440
201326592
941597696
100
0
40264704
23
103270400
7085056
0
0
666959872
20
74832
6750208
47042560
18685952
0
0
©Database Consult GmbH - Jachenau
bytes
bytes
bytes
percent
bytes
bytes
bytes
bytes
bytes
bytes
Performanceanalyse und Tuning von Oracle 10g Release 2
aggregate PGA auto target
aggregate PGA target parameter
bytes processed
cache hit percentage
extra bytes read/written
global memory bound
max processes count
maximum PGA allocated
maximum PGA used for auto workareas
maximum PGA used for manual workareas
over allocation count
PGA memory freed back to OS
process count
recompute count (total)
total freeable PGA memory
total PGA allocated
total PGA inuse
total PGA used for auto workareas
total PGA used for manual workareas
bytes
bytes
bytes
bytes
bytes
Folie 39 von xx
v$process_memory
Performanceanalyse und Tuning von Oracle 10g Release 2
• Memory Bereiche von Prozessen
• Monitoring über v$process_memory
• Neu in 10.2
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 40 von xx
13
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 2: Konfiguration und Prophylaxe
SGA- und PGA-Analysen
Active Workload Repository:
Kennzahlen, Metriken und Alerts
©Database Consult GmbH - Jachenau
09/2006
Folie 42 von xx
Ausblick und Terminologie
– AWR (Automatic Workload Repository) – historische Infos
– ASH (Active Session History) Infos über Sessions
– ADDM (Automatic Database Diagnostic Monitor) wertet Kennzahlen
aus und gibt Empfehlungen
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Die „automatic Performance Diagnostic und Tuning Features“
haben einen signifikanten Ausbau der Metriken und
Systemstatistiken bewirkt.
• Dadurch erhalten wir wertvolle Werkzeuge für die
Generierung von Lastprofilen und die Performance-Analyse
• Terminologie:
Folie 43 von xx
V$ Erweiterungen 10g
SGA
Historien/Klassen
Vordef. Metriken
Kennzahlen
•
•
•
•
•
•
09/2006
Kontextinfos
Kennzahlen – v$sysstat, v$sessstat, v$mystat
Kontextinfos – v$session_wait, v$sql
Metriken – v$metricname, v$metric
Klassifizierungen – v$metricgroup, v$system_wait_class
Historien über zirkulären Buffer in der SGA – v$filemetric_history
Summaries fassen Historien zusammen – v$sysmetric_summary
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
Summaries und Histogramme
Historien/Klassen
Folie 44 von xx
14
V$ Erweiterungen 10g
v$metricgroup
v$sysstat
v$metricname
Performanceanalyse und Tuning von Oracle 10g Release 2
v$statname (mit class)
v$metric
v$sysmetric
v$sysmetric_history
©Database Consult GmbH - Jachenau
09/2006
Folie 45 von xx
DBA_HIST_xxx Views
AWR
SGA
Summaries und Histogramme
Historien/Klassen
Historien/Klassen
Performanceanalyse und Tuning von Oracle 10g Release 2
SYSAUX
10g – Automatic Workload Repository
Vordef. Metriken
Kennzahlen
Kontextinfos
©Database Consult GmbH - Jachenau
09/2006
Folie 46 von xx
AWR
– Sammelt und speichert wait events, active session history, Systemstatistiken
– Aufwendige SQL-Statements etc.
– braucht statistics_level TYPICAL oder ALL
ADDM
WRH$-Tabellen
DBA_HIST Views
Advisories
dbms_workload_repository
V$Views
"Snapshots" im
SYSAUX-Tablespace
AWR
Background-Prozesse (MMON)
MMNL-Prozess
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Automatic Workload Repository enthält
SGA-Memory
ASH etc.
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 47 von xx
15
v$statistics_level
ACTIVATION
---------TYPICAL
TYPICAL
TYPICAL
ALL
TYPICAL
TYPICAL
ALL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
TYPICAL
STATISTICS_VIEW_NAME
--------------------V$DB_CACHE_ADVICE
V$MTTR_TARGET_ADVICE
SET
-----NO
NO
YES
YES
V$SEGSTAT
NO
V$PGA_TARGET_ADVICE
NO
V$SQL_PLAN_STATISTICS YES
V$SHARED_POOL_ADVICE NO
NO
V$SESSION_LONGOPS
NO
V$SQL_BIND_CAPTURE
NO
NO
NO
NO
V$ACTIVE_SESSION_HISTORY NO
V$UNDOSTAT
NO
V$STREAMS_POOL_ADVICE NO
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
STATISTICS_NAME
------------------------------------------Buffer Cache Advice
MTTR Advice
Timed Statistics
Timed OS Statistics
Segment Level Statistics
PGA Advice
Plan Execution Statistics
Shared Pool Advice
Modification Monitoring
Longops Statistics
Bind Data Capture
Ultrafast Latch Statistics
Threshold-based Alerts
Global Cache Statistics
Active Session History
Undo Advisor, Alerts and Fast Ramp up
Streams Pool Advice
Folie 48 von xx
AWR (Hidden) Parameter
Performanceanalyse und Tuning von Oracle 10g Release 2
_cache_stats_monitor - if TRUE, enable cache stats monitoring
_cursor_bind_capture_area_size
maximum size of the cursor bind capture area
db_cache_advice - Buffer cache sizing advisory
_db_mttr_advice - MTTR advisory
_disable_txn_alert - disable txn layer alert
_dml_monitoring_enabled - enable modification monitoring
_gc_statistics - if TRUE, kcl statistics are maintained
_library_cache_advice - whether KGL advice should be turned on
_longops_enabled - longops stats enabled
_object_statistics - enable the object level statistics collection
_rowsource_execution_statistics
if TRUE, Oracle will collect rowsource statistics
_smm_advice_enabled - if TRUE, enable v$pga_advice
statistics_level - statistics level
_threshold_alerts_enable
if 1, issue threshold-based alerts
timed_os_statistics
internal os statistic gathering interval in seconds
timed_statistics - maintain internal timing statistics
_ultrafast_latch_statistics
maintain fast-path statistics for ultrafast latches
©Database Consult GmbH - Jachenau
09/2006
Folie 49 von xx
AWR
– ermöglicht automatisierte Tuning-Aktionen
– liefert eine Fülle von Kennzahlen und Statistiken für die eigene Analyse
• Cons
– Platzverbrauch im SYSAUX-Tablespace (konfigurierbar)
– teilweise Ressourcen-intensive Operationen
• library cache latch contention und Snapshot Contention ggf. durch
grosse Anzahl von Bind Variablen oder grosse Anzahl von Child
Cursorn (SQL Statistiken langsam)
• Workaround durch Event
awr_flush_table_off level <wrh_tableid>
(siehe Metalink)
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Pro
Folie 50 von xx
16
AWR Konfiguration
Performanceanalyse und Tuning von Oracle 10g Release 2
_awr_restrict_mode = FALSE
STATISTICS_LEVEL = { TYPICAL | ALL }
View DBA_HIST_WR_CONTROL
(Default 60 Min. - 7 Tage)
dbms_workload_repository.modify_snapshot_settings
(interval => 45 -- Minuten
,retention => 20160 –- Minuten = 2 Wochen);
_addm_auto_enable = { TRUE | FALSE }
enable/disable auto stats collection job
_optimizer_autstats_job = { TRUE | FALSE }
©Database Consult GmbH - Jachenau
09/2006
Folie 51 von xx
AWR Platzverbrauch
Auszug Report
*********************************************
| Summary of SYSAUX Space Estimation
| *******************************************
| Est size of SM/ADVISOR
7.6 MB
| Est size of WM
6.9 MB
| Est size of LOGMNR
5.9 MB
| Est size of SM/OTHER
4.8 MB
| Est size of EM_MONITORING_USER
1.6 MB
| Est size of LOGSTDBY
0.9 MB
| Est size of AO
0.8 MB
| Est size of XSOQHIST
0.8 MB
| Est size of STREAMS
0.5 MB
| Est size of JOB_SCHEDULER
0.4 MB
| Est size of TSM
0.3 MB
| Est size of Others
6.9 MB
| Est size of SM/AWR
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• View
v$sysaux_occupants
für Komponente
SM/AWR
• Platzberechnung und
Report über Skript
?/rdbms/admin/
utlsyxsz.sql
155.1 MB
Folie 52 von xx
AWR
– Snapshot – schreiben von Kennzahlen aus AWR-Buffer auf
Platte/Repository für die Dauer von <Retention>
– Baseline – speichern von Snapshot-Bereichen über die Dauer von
<Retention> hinaus (als Referenz-Statistiken)
• Schnittstellen
–
–
–
–
09/2006
OEM
Package DBMS_WORKLOAD_REPOSITORY
Diverse Anbieter z.B. WISE
Reports über ?/rdbms/admin/awrrpt.sql
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Begriffe
Folie 53 von xx
17
AWR
– ggf. interessant, wenn Automatismus ausgeschaltet wurde
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
(low_snap_id => 22,
high_snap_id => 32,
dbid => 3310949047);
END;
/
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Snapshots können auch manuell erstellt, gelöscht etc. werden
Folie 54 von xx
Baselines
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
start_snap_id => 270,
end_snap_id => 280,
baseline_name => 'Hochlast Wochenende',
dbid => 3310949047);
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/
09/2006
©Database Consult GmbH - Jachenau
Folie 55 von xx
©Database Consult GmbH - Jachenau
Folie 56 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
• Speicherung von Kennzahlen und Metriken über die Retention
Periode hinaus ("beibehaltene Snapshots")
• Generiert per PL/SQL-Api mit jeweils einem Snapshot-Paar
• View DBA_HIST_BASELINES
AWR Report
Performanceanalyse und Tuning von Oracle 10g Release 2
09/2006
Copyright Database Consult GmbH
18
AWR Transport
Performanceanalyse und Tuning von Oracle 10g Release 2
• Auswertungen außerhalb des Produktivsystems
• Übertrag in zentrales Repository
• Daten extrahieren:
– DBMS_SWRF_INTERNAL.AWR_EXTRACT(
dmpfile => 'awr_data.dmp', dmpdir => 'TMP_DIR',
bid => 302, eid => 305);
• Übertrag in Übergangsschema:
– DBMS_SWRF_INTERNAL.AWR_LOAD(
SCHNAME => 'AWRTEMP',
dmpfile => 'awr_data', dmpdir => 'TMP_DIR' );
• Verschieben ins AWR der Zieldatenbank (SYS):
– DBMS_SWRF_INTERNAL.MOVE_TO_AWR(
SCHNAME => 'AWRTEMP_USER');
©Database Consult GmbH - Jachenau
09/2006
Folie 57 von xx
Metriken
Relationen von Kennzahlen, Quoten
144 vordefinierte Metriken, 9 Metrikgruppen
v$metricgroup – listet Gruppen
v$metricname – listet mögliche Metriken
SELECT group_name, metric_name, metric_unit
FROM v$metricname
WHERE metric_name LIKE '%File%' OR metric_name LIKE '%Hit%';
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
Folie 58 von xx
Metriken
Performanceanalyse und Tuning von Oracle 10g Release 2
METRIC_NAME
METRIC_UNIT
--------------------------Physical Block Writes (Files-Long)
Blocks
Physical Block Reads (Files-Long)
Blocks
Physical Writes (Files-Long)
Writes
Physical Reads (Files-Long)
Reads
Average File Write Time (Files-Long)
CentiSeconds Per Write
Average File Read Time (Files-Long)
CentiSeconds Per Read
PGA Cache Hit %
% Bytes/TotalBytes
Library Cache Hit Ratio
% Hits/Pins
Row Cache Hit Ratio
% Hits/Gets
Cursor Cache Hit Ratio
% CursorCacheHit/SoftParse
Redo Allocation Hit Ratio
% (#Redo - RedoSpaceReq)/#Redo
Buffer Cache Hit Ratio
% (LogRead - PhyRead)/LogRead
Library Cache Hit Ratio
% Hits/Pins
Buffer Cache Hit Ratio
% (LogRead - PhyRead)/LogRead
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 59 von xx
19
Metriken
Performanceanalyse und Tuning von Oracle 10g Release 2
• v$metric und v$sysmetric
– Aktuelle Metriken für die Instanz
– Messintervalle bestimmt durch v$metricgroup
• Unterschiedliche Sichtweisen relevanter Metriken durch
–
–
–
–
v$sessmetric
v$filemetric
v$eventmetric
v$servicemetric
©Database Consult GmbH - Jachenau
09/2006
Folie 60 von xx
Histogramme
– v$temp_histogram, v$file_histogram, v$event_histogram
SELECT event, wait_time_milli, wait_count
, round(wait_count/summe.total*100,2) "wait%"
FROM
v$event_histogram ,
(SELECT sum(wait_count) total FROM v$event_histogram
WHERE event = 'db file scattered read') summe
WHERE event = 'db file scattered read'
/
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Histogramme für aktuelle Metriken in unterschiedlichen Kontexten,
z.B.
Folie 61 von xx
Histogramme
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
EVENT
WAIT_TIME_MILLI WAIT_COUNT
wait%
---------------------- --------------- ------------- ---db file scattered read
1
4307 90,39
db file scattered read
2
18
0,38
db file scattered read
4
6
0,13
db file scattered read
8
19
0,4
db file scattered read
16
68
1,43
db file scattered read
32
135
2,83
db file scattered read
64
162
3,4
db file scattered read
128
45
0,94
db file scattered read
256
5
0,1
Folie 62 von xx
20
Historische Daten
Performanceanalyse und Tuning von Oracle 10g Release 2
• Zweistufiges Verfahren:
– Zyklische Puffer in der SGA samples im Minutentakt
– Workload Repository mit Tabellen etc, im SYSAUX-Tablespace
• statistics_level = {TYPICAL | ALL }
• View DBA_HIST_WR_CONTROL
DBID
2223592370
SNAP_INTERVAL
RETENTION
+00 01:00:00.000000 +04 00:00:00.000000
©Database Consult GmbH - Jachenau
09/2006
Folie 63 von xx
Historische Daten
Performanceanalyse und Tuning von Oracle 10g Release 2
BEGIN
dbms_workload_repository.modify_snapshot_settings
(interval => 45 -- Minuten
,retention => 20160 –- Minuten = 2 Wochen);
END;
/
• Aktuelle "History"- Informationen aus der SGA (minütlich)
–
–
–
–
–
–
v$active_session_history
v$sysmetric_history,
v$filemetric_history
v$waitclassmetric_history
v$servicemetric_history
v$session_wait_history
©Database Consult GmbH - Jachenau
09/2006
Folie 64 von xx
Historische Daten
Performanceanalyse und Tuning von Oracle 10g Release 2
• Speicherung der ASH Puffer im Shared Pool
– Abhängig von Sessions, Snapshot-Interval
• Zugriff auf das Repository über DBA_HIST-Views
– DBA_HIST_SNAPSHOT
– DBA_HIST_ACTIVE_SESS_HISTORY,
DBA_HIST_SQLSTAT, DBA_HIST_SQLBIND
– DBA_HIST_SYS_TIME_MODEL
– DBA_HIST_SESSMETRIC_HISTORY ...
SELECT * FROM v$sgastat WHERE name = 'ASH buffers';
POOL
NAME
BYTES
------------ ------------ -------shared pool ASH buffers 2097152
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 65 von xx
21
ASH
– Ringpuffer in der SGA (shared pool)
– Größe: 2MB x #CPUs (max. 5% der SGA bzw. <30MB) ermitteln:
select name, bytes/1024/1024 mb
from v$sgastat where name = 'ASH buffers';
• Daten aktiver Sessions (waiting/on CPU)
– Wait event mit p1 – p3, Modul, Action, Client-ID, Cursor-ID
• Gesammelt jede Sekunde (Default)
• aktuelle Details über v$active_session_history
• Historische Daten in AWR – geschrieben durch MMNL-Proz.
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Active Session History (ASH)
– DBA_HIST_ACTIVE_SESS_HISTORY
• ASH-Report über ?/rdbms/admin/ashrpt.sql
• Buffer dumpen mit alter system
set events 'immediate trace name ashdump level 10';
09/2006
©Database Consult GmbH - Jachenau
Folie 66 von xx
ASH
09/2006
©Database Consult GmbH - Jachenau
Folie 67 von xx
©Database Consult GmbH - Jachenau
Folie 68 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
_ash_disk_filter_ratio
Ratio of the number of in-memory samples to the number of samples
actually written to disk = 10
_ash_disk_write_enable
To enable or disable Active Session History
flushing = TRUE
_ash_dummy_test_param
Oracle internal dummy ASH parameter used ONLY for
testing! = 0
_ash_eflush_trigger
The percentage above which if the in-memory ASH is full the
emergency flusher will be triggered = 66
_ash_enable
To enable or disable Active Session sampling
and flushing = TRUE
_ash_sample_all To enable or disable sampling every connected session
including ones waiting for idle waits = FALSE
_ash_sampling_interval
Time interval between two successive Active
Session samples in millisecs = 1000
_ash_size
To set the size of the in-memory Active Session History
buffers = 1048618
ASH Report
Performanceanalyse und Tuning von Oracle 10g Release 2
09/2006
Copyright Database Consult GmbH
22
Advisor Stab
SQL Tuning
SQL Access
Space
Memory
Segment
SGA Advisor
Undo
PGA Advisor
MTTR
JavaPool, Shared Pool etc.
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
ADDM
Folie 69 von xx
ADDM
– automatisch im Anschluss an Snapshot
– explizit über EM oder API (DBMS_ADVISOR, addmrpt.sql)
• Ziel: Liefert detaillierte Empfehlungen
• Empfehlungen über Views und Reports
– Probleme (root causes) : Symptome : Informationen (non-problem
areas)
• Views:
–
–
–
–
DBA_ADVISOR_TASKS
DBA_ADVISOR_LOG
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Automatic Database Diagnostic Monitor
• analysiert die Daten des AWR
• Report über ?/rdbms/admin/addmrpt.sql
09/2006
©Database Consult GmbH - Jachenau
Folie 70 von xx
ADDM
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
•
DECLARE
task_name VARCHAR2(30) := 'SCOTT_ADDM';
task_desc VARCHAR2(30) :=
'ADDM Feature Test';
Ausführungsschritte
task_id NUMBER;
(manuell)
BEGIN
dbms_advisor.create_task('ADDM‚
– Task anlegen
, task_id, task_name, task_desc, null);
– Randbedingungen festlegen: dbms_advisor.set_task_parameter(
start und Ende, Instanz
'SCOTT_ADDM', 'START_SNAPSHOT', 1);
dbms_advisor.set_task_parameter(
– Task ausführen
'SCOTT_ADDM', 'END_SNAPSHOT', 3);
– Empfehlungen
dbms_advisor.set_task_parameter(
beurteilen/anwenden
'SCOTT_ADDM', 'INSTANCE', 1);
dbms_advisor.set_task_parameter(
'SCOTT_ADDM', 'DB_ID', 494687018); (3)
dbms_advisor.execute_task('SCOTT_ADDM');
END;
/
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE
1000
COLUMN get_clob FORMAT a80
SELECT dbms_advisor.get_task_report(
'SCOTT_ADDM', 'TEXT', 'TYPICAL')
FROM sys.dual;
Folie 71 von xx
23
ADDM Report
FINDING 2: 35% impact (1456 seconds)
-----------------------------------SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with
SQL_ID gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2
and PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
FINDING 1: 65% impact (2734 seconds)
-----------------------------------PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr.
Refer to the "Tuning PL/SQL Applications" chapter of
Oracle's
"PL/SQL User's Guide and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
Folie 72 von xx
Advice
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Hilfsmittel bei der Konfiguration von Speichergrössen
• verfügbar ab statistics_level = TYPICAL
• V$-Views
–
–
–
–
–
–
–
v$shared_pool_advice
v$java_pool_advice
v$streams_pool_advice
v$db_cache_advice
v$sga_target_advice
v$pga_target_advice
v$mttr_target_advice
• DBA-Views
– DBA_HIST_SHARED_POOL_ADVICE
– etc.
©Database Consult GmbH - Jachenau
09/2006
Folie 73 von xx
ATO
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Automatic Tuning Optimizer
– SQL Access Advisor: Indizes, MVs
– SQL Tuning Advisor: SQL, SQL-Profile Erstellung
• Expertensystem zur Optimierung von SQL-Statements
– „Optimizer mit sehr weiten Analysezeiten“
• Detaillierte Analyse erstellt Report mit
Verbesserungsvorschlägen
• Expliziter Aufruf über Schnittstelle:
–
–
–
–
09/2006
Paket DBMS_SQLTUNE oder
Enterprise Manager
SQL-Vorlagen – einzeln oder in Sets
manuelle Angabe, Cursor-Cache oder AWR-Historie
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 74 von xx
24
SQL Acess Advisor
– Einzelstatement, Cached Statements, Tuning sets
• Vorgehen
–
–
–
–
Task definieren
Workload neu definieren oder verlinken
Empfehlungen generieren
Ergebnisse evaluieren
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Analysiert Indizes und Materialized Views und Materialized
View Logs
• Nutzung über Enterprise Manager oder API
(DBMS_ADVISOR)
• Input
Folie 75 von xx
SQL Acess Advisor
Performanceanalyse und Tuning von Oracle 10g Release 2
• Unterschiedliche Analyse und Reporting-Stufen
– Comprehensive
– Limited
• Quicky mit einzelnen Statements
– DBMS_ADVISOR.QUICK_TUNE(
DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,
'SELECT AVG(amount_sold) FROM sh.sales
WHERE promo_id=10');
Recommendation
Comprehensive
Create new index
Yes
Create new materialized view
Yes
Create new materialized view log
Yes
Drop unused index
Yes
Drop unused materialized view
Yes
Change type of existing indexes
Yes
Add columns to end of existing indexes Yes
Add columns/clauses to materialized view log Yes
09/2006
Limited
Yes
Yes
Yes
No
No
No
Yes
Yes
©Database Consult GmbH - Jachenau
Folie 76 von xx
Übersicht DBMS_SQLTUNE
Performanceanalyse und Tuning von Oracle 10g Release 2
Tuning Sets
CREATE_SQLSET
CAPTURE_CURSOR_CACHE_SQLSET
SELECT_SQLSET
DROP_SQLSET
SQL-Profiles
ACCEPT_SQL_PROFILE
DROP_SQL_PROFILE
ALTER_SQL_PROFILE
09/2006
Tasks
CREATE_TUNING_TASK
EXECUTE_TUNING_TASK
REPORT_TUNING_TASK
DROP_TUNING_TASK
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 77 von xx
25
Grenzwerte
– Jeweils eine Warnschwelle und eine kritische Schwelle
• API über DBMS_SERVER_ALERT
DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL
–- Art des Schwellwertes: CPU pro CALL
, DBMS_SERVER_ALERT.OPERATOR_GE, '8000'
-- Warnschwelle > 8000 Mikrosekunden
, DBMS_SERVER_ALERT.OPERATOR_GE, '10000'
–- kritische Schwelle > 10000 MS
, 1 –- Beobachtungsdauer in Minuten
, 2 –- Anzahl der Überschreitungen bis Alert gen. wird
, 'T10G' -- Instanz
, DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE
, 'appl1.dcconsult.de')
–- Datenbank Service für den Schwellw. gelten soll
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Definition von Grenzwerten für Metriken
Folie 78 von xx
Server Generated Alerts
– Überschreitung eines Metrik-Grenzwertes, z.B physical reads/sec
– Vorfall eines Events, z.B snapshot too old.
• Meldungen erscheinen in persisten queue ALERT_QUE
– Darstellung in OEM
– manuell über Package DBMS_AQADM – Subscription für die Queue
• von dort als Metrik-Historien in AWR übernommen.
• OEM kann Alerts als Mail etc. weiterleiten.
• Views
Performanceanalyse und Tuning von Oracle 10g Release 2
• Alerts bei
– DBA_OUTSTANDING_ALERTS
– DBA_ALERT_HISTORY
– v$alert_types
©Database Consult GmbH - Jachenau
09/2006
Folie 79 von xx
Segment Level Statistiken
– Für Tabellen und Indizes
– Mindesten STATISTIC_LEVEL = TYPICAL
• v$segstat_name
– Übsicht über Statistikarten/Aktivierung
• v$segstat und v$segment_statistics
– Statistikwerte für konkrete Segmente
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• ab 9iR2 automatisch Statistiken auf Basis von Segmenten
Folie 80 von xx
26
Segment-Level Statistics
Performanceanalyse und Tuning von Oracle 10g Release 2
V$segstat_name
STATISTIC# NAME
0
logical reads
1
buffer busy waits
2
db block changes
3
physical reads
4
physical writes
5
physical reads direct
6
physical writes direct
8
global cache cr blocks served
9
global cache current blocks served
10
ITL waits
11
row lock waits
©Database Consult GmbH - Jachenau
09/2006
Folie 81 von xx
Segment Level Statistiken
object_name,statistic_name,value
v$segment_statistics
object_name = 'EMP'
owner = 'DEVELOPER'
value > 0;
OBJECT_NAME
-----------EMP
EMP
EMP
STATISTIC_NAME
VALUE
------------------ --------logical reads
160
physical reads
5
physical writes
1
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
SELECT
FROM
WHERE
AND
AND
Folie 82 von xx
Table Monitoring
– View USER_TAB_MODIFICATIONS
– Eintrag über SMON alle 3(!) Stunden; FLUSH möglich
• DBMS_STATS kann dann per Option Statistiken automatisch
aktualisieren
– "GATHER STALE": Aktualisierung, wenn > 10% der Rows verändert
wurden
– "GATHER AUTO" zusätzliche Generierung, wenn keine Statistiken
vorliegen
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Protokollierung von DML-Häufigkeiten für bestimmte
Tabellen – MONITORING
Folie 83 von xx
27
Table Monitoring
Performanceanalyse und Tuning von Oracle 10g Release 2
-- Monitoring überprüfen
SELECT table_name
FROM user_tables
WHERE monitoring = 'YES';
-- .. Und einschalten
ALTER TABLE emp MONITORING;
BEGIN dbms_stats.ALTER_SCHEMA_TAB_MONITORING(
'DEVELOPER', TRUE); END;
BEGIN
dbms_stats.FLUSH_DATABASE_MONITORING_INFO;
END;
©Database Consult GmbH - Jachenau
09/2006
Folie 84 von xx
Table Monitoring
TABLE_NAME
INSERTS
UPDATES DELETES TRU
---------- --------- ---------- ------- --EMP
0
4
0 NO
BEGIN
dbms_stats.GATHER_SCHEMA_STATS(
ownname => 'DEVELOPER',
options => 'GATHER STALE');
END;
Performanceanalyse und Tuning von Oracle 10g Release 2
SELECT * FROM user_tab_modifications;
-- Alternativ GATHER AUTO (auch leere Stat.)
©Database Consult GmbH - Jachenau
09/2006
Folie 85 von xx
Index Monitoring
Performanceanalyse und Tuning von Oracle 10g Release 2
• Indexnutzung protokollieren (YES/NO)
• Explizites einstellen über ALTER INDEX
• Abfragen über V$OBJECT_USAGE
– Nur für eigenes Schema
ALTER INDEX emp_idx MONITORING USAGE;
desc v$object_usage
INDEX_NAME
NOT NULL VARCHAR2(30)
TABLE_NAME
NOT NULL VARCHAR2(30)
MONITORING
VARCHAR2(3)
USED
VARCHAR2(3)
START_MONITORING
VARCHAR2(19)
END_MONITORING
VARCHAR2(19)
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 86 von xx
28
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 3: Lastprofile und Analysemethoden
09/2006
©Database Consult GmbH - Jachenau
Folie 91 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 3: Lastprofile und Analysemethoden
Tracing Werkzeuge
09/2006
©Database Consult GmbH - Jachenau
Folie 92 von xx
Polling versus Tracing
t
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
Polling
Tracing
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 93 von xx
29
Diagnostische Events
– 10046 – (extended) SQL-Trace inkl. Waits und Binds
– 10053 – Optimizer-Internals
– 10031, 10032 – Sort Internals
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Generierung von zusätzlichen Debug- und TraceInformationen
• Level-Angaben zur Steuerung des Detailierungsgrades
• schreiben Informationen in ASCII-Trace-Dateien
• Dateien können „roh“ interpretiert oder über Tools formatiert
werden.
• Hier interessant:
• Zu Event 10046 existieren Alternativen
©Database Consult GmbH - Jachenau
09/2006
Folie 94 von xx
Package DBMS_MONITOR
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• „End to End Application Tracing“
– Statistik-Generierung
– SQL-Tracing
• Datensammlung auf unterschiedlichen Ebenen:
–
–
–
–
Session
Service, Modul und/oder Action - hierarchisch
Client Identifier
Instanz/Datenbank
• manche Filter sind kombinierbar
• SQL-Trace erzeugt „rohe“ Trace-Dateien zur
Weiterverarbeitung
©Database Consult GmbH - Jachenau
09/2006
Folie 95 von xx
STATSPACK
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Verfügbar ab Version 8i
• Erweiterung von utlbstat/utlestat
• Snapshots von V$-Objekten
– Umfänge steuerbar über Level 0 bis 10
•
•
•
•
•
09/2006
Reports über Delta-Werte
Geeignet zur Parametrierung der Instanz,
Nur bedingt geeignet für einzelne Anwendungen
Vorgeschaltete, strategische Analyse wichtig!
Braucht mehr Ressourcen als AWR
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 96 von xx
30
STATSPACK
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Installiert über ?/rdbms/admin/spcreate.sql
– User PERFSTAT
– Hilfstabellen
– Package STATSPACK
• Arbeiten mit
– EXECUTE statspack.snap;
– Job über SPAUTO.SQL
– Auswertungen:
• Instanzreport über SPREPORT.SQL und SPREPINS.SQL
• SQL-Report über SPREPSQL.SQL und SPRSQINS.SQL
©Database Consult GmbH - Jachenau
09/2006
Folie 97 von xx
Eigene Skripte
db file ... und direct path ... Events
latch free
enqueue und log file synch
Buffer busy waits und free buffer waits und library chache
pin
– v$sessstat mit CPU-Statistiken ggf. v$sql
–
–
–
–
•
•
•
•
über Logoff-Trigger für Zusammenfassungen
Prozedurale Snapshots für Details – Auflösung von p1, p2, p3
Hilfstabellen speichern Historien
vertretbarer Overhead
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Gurtgeeignet für Versionen < 10g
• Strikte Beschränkung auf wichtige Kennzahlen und Wait events,
z.B.
Folie 98 von xx
TKPROF
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• „transient kernel profiler“
• formatiert einzelne SQL-Trace-Dateien
• Trace-Dateien erzeugt über
– Event 10046. DBMS_MONITOR, sql_trace = true
• unterschiedliche Filter und Sortierungen verfügbar
Tkprof <trace> <output> <explain>
<insert> <sort> <sys> .....
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 99 von xx
31
TRCSESS Werkzeug
– Client-Identifier
– Module, Actions
– Sessions, Services
• Nötig für Shared-Server, Parallelisierungen, Connection Pooling
• Java-Werkzeug
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Konsolidieren von Trace-Dateien für
Folie 100 von xx
Trace-Analyzer
– RDBMS > 8.1.6
• Über Metalink: TRCANLZR.sql (Note 224270.1)
• Erweiterte Funktionalität und Reports – auch für 8i
funktionabel
• läuft vollständig in der Datenbank – Tabellen und Prozeduren
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Erzeugt detailliertes Last- und Ressourcenprofil daher sehr
gut für Response Time Analyse
• Skriptsammlung zur formatierten Ausgaben von 10046er
Traces
Folie 101 von xx
hammerora
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Open Source Tool
• verfügbar über www.sourceforge.net
• Generiert Lasttest-Skripte aus SQL-Tracedateien
–
–
–
–
09/2006
Umwandlung der Tracedatei in Oratcl
Oracle-Instantclient muss auf Testmaschine vorhanden sein
Virtuelle Nutzer können eingerichtet werden
TCL-Skripte editierbar
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 102 von xx
32
dbaman
– Generierung von Testskripten
– u.a. wirkungsvolle Simulation von Applikationen
• Entwickelt von James Morle für „Scaling Oracle8i“
– http://www.morle.com/dbaman/index.htm
– Shareware – getestet bis Oracle 8.1
– verfügbar für Unix
• Voraussetzungen
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Erweiterung der TCL-Sprache für den Zugriff auf OracleDatebank über OCI
• Parsing von Oracle-Tracedateien
– TCL 8.0, C-Compiler, Oracle 7.3+, 8.x, > 8 ?
©Database Consult GmbH - Jachenau
09/2006
Folie 103 von xx
Application Services
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Logische Bereiche zur
–
–
–
–
"group of applications with common attributes"
Verteilung der Arbeitslast, Job Scheduling
Performancemessung und Monitoring
Grenzwerte und Tuning
select name, network_name from v$services;
-NAME
NETWORK_NAME
---------------- ----------------T10G.NndaDevi.de T10G.NandaDevi.de
T10GXDB
T10GXDB
SYS$BACKGROUND
SYS$USERS
©Database Consult GmbH - Jachenau
09/2006
Folie 104 von xx
Application Services
– v$active_services
– v$service_events, v$service_metrics u.a.
– v$session, DBA_THRESHOLDS
• Konfiguration:
– service_names (init.ora)
– DBMS_SERVICE (auch disconnect_session)
BEGIN
DBMS_SERVICE.CREATE_SERVICE
(SERVICE_NAME => 'App1', NETWORK_NAME => 'App1');
DBMS_SERVICE.START_SERVICE
(SERVICE_NAME => 'App1', INSTANCE_NAME => 'T10G');
END;
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Views:
Folie 105 von xx
33
Identifizierung
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Generiert individuelles Infix für Trace-Dateien
• Dynamisch für die Session
• Immer dann, wenn Session mehrere Prozesse benutzt:
– Parallel Query
– Shared Server
ALTER SESSION SET
tracefile_identifier = 'GU02';
Format: <sid>_ora_<pid>_GU02.trc
©Database Consult GmbH - Jachenau
09/2006
Folie 106 von xx
Application Info
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• client_info, module, action über DBMS_APPLICATION_INFO
• client_id über DBMS_SESSION – „auditierbar“
• Filtern teilweise möglich über
– v$session,
– v$sqlarea, v$sql
– dbms_monitor
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO
( CLIENT_INFO => 'Test-Client');
DBMS_SESSION.SET_IDENTIFIER
(CLIENT_ID => 'Test-ID');
END;
©Database Consult GmbH - Jachenau
09/2006
Folie 107 von xx
Markierungen
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
• Individuelles Schreiben in Trace- und Alert-Dateien
– Markierung relevanter Code-Kontexte
• Setzen von Zeitstempeln
BEGIN
-- schreibt Zeitstempel in TRACE-Datei
dbms_system.ksdddt;
-- Einzug mit Doppelpunkten ("::::")
dbms_system.ksdind(4);
dbms_system.ksdwrt (1, 'Output written to trace file');
dbms_system.ksdwrt (2, 'Output written to alert log');
dbms_system.ksdwrt
(3, 'Output written to both trace file and alert log');
END;
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 108 von xx
34
Resumeé
Performanceanalyse und Tuning
von Oracle 10g Release 2
Werkzeuge
Oracle bietet eine Fülle von Werkzeugen, Kennzahlen und
Statistiken.
Projekte müssen die für sie relevanten Werkzeuge und
Methoden festlegen, um optimal optimieren zu können.
Werkzeuge und Kennzahlen sollten so überschabar und
einfach wie möglich sein!
©Database Consult GmbH - Jachenau
09/2006
Folie 109 von xx
Tuning-Projekt
Performanceanalyse und Tuning von Oracle 10g Release 2
• Exakte Formulierungen, aus den folgenden Bereichen:
–
–
–
–
Programmatisches Umfeld
Relevanz für das geschäftliche Umfeld
Ist- und Soll-Zahlen
Genaue Angaben zum Kontext des Performance-Problems
• Server, Instanz, Zeitrahmen
• Session, Modul, Schema, Service
– erwarteter (geschäftlicher) Nutzen
• Entscheidung zur Strategie und zu den Werkzeugen
– Polling versus Tracing
– Auswertungsverfahren
• Keine Parametervorgaben akzeptieren!
©Database Consult GmbH - Jachenau
09/2006
Folie 110 von xx
Tuning-Projekt
Performanceanalyse und Tuning von Oracle 10g Release 2
• Polling
–
–
–
–
–
–
Schnell einsetzbare V$Abfragen
genaue Synchronisierung erforderlich – manuell oder Trigger
unmittelbare Auswertung für Session oder Service
keine zusätzlichen Tools
lückenhafte Kennzahlen
Ungenaue Analysen bei diffusen Trends
• Tracing
–
–
–
–
09/2006
lückenlose Protokollierung, exakte Kontexte
alle Filter möglich: Session, Module, Service
Auswertung erfordert Tools
ggf. grosse Datenmenge und damit erschwerte Analysen
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 111 von xx
35
Polling
– v$sess_time_model
– v$sessstat – diverse Statistiken
• Wartezeiten:
–
–
–
–
–
–
–
–
09/2006
v$session_event – mit Historie der Session
v$session – mit aktuellen Wartezyklen
v$session_wait – aktuelle Wartezyklen
v$session_wait_history – die letzten 10 Wartezyklen akt.S.
v$service_event – Zusammenfassungen per Service per Event
v$session_wait_class – Zusammenfassung nach Klassen
v$service_wait_class – Zusammenfassung nach Klassen
v$active_session_history – Historie v.Wartezyklen akt.
Sessions
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• CPU-Zeiten:
Folie 112 von xx
CPU-Statistiken >= 10g
Performanceanalyse und Tuning von Oracle 10g Release 2
background cpu time
background elapsed time
connection management call elapsed time
DB CPU
DB time
failed parse elapsed time
failed parse (out of shared memory) elapsed time
hard parse (bind mismatch) elapsed time
hard parse elapsed time
hard parse (sharing criteria) elapsed time
inbound PL/SQL rpc elapsed time
Java execution elapsed time
parse time elapsed
PL/SQL compilation elapsed time
PL/SQL execution elapsed time
repeated bind elapsed time
RMAN cpu time (backup/restore)
sequence load elapsed time
sql execute elapsed time
09/2006
©Database Consult GmbH - Jachenau
Folie 113 von xx
CPU Statistiken <= 10g
– Parsing von SQL
– Logical IO/Blockzugriffe im Buffer
12
11
161
46
43
332
331
330
328
329
8
09/2006
CPU used by this session
CPU used when call started
gc CPU used by this session
global enqueue CPU used by this session
IPC CPU used by this session
parse count (failures)
parse count (hard)
parse count (total)
parse time cpu
parse time elapsed
recursive cpu usage
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Aus v$sysstat oder v$sesstat
• CPU Zeit im wesentlichen nötig für
Folie 114 von xx
36
Tracing >= 10g
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
Hierarchische Filter für Servicename, Modul und Action
Separater Filter für Client Identifier
Regeln wirken kumulativ
Ausgabe der definierten regeln über View
DBA_ENABLED_TRACES
dbms_monitor.serv_mod_act_trace_enable
('APP1', 'PACK_X', 'Proc_Y');
dbms_monitor.serv_mod_act_trace_disable
('APP1', 'PACK_X', 'Proc_Y');
dbms_monitor.client_id_trace_enable('GU');
dbms_monitor.client_id_trace_disable('GU');
©Database Consult GmbH - Jachenau
09/2006
Folie 115 von xx
Tracefile ermitteln und öffnen
Performanceanalyse und Tuning von Oracle 10g Release 2
Rem ggf. an OS oder Ora-Version anpassen
SET SUFFIX TRC
COLUMN filename NEW_VALUE filename
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid
||decode(p3.value,null,'','_'||p3.value) filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2,
v$parameter p3
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p3.name = 'tracefile_identifier'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID');
EDIT &&filename
SET SUFFIX SQL
COLUMN filename CLEAR
©Database Consult GmbH - Jachenau
09/2006
Folie 116 von xx
Tracing
Performanceanalyse und Tuning von Oracle 10g Release 2
• Mögliche weitere Arbeitsschritte:
–
–
–
–
–
09/2006
ggf. zusammenführen verschiedener Trace-Dateien
Verstehen der „rohen“ Trace-Datei
Formatierung über TKPROF
Formatierung über Trace Analyzer (trcanlzr.sql)
Tools von www.hotsos.com
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 117 von xx
37
Tracing
Performanceanalyse und Tuning von Oracle 10g Release 2
• Zusammenführen von Trace-Dateien für
– Client-Identifier
– Module, Actions
– Sessions
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]
©Database Consult GmbH - Jachenau
09/2006
Folie 118 von xx
Tracing – Rawtrace
Performanceanalyse und Tuning von Oracle 10g Release 2
• sinnvoll bei unvollständigen Auswertungen
• exakte Zuordnung von Wait Events zu Calls
• Gliederung
– Allgemeiner Header
– Identification:
*** ACTION NAME:(Action X) 2005-11-03 13:24:10.265
*** MODULE NAME:(Module A) 2005-11-03 13:24:10.265
*** SERVICE NAME:(SYS$USERS) 2005-11-03 13:24:10.265
*** CLIENT ID:(GU) 2005-11-03 13:24:10.265
*** SESSION ID:(16.1022) 2005-11-03 13:24:10.265
=====================
09/2006
©Database Consult GmbH - Jachenau
Folie 119 von xx
Tracing – Rawtrace
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
•
•
•
•
•
Cursor Informationen direkt nach Parse Call oder später
Nummer nimmt Bezug auf nachfolgende Calls
len – Länge des Stmts,
uid – schema ID des parsenden Benutzers
oct – Oracle Call Type
lid – privileged user id
tim Zeitstempel in Mikrosekunden (>= 9i) z.B. gettimeofday
hv – hash value
ad - address
PARSING IN CURSOR #7 len=32 dep=0 uid=58 oct=3 lid=58
tim=22374257176 hv=2935929963 ad='1fd73f00'
select count(*) from dba_objects
END OF STMT
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 120 von xx
38
Tracing – Rawtrace
Bindevariablen werden positionsbedingt aufgeführt
oacdty – external datatype (hier varchar) (entspricht dump-Ausgabe)
avl – Länge; value – Länge
Details über Note 39817.1 (Metalink)
Scl und pre = scale und precision
=====================
PARSING IN CURSOR #7 len=58 dep=1 uid=58 oct=3 lid=58
tim=535121950 hv=1332351685 ad='1fcb0338'
SELECT COUNT(*) FROM DBA_TABLES WHERE TABLE_NAME LIKE :B1
END OF STMT
...
BINDS #7:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0762442c bln=32 avl=04 flg=09
value="DBA%"
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
•
Folie 121 von xx
Tracing – Rawtrace
– im Kontext von DB-Calls (erscheinen vor diesen hier grün)
– zwischen DB-Calls (erscheinen separat hier blau)
• nam – Names des Events
• ela – elapsed time
• die letzten 3 entsprechen p1, p2, p3 von v$session_wait
EXEC #7:c=156250,e=153309,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,tim=535275386
WAIT #7: nam='db file sequential read' ela= 11988 file#=1 block#=10859
blocks=1 obj#=3 tim=535338348
FETCH #7:c=109375,e=123525,p=1,cr=2797,cu=0,mis=0,r=1,dep=1,og=1,tim=535398965
EXEC #10:c=265625,e=277739,p=1,cr=2799,cu=0,mis=0,r=1,dep=0,og=1,tim=535399206
WAIT #10: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1
p3=0 obj#=3 tim=535399302
WAIT #10: nam='SQL*Net message from client' ela= 459 driver id=1111838976
#bytes=1 p3=0 obj#=3 tim=535399827
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Wait Events
Folie 122 von xx
Tracing - Rawtrace
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=5248
pr=0 pw=0 time=407489 us)'
STAT #7 id=2 cnt=50337 pid=1 pos=1 obj=2371 op='VIEW DBA_OBJECTS
(cr=5248 pr=0 pw=0 time=1562536 us)'
STAT #7 id=3 cnt=50337 pid=2 pos=1 obj=0 op='UNION-ALL (cr=5248
pr=0 pw=0 time=1159833 us)'
STAT #7 id=4 cnt=50337 pid=3 pos=1 obj=0 op='FILTER (cr=5247 pr=0
pw=0 time=505435 us)'
STAT #7 id=5 cnt=51503 pid=4 pos=1 obj=0 op='HASH JOIN (cr=625
pr=0 pw=0 time=980596 us)'
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• STAT kennzeichnet Rowsources (Ausführungspläne) – ähnlich
plan_table
• id – identifier; cnt – rowcount ; pid – parent id
• obj – object id; op – operation, time – Zeit in Mikrosekunden
• cr – consisent reads; pr – physical reads; pw – physical writes
Folie 123 von xx
39
Tracing - Rawtrace
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=3 lid=0 tim=3282021354
hv=1343089354 ad='2aac0994'
select node,owner,name from syn$ where obj#=:1
END OF STMT
PARSE #1:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3282021346
PARSING IN CURSOR #1 len=179 dep=1 uid=0 oct=3 lid=0 tim=3282058960
hv=2812844157 ad='2aaf9d38'
select owner#,..... from dependency$ d, obj$ o where d_obj#=:1 and
p_obj#=obj#(+) order by order#
END OF STMT ...
=====================
PARSING IN CURSOR #7 len=32 dep=0 uid=58 oct=3 lid=58 tim=3282347867
hv=2935929963 ad='1fcc1024'
select count(*) from dba_objects
END OF STMT
PARSE #7:
c=328125,e=327028,p=0,cr=97,cu=0,mis=1,r=0,dep=0,og=1,tim=3282347858
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Rekursive Calls erscheinen vor ihren Parents
• Parents enthalten in elapsed time die Zeiten ihrer Kinder
Folie 124 von xx
Tracing - Rawtrace
Performanceanalyse und Tuning von Oracle 10g Release 2
5
dep=0
2
3
4
dep=1
dep=1
dep=1
1
dep=2
09/2006
©Database Consult GmbH - Jachenau
Folie 125 von xx
TKPROF
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
C:\oracle\admin\ora102\udump>tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename
Use 'schema.tablename' with 'explain=' option.
explain=user/password
Connect to ORACLE and issue EXPLAIN PLAN.
print=integer
List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no
TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no
Record summary for any wait events found in the trace
file.
sort=option
Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
Folie 126 von xx
40
TKPROF Output
–
–
–
–
count
cpu
elapsed
disk
query
current
rows
Summaries
read consistency
kein Schema-Trap mehr!
Recursion-Trap
Time Trap (locking)
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Zeiten nur bei timed_statistics = true
• „Traps“ beachten
Folie 127 von xx
TKPROF
call
count
------- -----Parse
1
Execute
1
Fetch
4
------- -----total
6
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.06
0.06
0
2
0
0.02
0.01
0
0
0
0.05
0.04
0
7
0
-------- ---------- ---------- ---------- ---------0.13
0.12
0
9
0
rows
---------0
0
45
---------45
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows
------45
Row Source Operation
--------------------------------------------------TABLE ACCESS FULL EMPLOYEES (cr=7 r=0 w=0 time=672 us)
Performanceanalyse und Tuning von Oracle 10g Release 2
***************************************************************************
select * from hr.employees where department_id = 50
Elapsed times include waiting on following events:
Event waited on
Times
Max. Wait Total Waited
---------------------------------------Waited ---------- -----------SQL*Net message to client
4
0.00
0.00
SQL*Net message from client
4
20.91
20.93
********************************************************************************
09/2006
©Database Consult GmbH - Jachenau
Folie 128 von xx
Nachteile TKPROF
Performanceanalyse und Tuning von Oracle 10g Release 2
• Eingeschränkte Übersicht der Wait Events
• Keine Unterscheidung der Calls in User/recursive und
Internal/recursive
• Zeigt keine aktuellen Werte von Bindevariablen!
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 129 von xx
41
Trace-Analyzer
– RDBMS > 8.1.6
• Über Metalink: TRCA.zip
• Erweiterte Funktionalität und Reports – auch für 8i
funktionabel
• Arbeitet über Hilfstabellen und PL/SQL-Prozeduren
– Zugriff auf Trace mittels DIRECTORY
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Skriptsammlung zur formatierten Ausgaben von 10046er
Traces
Folie 130 von xx
Trace-Analyzer
– Nötige Grants erteilen (über SYS)
– Hilfstabellen (22) und Package (1) anlegen (unter user)
– Analyse starten:
• START TRCANLZR.sql UDUMP prod_ora_9105.trc;
• Wenn keine rekrusiven Statements, dann vorher
TRCAISYS.sql NO;
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
• Fokus: analysierender Benutzer (Generator von 10046)!
• Arbeitsweise:
Folie 131 von xx
Trace-Analyzer (Report)
TOP SQL (SUMMARY OF CPU, ELAPSED AND WAITS PER TOP EXPENSIVE CURSOR)
GAPS OF NO TRACE ACTIVITY
SUMMARY OF CALLS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
SUMMARY OF CALLS BY COMMAND TYPE, USER (INTERNAL LAST) AND NONRECURSIVE/RECURSIVE
SUMMARY OF WAITS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
DETAIL OF NON-IDLE WAITS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
HOTTEST 5 BLOCKS (MOST TIMES WAITED FOR)
SUMMARY BY SQL STATEMENT (CURSOR)
Performanceanalyse und Tuning von Oracle 10g Release 2
Introduction...
*** Vorstellung der einzelnen Cursor mit Bindevariablen etc.
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 132 von xx
42
Trace-Analyzer
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
********************************************************************************************
TRCANLZR.sql 115.9 NOTE:224270.1 2003-02-06 15:42:01
********************************************************************************************
TRACE_DIRECTORY..........: /amer/oracle/visus86/visus86ora/8.1.7/admin/visus86/udump
(ALIAS:UDUMP)
TRACE_FILENAME...........: visus86_ora_17714.trc (TRACE_ID:31)
INSTANCE_AND_RELEASE.... : VISUS86 (ON TDASOL3) 8.1.7.3.0 (SUNOS - PRODUCTION)
TRACE_SIZE...............: 1332588 BYTES (IN 25314 LINES)
TRACED_INTERVAL..........: STARTED ON 2003-02-04 11:03:13.545, AND LASTED 11.99 SECS
USER_ELAPSED_TIME........: 11.99 SECS GAPS_WITH_NO_ACTIVITY....: 0.00
EFFECTIVE_TRACED_INTERVAL: 11.99
ACCOUNTED_CPU_TIME.......: 6.68 SECS (TOTAL SERVICE TIME)
ACCOUNTED_ELAPSED_TIME...: 20.17 (RECURSIVE AND NON-RECURSIVE)
WAITED_NON-IDLE_TIME.....: 7.72 SECS
WAITED_IDLE_TIME.........: 0.90
********************************************************************************************
Folie 133 von xx
Trace Analyzer
09/2006
©Database Consult GmbH - Jachenau
Performanceanalyse und Tuning von Oracle 10g Release 2
NUMBER_OF_CURSORS........: 231 (USER), 172 (INTERNAL <SYS>), 403 (TOTAL)
UNIQUE_SQL...............: 136 (USER), 44 (INTERNAL <SYS>), 180 (TOTAL)
***************************************************************************************
TOP SQL (SUMMARY OF CPU, ELAPSED AND WAITS PER TOP EXPENSIVE CURSOR)
====================================================================
cursor user
non-idle idle
id
id
command type
count cpu top elapsed top waits top waits top
------ ---- ----------------------- ------ ----- --- ------- --- ----- --- ----- --98.... 65.. pl/sql execute.........
2 0.71 1
2.21 2
0.00
0.00
99.... 65.. insert.................
2 0.69 2
2.19 3
1.81 2
0.00
18.... 65.. pl/sql execute.........
4 0.61 3
1.93 4
0.00
0.00
137... 0... insert.................
375 0.53 4
0.71 5
0.02
0.00
113... 65.. select.................
3 0.40 5
3.00 1
2.11 1
0.01
115... 65.. select.................
125 0.05
0.36
0.31 3
0.00
5..... 0... select.................
15 0.01
0.22
0.22 4
0.00
29.... 0... select.................
266 0.05
0.27
0.21 5
0.10 2
34.... 65.. select.................
3 0.24
0.38
0.00
0.11 1
130... 65.. select.................
63 0.01
0.01
0.00
0.05 3
133... 65.. select.................
17 0.06
0.24
0.20
0.05 4
12.... 65.. pl/sql execute.........
74 0.21
0.23
0.00
0.04 5
***************************************************************************************
Folie 134 von xx
Trace-Analyzer
• Sehr nützlich: Wait Events werden nach User und Segment
individuell gruppiert!
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
Details for wait event
Times Count
Max. Total
Blocks
'db file scattered read (multiblock full scan)'
Waited Zero Time Wait Waited Accessed
--------------------------------------------------- ------ --------- ---- ------ -------bom.bom_calendar_dates.............................
26
8 0.02
0.19
178
mrp.mrp_schedule_dates_n1..........................
2
0 0.04
0.06
21
mrp.mrp_load_parameters............................
2
0 0.02
0.03
6
--------------------------------------------------- ------ --------- ---- ------ -------total..............................................
30
8 0.04
0.28
205
Folie 135 von xx
43
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 4: Cost based Optimizer
©Database Consult GmbH - Jachenau
09/2006
Folie 144 von xx
Performanceanalyse und Tuning von Oracle 10g Release 2
Teil 4: Cost based Optimizer
Arbeitsweise des CBO
©Database Consult GmbH - Jachenau
09/2006
Folie 145 von xx
Übersicht
– veränderte Defaults (OPTMIZER_MODE = ALL_ROWS)
– explizites Setzen möglich
– Algorithmen mit altem Stand (V6/V7)
• dadurch gleichbleibende Zugriffspläne
• CBO unter 10g R1 und R2
– erweitertes Kostenmodell: CPU- und IO-Kosten, dynamic sampling per
default
– erweiterte Query Transformationen
– weiter entwickelte Berechnungsmodelle
– verbesserte Plan_Table
Performanceanalyse und Tuning von Oracle 10g Release 2
• Ab 10g keine offizielle Unterstützung des Rule base
Optimizer (RBO)
• CBO-Krux:
– Weiterentwicklungen in den Rechenmodellen und Defaults können
Überraschungen bei Zugriffsplänen verursachen („Instabilität“)
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 146 von xx
44
Übersicht
Performanceanalyse und Tuning von Oracle 10g Release 2
CBO arbeitet i.d.R. zufriedenstellend wenn er korrekt
konfiguriert wurde!
Gute Kenntnis der Grundlagen (init.ora und Statistiken)
garantieren Verlässlichkeit!
©Database Consult GmbH - Jachenau
09/2006
Folie 147 von xx
Einflussfaktoren CBO
Performanceanalyse und Tuning von Oracle 10g Release 2
Statistiken
io
CPU
Indexstrukturen
Systemparameter
CBO
Constraints
Interne
Algorithmen
Hints
SQLKonstrukte
09/2006
©Database Consult GmbH - Jachenau
Folie 148 von xx
Serverparameter CBO
Diverse
CURSOR_SHARING
SKIP_UNUSABLE_INDEXES
DB_FILE_MULTIBLOCK_READ_COUNT
parallel processing
underscore-Parameters
Memory
WORKAREA_SIZE_POLICY
PGA_AGGREGATE_TARGET
HASH_AREA_SIZE
SORT_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
Query transformation
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
STAR_TRANSFORMATION_ENABLED
OPTIMIZER_SECURE_VIEW_MERGING
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
Grundlegend
OPTIMIZER_MODE
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_INDEX_CACHING
DB_FILE_MULTIBLOCK_READ_COUNT
Folie 149 von xx
45
Grundeinstellungen / Migration
– Systeme und Anwendungen haben unterschiedliche Anforderungen
– Kaum universelle Parametervorgaben möglich
• Weiteres Vorgehen
–
–
–
–
–
–
Grundlegende Parameter einstellen
Systemstatistiken generieren
Objektstatistiken generieren (table, column, index)
Workarea Policy einstellen (sort areas, hash areas)
bei globaler Schieflage weitere Serverparameter anpassen
bei einzelnen Ausreissern diese individuell optimieren
Performanceanalyse und Tuning von Oracle 10g Release 2
• Keine Prognosen ohne Tests!
• Migration
– in harnäckigen Fällen plan stability einsetzen
©Database Consult GmbH - Jachenau
09/2006
Folie 150 von xx
Plan Stability für Migration
Performanceanalyse und Tuning von Oracle 10g Release 2
GRANT CREATE ANY OUTLINE TO <appuser>
/
ALTER SESSION SET CREATE_STORED_OUTLINES = rbolike
/
-- SQL auführen
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE
/
-- Statistiken und OPTIMIZER_MODE einstellen
ALTER SESSION SET USE_STORED_OUTLINES = rbolike
/
©Database Consult GmbH - Jachenau
09/2006
Folie 151 von xx
Statistiken
Performanceanalyse und Tuning von Oracle 10g Release 2
• CBO relevante Statistiken
– Objektstatistiken (Table, Index)
– Systemstatistiken (CPU, IO)
– Sonderfälle
• Data Dictionary
• Fixed Tables
• CBO Statistiken
– Schnittstelle DBMS_STATS (nicht mehr ANALYZE)
– generieren, setzen, exportieren, importieren, löschen, einfrieren
– ab 10g Statistik Historien
• Segment Statistics
– kein Einfluss auf CBO Entscheidungen
– Bestandteil des AWR
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 152 von xx
46
Vorteile DBMS_STATS
Globale Statistiken für partitionierte Objekte
sammelt auch für externe Tabellen und V$-Tabellen
kann parallelisiert werden
keine schlagartige Invalidierung der abhängigen Cursor
Analyze generiert zusätzlich, jedoch ohne Relevanz für CBO
(Ausnahmen CHAIN_CNT)
–
–
–
–
–
EMPTY_BLOCKS
CHAIN_CNT
AVG_SPACE
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
•
Folie 153 von xx
Systemstatistiken
– erstellt nach Defaults bei erstem Intanzstart
– wertlos
– ioseektim = 10ms
iotrfspeed = 4096 bytes/ms
cpuspeednw = gathered value, varies based on system
– erzeugt durch dbms_stats.gather_system_stats()
• Workload
– errechnet Werte durch Messung (start-stop) über längeren Zeitraum
– empfehlenswert, da genauer
– sehr wichtig für CBO
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• Noworkload
Folie 154 von xx
Systemstatistiken
– Siehe PLAN_TABLE und v$sql_plan
– Betreffende Spalten sind sonst NULL
• Analyse über eine vorgegebene Zeitspanne während
charakteristischer Last
• Aktivitäten: Sammeln, Export, Import, Setzen
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Performanceanalyse und Tuning von Oracle 10g Release 2
• Sammlung von Statistiken für System-IO und CPU
Charakteristik
• Notwendig zur (korrekten) Ermittlung von CPU_COST und
IO_COST
Folie 155 von xx
47
Systemstatistiken
END;
-- laufende Kontrolle
STATID
C1
--------- ------------SYSTEM_01 AUTOGATHERING
...
SYSTEM_01 COMPLETED
C2
C3
---------------- ---------------08-09-2003 16:29 08-11-2001 16:29
08-09-2001 16:41 08-09-2001 16:43
Performanceanalyse und Tuning von Oracle 10g Release 2
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'INTERVAL', interval => 2
, stattab => 'STAT_TABLE' , statown => 'DEVELOPER'
, statid => 'SYSTEM_01');
-- oder
dbms_stats.gather_system_stats(’start’)
dbms_stats.gather_system_stats(’stop’)
©Database Consult GmbH - Jachenau
09/2006
Folie 156 von xx
Systemstatistiken
Performanceanalyse und Tuning von Oracle 10g Release 2
-- Löschen von Dictionary aux_stats$
BEGIN
DBMS_STATS.DELETE_SYSTEM_STATS;
END;
-- Übertragen in Dictionary = aktivieren
BEGIN
DBMS_STATS.IMPORT_SYSTEM_STATS(
stattab => 'STAT_TABLE', statid => 'SYSTEM_01',
statown => 'DEVELOPER');
END;
SELECT * FROM sys.aux_stats$;
©Database Consult GmbH - Jachenau
09/2006
Folie 157 von xx
Systemstatistiken
09/2006
STATUS
DSTART
DSTOP
FLAGS
CPUSPEEDNW
IOSEEKTIM
IOTFRSPEED
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
Performanceanalyse und Tuning von Oracle 10g Release 2
SYSSTATS_INFO
SYSSTATS_INFO
SYSSTATS_INFO
SYSSTATS_INFO
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
SYSSTATS_MAIN
COMPLETED
08-01-2006 12:50
08-01-2006 12:50
1
1007,07785642063
10
4096
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 158 von xx
48
Systemstatistiken
• PLAN_TABLE.CPU_COST: benötigte Maschinen-Zyklen
• PLAN_TABLE.IO_COST: gelesene Datenblöcke
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
sreadtim : wait time to read single block, in
milliseconds
mreadtim : wait time to read a multiblock, in
milliseconds
cpuspeed : cycles per second, in millions
mbrc : mulitblock read count (average)
maxthr:maximum I/O system throughput, in bytes/sec
Slavethr:average slave I/O throughput, in bytes/sec
Folie 159 von xx
DBMS_STATS
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• dbms_stats.gather_fixed_table_stats
• dbms_stats.gather_dictionary_table_stats
• dbms_stats.restore_table_stats(
null,'sales',systimestamp - interval '1' day);
• dbms_stats.convert_raw_value
• LOCK_TABLE_STATS, LOCK_SCHEMA_STATS,
UNLOCK_TABLE_STATS, UNLOCK_SCHEMA_STATS
Folie 160 von xx
Statistics History
Performanceanalyse und Tuning von Oracle 10g Release 2
• Statistiken werden per default automatisch historisiert
• APIs über DBMS_STATS
–
–
–
–
09/2006
GET_STATS_HISTORY_RETENTION
GET_STATS_HISTORY_AVAILABILITY
PURGE_STATS
ALTER_STATS_HISTORY_RETENTION
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 161 von xx
49
Column Usage Monitoring
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
•
•
•
•
•
•
Keep track of columns that have been used
in different predicate types
– Equality
– Range
– Equi-join
– Non Equi-join
– LIKE
– IS [NOT] NULL
siehe Dict unter col_usage$ und mon_mods$
_col_tracking_level
©Database Consult GmbH - Jachenau
09/2006
Folie 162 von xx
Index
Performanceanalyse und Tuning von Oracle 10g Release 2
• Index Creation & Rebuild
– automatische Erstellung von Statistiken bei
• CREATE INDEX
• ALTER INDEX REBUILD
• Bei function based dann Statistiken für “virtuelle Spalte
• Ovewrhead zu vernachlässigen
©Database Consult GmbH - Jachenau
09/2006
Folie 163 von xx
Views
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
•
•
•
•
•
•
DBA_TAB_STATISTICS
DBA_IND_STATISTICS
DBA_TAB_STATS_HISTORY
DBA_TAB_COL_STATISTICS
dba_part_col_statistics
dba_subpart_col_statistics
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 164 von xx
50
Regeln zum Sammeln von Statistiken
©Database Consult GmbH - Jachenau
09/2006
Performanceanalyse und Tuning von Oracle 10g Release 2
• DBMS_STATS
• Tabellen mit Sample Size (wenige Prozent) DBMS_STATS
korrigiert nach oben)
Partitions mit global stats, grosse Tabellen parallel
• Indizes mit Compute Zeit testen
• Beí Cascade ggf. automatisch 100% für Indizes (testen)
• Gather Stale zur Optimierung falls statistics_level= typical
• Histogramme für Spalten in Where-Klauseln
size Skewonly schaut in col_usage$
• Achtung vor „temporären“ Tabellen – ggf. locken
• ggf. Hilfstabelle und Wrapper-Prozedur zur Individuellen
Konfiguration
Folie 165 von xx
Literatur
Performanceanalyse und Tuning von Oracle 10g Release 2
James Morle – Scaling Oracle 8i
Cary Millsap with Jeff Hold – Optimizing Oracle Performance
R.Shee, K. Deshpande, K. Gopalakrishnan – Oracle Wait Interface
Jonathan Lewis – Cost based Oracle
Diverse Metalink-Artikel
09/2006
©Database Consult GmbH - Jachenau
Copyright Database Consult GmbH
Folie 225 von xx
51
