DB2 UDB Application Tuning 101: The Nuts and Bolts Draft Version

1. DB2 UDB Application Tuning 101: The Nuts and Bolts � Draft Version

2. AGENDA Application Tuning Basics Access Path Basics Problematic SQL DB2 UDB Traces Benchmarking Discuss generic items. Questions during the presentation A Little of everything � Technical, Philosophy, War Stories and Humor Use analogies and metaphors when I speak Tuning is Organization problem more than Technical problem Solutions are often small for big problem. Effective QA is lacking Development Org Discuss generic items. Questions during the presentation A Little of everything � Technical, Philosophy, War Stories and Humor Use analogies and metaphors when I speak Tuning is Organization problem more than Technical problem Solutions are often small for big problem. Effective QA is lacking Development Org

3. Abstract Restatement Application Tuning is a critical activity for the DBAs and Developers. This presentation reviews the how to, the steps involved and the key items the DBA/Developer should look for during Application Tuning process. Not a perfect process. Things get overlooked NASA Story Not a perfect process. Things get overlooked NASA Story

4. Application Tuning Basics To know that we know what we know, and to know that we do not know what we do not know, that is true knowledge. -----Copernicus This section I review the basics at a summary level. Books �IBM Redbooks, Craig Mullins Book This section I review the basics at a summary level. Books �IBM Redbooks, Craig Mullins Book

5. DB2 Optimizer Basics A C Program that calculates the most efficient access plan for a piece of SQL. Parses, Rewrites, Optimizes SQL Inputs: SQL Machine Configuration � No. CPUs & Memory DB2 Catalog Tables Outputs Access Plan for Plan/Package or Dynamic SQL Optimizer is IBM most complicated piece of software IBM has dedicated staff at Silicon Valley Labs supporting the Optimizer and problems. Mathematical Models for costing I/O, Buffer Pool Use and Sorts, etc�. Finds lowest cost Access Path. Joins cause problems Ph ds are smart Hint overrides Human are not Optimized � Home Depot example. Optimizer is IBM most complicated piece of software IBM has dedicated staff at Silicon Valley Labs supporting the Optimizer and problems. Mathematical Models for costing I/O, Buffer Pool Use and Sorts, etc�. Finds lowest cost Access Path. Joins cause problems Ph ds are smart Hint overrides Human are not Optimized � Home Depot example.

6. Basic DB2 SQL Processing Stage 1 Processing vs. Stage 2 Filer out pages before the Buffer See Sherryl Larsens presentation of IBM Manuals Stage 1 Processing vs. Stage 2 Filer out pages before the Buffer See Sherryl Larsens presentation of IBM Manuals

7. Access Path Basics All men by nature desire to know. ---- Aristotle  I review Access Paths at summary level. See your IBM Manuals for details the way the Server applies filters and result sets during the procesing. Understand the SQL Engine. I review Access Paths at summary level. See your IBM Manuals for details the way the Server applies filters and result sets during the procesing. Understand the SQL Engine.

8. EXPLAIN PLAN Access Path Basics METHOD for JOINs 0 First outer table access or not used 1 Nested Loop Join 2 Merge Scan 3 SORTs to support ORDER BY, GROUP BY, DISTINCT & UNION 4 Hybrid Join Code translation Code translation

9. EXPLAIN PLAN Access Path Basics - Continue ACCESSTYPE I Matching Index Scan I1 One-fetch Index scan N Matching Index Scan for each IN-list value R Table space Scan M Multiple index scan MX Matching Index Scan RID List MI Intersection of RID Lists due to ANDed predicates MU Union of RID lists, due to ORed predicates Blank not used or clustering index for INSERTs or no index for UPDATEs or DELETEs WHERE CURRENT OF; or not applicable. MATCHCOLS � Indicates number of key columns matched for I, I1, N, & MX. Access Type R is a red Flag Watch for Matching Index and Mattch Cols is null. F&F Story Access Type R is a red Flag Watch for Matching Index and Mattch Cols is null. F&F Story

10. Problematic SQL Light tomorrow with today!  -----Elizabeth Barrett Browning Most SQL is not problematic. I/O , Accidental Cartesian, Most SQL is not problematic. I/O , Accidental Cartesian,

11. Problematic SQL Inefficient SQL Long Run Times User Complaints Production Support & Developer Staff Complaints/Concerns High CPU High IN DB2 Time Collect Feedback from everywhere and everybody Manage with a Tool � Excel or something. Time is a universal measurement everyone understands. Disk I/O Latency, Seek Time, Read Time, Buffer Transfer Time, etc,,,, may not be universally understood by all.Collect Feedback from everywhere and everybody Manage with a Tool � Excel or something. Time is a universal measurement everyone understands. Disk I/O Latency, Seek Time, Read Time, Buffer Transfer Time, etc,,,, may not be universally understood by all.

12. Bottlenecks � Summary Level What/Where Performance-related Variables that might cause Bottlenecks? Machine Configuration Network Application SQL Itself Design Besides the SQL itself, when executes the following areas can cause Bottlenecks. Areas of potential bottlenecks Out of your control sometimesBesides the SQL itself, when executes the following areas can cause Bottlenecks. Areas of potential bottlenecks Out of your control sometimes

13. Potential Bottleneck Performance Concerns (Your Martha Stewart Worry List) Architecture CPU I/O Network Concurrency Application Query Logical Design Physical Design Server(s) Configuration Optimizer Lock Management Concurrency Maintenance � Reorgs Load/Unload Index Creation Batch Activity TSO Users Out of Developer Control DB2 Trace ca give some answers Network Tracers for Distributed Apps 16/32 TimeoutOut of Developer Control DB2 Trace ca give some answers Network Tracers for Distributed Apps 16/32 Timeout

14. Performance Concerns - Continued  SQL Query Appropriate Joins Path � NL vs. MS vs. HJ Predicate Filtering Parallelism Logical Design De-normalize from third to second normal form Vertical/horizontal segmentation of infrequently referenced data Physical Design Too many/few indexes Summary Data Redundant Data Partitioning

15. Performance Concerns - Continued Application Issues Poorly written SQL Repeatedly issues the same SQL Cursor Use/Misuse Batch Issues Division of work between client and server Server Configuration Memory Sort Pool EDM Optimizer Overhead of Applications Log DB2 Log Overhead of Applications Log DB2 Log

16. Where to Look for It in Existing Programs AUTHID or SECONDARY AUTHID .PLAN_TABLE JES2 Logs CA7 Reports DB2 monitoring tools OMEGAMON CA INSIGHT IBM TOOLS DB2 Log for Errors The IT is the R � Access Type, CPU process intensive operation (JOINS) Sequential Pre-fetch that might tie up the buffer. The IT is the R � Access Type, CPU process intensive operation (JOINS) Sequential Pre-fetch that might tie up the buffer.

17. Identifying the Usual Suspects Starting Point Find Plans/Packages with Full Tablespace Scans SELECT PROGNAME, QUERYNO, QBLOCKNO, METHOD, ACCESSTYPE,MATCHCOLS,ACCESSNAME, INDEXONLY, PREFETCH FROM AUTHID.PLAN_TABLE WHERE ACCESSTYPE = �R�; Cross-check PROGRAM to list of Long Running Jobs Lists Get list of plan with SQL with Full Tabblespace scan. This is at least a starting point. Get list of plan with SQL with Full Tabblespace scan. This is at least a starting point.

18. CSI � Detective questions (Who Do I Vote off the Island or adjust their personalities (tune the SQL)) DNA of SQL What tables is SQL accessing? Why & What Business Condition(s)? Frequency? For Cursors, how many rows are being FETCHed? SORTing needs? Locking considerations? Does this SQL Play Nice to its Neighbors? CPU Bound? I/O Bound? High-level questions for each piece of SQL Clock Time is the primary driver. Time is the Universal can identify with. High-level questions for each piece of SQL Clock Time is the primary driver. Time is the Universal can identify with.

19. 1 second is an eternity in DB2. Milli �seconds should be the time frame a SQL Query is resolved and rows returned. Online applications like ebay and bn have huge searches and they come back fast. Banking, Telecom, Insurance�etc� Batch 5 minutes job. Spend 100 hours fixing SQL with one new index.Milli �seconds should be the time frame a SQL Query is resolved and rows returned. Online applications like ebay and bn have huge searches and they come back fast. Banking, Telecom, Insurance�etc� Batch 5 minutes job. Spend 100 hours fixing SQL with one new index.

20. �Sweat the Small Stuff� � with apologies to Stephen Covey Mr. Covey sold millions of books with his simple advice. Ancient religious and philosophical texts tell us the same thing. This doesn�t apply to DB2 though. Small things in DB2 can cause big problems. Little sorts, little sql can have huge cpu implications ove time if program is designed poorly. DB2 is rarely the problem, but design is. DB2 itself becomes the victim here and DBA must defend it. Rooting out poorly designed programs/SQL can become a politically destroying career move if PM Developers are sensitive to embarrassment. State Code Table Mr. Covey sold millions of books with his simple advice. Ancient religious and philosophical texts tell us the same thing. This doesn�t apply to DB2 though. Small things in DB2 can cause big problems. Little sorts, little sql can have huge cpu implications ove time if program is designed poorly. DB2 is rarely the problem, but design is. DB2 itself becomes the victim here and DBA must defend it. Rooting out poorly designed programs/SQL can become a politically destroying career move if PM Developers are sensitive to embarrassment. State Code Table

21. Myth vs. Reality Myth: �Small Tables Don�t Need Indexes� Reality: Though a tablespace scan may seem better, it still requires the CPU cycle through the pages. An index has an absolute pointer to the row(s) needed. Let the RID Pool be your friend. Reality: Explain Plan quantifies cost of SQL. It does not measure frequency. State Code Promotion tables can be black hole of CPU. 1 million Customer Accounts looking up State Code Promotions. DBA chose not put index on table. Disaster � Nested Loop Join turned into cpu monster. Absolute Pointer to a row. State Code vs. PRomotions State Code Promotion tables can be black hole of CPU. 1 million Customer Accounts looking up State Code Promotions. DBA chose not put index on table. Disaster � Nested Loop Join turned into cpu monster. Absolute Pointer to a row. State Code vs. PRomotions

22. SORTs can be a cancer lurking in your CPUs. CPUs cycles are not free SORT POOL does have size limits CPUs cycles are not free SORT POOL does have size limits

23. SORT Notes AGREGATE FUNCTIONs DB2 Optimizer will use SORT Avoidance is possible to prevent sorting of result set if index used because data is already sorted. Sorts use CPU. Small SORTs are no innocent Is SORT Pool sized enough Explain Plan don�t consider number of executions Cluster Indexes are already SORTed. GROUP BY, ORDER BT, DISTINCT, UNION and JOINsGROUP BY, ORDER BT, DISTINCT, UNION and JOINs

24. The primary goal of Application Tuning is to reduce Disk I/O. High-Level Statement � Some may disagree. Why: Disk I/O is a higher in time compared to other physical operations on a computer. High-Level Statement � Some may disagree. Why: Disk I/O is a higher in time compared to other physical operations on a computer.

25. Electrons move faster than disk heads. Information in Buffers can be accessed much faster than disk i/o. 1/100,000 thousands of a second Disk Latency � Seek Time, Read Time, Buffer Transfer Time, etc� Information in Buffers can be accessed much faster than disk i/o. 1/100,000 thousands of a second Disk Latency � Seek Time, Read Time, Buffer Transfer Time, etc�

26. DB2 UDB Traces All truths are easy to understand once they are discovered; the point is to discover them. -----Galileo Galilei  Before going to DB2 Trace Ask the following: Is tablesspace re-organized, extents? EDSM Pool, Check Bind PAramters Again. Second opinion on Explain Plan Baggage with Traces CA INsight and OMEGMON CPU spikes going on Before going to DB2 Trace Ask the following: Is tablesspace re-organized, extents? EDSM Pool, Check Bind PAramters Again. Second opinion on Explain Plan Baggage with Traces CA INsight and OMEGMON CPU spikes going on

27. DB2 Traces DB2 Produces internal SMF Records SMF 100 Type Records are Accounting SMF 102 Type Records are Performance DB2PM is Performance Analysis Tool Batch Reports DB2 Subsystem Online Monitor GUI with snapshot live DB2 Subsystem 102 contain detailed performance information Baggage/Overhead associcated with these102 contain detailed performance information Baggage/Overhead associcated with these

28. DB2 Tracing Panel Enable Trace elementsEnable Trace elements

29. SMF 102 Records Class 1 (Elapsed time)Class 2 (In-DB2 time)Class 3 (Wait times)Class 7 (Package level In-DB2)Class 8 (Package level Wait)

31. Trace Commands START TRACE � starts one or more type of traces DISPLAY TRACE � displays trace options in effect STOP TRACE � Stops any trace MODIFY TRACE � Change the IFCIDs on active trace

32. DB2PM Reports DB2PM Short Report DB2 Response Time Resources Used � Processor and CPU Lock Suspensions Application Code Changes Wait Times � Processor, I/O Wait or Lock Wait Short Report provides snapshot viewShort Report provides snapshot view

33. DB2PM Long Report Class 1 Elapse Time Time before the first SQL statement. DB2 create thread time. Time after the DB2 terminate thread. Not-in-DB2 Time � This is the calculated difference between Class 1 and Class 2 elapse time. If time spent outside DB2 (but within the DB2 accounting interval) is lengthy, the problem will be found in the application, CICS, IMS, or the overall system, and not within DB2. Lock/Latch Suspension Time � This value shows contention for DB2 resources. Check the "Locking Summary" section of this report for additional information, then proceed to the Locking Reports for help. This helps identify whether the problem is related to DB2 or some "outside" cause.This helps identify whether the problem is related to DB2 or some "outside" cause.

34. Synchronous I/O Suspension Time � This is total application wait time for DB2 synchronous I/Os. If the number of I/Os is high, check for: A change in access path. Application code changes. System-wide DB2 bufferpool problems. RID pool failures. System-wide EDM pool problems. Asynchronous Read Suspensions � This is the accumulated time for read I/O done under a thread other than this thread. It includes time for Sequential prefetch, List prefetch, Sequential detection or Synchronous read performed by another thread. The Rule-of-Thumb for Sequential prefetch or Sequential detection (asynchronous I/O) is 1 to 2 milliseconds per page. The Rule-of-Thumb for List prefetch is 3-4 milliseconds per page. Check "Other Read I/O" to locate value. Synchronous I/O Suspension Time If I/O time is greater than expected, check for I/O contention. A Synchronous read should take from 15-25 milliseconds, depending on the DASD device. If this value is longer, use RMF to check for DASD contention. Synchronous I/O Suspension Time If I/O time is greater than expected, check for I/O contention. A Synchronous read should take from 15-25 milliseconds, depending on the DASD device. If this value is longer, use RMF to check for DASD contention.

35. Not-Accounted-For DB2 Time � This is accounting class 2 time that is not part of class 2 CPU or class 3 suspensions, and is normally due to MVS paging, processor wait time or time spent waiting for parallel tasks to complete. Check the "Not Account" field for this value.

36. Benchmarking There art two cardinal sins from which all others spring: Impatience and Laziness. ----- Kafka (1883-1924)

37. Application Benchmarking What are the Organization Goals? Why, Who, What & How Measured Proof that Application Executing Efficiently 1,000,000 Customer Accounts Updated Nightly 20,000,000 Calls Processed Nightly Nightly Batch Jobs Online Screens Response Time

38. Tuning Solutions  Normalization is good but causes many JOINs Review Cardinality of Data Values For all ACCESSTYPE �R�s, create index if possible if amount of rows is < 25 % being retrieved in Result Set Small Tables with ACCESSTYPE �R�s is not always good. Index-able Predicates reduce i/o Use Clustering Indexes to reduce Sort and CPU Costs Nested Loops for JOINs are not always innocent Indexes, Indexes, Indexes Kafka �s writings are the driest the world had seen in the 20th Cenrtury. Modennist. Not exactly the Mark Twain touch-felly stuff. New programs to review co designed with DBA to insure new process is DB2 Friendly. I/O is Expensive. Kafka �s writings are the driest the world had seen in the 20th Cenrtury. Modennist. Not exactly the Mark Twain touch-felly stuff. New programs to review co designed with DBA to insure new process is DB2 Friendly. I/O is Expensive.

39. Session Title: DB2 UDB Application Tuning 101: The Nuts and Bolts Session: D