DB2 for z/OS - SQL und spezielle Tips
Transcrição
DB2 for z/OS - SQL und spezielle Tips
(*) 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 DB2 9 SQL_Performance SQL_Performance(Einführung) (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 DB2 9 SQL_Performance SQL_Performance(Einführung) (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 DB2 9 SQL_Performance 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 DB2 9 SQL_Performance SQL_Performance--Tips 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: SELECT EMPNO, LASTNAME 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: SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE SALARY * 1.1 > 50000.00 SELECT EMPN, LASTNAME FROM EMPLOYEE WHERE HIREDATE – 7 DAYS > ? Sollte wie folgt umgeschrieben werden: SELECT EMPNO, LASTNAME 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 9 SQL_Performance SQL_Performance--Tips 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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%”) SELECT E.EMPNO, E.LASTNAME 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 SELECT E.EMPNO, E.LASTNAME 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: Tip #12: Versuchen Sie ein “IN” Query in eine “EXISTS”-Query umzuschreiben und umgekehrt… 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: Tip #12: Versuchen Sie ein “IN” Query in eine “EXISTS”-Query umzuschreiben und umgekehrt… 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: Tip #26: Nutzen Sie die Vorteile der V9 “Native Stored Procedures” – Beispiel1…. Dez 2010 31 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: Tip #26: Nutzen Sie die Vorteile der V9 “Native Stored Procedures” – Beispiel 2 …. Dez 2010 32 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips Top 25+ Tuning Tips: 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 DB2 9 SQL_Performance SQL_Performance--Tips “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 DB2 9 SQL_Performance SQL_Performance--Tips “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