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

1
DB2 UDB Application Tuning 101 The Nuts and
Bolts Draft Version
Platform DB2 UDB for z/OS
Brad Clevinger of EDS IBM Certified Solutions
Expert - DB2 UDB V7.1 Database Administration for
OS/390 Oracle 9i 8i Certified Database
Administrator Colorado DB2 Users Group For Z/OS
Technical Session D March 17, 2005
2
AGENDA

• Application Tuning Basics
• Access Path Basics
• Problematic SQL
• DB2 UDB Traces
• Benchmarking

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.

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

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

6
Basic DB2 SQL Processing
7

• Access Path Basics
• All men by nature desire to know.
• ---- Aristotle

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

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.

10

• Problematic SQL
• Light tomorrow with today!
• -----Elizabeth Barrett Browning

11
Problematic SQL

• Inefficient SQL
• Long Run Times
• User Complaints
• Production Support Developer Staff
  Complaints/Concerns
• High CPU
• High IN DB2 Time

12
Bottlenecks Summary Level

• What/Where Performance-related Variables that
  might cause Bottlenecks?
• Machine Configuration
• Network
• Application
• SQL Itself
• Design

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

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

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

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

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?

19

• 1 second is an eternity in DB2.

20

• Sweat the Small Stuff
• with apologies to Stephen Covey

21
Myth vs. Reality

• Myth Small Tables Dont 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.

22
SORTs can be a cancer lurking in your
CPUs.
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 dont consider number of executions
• Cluster Indexes are already SORTed.

24

• The primary goal of Application Tuning is to
  reduce Disk I/O.

25

• Electrons move faster than disk heads.

26

• DB2 UDB Traces
• All truths are easy to understand once they are
  discovered the point is to discover them.
  -----Galileo Galilei

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

28
DB2 Tracing Panel
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)

30
(No Transcript)
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

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.

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.

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 Rs, create index if possible
  if amount of rows is lt 25 being retrieved in
  Result Set
• Small Tables with ACCESSTYPE Rs 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

39
Session Title DB2 UDB Application Tuning 101
The Nuts and Bolts Session D
Brad Clevinger EDS Bradford.Clevinger_at_eds.com