Strategisches Tuning - Database Consult GmbH

Strategisches Tuning – Teil 1
Werkzeuge, Lastprofile
TOAD User Konferenz 2007
Dr. Günter Unbescheid
Database Consult GmbH
Statement 1
Strategisches Tuning – Teil 1
Unzureichend performante
performante Systeme
Systeme sind
sind
Unzureichend
normal und
und
normal
aus diesem
diesem Grunde
Grunde kein
kein Grund
Grund zur
zur
aus
Beunruhigung.
Beunruhigung.
Auf Überraschungen sind wir
vorbereitet. – Nur die
alltäglichen Dinge brechen
über uns herein wie
Katastrophen.
Stanislaw Jercy Lec
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 2 von
1
Statement 2
Strategisches Tuning – Teil 1
Tuningist
istdaher
daherein
ein
Tuning
alltägliches
Geschäft,
alltägliches Geschäft,
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
10/2007
Folie 3 von
Statement 3
Strategisches Tuning – Teil 1
Tuning erfordert
erfordert
Tuning
Wissen,
Geduld,
Neugier,
Phantasie,
Wissen, Geduld, Neugier, Phantasie,
Konzentration
auf
das
Wesentliche,
Konzentration auf das Wesentliche,
Werkzeuge
Werkzeuge
Wenig gedeiht,
Zuviel zerstreut.
Volksmund
10/2007
Database Consult GmbH
Phantasie ist wichtiger als
Wissen,denn Wissen ist
begrenzt.
Albert Einstein
©Database Consult GmbH - Jachenau
Folie 4 von
2
Statement 4
Strategisches Tuning – Teil 1
DieKenntnis
Kenntnisder
der Features
Featuresmacht
machtnoch
noch
Die
kein
performantes
System
kein performantes System
Wir leben in einer Zeit vollkommener Mittel
und verworrener Ziele.
Albert Einstein
©Database Consult GmbH - Jachenau
10/2007
Folie 5 von
Resumee
Strategisches Tuning – Teil 1
[Tuning]ist
ist wie
wieRudern
Rudern gegen
gegenStrom.
Strom.
[Tuning]
Sobald
man
aufhört,
treibt
man
zurück.
Sobald man aufhört, treibt man zurück.
Anlehnungan
anBenjamin
BenjaminBritten
Britten
InInAnlehnung
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 6 von
3
Auf den Punkt...
Strategisches Tuning – Teil 1
Wissen
Strategie
Werkzeuge
Features
Psychologie
©Database Consult GmbH - Jachenau
10/2007
Folie 7 von
Agenda
Strategisches Tuning – Teil 1
• Teil 1 – Werkzeuge, Erstellung von Profilen
– Analysestrategien
– Informationsquellen der Versionen 10g und 11g
– Erstellung von Lastprofilen
• Teil 2 – Analyse von Lastprofilen
– Wait Events
– CPU-Zeiten
– SQL Zugriffspläne
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 8 von
4
Strategisches Tuning – Teil 1
Teil 1: Werkzeuge, Lastprofile
Analysestrategien
©Database Consult GmbH - Jachenau
10/2007
Folie 9 von
Tuning Features und Tools.....
Redo Log Advisor
Buffer Cache Advisor Extended Statistics
SQL Repair Advisor
Segment Advisor
EM
V$-Tabellen
Adaptive Cursors
Library Cache Advisor
Variable Peeking
PGA Advisor
ADDM
AWR
TOAD
Klares Nutzungskonzept
Statspack
Plan Stability
ADR
unabdingbar!
SQL Plan Management
SPA
SQL Monitoring
Spotlight
Undo Advisor
ASH
init Parameter
SQL Tuning Advisor
Database Consult GmbH
TKPROF
Trace Analyzer
Result Cache
10/2007
Strategisches Tuning – Teil 1
Dynamic Sampling
Hora
SQL*Navigator
SQL Test Builder
OraSRP
DD - Views
SQL Access Advisor
©Database Consult GmbH - Jachenau
Folie 10 von
5
Zielsetzung
Strategisches Tuning – Teil 1
Akzeptanz
Akzeptable Antwortzeiten
SLA-Erfüllung
Skalierbarkeit
Minimierung
des
Ressourcenverbrauchs
+ Angepasstes HW Sizing
Funktionale Vollständigkeit
©Database Consult GmbH - Jachenau
10/2007
Folie 11 von
... nach dem Lehrbuch
– 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“
• In der Praxis kommen Systemdesign und Approval oft zu kurz
(externe Zukäufe, Termindruck ...)
Strategisches Tuning – Teil 1
• Tuning ist Teil des gesamten Oracle-Management Prozesses
– erhöhter Tuningbedarf bei kritischen Anwendungen
Monitoring
Tuning
Systemdesign
Approval
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 12 von
6
Aktionspyramide
Lastprofile
reaktiv
Strategisches Tuning – Teil 1
Tuning
Monitoring
Reporting
Optimierte Implementierung
Datenbankdesign
Systemanalyse
proaktiv
„performance
engineering“
©Database Consult GmbH - Jachenau
10/2007
Folie 13 von
Instrumentarium
– Monitoring, Tuning, Ticketing
– Redundanzen können optimierend wirken (nicht bei Ticketing)
• Know-How
– gezielte Aus- und Weiterbildung
– Neugier und Experimentierfreude
Strategisches Tuning – Teil 1
• Bewusste Werkzeugwahl
• IST-Bewusstsein
– Applikation (Architektur, Abhängigkeiten)
– Datenbank (Baselines, Abhängigkeiten)
• Teamfähigkeit
– DB, Netzwerk, Applikation, OS, Storage
– angepasste Organisationsstrukturen, Public Relations
– angepasste Arbeitsabläufe mit problemorientierter
Standardisierung, Eigenverantwortlichkeit
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 14 von
7
Strategie
Strategisches Tuning – Teil 1
• Reaktion bei Abweichung von
Zielvorgaben/Erwartungen
– 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
©Database Consult GmbH - Jachenau
10/2007
Folie 15 von
Strategie
Strategisches Tuning – Teil 1 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
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 16 von
8
Fokussierung
©Database Consult GmbH - Jachenau
10/2007
Strategisches Tuning – Teil 1 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 17 von
Fokussierung
C D S S S C D D S C
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 %
10/2007
Database Consult GmbH
Strategisches Tuning – Teil 1
Session 1
Session 2
C = 25 %
D = 75 %
S=0%
©Database Consult GmbH - Jachenau
Folie 18 von
9
Strategisches Tuning – Teil 1
Teil 1: Werkzeuge, Lastprofile
Informationsquellen 10g/11g
©Database Consult GmbH - Jachenau
10/2007
Folie 19 von
Übersicht
Strategisches Tuning – Teil 1
• Klare, effizient abrufbare Informationen im
Tuningkontext:
• Applikation
– Systemarchitektur (Diagramm)
– Verantwortlichkeiten/Personen
• Datenbank
– Kennzahlen, Metriken, Services – aktuell und historisch
– Schema-Informationen/Abhängigkeiten (Diagramm)
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 20 von
10
Beispieldiagramm Systemarchitektur
Strategisches Tuning – Teil 1
10/2007
©Database Consult GmbH - Jachenau
Folie 21 von
Beispieldiagramm Systemarchitektur
Strategisches Tuning – Teil 1
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 22 von
11
Schemadiagramm
Strategisches Tuning – Teil 1
10/2007
©Database Consult GmbH - Jachenau
Folie 23 von
©Database Consult GmbH - Jachenau
Folie 24 von
Infrastruktur
Strategisches Tuning – Teil 1
10/2007
Database Consult GmbH
12
Monitoring
Strategisches Tuning – Teil 1
10/2007
©Database Consult GmbH - Jachenau
Folie 25 von
©Database Consult GmbH - Jachenau
Folie 26 von
Strategie
Strategisches Tuning – Teil 1
10/2007
Database Consult GmbH
13
Analyse
Strategisches Tuning – Teil 1
©Database Consult GmbH - Jachenau
10/2007
Folie 27 von
Strategisches Tuning – Teil 1
Teil 1: Werkzeuge, Lastprofile
Erstellung von Lastprofilen
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 28 von
14
Session Lokalisierung
Strategisches Tuning – Teil 1
10/2007
©Database Consult GmbH - Jachenau
Folie 29 von
©Database Consult GmbH - Jachenau
Folie 30 von
Tracefiles
Strategisches Tuning – Teil 1
10/2007
Database Consult GmbH
15
Identifizierung
Strategisches Tuning – Teil 1 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
10/2007
©Database Consult GmbH - Jachenau
Folie 31 von
Tracefile ermitteln und öffnen
Strategisches Tuning – Teil 1
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
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 32 von
16
Application Info
Strategisches Tuning – Teil 1 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
10/2007
Folie 33 von
Tracing <= 10g
•
•
•
•
10/2007
Database Consult GmbH
Strategisches Tuning – Teil 1
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set events
'10046 trace name context forever, level 12'
alter session set events
'10046 trace name context off';
Level 1 – wie sql_trace = true
Level 4 – 1 + Bindevariablen
Level 8 – 1 + wait events
Level 12 – 1 + BV + WE
©Database Consult GmbH - Jachenau
Folie 34 von
17
Tracing <= 10g
10/2007
©Database Consult GmbH - Jachenau
Strategisches Tuning – Teil 1
dbms_system.set_bool_param_in_session
(72, 166, 'timed_statistics', true);
dbms_system.set_int_param_in_session
(72, 166, 'max_dump_file_size', 2147483647);
dbms_system.set_ev(72, 166, 10046, 8, '');
/* Tracing ausschalten */
dbms_system.set_ev(72, 166, 10046, 0, '');
-- ################
DBMS_SUPPORT.START_TRACE_IN_SESSION
(SID => 12, SERIAL => 77
, WAITS => TRUE, BINDS => TRUE);
Folie 35 von
Tracing <= 10g
Strategisches Tuning – Teil 1
# ORADEBUG über SQLPlus
# für Prozess
ORADEBUG SETORAPID 8 –- verifizieren über v$process, oder
ORADEBUG SETMYPID –- fuer eigenen Prozess
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
# für die eigene Session
ORADEBUG SETMYPID; -- Debug current session.
ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
ORADEBUG TRACEFILE_NAME; -- anzeigen current trace file
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF
# weitere Alternativen
EXECUTE dbms_system.set_ev (9,29,10046,8,'');
EXECUTE dbms_system.set_ev (9,29,10046,0,'');
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 36 von
18
Tracing <= 10g
Strategisches Tuning – Teil 1
-- Logon-Trigger
CREATE OR REPLACE TRIGGER b4711_logon
AFTER LOGON ON b4711.SCHEMA
BEGIN
dbms_session.set_sql_trace (TRUE);
END;
CREATE OR REPLACE TRIGGER b4711_logoff
BEFORE LOGOFF ON b4711.SCHEMA
BEGIN
dbms_session.set_sql_trace (FALSE);
END;
-- Beliebig aktivieren
ALTER TRIGGER b4711 _logon ENABLE;
ALTER TRIGGER b4711 _logon DISABLE;
©Database Consult GmbH - Jachenau
10/2007
Folie 37 von
Tracing <= 10g
Strategisches Tuning – Teil 1
•
•
•
•
Dauerhaftes Setzen der Events
Mehrfache Events immer in Folge oder über ":"!
Nicht für immediate dumps geeignet
Alert-Datei zeigt aktive Events
# SQL-Trace aktiviert am 12.08.2003 GU
EVENT='10046 trace name context forever, level 12'
# Interna des CBO-Optimizers aktiviert am 1.9.03
EVENT='10053 trace name context forever, level 1'
# alternativ mit :, \ für Zeilenumbruch
EVENT="\
10046 trace name context forever, level 12:\
10053 trace name context forever, level 1"
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 38 von
19
Tracing <= 10g
Strategisches Tuning – Teil 1
• Lesen eingestellter Events und Level
• Zeigt Werte nur für die eigene Session
declare event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event
'||to_char(i)||' set at level '
|| to_char(event_level));
end if;
end loop;
end;
10/2007
©Database Consult GmbH - Jachenau
Folie 39 von
Events – Auswahl
Strategisches Tuning – Teil 1
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 40 von
20
Tracing >= 10g
Strategisches Tuning – Teil 1
•
•
•
•
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
10/2007
Folie 41 von
Tracing
Strategisches Tuning – Teil 1
• 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]
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 42 von
21
Strategisches Tuning – Teil 1
Bis nachher...
nachher...
Bis
10/2007
Database Consult GmbH
©Database Consult GmbH - Jachenau
Folie 43 von
22