DB2 for z/OS - SQL und spezielle Tips

Transcrição

(*)
IBM DB2 for z/OS
DB2 Version 9
25+ SQL Performance Tips für Entwickler
(DB2_9_25+Tips_for_SQL_Performance.ppt)
(*) ist eingetragenes Warenzeichen der IBM International Business Machines Inc.
Dez 2010
1
DB2 Version 9 (Überblick)
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SHRLEVEL(REFERENCE) for REORG of LOB TS
of LOB table spaces
Online RENAME COLUMN / INDEX
Online CHECK DATA and CHECK LOB
Faster REORG by intra-REORG parallelism
More online REORG by eliminating BUILD2 phase
LOB Lock reduction
Skipping locked rows option
Online REBUILD INDEX
Change SCHEMA & VCAT
Tape support for BACKUP / RESTORE SYSTEM
utilities
Recovery of individual tablespaces and indexes from
volume-level backups
Enhanced STOGROUP definition
Utility TEMPLATE switching
Conditional restart: automatic search for appropriate
checkpoint
CLONE Table: fast replacement of one table with
another Buffer management by WLM
Global query optimization
Generalizing sparse index / inmemory data caching
Optimization Service Center
Autonomic reoptimization
Logging enhancements
LOBs Network Flow Optimization
Faster operations for variablelength rows
Dez 2010
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
NOT LOGGED table spaces
Index on expressions
Universal Table spaces
Partition-by-growth table spaces
APPEND option at insert
Autonomic index page split / Index page sizes 8K, 16K, 32K
Support for optimistic locking
Faster and more automatic DB2 restart
MODIFY RECOVERY enhancements
RLF improvements for remote application servers such as SAP
Preserving consistency when recovering individual objects to a
prior point in time
DECIMAL FLOAT, BIGINT, VARBINARY, BINARY
TRUNCATE TABLE statement
MERGE statement
FETCH CONTINUE
ORDER BY and FETCH FIRST n ROWS in sub-select and full-select
ORDER OF extension to ORDER BY
Various scalar functions
XML support in DB2 engine
Native SQL Stored Procedures, able to use zIIP
SELECT FROM
UPDATE/DELETE/MERGE
IPv6 support
Unified Debugger
Automatic creation of database objects
Utilities CPU reduction
2
DB2 9 (Überblick/Kompatibilität)
DB2 V9 – Unterstützung der nächsten Welle von Web Anwendungen
XML Datenspeicher
SQL Portierbarkeit in der DB2 Familie
DDF Erweiterungen für verteilte Anwendungen
Dez 2010
3
DB2 9 WS - Ziele
DB2 V9 – SQL-Tuning für Entwickler
Nach dem WS sollten Entwickler in der Lage sein:
Die SQL Optimization besser zu verstehen
“stage 1” und “stage 2” abschätzen zu können und
„indexable“ gegen „non indexable“ SQL Prädikate abzuwägen
SQL Standards und Richtlinien bzgl. der Performance zu unterstützen und im eigenen
Unternehmen zu etablieren
Die Top 25 Tuning Tips bei allen Queries, Programmen und Applikationen umzusetzen
Die Top 10 Schritte zum Tuning einer Query, eines Programms, bzw. Einer Applikatione
nachzuvollziehen
Dez 2010
4
DB2 9 SQL_Performance
SQL_Performance(Einführung)
(Einführung)
SQL-Tuning für Entwickler - Erfahrungen
Der Grossteil der Performanceprobleme in Applikationen sind begründet in schlecht kodierten
Programmen bzw. Ungünstig geschriebenem SQL.
Schlecht performantes SQL ist zu 80% für ungenügende Antoert- und Laufzeiten verantwortlich
Die meisten Entwickler benötigen Aus- und Weiterbildungt für das Lesen, Analysieren und Reagieren
auf den DB2 Explain Output.
Es gibt viele SQL Entwickler in der IT Industrie, aber nur weniger als 10% kennen die PerformanceAnforderungen zur SQL-Programmierung und wie die Probleme zu lösen sind.
Entwickler sollten immer mit dem Gedanken an Performance programmieren
Programmierer sollten mit ZWEI Zielsetzungen ihre Arbeit tun:
Die richtigen Resultate für ihre Datenanforderungen zu erhalten und
Die Resultate so schnell und effizient wie nur möglich zu erhalten
Viele Programmierer haben das zweite Ziel aus den Augen verloren:
Weil sie nicht wissen, wie sie ihre Programme schneller machen können
Weil sie die Schuld bei anderen Komponenten ihrer Umgebung suchen (Datenbank, Netzwerk,
TCP/IP, “workstation”, Betriebssystem etc.), oder
Weil sie denken, die Verarbeitung sei bei der Menge an Daten doch ganz gut ….
Dez 2010
5
(Einführung)
Der DB2 Optimizer
DB2‟s Zugriffspfade werden auf vier verschiedene Weisen beeinflusst:
“rewriting” einer Query in ein effizienteres Format
“creating”, “altering” bzw. “dropping” von Indexes.
“updating” der Katalogstatistiken, die DB2 zur Schätzung der Zugriffskosten heranzieht
über den Einsatz von „Optimizer Hints“
Dez 2010
6
(Einführung)
SQL-Tuning für Entwickler – Stage 1 vs Stage 2
Prädikate
SQL-Query
Stage 1 (DB2 Data Manager)
ist dafür verantwortlich, Daten, die in Pages gespeichert
sind, in ein „result set” von “rows and columns” zu
transferieren. Prädikate, die auf eine einfache, direkte Art
und Weise geschrieben wurden, können normalerweise
vom Daten-Manager mit relativ wenig Aufwand geschätzt
werden.
RDM (DB2 Relational Data Manager)
Stage1
Zugriff auf Tabelle über
über IX-Baum falls IX-able
Stage 2 (Relational Data Services)
behandeln eher komplexere Prädikate, DatenTransformationen und Berechnungen. Die Stage 2
Prädikate sind viel teurer aufzulösen für DB2 wie Stage 1.
Dies wegen zusätzlicher Verarbeitung und zusätzlichen
Codepfaden. Zusätzlich dazu kann RDS keine effiziente
Verwendung von Indexen garantieren.
Anm.:
Dez 2010
Stage 1 = Sargeable
Stage 2 = Non Sargeable. Die Verarbeitung in
diesem Teil des RDS sind teuerer als in RDM
Stage 1.
“Indexable predicates” werden zuerst
ausgewählt, dann Stage 1 und dann Stage 2
RDS (Relational Data Services)
Stage 2
bestimmte
Daten-pages
der Tabelle
über RID's
einige
IX-pages
der
Tabelle
BM (Buffer Manager)
Phys.
I/O
7
SQL_Performance--Tips
Top 25+ Tuning Tips: Tip #1
Nutzen Sie Visual Explain im Optimization Service Center oder Data Studio, um alle “stage 2”
Prädikate erkennen zu können. Die Filterfaktoren sollten dann ebenfalls beachtet werden.
•
•
Dez 2010
Die FETCH Box zeigt alle Prädikate, die mit dem IX nichts zu tun haben….
Die IXSCAN Boxen zeigen den “matching index” und “screening index”-Prädikat Informationen
8
Top 25+ Tuning Tips:
Tip #2/1: Entfernen Sie alle / einige “Scalar functions”, die auf Prädikatsspalten kodiert wurden…
Beispiel:
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE YEAR(HIREDATE) = 2005
Sollte wie folgt umgeschrieben werden:
FROM EMPLOYEE
WHERE HIREDATE BETWEEN ‘2005-01-01’ and ‘2005-12-31’
Tip #2/2: Entfernen Sie alle / einige mathematischen Berechnungen in den Prädikaten…..
Beispiel:
FROM EMPLOYEE
WHERE SALARY * 1.1 > 50000.00
SELECT EMPN, LASTNAME
FROM EMPLOYEE
WHERE HIREDATE – 7 DAYS > ?
Sollte wie folgt umgeschrieben werden:
FROM EMPLOYEE
WHERE SALARY > 50000.00 / 1.1
SELECT EMPN, LASTNAME
FROM EMPLOYEE
WHERE HIREDATE > DATE(?) + 7 DAYS
DB2 V9: Es gibt jetzt Index on expressions (als Alternative)!
Dez 2010
9
Tip #3: Vermeiden Sie DISTINCT, wo immer es möglich ist…
Sollten Duplikate aus dem Ergebnis einer Abfrage entfernt werden sollte man folgendes versuchen:
•
„Group By‟, was Indexe verwenden kann, um einen SORT wegen Eindeutigkeit zu vermeiden…
•
Umformulieren der Query mit “IN”- bzw. “EXISTS” Klausel. Das funktioniert aber nur, wenn die
Tabelle, die diese doppelten Werte verursacht – z.B. wegen einer “one to many relationship” diese Daten nicht als Teil des “result set” zurückliefern muss.
VOR DB2 V9 verursachte das Schlüsselwirt DISTINCT fast immer einen SORT. Jetzt kann DB2
die Vorteile eines Index zur Vermeidung von SORTs nutzen (“unique” oder “non unique”).
Tip #4: Minimieren Sie die Anzahl SQL “requests” an DB2..
Dies ist ein grosser Fortschritt beim Tunen der Performance eines Programms – besonders für Batch
Programme. Jedes Mal, wenn ein SQL-Call an den “database manager” erfolgt, entseht “overhead”beim
Senden des SQL Statements an DB2. Es wird von einem “address space” des Betriebssystems in den
“adress space” von DB2 zur Ausführung transferiert.
Aus diesem Grunde sollten die Entwickler folgende Faktoren minimieren:
•
Anzahl der OPEN/CLOSE Aktionen auf den Cursors
•
Anzahl von “random SQL” Aktionen (bekannt als “synchronized reads” in den DB2 Monitoren).
V8: Multi Row FETCH, UPDATE, INSERT. RECURSIVE SQL. SELECT FROM INSERT.
V9: „UPSERT‟ Verarbeitung. FETCH First / Order By in Subqueries. MERGE.
Dez 2010
10
DB2 V9 – SQL Erweiterungen
MERGE
• UPDATE und INSERT Operation
• Für OLTP Anwendungen wie SAP
MERGE
INTO account AS T
USING VALUES (:hv_id, :hv_amt) FOR 5 ROWS AS S(id,amt)
ON T.id = S.id
WHEN MATCHED THEN
UPDATE SET balance = T.balance + S.amt
WHEN NOT MATCHED THEN
INSERT (id, balance) VALUES (S.id, S.amt)
NOT ATOMIC CONTINUE ON SQLEXCEPTION
MR Update ?
MERGE
INTO account AS T
USING VALUES (:hv_id, :hv_amt) FOR 5 ROWS AS S(id,amt)
ON T.id = S.id
WHEN MATCHED THEN
UPDATE SET balance = T.balance + S.amt
NOT ATOMIC CONTINUE ON SQLEXCEPTION
Dez 2010
11
Tip #5: Ziehen Sie Stage 1- den Stage 2 – Prädikaten vor…
Versuchen Sie IMMER Stage 1 – und “indexable”-Prädikate zu schreiben. Genrell sind Stage 2 Prädikate nicht so
schnell und brauchen jedenfalls mehr CPU. (Siehe IBM SQL Reference Guide …. Dort gibt es eine Liste der
Stage 1 / Stage 2 Prädikate).
Achten Sie dabei auf die richtige DB2 Version, da diese Zurodnung release-abhängig ist.
Empfehlung: Nutzen Sie Visual Explain (V8), OSC (V9), Data Studio (V9).
IBM DB2 Manuals: Suchen Sie nach ==> “Summary of Predicate Processing”
http://www-306.ibm.com/software/data/db2/zos/v9books.html
Predicate Type
======================================
COL = value
COL = noncol expr
COL IS NULL
COL op value
COL op noncol expr
COL BETWEEN value1 AND value2
COL BETWEEN noncol expr1 AND noncol expr
value BETWEEN COL1 AND COL2
COL BETWEEN expression1 AND expression2
COL BETWEEN COL1 AND COL2
COL LIKE 'pattern'
COL IN (list)
COL <> value
COL <> noncol expr
COL IS NOT NULL
COL NOT BETWEEN value1 AND value2
Dez 2010
Indexable?
==========
Y
Y
Y
Y
Y
Y
Y
N
Y
N
Y
Y
N
N
Y
N
Stage 1?
=======
Y
Y
Y
Y
Y
Y
Y
N
Y
N
Y
Y
Y
Y
Y
Y
Bemerkungen
==================
16
9,11,12,15
20,21
13
9,11,12,13
13
9,11,12,13,15,23
6,7,11,12,13,14
10
5
17,18
8,11
8,11
21
12
Tip #6: Platzieren Sie niemals die FILTER-Logik in das Programm…
Es ist IMMER am besten, die “filtering logic” al Prädikate im SQL-Statement zu formulieren. Wenn Sie
bestimmte Prädikate nicht im SQL schreiben, so bringen Sie den DM dazu zusätzliche “rows” zu lesen, die dann
über die Programmlogik eliminiert oder übergangen werden müssen. Man nennt dies “Stage 3 Verarbeitung”
Versuchen Sie diese Methode nur, wenn Performance ein Problem darstellt und alle anderen Methoden nicht
zum gewünschten Ergebnis geführt haben.
Tip #7: Bei der Nutzung von CURSORs setzen Sie “ROWSET positioning” ein. Sie nutzen damit MR FETCH,
MR UPDATE und MR INSERT…
Seit DB2 V8 gibt es die Möglichkeit der MR Verarbeitungsmethode. Nun hat man die Chance, bei FETCH, UPDATE
und INSERT mehr als eine “row” gleichzeitig zu verarbeiten. Dies vermindert den “network traffic” und andere damit
verbundenen Kosten bei jedem Call an DB2.
Die Empfehlung lautet: Starten Sie mit “100 row fetches”, “inserts” bzw. “updates” , um dann andere “rowset”-Grössen
zu testen. Es hat sich manifestiert, dass dieses Vorgehen die “runtime on” durchschnittlich um 35% senkt.
Dazu folgende Anmerkungen:
1)
2)
3)
4)
5)
6)
7)
8)
Dez 2010
Prüfen Sie „Get Diagnostics‟ nur bei einem SQLCODE <> 0
Bei “multi-row fetch “ erhalten Sie den SQLCODE +354, der bedeutet: “EIN oder Mehrere Fehler sind aufgetreten”
Beim “non atomic MR insert” gibt es den -253 wenn eine “row” fehlerhaft ist , wenn alle fehlerhaft sind, den -254
Beim “atomic MR insert” erhält man den “echten” SQLCODE des ersten Fehlers. Dennoch muss man noch diagnostizieren,
welche “row” den Fehler ausgelöst hat.
Erhält man mehrfache Fehler, so werden sie in umgekehrter Reihenfolge angezeigt: Sie kommen aus einem “stack”.
Führt man eine MR-Operation durch und erhält SQLCODE = 0, so gibt es keinen Grund für GET DIAGNOSTICS, dere SEHR
teuer ist. So sollte man erst den check SQLCODE prüfen und nur im Fehlerfall DIAGNOSTICS aufrufen.
MR für eine kleine Menge von “rows” kann trotzdem Vorteile haben, z.B. wenn die “row” sehr kurz ist.
Die besten Resultate erzielt man jedoch mit Cursorverarbeitung und einer grossen Menge an Daten.
13
Tip #8: Nutzen Sie die Vorteile von “Scalar Fullselect” in einer SELECT-Klausel…
Oft wird zum Output eines SQL eine Kombination aus Detail- und Aggragatsinformationen benötigt. Es gibt b isher
typische Vorgehen um das zu kodieren, aber seit DB2 V8 kann mna dfas mit dem sogenannten “Scalar Fullselect” und
damit hat man einen andere Option der Indexnutzung.
Beispiel: Individueller Mitarbeiter-Report mit kumuliertem Durchschnitt pro Abteilung:
SELECT
FROM
ORDER BY
E1.EMPNO, E1.LASTNAME,
E1.WORKDEPT, E1.SALARY, (SELECT AVG(E2.SALARY)
FROM EMPLOYEE E2
WHERE E2.WORKDEPT = E1.WORKDEPT) AS DEPT_AVG_SAL
EMPLOYEE E1
E1.WORKDEPT, E1.SALARY
Früher hatte man nur die Option: „netsed table select“ oder „common table expressions“
Tip #9: Achten Sie auf Tablespace Scans …
Wenn man als Entwickler erkennt, dass ein TS Scan in der SQL Ausführung stattfindet, dann kann man folgende
Checkliste durchgehen:
• Die Prädikate sind schlecht kodiert und deshalb “non-indexable”
• Die Prädikate in der Query passen zu keinem der verfügbaren Indexe auf der Tabelle
• Die Tabelle ist klein und DB2 entscheidet sich für TS Scan , da dieser schneller sein könnte als IX Verarbeitung
• Die Katalogstatistiken sagen, dass die Tabelle klein ist, oder die Statistiken fehlen völlig
• Die Prädikate sind so geschrieben, dass DB2 annehmen muss, die Query wwürde eine so grosse mange an Daten
lesen, dass ein TS Scan die optimale Zugriffsmethode wäre
• Die Prädikate so formuliert, dass DB2 einen “non-clustered index” auswählt und die zu lesende Menge der Pages ist
gross genug, bezogen auf die Gesamtanzahl Pages in der Tabelle, dass DB2 den TS Scan anstösst.
• Die TS Datei, bzw. Die IX Dateien sind physisch in Unordnung und bräuchten einen REORG.
Dez 2010
14
Tip #10: Kodieren Sie nur die Spalten, die im SQL wirklich gebraucht werden…
Extra Spalten können folgende Auswirkungen auf die Pfadauswahl haben:
• Der Optimizer wählt NICHT „Index Only‟
• Die Kosten von (zusätzlichen) Sorts werden höher
• Die Entscheidung des Optimizer‟s für oder gegen eine bestimmte “join” Methode
Beispiel: Der Optimizer kann einen “Merge Scan Join” wählen, wenn:
- Die “qualifying rows” von beiden Tabellen “new” und
“composite tables” sehr viele sind
- Das Join Prädikat nicht genügend signifikante Filter vorsieht
Tip #11: Achten Sie auf jede beliebige Art von SORTs…
Sorts sind teuer. Manchmal löst eine SQL Query mehrfache Sortvorgänge aus, um das Resultat so zurückzubekommen, wie gewünscht.
Sehen Sie sich die Optimizing-Ergebnisse mit einem DB2 “explain
tool” an, um sehen zu können , ob SORTs abgerufen werden. Entscheiden Sie dann, ob etwas getan werden kann, diese SORTs zu
vermeiden.
Sorts werden ausgelöst von:





„ORDER BY…‟
„GROUP BY…‟
„DISTINCT‟
„UNION‟ im Gegensatz zu „UNION ALL‟
Join Verarbeitung. Achten Sie auf die “clustering” Reihenfolge
der Daten in den Tabellen
 „In List“ Subqueries
Dez 2010
15
Tip #12: Versuchen Sie ein “IN” Query in eine “EXISTS”-Query umzuschreiben und umgekehrt…
Jede Query-Art wird dieselben Resultate liefer4n, aber vollkommen unterschiedlich arbeiten. Typischerweise wird eine
Art besser laufen, als die andere – abhängig von der Verteilung der Daten. Beispiel:
Diese “Non Correlated Subquery”
Kann auch so geschrieben werden:
SELECT E.EMPNO, E.LASTNAME
FROM EMPLOYEE E
WHERE E.EMPNO IN
(SELECT D.MGRNO
FROM DEPARTMENT D
WHERE D.DEPTNO LIKE ‘D%”)
FROM EMPLOYEE E
WHERE EXISTS
(SELECT 1
FROM DEPARTMENT D
WHERE D.MGRNO = E.EMPNO
AND D.DEPTNO LIKE ‘D%’)
V9: Global Query Optimization. Optimizer now tries to determine how an access path of one query block may
affect the others. This can be seen at times by DB2 rewriting an „Exists‟
subquery into a join, or an „In‟ subquery into an „Exists‟ subquery . V9
optimization calls this „Correlating‟ and „De-correlating‟.
Correlating und De-Correlating in der Optimierung
FROM UKRAS.EMP E
WHERE EXISTS
(SELECT 1
FROM UKRAS.EMPPROJACT EPA
WHERE E.EMPNO = EPA.EMPNO)
DB2 V9 transformiert die Query in einen „nested loop join“
Dez 2010
16
Correlating und De-Correlating als Optimierungsmaßnahme Original im Vergleich zur „Transformed query“.
Dies wird in V9 über die “Global Query
Optimization” abgewickelt. VOR V9, hat
DB2 eine solche Query in zwei Teile
aufgespalten: Die Subquery und die
“outer query“. Jeder Teil wurde unabhängig
voneinender optimiert. „Global query
optimization” setzt DB2 in die Lage, die
Query als Ganzes zu betrachten und
zu optimieren.
Dies wird möglich, indem DB2
- Erkennt, welche Auswirkung ein Queryblock auf einen anderen hat
- Darüber nachdenkt Queryblocks anders anzuordnen
V9 – Der Optimizer bezieht in seine Überlegungen “correlated”, “non correlated”,
und “joins“ mit ein
Dez 2010
17
Correlating und De-Correlating als Optimierungsmaßnahme Original im Vergleich zur „Transformed query“.
SELECT
FROM
WHERE
E.EMPNO, E.LASTNAME
UKRAS.EMP E
E.EMPNO IN
(SELECT
EPA.EMPNO
FROM
UKRAS.EMPPROJACT EPA)
DB2 V9 transformiert „IN“ in einen „EXISTS“
Dez 2010
18
Tip #13: Stellen Sie sicher, dass die Verteilungsstatistiken der Daten aktuell sind…
Dies erfolgt vor allem über das Utility RUNSTATS auf Tabellen und zugehörige Indexe. Dieses Utility aktualisiert
die Systemtabellen mit Verteilungs- und Statistik-Informationen, die der Optimizer für die Auswahl der Zugriffspfade
nutzt. Einige Informationen, die RUNSTATS zur Verfügung stellt, sind:





Die Grösse der Tabellen (# of rows)
Die kardinalität der Spalten
Der Prozentsatz der “rows” (“frequency”) mit bestimmten Werten in den Spalten
Die physischen Charakteristika der Daten- und Index-Dateien
Informationen pro Partition
Achten Sie auf die „Statstime‟ Spalte in den Katalogtabellen,. Sie sagt aus, WANN die letzten RUNSTATS-Läufe auf
den Tabellen erfolgt sind.
DB2 V8: Volatile Tabellen – DB2 nutzt einen Index ohne Rücksicht auf Statistiken
1) FREQVAL Statistics sind für alle Spalten wichtig, die ungleich verteilte Datenwerte enthalten können. Beispiel:
Einige Tabellen enthalten Statuscode Spalten mit mehreren verschiedenen Werten. Sind die Werte nun so
verteilt, dass eine hoher oder auch niedriger Prozentsatz von “rows” die meisten Werte enthalten, so sollte die
Tabelle FREQVAL Statistiken auf dieser Spalte erhalten.
2) Statistiken sind typischerweise in der Produktionsumgebung “up to date”. In Testumgebungen sind diese alt
oder zurückgesetzt…
3) “Volatile tables” sind immer eine Herausforderung. Statistiken reflektieren nur einen bestimmten Zeitpunkt.
Indem die Tabelle als “volatile” deklariert wurde, wird der Optimizer vorwiegend einen IX-Scan anstatt eines
TS Scans in Betracht ziehen. Der Zugriffspfad wird eng von den existenten Table-Statistiken abhängen.
Dez 2010
19
Tip #14: Basis-RUNSTATS Daten sind unerlässlich…
Alle Tabellen in allen Umgebungen sollten folgende (Basis-) Statistiken besitzen:
 “Cardinality statistics” auf allen Spalten in allen Tabellen
 “Frequency Value statistics” auf allen Spalten mit ungleicher (Werte-)Verteilung
 “Group statistics” (distinkte Werte bezogen auf eine Gruppe von Spalten) für jedes “set of columns“, das korreliert
DB2 V9: Quantile Statistics (weitere Detailbetrachtung von Statistiken).
Hilft bei “range”-Prädikaten, “between predicates” und dem “like” Prädikat. Besonders, wenn sogen. „Hot
Spots‟ in der Datenverteilung existieren.
1) Correlate Columns:
SELECT COUNT (DISTINCT CITY) FROM TABLE1;
SELECT COUNT (DISTINCT STATE) FROM TABLE1;
-- = 3
-- = 4
-- Ergebnis: 3*4 = 12
SELECT COUNT (*) FROM
(SELECT DISTINCT CITY, STATE FROM TABLE1) AS X; -- = 4
*** Da 4 < 12, werden die Spalten CITY und STATE als korrelierend bezeichnet.
2) Quantile Statistics
RUNSTATS INDEX(„UKRAS"."XEMP02"
HISTOGRAM NUMCOLS 1 NUMQUANTILES 20)
SHRLEVEL CHANGE
REPORT YES
Dez 2010
20
Tip #15: Wenn möglich, verwenden Sie “hard coding” anstatt “host variable”. Oder REOPT. …
Beispiel: Eine Tabelle habe 1 Million “rows”. Auf dieser Tabelle gibt es einen Index auf der Spalte “StatusCode”.
Nach einem typischen Luaf eines RUNSTATS wird der Optimizer wissen, dass es für das Feld “StatusCode” 3 Werte
gibt. Nach einem speziellen RUNSTATS – Lauf, der die “frequency value statistics” für diese Spalte evaluiert, kennt
DB2 folgende Verteilungen:
• StatusCode Wert „A‟ enthalten in 50% der Daten
• StatusCode Wert „B‟ enthalten in 45% der Daten
• StatusCode Wert „C‟ enthalten in 05% der Daten
Ein Programm enthält nunhfolgendes SQL-Statement. Es liefert immer den Wert „C‟:
SELECT
FROM
WHERE
Dez 2010
COL1, COL2, COL3
TABLE
STATUS_CD = :HOST-VARIABLE
-- vs STATUS_CD = ‘C’
Static Bound Packages
–
REOPT(NONE) REOPT(ALWAYS) or VARS
Dynamic Packages
-
REOPT(ONCE) REOPT(AUTO)
21
Tip #16: Indizieren Sie “correlated Subqueries”. …
Es gibt eine ganze Reihe von Dingen, die beachtet werden müssen, wenn man ein SQL Statement ausführen läßt, das
eine “Correlated Subquery” enthält. “Correlated Subqueries” können mehrfach ausgeführt werden, um die SQL
Anforderung erfüllen zu können. Mit Rücksicht darauf, sollte die Subquery über einen Index laufen, um mindestens
mehrfache TS Scnas zu vermeiden. Wird die “correlated subquery” tausende von Malen ausgeführt, so wäre es am
besten, sie würde mit Indexonly = „Yes‟ ausgeführt. Dies kann zu einer Änderung existenter IX führen.
Beispiel:
SELECT
FROM
WHERE
E.EMPNO, E.LASTNAME
EMPLOYEE E
EXISTS
(SELECT 1
FROM
WHERE
AND
DEPARTMENT D
D.MGRNO = E.EMPNO
D.DEPTNO LIKE ‘D%’)
Tip #17: Vermeiden Sie Diskrepanzen in “non-column” Ausdrücken…
Wann immer man Prädikate, die “non column expressions” enthalten, schreibt, ist es wichtig, sicherzustellen, dass
das Resultat eines Ausdrucks dieselbe Definition hat, wie die Spalte, mit der es verglichen werden soll.
Der Optimizer kann sehr wohl Werte vereinfachen, um die Spalte mit einem einfacheren w(ert vergleichen zu
können. Er kann jedoch (noch) nicht höherwertige werte mit der Spalte vergleichen. Das ist nicht immer ein Problem,
seit in DB2 V8 die Vergleichsfähigkeit verbessert wurde. Es können unterschiedliche Datentypen verglichen werden
– vorausgesetzt sie sind KOMPATIBEL. Dennoch ist es besser, sicher zu sein, dass ein Vergleich möglich ist.
Beispiel:
WHERE EDLEVEL = 123.45 * 12 sollte so formuliert sein:
Dez 2010
WHERE EDLEVEL = SMALLINT(Round(123.45*12,0))
22
Tip #18: Subquery –Prädikate mit MAX/MIN können oft Stage 2 Prädikate darstellen. …
Enthält das Prädikat einer Subquery Min oder Max Werte, sollte man wissen, ob die betroffene Spalte als “nullable”
oder mit NOT NULL definiert ist. Dies kann nämlich den Unterschied zwischen einem “Stage 1” und einem “Stage
2” Prädikat ausmachen. Dies wiederum hängt mit dem Oprerator im Prädikat zusammen: ungleich dem =’ Zeichen.
Beispiel: Ist das Feld E.HIREDATE als NOT NULL definiert sein, so sollte wie folgt formuliert sein:
SELECT
FROM
WHERE
E.EMPNO, E.LASTNAME
EMPLOYEE E
E.HIREDATE <=
(SELECT
FROM
WHERE
AND
COALESCE(MAX(T2.HIREDATE), ‘9999-12-31’)
TABLE2 T2
…..
….. )
Auszug aus dem “Summary of Predicate Processing”
Predicate Type
Indexable
Stage 1
-------------------------- ------------ --------COL op (noncor subq)
Y
Y
Notes
------28
28) Dieser Prädikatstyp ist NICHT “stage 1”, wenn ein sogen. “nullability mismatch” möglich ist.
Dez 2010
23
Tip #19: Für OLTP “Front End” Verarbeitung sollte man “Optimize for XX Rows” als Optimizer
Hinweis geben, um den RID List Prefetch Sort zu vermeiden…
Dies sollte vor allem dann versucht werden, wenn die Anzahl der benötigten “rows” signifikant kleiner ist als deren
Gesamtzahl oder aber die Gesamtzahl der “rows” selbst ist minimal.
Beispiel:
SELECT EMPNO, LASTNAME, DEPTNO, SALARY
FROM EMPLOYEE
WHERE DEPTNO > ?
OPTIMIZE FOR 14 ROWS
Wenn man sicher ist, dass auf dem Bildschirm nur 14 “rows” verarbeitet werden sollen, egal wieviele “rows”
geliefert werden, so sollte das SQL Statement „Fetch First 14 Rows Only‟ als Klausel enthalten.
1) DB2 nutzt die OPTIMIZE FOR n ROWS Klausel, Zugriffspfade zu finden, die die “response time” zur
Suche der ersten “rows“, minimieren können.
2) OPTIMIZE FOR 1 ROW kann SORTs vermeiden: Dieses Statement veranlasst DB2, einen Zugriffspfad zu
wählen, der die ersten qualifizierenden “rows” schnell zurückliefert; d.h. wann immer möglich vermeidet Db2
einen Zugriffspfad, der einen SORT vorsieht. Gibt man einen Wert für n an, der nicht 1 ist, so wählt DB2
einen kostenbasierten Zugriffspfad und SORTs werden nicht zwangsläufig vermieden.
3) Typischerweise wird ein „List Prefetch‟ verhindert: Ist der Filterfaktor hoch genug, dann veranlasst
„Optimize for 1 Row‟ den Optimizer zu einem TS Scan.
Dez 2010
24
Tip #20: Schreiben Sie INSERTs mit SELECT (seit V8)…
DB2 V8 schuf die Möglichkeit, die Daten, die Eingefügt wurden, im selben Statement gleichzeitig zu lesen. Dies
spart Calls an DB2. Man nennt das „Relational Programming” im Gegensatz zum „Procedural Programming‟. Das
Statement kann folgende Information liefern.
 “Identity columns” / “sequence” werte, die von DB2 automazisch zugewiesen werden
 “User-defined defaults” und Ausdrücke, deren ergebnis dem Entwickler nicht bekannt sein kann
 Spalten, die von Triggers modifiziert werden und die von INSERT zu INSERT unterschiedlich sein können
 ROWIDs, CURRENT TIMESTAMP –ebenfalls automatisch zugewiesen
Beispiel:
SELECT
FROM
C1, C2, C5, C6, C8, C11, C12
FINAL TABLE
(INSERT (
C2, C3, C4, C5, C6, C8, C10)
VALUES (
‘ABC’, 123.12, ‘A’, ‘DEF’,
50000.00, ‘GHI’, ‘2008-01-01’)
)
Geeignet zum Selektieren sind:
• Werte von automatisch generierten Spalten wie ROWID oder “identity column“
• Beliebige “default” Werte für Spalten
• Alle Werte einer eingefügten Zeile, ohne Spaltennamen angeben zu müssen
• Alle über einen “multiple-row INSERT” eingefügten Werte
• Werte, die über einen BEFORE INSERT Trigger geändert werden (können)
Dez 2010
25
Tip #21: Prüfen auf “non existence”…
Kodiert man Logik, um festzustellen, welche Zeilen in einer Tabelle existieren, in einer anderen aber nicht, so gibt es
eine Reihe von bekannten Lösungsmöglichkeiten. Eine ist, “outer join” Logiken zu schreiben und anschließend mit
„WHERE D.MGRNO IS NULL‟ in der anderen Tabelle zu prüfen oder eine „NOT EXISTS‟ Logik zu formulieren.
Die folgenden 2 Beispiele liefern alle Mitarbeiter, die lt. Abteilungstabelle keine Manager sind . ABER, die zweite
Query ist weitaus effizienter. DB2 Visual Explain zeigt für jedes Prädikat an, wann die Filterung stattfindet.
Beispiel 1:
SELECT
FROM
WHERE
E.EMPNO, E.LASTNAME
EMPLOYEE E LEFT JOIN
DEPARTMENT D ON D.MGRNO = E.EMPNO
D.MGRNO IS NULL
Beispiel 2:
SELECT
E.EMPNO, E.LASTNAME
FROM
EMPLOYEE E
WHERE NOT EXISTS
(SELECT
FROM
WHERE
1
DEPARTMENT D
D.MGRNO = E.EMPNO)
Prädikatsprüfung mit Visual Explain:
1) Stage 1 versus Stage 2 Prädikate
2) Wann wird das “filtering” in den Prädikaten eingesetzt
3) „Filter Factor“ für Prädikate
Dez 2010
26
Tip #22: Unterlassen Sie es, eine Zeile zu Selektieren, um zu entscheiden, welche Verarbeitungslogik
die richtige ist - UPDATE oder INSERT …
Dies erfordert einen zusätzlichen Call an DB2. In V9: Nutzen Sie den „Upsert‟ - das SQL Merge Statement
MERGE INTO EMPLOYEE E
USING (VALUES ('000999', 'TONY', 'ANDREWS', 'A00') )
AS NEWITEM (EMPNO, FIRSTNAME, LASTNAME,DEPARTMENT)
ON E.EMPNO = NEWITEM.EMPNO
WHEN MATCHED THEN
UPDATE SET FIRSTNAME = NEWITEM.FIRSTNAME, LASTNAME = NEWITEM.LASTNAME
WHEN NOT MATCHED THEN
INSERT (EMPNO, FIRSTNAME, LASTNAME, DEPARTMENT)
VALUES ( NEWITEM.EMPNO, NEWITEM.FIRSTNAME,
NEWITEM.LASTNAME, NEWITEM.DEPARTMENT)
V9: Das SQL Merge Statement ist genau für diese Situation gedacht
Natürlich kann der MERGE auch mit “Rowsets” und “Arrays” eingesetzt werden. Tests haben gezeigt, dass der
MERGE weitaus effizienter ist als
 SELECT gefolgt von INSERT oder UPDATE und INSERT
- INSERT als erstes (bei “duplicate” SQLCODE -803), dann UPDATE.
Die Grösse der “arrays” hat kaum Unterschiede zum “fetching” gezeigt.
Die Ausführung von mehreren MERGE's mit “arrays” von 100 bzw. 1000 “rows” auf einmal gegenüber weniger
“fetches” mit 10,000 “rows” zeigte nur geringe Unterschiede.
ABER: man muss seine eigenen Tests durchführen.
Dez 2010
27
Tip #23: Nutzen Sie die Möglichkeiten des „UPDATE where Current of Cursor‟ und „DELETE
Where Current of Cursor‟. Nutzen Sie die neuen RIDs….
Was ist zu tun, wenn ein Cursor „Read Only‟ ist:
• Werden “rows” ausserhalb der Cursor verarbeitung gelöscht, so kann man den “Dynamic scrollable cursor”
verwenden. Dies ermöglicht ein „Order By‟ in der Cursor Definition und zusätzlich ein „Delete Where Current of
Cursor‟ Statement innerhalb der Verarbeitung.
• Lesen Sie die ROWID (RID in V9) für jede “row” im „Read Only Cursor” und führen Sie alle
UPDATES/DELETEs über die ROWID/RID Werte aus. Dies hilft der Performance.
V9: Man kann die RID einer “row” erhalten ohne eine Spalte mit dem Datentyp ROWID definiert zu haben.
Select EMPNO, LASTNAME, RID(UKRAS.Emp)
From
UKRAS.EMP
Where EMPNO = ‘000010’
Das Resultat ist BIGINT.
Dez 2010
28
Tip #24: Nutzen Sie LEFT OUTER JOINs anstatt RIGHT OUTER JOINs….
Schreibt man eine OUER JOIN Logik, so ist es egal ob man einen „Left Outer Join‟ oder einen „Right Outer Join‟
formuliert. Um die Logik korrekt zu bekommen, ist es alleine ausschlaggebend, die sogen. “Driver Table” richtig
angegeben zu haben. Dies ist also weniger ein Tuning Tip als mehr ein Tip den Entwicklern zu helfen, zu verstehen,
dass Left outer Joins leichter lesbar sind.
Entwickler sollten in DB2 ausschließlich „Left Outer Joins‟ schreiben. Dies ist klarer, da die „Driver Table” immer als
erste angegeben sein wird und alle folgenden Tabellen als „Left Outer Join‟ an sie gebunden werden.
Die DB2 Optimierung konvertiert “right outer joins” IMMER auf “left outer joins”. Siehe “explain output“.
Die Spalte = Join_Type in der Plan_Table
Tip #25: Nutzen Sie Korrelationsvariable zur besseren Lesbarkeit….
Korrelations-IDs sollten sinnvoll für die referenzierte Tabelle sein. Verwenden Sie nicht automatisch A, B, C, D,
etc… Besser ist es E für die “Employee table”, D für die “Department table” zu verwenden:
SELECT
E.EMPNO, E.LASTNAME
FROM
UKRAS.EMP E
WHERE EXISTS (SELECT
1
FROM
UKRAS.DEPT D
WHERE
D.MGRNO = E.EMPNO
AND
D.DEPTNO LIKE ‘D%’)
Dez 2010
29
Tip #26: Nutzen Sie die Vorteile der V9 “Native Stored Procedures”….
1) Für STPs, die in SQL geschrieben sind und die im DB2 Database Services Address Space (aka DBM1) ausgeführt
werden .
2) Native STPs sind mehr CPU-effizient als “external STPs” und köpnnen die zIIP “engine” nutzen, wenn sie vom
DB2 “Distributed Data Facility“ aufgerufen werden
3) “External STPs” benötigen ein “language environment” in dem sie ablaufen können. Dieses wird über den “WLMmanaged address space” angeboten. “Threads” werden “suspended” sobald TCBs zugewiesen werden.
4) Wird eine “native STP” aufgerufen, so schaltet DB2 vom “caller's package” auf das “STP package” um. Es gibt
keine “thread suspension” und kein “task switching” und somit keine Verzögerung in der STP Ausführung
5) Native STPs sind für DB2 intern und erlauben jeden beliebigen API Call zwischen den “address spaces” von DB2
und dem “language environment”.
6) Voraussetzung: V9 New Function Mode
7) SQL Code läuft interpretativ und als Teil des Package. Achten Sie auf STPs mit einer großen Menge an Code.
8) Externe Ressourcen (für DB2) sind nicht erforderlich, um eine “nbative STP” vorzubereiten oder auszuführen; z.B.
“compiles” und “load libraries”.
9) Kein WLM “application execution environment”: wird eine “native SQL procedure” gerufen, so läuft sie im DB2
“database services addressspace“ (DBM1).
1) “zIIP engines” sind spezielle Maschinen(“engines”) auf dem Mainframe, die bestimmte Workloads übernehmen
können, aber nicht in die Mainfranme – Software eingreifen , was das “CPU pricing” durchaus negativ
beeinflussen könnte .
2) Native SQL Prozeduren sind “zIIP eligible“
Dez 2010
30
Tip #26: Nutzen Sie die Vorteile der V9 “Native Stored Procedures” – Beispiel1….
Dez 2010
31
Tip #26: Nutzen Sie die Vorteile der V9 “Native Stored Procedures” – Beispiel 2 ….
Dez 2010
32
Zusammenfassung: 10+ Schritte, um ein Query zu “tunen”
1)
Prüfen Sie JEDES Prädikat: Ist es “indexable” und “Stage 1”?
2)
Ist ein DISTINCT verwendet: Ist er erforderlich? Kann er umgeschrieben werden ? – Versuchen Sie GROUP
BY!.
3)
Gibt es Subqueries: Schreiben Sie „In‟ in„Exists‟ und umgekehrt um
4)
Prüfen Sie die Statistiken auf jeder Tabelle und jeder betroffenen Spalte…
5)
Prüfen Sie die Anzahl , wie oft ein SQL Statement ausgeführt wird. Kann die Logik dahingehend geändert
werden, dass die Anzahl “requests” vermindert wird?
6)
Prüfen Sie die DB2 Explain Ergebnisse. Gibt es Scans? Gibt es SORTs?
7)
Prüfen Sie DB2 VE. Gibt es Prädikate mit “verrückten” Filterfaktoren ?
8)
Prüfen Sie DB2 Explain. Ist da ein JOIN, so untersuchen Sie, in welcher Reihenfolge die Tabellen verarbeitet
werden ?
9)
Gibt es “correlated subqueries”? Könnten sie “Index-Only” sein?
10) Gibt es in den Prädikaten Spalten mit ungleich verteilten Datenstatistiken ? Können die Werte “hard coded”
werden?
11) Gibt es “range predicates”. Könnten “histogram statistics” helfen?
Dez 2010
33
Zusammenfassung: Einige neue “features” aus DB2 V8
1) Mehr Stage 1 Prädikate
2) Multi Row Fetch, Update und Insert
3) Multiple Distincts
4) “Expressions” im „Group By‟
5) „Common Table Expression“
6) „Dynamic Scrollable Cursors“
7) „Sequences „ als Ergänzung zu „Identity Columns“
8) “Materialized Query Tables” (MQTs)
9) Rekursives SQL
10) Effizientere IX Nutzung : “Forward” / “Backward scans”
11) Neue XML Funtionen und Datentypen
12) Neues „Get Diagnostics‟ für “warning” und Fehler- Information
13) SELECT aus einem INSERT Statement
14) “Scalar Fullselect” in einem „Select‟, „Case‟, Update, etc.
Dez 2010
34
Zusammenfassung: Neue “features” aus DB2 V9
1) Set Operationen „Intersect‟ und „Except‟
2) MERGE Statement für „Upsert‟ Verarbeitung ( Insert oder Update )
3) OLAP Features für das “Ranking” und die Nummerierung von Daten
4) Native SQL Stored Procedures
5) „Instead of‟ Triggers
6) Neuer Support und neues SQL für XML Daten
7) „Optimization Service Center“ (OSC)
8) „Distinct sort avoidance“ mit „non unique“ Indexen
9) „Indexing on Expressions“
10) „Skipped locked data“
11) TRUNCATE Statement
12) „Array host variables” werden nun in “stored procedures” unterstützt
13) “Timestamp auto update” für INSERT/UPDATE
14) „Optimistic locking“
15) Neue Datentypen: DECFLOAT , BIGINT, BINARY etc.
16) „Select from Update /Delete” um alte/neue datenwerte zu erhalten
17) Fetch First, Order BY innerhalb von Subqueries
18) REOPT AUTO (Dynamic SQL)
19) Data Studio für „Native Stored Procedures“
Dez 2010
35
“When you know something, say what you know.
When you don't know something, say that you
don't know.
That is knowledge.”
Die Erfahrung zeigt
Dass Standards, Richtlinien und “walkthroughs” CPU Kosten sparen, aber auch
“incident reporting” in IT Abteilungen.
“There is always time for a 10 minute
walkthrough”
Dez 2010
36
“Ich habe gelernt, dass
Wenn die Entwickler aus- und weitergebildet werden
Gute SQL Programmierstandards existieren,
Und qualitätsgetrieben Ergebnisse verlangt werden Das “incident reporting” gering ist,
Die CPU Kosten nicht ausser Kontrolle geraten
Und die meisten Performance Probleme gefunden werden, bevor die Programme in die
Produktion übergeben werden …..
Dez 2010
37

DB2 for z/OS - SQL und spezielle Tips

Transcrição

Documentos relacionados

1. ¨Ubung - Datenbankprogrammierung

Versionsvergleich 2010 - 2016

Lebenslauf - ps

DOC PPS-FM11-WIN - Terminal Systems

pzetrm21-win - Terminal Systems

August-Ausgabe

DB2 News

funktionen der sks business finanzbuchhaltung

Übungsblatt 11 Abgabe bis 28. 1. 2007, 12:00 Uhr

Überblick zu den Editionen von SQL Server 2008 R2