Zugriffspfad

DB2
SYSUSERDAY 2009
Methoden und Techniken
zur Stabilisierung von
Datenbank Zugriffspfaden
Thomas Baumann
Die Mobiliar, Bern, Schweiz
Agenda
Überraschende Mess Ergebnisse
• V8  V9 CM Migrations Beispiel
• Static SQL
Zugriffs
Pfad
Fallback
... und
warum„s dies
ausgerechnet
für V9 gibt
• Reduziere Faktoren für Zugriffspfad
Instabilität der Schlüssel zur Zugriffspfad Stabilität
• Kleine Ueberlebenshilfe für Dyn SQL
Zugriffspfad
was Fallback
schiefgehen kann, wird schiefgehen…
• Applikations Profile the new kid on the block
DB2
SYSUSERDAY 2009
2
Swiss Mobiliar: Key Facts at
a Glance
(2008-12-02 @ 10:40 AM)
DB2P Data Sharing Group
(V8 New Function Mode)
DB1P
DB2P
DB3P
31K
GetPage
Requests
Bpools
(14 GByte)
Global Dyn Stmt Cache
(640 MByte)
IMS/TM
Application
Server
Services
6485 static SQL Queries*) / second
(+10.6%)
601 dynamic SQL Queries*) / second (+34%)
*) 1
DB2
SYSUSERDAY 2009
open / n fetch / 1 close = 1 Query
3
Disclaimer
•
•
The Information contained in this presentation has not
been submitted to any formal Swiss Mobiliar or other
review and is distributed on an „as is„ basis without any
warranty either expressed or implied. The use of this
information is the user„s responsibility.
The procedures, results and measurements presented in
this paper were run in either the test and development
environment or in the production environment at Swiss
Mobiliar in Berne, Switzerland. There is no guarantee that
the same or similar results will be obtained elsewhere.
Users attempting to adapt these procedures and data to
their own environments do so at their own risk. All
procedures presented have been designed and developed
for educational purposes only.
DB2
SYSUSERDAY 2009
4
V8  V9 CM Migration
• Wieviele Queries ändern Zugriffspfad?
• Statistik über Zugriffs Pfad Aenderungen
Die DB2
Migrate
Optimizer
Runstats
Rebind
Runstats
Rebind
to V9 CM
Engine
ändert
im CM
Compare Access Paths
(conversion
•Vergleich von 22793 Queries
mode)
•4855 Queries mit geändertem Zugriffspfad (21.3%)
•Query zur Ermittlung dieser Daten in Notizenseiten
DB2
SYSUSERDAY 2009
5
V8  V9 CM Migration
• Einige detailliertere Zahlen
• Unterschiede in PREFETCH („S‟ vs. ‟D‟) ausgeblendet
• V9 CM: Wechsel von sequential zu dynamic
• Noch immer 17.7% aller Queries haben geänderten
Zugriffspfad in V9 CM
• Neue I/O-Kosten Berechnungsformel für Indexes
ausgeblendet
•
•
•
•
V9: DATAREPEATFACTORF
setze Zparm STATCLUS=STANDARD
wiederhole Runstats+Rebind
14.7% aller Queries mit geändertem Zugriffspfad
DB2
SYSUSERDAY 2009
6
V8  V9 CM Migration
• Wieviele verschiedene Zugriffspfade
existieren für die 22793 Queries?
• V8: 4535 Zugriffspfade • V9: 4546 Zugriffspfade
3581 Zugriffspfade in V8 und V9 benutzt
954 Zugriffspfade in V9 nicht mehr benutzt
965 neue Zugriffspfade in V9 CM
• Siehe Notizenseiten für Detail Queries
• Notwendigkeit für Fallback Möglichkeit!
DB2
SYSUSERDAY 2009
7
V8  V9 CM Migration
• Zugriffspfad Fallback Vorbereitung (1/2)
• Plan Management (Zparm/Bind PLANMGMT)
• BASIC
Rebind
active copy
previous copy
• EXTENDED
previous copy
Rebind
active copy
original copy
nur falls es noch keine original copy gibt
DB2
SYSUSERDAY 2009
8
V8  V9 CM Migration
• Fallback auf Package Copy
• SWITCH (PREVIOUS)
Rebind
active copy
• SWITCH (ORIGINAL)
Rebind
previous copy
previous copy
active copy
original copy
DB2
SYSUSERDAY 2009
9
V8  V9 CM Migration
• Zugriffspfad Fallback Vorbereitung(2/2)
• Optimizer Hints
• kann nützlich sein nach neuem BIND
• aufgepasst auf neue Queryno
• Notizenseite enthält Check-Query zur Identifikation
von “verlorenen” Optimizer Hints nach Bind oder
Rebind von Packages:
t1
t2
Package p ohne
mit Opthint Opthint
DB2
SYSUSERDAY 2009
t3
Package p
ohne
Opthint
t4
mit
opthint
Zeit
10
V8  V9 CM Migration
• Für welche Art Queries ändert der
Zugriffspfad? (1/3)?
• 1. Ansatz: Queries mit hohem vorausgesagtem
CPU-Verbrauch (gemessen @ V8)?
% Queries mit
geändertem
Zugriffspfad
70
50
30
10
1
Vorausgesagter
CPU Verbrauch
in msec
2-10 11-100 101-1K >1K
• Nützen diese Daten wirklich etwas?
DB2
SYSUSERDAY 2009
11
V8  V9 CM Migration
• Für welche Art Queries ändert der
Zugriffspfad (2/3)?
• 2.Ansatz: Queries mit kleinem Unterschied bei
den vorhergesagten Kosten zwischen Original
Vorhersage und Vorhersage, bei der der erste
selektierte Index nicht existieren würde?
• Gute Idee, aber schwierig zu berechnen!
• Besser wäre reduzierte Anzahl Query
Kandidaten
DB2
SYSUSERDAY 2009
12
V8  V9 CM Migration
• Für welche Art Queries ändert der
Zugriffspfad (3/3)?
• 3. Ansatz: Queries mit Unterschieden zwischen
CPU-Verbrauchsvorhersage und –messung
• Verbrauch höher als Vorhersage
• Verbrauch kleiner als Vorhersage
% Queries mit
geändertem
Zugriffspfad
DB2
SYSUSERDAY 2009
0.1
1
Verhältnis
10 vorhergesagte/gemessene
CPU
13
V8  V9 CM Migration
• EinExperiment : “Reduzierte” Statistiken
• SYSINDEXES.NLEVELS
• Gleicher Wert für alle Indexes einer Tabelle
• SYSINDEXES.CLUSTERRATIOF = 0.95
• SYSINDEXES.DATAREPEATFACTOR
• Zparm STATCLUS=STANDARD vor Runstats
• Zparm NPGTHRSH = -1
• NPGTHRSH = 100 ev. besserer Kompromiss
• Wiederhole V8V9 CM Migrations Szenario:
• Queries mit geändertem Zugriffspfad: 2089 (9.1%)
DB2
SYSUSERDAY 2009
14
Dynamic Queries
• Fokus auf Zugriffspfad statt auf Query
Text
• Plan, Package, Authid oft nicht nützlich zur
Identifikation/Gruppierung von Queries
• Viele gleiche oder ähnliche Queries
• Gruppiere Queries anhand von benutztem
Zugriffspfad
DB2
SYSUSERDAY 2009
15
Das M3 Framework
Nur für Zeilen mit
Global Dynamic
STAT_EXEC > 0
Statement Cache
-START
TRACE(MON)
CLASS(1)
IFCID(318)
Dt
EXPLAIN
STMTCACHE
ALL
DSN_STATEMENT_
CACHE_TABLE
EXPLAIN
STMTCACHE
STMTID x
PLAN_
TABLE
Zeit
DSN_STATEMNT_
TABLE
Für den Oracle DBA:
Für den Oracle DBA:
V$SQLAREA ist V$SQL_PLAN
dasselbe
ist dasselbe wie die
Zugriffs wie
Pfad Aggregation
(Performance
Daten gruppiert
die DSN_STATEMENT_CACHE_TABLE
PLAN_TABLE
nachnach Zugriffspfad)
EXPLAIN STMTCACHE STMTID x.
Zugriffspfad Gruppierung
bereits berechnet:
Summary Table
PLAN_HASH_VALUE Kolonne.
DB2
SYSUSERDAY 2009
16
Die M3 Summary Tabelle
Advanced Query Tuning
M3
V2.2
• Lerne deinen Workload kennen
Trace start
2007/9/24 10:11 am
Cache snapshot 2007/9/24 10:27 am
Dynamic SQL Performance Diagnosis for DB2P
• Beispiel 13.01.2009:
Emergency Tuning
ET01
ET02
Refresh
No of Stmt Groups with high CPU / Statement Ratio
% of Unreferenced Cached Statements
Overall
% Change
0
0%
0
56%
-5%
0.59
• 199277Performance
Queries
durchgeführt
Control: SQL Statement
No of Stmt Groups with Inefficient Predicates
No of Stmt Groups with Resource
Intensive Sorts
• 1009 verschiedene
Zugriffspfade
verwendet
No of Stmt Groups with Intensive Locking
Critical
Applications
% Change
0
0%
66%
0%
0.66
Overall
% Change
Crit. Appl.
% Change
PC01
0
0%
0
0
0%
PC02
0
0%
0
0
0%
0
-100%
1
0
-100%
0
0%
0
0
0%
Overall
% Change
44
-10%
49
23
-12%
26
0
0%
0
0
0%
0
Overall
% Change
Crit.Appl.
% Change
2264
11%
2091
4%
Overall
% Change
Crit. Appl.
% Change
PC03
Summary
Table
PC04
No of Stmt Groups with Optimizer Challenges
Performance Control: Memory Management
Dyn Statement Cache Min Residency Time (min)
Local Buffer Pools Min Residency Time (min)
No of Failed Writes to Group Buffer Pool
No of Sync I/Os caused by Cross-Invalidation (per min)
Performance Control: Data Management
Getpage requests / second
während 15-min Zeitintervall.
PC05
PC06
PC07
PC08
PC09
Performance Management
PM01
PM02
PM03
PM04
PM05
PM06
PM07
PM08
PM09
Tspce/Index Runstats Efficiency
Tspce/Index Reorg Efficiency
Optimization Potential
Unused Indexes
Total CPU Time per second
Total Sync I/O Time per second
Total Elapsed Time per second
CPU Seconds / Processed Row
Elapsed Seconds / Processed Row
DB2
SYSUSERDAY 2009
DynSQL Performance Index (100=31.08.2007)
DSPI
n/a
n/a
62%
20
0.08500
n/a
n/a
3%
11%
4%
1.59921
1.84253
0.00004
0.00096
0.234
206.19
2047
3
0.6
18
0.08183
n/a
n/a
57%
n/a
0.07147
n/a
n/a
2%
n/a
-7%
11%
1.44049
1.38093
8%
11%
-50%
-45%
1.6585
0.00008
0.00173
1.59572
0.00007
0.00166
7%
-13%
3%
92.59%
107.058
118.32
7.52%
17
0.234
M3 Ressourcen Verbrauch
Dt
EXPLAIN STMTCACHE ALL
(23894 Statements)
Fuel Consumption
EXPLAIN
City
13 mpg
STMTCACHE
Highway
19 mpg
STMTID
x (6306
mal
Tank capacity
durchgeführt) *)
approximate 16.6 gal
*) für
DB2
SYSUSERDAY 2009
alle Statements mit stat_exec > 0
18
Risiko Minimierung
• Analysiere Zugriffs Pfade
• Zugriffs Pfade mit
• CPU Vorhersage >> CPU Messung
vorhergesagte CPU (dsn_statemnt_table)
>2
gemessene CPU (dsn_statement_cache_table)
& Anzahl Ausführungen Zugriffspfad > 1 / sec
Klare Entscheidungs Hilfe für Optimizer:
1 Index mit 3 Spalten anstelle
3 Indexes mit je 1 Spalte
Bei der Schweiz. Mobiliar (Messung am 13.01.09), identifizierte
DB2dieses Query 44 verschiedene Zugriffspfade (4.3% aller dynamic
SQL Zugriffspfade),
welche genauer untersucht werden mussten.
SYSUSERDAY
2009
19
Risiko Minimierung
• Vermeide Zugriffs Pfad Konflikte
• Falls nicht schon beim DB Design zumindest durch
Statistiken
• Query Pre-Optimization
• Katalog Statistik Updates auf SYSINDEXES
• NLEVELS
• CLUSTERRATIOF / DATAREPEATFACTORF
• Query Re-Optimization
• REOPT(AUTO): Achtung bei OO-Applikationen
DB2
SYSUSERDAY 2009
20
Dyn SQL AccessPath Fallback
• Kein Fallback, Opt Hints kaum nutzbar
• Bereite Notfall Catalog Updates vor:
• Re-Update ab SYS[INDEXES¦TABLES]_HIST
• Direkte Updates von
COLCARDF/FIRSTKEYCARDF
NLEVELS / NLEAF
CARDF
• Update Kontrolle
• Runstats
• Dynamic Statement Cache leeren:
RUNSTATS … UPDATE NONE REPORT NO
DB2
SYSUSERDAY 2009
21
Dyn SQL AccessPath Fallback
Table
Column
Defaultwert
(o. Runstats)
vom Optimizer benutzt bei
SYSTABLES
CARDF
NPAGES
10000
Indexscan mit tiefer Clusterratio
1+ CARDF/20 Indexscan mit hoger Clusterratio
SYSTABLESPACE
NACTIVEF
1 + CARDF/20 tablespace scan
SYSCOLUMNS
COLCARDF
HIGH2KEY
LOW2KEY
25
-----
DB2
SYSUSERDAY 2009
FF für „=„ und Range Abfragen
FF für Range Abfragen
FF für Range Abfragen
22
Dyn SQL AccessPath Fallback
Table
Column
Defaultwert
(o. Runstats)
SYSINDEXES
FIRSTKEYCARDF
25
FULLKEYCARDF
25
NLEAF
CARDF/300
NLEVELS
0
CLUSTERRATIOF/
0
DATAREPEATFACTORF
SYSCOLDIST(STATS)
NAME / NUMCOLUMNS --COLVALUE
--FREQUENCYF
--DB2
SYSUSERDAY 2009
vom Optimizer benutzt bei
FF für matching Indexscan
FF für matching IndexScan
Schätzung Index I/O Aufwand
Schätzung Index I/O Aufwand
Schätzung Tspce I/O Aufwand
Daten nicht gleichverteilt
Daten nicht gleichverteilt
Daten nicht gleichverteilt
23
Dyn SQL AccessPath Fallback
COLCARDF Beispiel für Range Abfragen
COLCARDF
>= 100M
>=
10M
>=
1M
>= 100,000
>= 10,000
>= 1,000
>=
100
>=
2
=
1
<=
0
Filter-Factor
für </>[=]
1/10,000
1/3,000
1/1,000
1/300
1/100
1/30
1/10
1/3
1
1/3
DB2
SYSUSERDAY 2009
Filter-Factor
für LIKE/BETWEEN
3/100,000
1/10,000
3/10,000
1/1,000
3/1,000
1/100
3/100
1/10
1
1/10
24
Applikations Profile
• Die Idee
• Applikations-abhängige Optimierungs Regeln
• z.B: “Access Path Stability” Profil vs.
“Access Path Peak Performance” Profil
DB2
SYSUSERDAY 2009
25
DB2
SYSUSERDAY 2009
26
DB2
SYSUSERDAY 2009
27
Choose sport profile
DB2
SYSUSERDAY 2009
28
DB2
SYSUSERDAY 2009
29
DB2
SYSUSERDAY 2009
30
DB2
SYSUSERDAY 2009
31
DB2
SYSUSERDAY 2009
32
DB2
SYSUSERDAY 2009
33
DB2
SYSUSERDAY 2009
34
DB2
SYSUSERDAY 2009
35
Choose comfort profile
DB2
SYSUSERDAY 2009
36
DB2
SYSUSERDAY 2009
37
DB2
SYSUSERDAY 2009
38
DB2
SYSUSERDAY 2009
39
DB2
SYSUSERDAY 2009
40
DB2
SYSUSERDAY 2009
41
DB2
SYSUSERDAY 2009
42
DB2
SYSUSERDAY 2009
43
DB2
SYSUSERDAY 2009
44
Sport profile’s risk
DB2
SYSUSERDAY 2009
45
DB2
SYSUSERDAY 2009
46
DB2
SYSUSERDAY 2009
47
DB2
SYSUSERDAY 2009
48
DB2
SYSUSERDAY 2009
49
Comfort profile’s risk
DB2
SYSUSERDAY 2009
50
DB2
SYSUSERDAY 2009
51
Switch to your
favourite profile
DB2
SYSUSERDAY 2009
52
Applikations Profile
“If you compare the driver‟s intended direction (by
steering angle) to the vehicle‟s actual direction
•measuring
Die Idee
(by measuring vehicle rotation and individual road wheel
• Applikations-abhängige
Optimierungs
Regeln
speeds)
and detect when the vehicle
is not going
where
• z.B:
“Access Path
vs. control)
the driver
is steering,
DSCStability”
(dynamicProfil
stability
intervenes and
applies
thePeak
brakes
to individualProfil
wheels
“Access
Path
Performance”
asymmetrically.”
• Vergleiche mit Mobil Telefon Profil!
“If •you
compare the
estimated
access
Vergleiche
mitoptimizer‟s
Ein-/Ausschalten
von
ESC path
cost (elektronische
(by measuring dsn_statemnt_table.procsu)
to the
Stabilitätskontrolle) bei PKW
query‟s
actual
performanc
numbers
(heisst
Dynamic
Stability Control
bei BMW)
(dsn_statement_cache.stat_cpu) and detect when the
runtime numbers are different from what was predicted,
the DBA intervenes and applies corrections to the access
path or the statistics collected.”
DB2
SYSUSERDAY 2009
53
Applikations Profile
• Verschiedene Statistik-Pakete für
verschiedene Anwendungen
• Statische Queries
• Aggressive, genaue, aktuelle Statistiken
• Zparm NPGTHRSH = 0 (oder kleiner pos. Wert)
• Wiederherstellung vor Rebind
• Dynamische Queries
•
•
•
•
Balanciere NLEVELS
CLUSTERRATIOF = 0.95
DATAREPEATFACTORF = -1
Zparm NPGTHRSH = -1
DB2
SYSUSERDAY 2009
54
Applikations Profile
• Applikations Profile im
Tagesgeschäft
• Schalte um auf peak performance profile
(korrekte Statistiken) vor Rebind
• Update aus SYSINDEXES_HIST
• SET SYSPARM LOAD(name)
• Schalte um auf access path stability profile
• Nach Runstats
• Nach (Re)bind
• Nicht vergessen: Leere dyn. SQL Stmt Cache
DB2
SYSUSERDAY 2009
55
Applikations Profile
in DB2 V9.1
• IP-address abhängige ZPARMS
• NPAGES Threshold
• -1:Verwende Index wann immer möglich.
• 0:Selektiere Zugriffspfad kosten-basiert
• n:Verwende Index bei Tabellen mit < n pages,
berechne kosten-basiert für Tabellen > n.
• Ein-/Ausschalten Star Joins
• DISABLE: Star Join Verarbeitung augeschaltet
• ENABLE: Star Join Verarbeitung möglich
DB2
SYSUSERDAY 2009
56
Applikations Profile
in DB2 V9.1
• IP-address abhängige ZPARMS im
Tagesgeschäft
• Create a profile
• NPAGES=-1 für selektierte IP-Addressen
• NPAGES= 0 für alle übrigen
• Start Profile
• De-Aktiviere spezifisches Profil
• Stopp alle Profile
DB2
SYSUSERDAY 2009
57
Rückblick
• V8  V9 CM Migration
• Vorbereitung für Access Path Fallback!
• Risiko Minimierung
• Klare Entscheidungshilfen für Optimizer
• Dynamic Query Access Path Fallback
• Bereit sein für den Notfall
• Neuer Ansatz: Applikations Profile
DB2
SYSUSERDAY 2009
58
Methoden und Techniken zur Stabilisierung
von Datenbank Zugriffspfaden
Thomas Baumann
Swiss Mobiliar, Switzerland
[email protected]
DB2
SYSUSERDAY 2009
59