Case Studies in DB2 for zOS Performance Tuning Part 2

1
Case Studies in DB2 for z/OS Performance
TuningPart 2
Session B11 Beginner/Intermediate
www.mdug.org Download Presentations

• Lock Lyon
• Fifth Third Bancorp

May 9, 2007 300 p.m. 400 p.m. Platform z/OS
2
Outline

• Performance Tuning for the intermediate DBA who
  is relatively new to DB2 for z/OS
• The New DB2 DBAs Differing Backgrounds,
  Different Expectations
• Five Common Tuning Tactics
• Five Common Performance Challenges
• Where to Begin

3
Is Performance Important?
REVIEW

• The DBAs Most Important Task Performance
  Monitoring (81)
• Most Important Tool Functions Monitoring
  Performance and Availability (83)
• Top DB Challenge Reducing Cost (47)
• Areas of Responsibility for DB Monitoring
  Performance and Tuning of Database (70)

4
The New DB2 DBAs
REVIEW

• Differing Backgrounds
• Differing Expectations
• Data Modeling and Database Design
• SQL Review
• Backup and Recovery
• Security
• Subsystem Installation and Configuration
• Performance Monitoring and Tuning

Operations Application Support Programming DBA
(other platform) Management Systems
Programming University Technical School
5
Approaches to Tuning
REVIEW

• or, Whats the Best Use of Your Time?
• Your Assignment Tune
• How do you judge the best strategies?

SQL Tuning behavior-based Resource Constraint
Tuning resource-based Application
Tuning application-based Object
Tuning architecture-based Systems
Tuning enterprise-based
6
Before You Tune The Laws
REVIEW

• Lyons Laws of Database Administration
• 1 Data Recoverability
• 2 Data Availability
• 3 Data Security

7
A Checklist for the DBA

• The Map (Pictures for Communication)
• The Mainframe Environment
• TCP/IP, JES, z/OS, CICS, DBMSs, IRLM, CPU,
  Memory, DASD, etc., etc
• Your DB2 Subsystem Profile
• Tools
• Change Control
• Survival Kit

See Part 1
8
Your Tools

• SMF Reporting
• Accounting Short and Long Reports
• Statistics Reports
• Deadlock/Timeout Report
• SQL Trace Report
• EXPLAINs
• RMF Reports (Advanced)

9
Autonomics and Automation

• Autonomics
• Automation
• Data Gathering
• Reporting
• Analysis

10
The Three Most Important Performance Reports

• The Accounting Short Report
• Explains
• Health Checks
• Historical Access Paths (Explain )
• Accounting Long Report (Accounting Detail)
• Top n Reports

Reactive
Predictive, ProActive
11
Historical Access Paths

• Extended EXPLAIN
• History of changes
• Trend analysis
• Possible Extensions (without tools)
• Join to SysTables, SysIndexes, SysColumns
• Alternate indexes
• Stats (column attributes cardinality, etc.)
• Joins to SysKeys, SysRels (RI)

12
Accounting Long Report

• Drill Down into Plan/Package Details

Elapsed Time Summary
Class 2 Time Summary
Time Details
Suspensions
Highlights
Global Contention
SQL DML
Locking
SQL DCL
SQL DDL
Terminations
Drains / Claims
Data Capture
Data Sharing
Query Parallelism
13
Accounting Long Report

• More!

Stored Procedures
UDFs
Triggers
Logging
ROWIDs
RID Lists
Average SU
Dynamic SQL
Misc (LOBs)
Buffer Pools
Grp Buffer Pools
14
Top n Reports

• Time Period May Depend on Volume, Connection
• Daily (or shorter)
• Weekly (may not be possible unless reduced)
• Customize for Environment
• On-line
• Distributed
• Batch (IMS, TSO, utility, etc.)
• 10, 20, 50, 100 Based on Experience

15
Five Common Tuning Tactics

• Accounting Report Drill-Down
• Automated Top n Analysis
• Selective Optimizer Hints
• Constraint Tradeoffs
• Explain Automation Regular Review

16
Accounting Report Drill-Down

• Beginning with Accounting Short Report, Drill
  Down (Usually via Accounting Long Report)
• Typical Use
• Execute, look for exceptions
• Run Long Report for exceptions
• Begin Here
• Become familiar with Long and Short reports
• Run regularly for a critical system

17
Accounting Drill-Down Study
ACCOUNTING REPORT - SHORT
PRIMAUTH PLANNAME SELECTS INSERTS UPDATES
DELETES CLASS1 EL.TIME CLASS2 EL.TIME GETPAGES
MEMBER FETCHES OPENS CLOSES PREPARE CLASS1
CPUTIME CLASS2 CPUTIME BUF.UPDT ----------
------ ------- ------- ------- --------------
-------------- -------- PRODBAT1 DSNTIB71
0.00 0.00 0.00 0.00 506.436522
505.754667 1500.4K DB2P 70.0K 1.00
1.00 1.00 52.485688 52.275601
154.9K   -----------------------------------------
------------------------------------ PROGRAM
NAME SQLSTMT CL7 ELAP.TIME CL7 CPU TIME CL8
SUSP.TIME CL8 SUSP DSNTIAUL 70.0K
505.754665 52.275600 339.298783
73.3K -------------------------------------------
----------------------------------
ACCOUNTING REPORT - LONG
PRIMAUTH PRODBAT1 PLANNAME DSNTIB71 MEMBER
DB2P   ELAPSED TIME DISTRIBUTION
--------------------------------------------------
-- APPL
DB2 gt 28
SUSP
gt 72
ACCOUNTING REPORT - LONG CLASS 3
SUSPENSIONS AVERAGE TIME AV.EVENT -------------
------- ------------ -------- LOCK/LATCH(DB2IRL
M) 0.225273 43876.00 SYNCHRON. I/O
0.961634 450.00 DATABASE I/O
0.961634 450.00 LOG WRITE I/O
0.000000 0.00 OTHER READ I/O
337.465217 28995.00
ACCOUNTING REPORT - LONG BP10
BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- -------- BPOOL
HIT RATIO () 30.91 N/A GETPAGES
1178.2K 1178234 BUFFER UPDATES
0.00 0 SYNCHRONOUS WRITE
0.00 0 SYNCHRONOUS READ 157.00
157 SEQ. PREFETCH REQS 22349.00
22349 LIST PREFETCH REQS 0.00 0
DYN. PREFETCH REQS 3559.00 3559 PAGES
READ ASYNCHR. 813.8K 813836
ACCOUNTING REPORT - LONG BP7
BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- -------- BPOOL
HIT RATIO () 82.14 N/A GETPAGES
151.1K 151054 BUFFER UPDATES
154.9K 154874 SYNCHRONOUS WRITE
0.00 0 SYNCHRONOUS READ 97.00
97 SEQ. PREFETCH REQS 6897.00
6897 LIST PREFETCH REQS 0.00 0
DYN. PREFETCH REQS 0.00 0 PAGES
READ ASYNCHR. 26880.00 26880
18
Automated Top n Analysis

• Choose Top n Reports Schedule Regular
  Executions and Review Results
• Typical Uses
• Specific applications having issues
• Connections, utilities
• Begin Here
• Resource constraints
• Critical applications (elapsed, CPU, wait times)

19
Selective Optimizer Hints

• Typical Uses
• Pin access paths for critical SQL statements
• Possible workaround for some Dynamic SQL
• SQL accessing special objects (work tables)
• Begin Here
• Organize documentation and regularly review
• Choose queries that are critical
• Re-evaluate after maintenance

20
Constraint Tradeoffs

• Basic Steps
• List resource constraints and time periods
• List co-incident resource availability
• Develop method to trade-off resources
• Typically Used in an Environment having
  Short-Term Resource Constraints
• Begin Here
• Use Accounting Reports to discover Targets

21
Constraint Tradeoffs - Study

• Non-Data Sharing
• CPU hovers near 100 during day
• Analysis
• Review DB2PM Accounting Reports
• Excessive CPU due to DB2
• Excessive CPU due to applications
• Excessive CPU due to access paths
• Other (?)

22
Constraint Tradeoffs - Study

• Team created a process for finding CPU Hog
  Applications
• Review Acctg Report Avg In-DB2 TCB Time,
  SELECTs
• Create Metrics CPU per SELECT, per INSERT,
  etc.
• Entered in SpreadSheet, sorted (descending!)
• Team did Resource Constraint Analysis
• Allocated additional 1 GB of real memory to DB2
• Team allocated several BPs in dataspaces,
  assigned heavily-used tables
• Allocated 100 GB additional DASD to DB2

23
Constraint Tradeoffs - Study

• Team did Resource Constraint Analysis
• For many small and heavily-used tables, COMPRESS
  NO
• DASD increased, CPU increased (additional I/Os)
• Net decrease in CPU due to elimination of
  compress/decompress
• Increased SLAs for certain low-priority
  distributed apps
• Combined small stored procedures into single
  modules
• Enlarged dynamic SQL cache
• Net decrease in CPU due to elimination of stored
  procedure overhead, fewer dynamic SQL binds

24
Explain Automation Regular Review

• Specify EXPLAIN(YES) on Every Bind
• Save Historical Explain Data Review
• Typical Uses
• Cross-reference to objects, applications
• Accessing special objects (DPSIs, MQTs, UDFs)
• Begin Here
• Develop method(s) of saving history
• May need to implement QUERYNO in SQL
• Consider most useful comparison reports

25
Five Common Performance Challenges

• Dynamic SQL
• Index Proliferation
• Shrinking Batch Window
• Death by Random I/O
• Strategies for Reorg Stats Hints Rebind

26
Dynamic SQL

• Surprise SQL Arrives!
• Unknown access path(s)
• CPU for prepare/execute
• Activity in Statistics Reports

27
Dynamic SQL

• What Can You Analyze?
• Determine method of SQL Capture
• Use SQL cache for access path re-use
• Consider Optimizer Hints
• Begin Here
• Review current tool suite
• Review Statistics Reports

STATISTICS REPORT (SHORT) CPU TIMES
TCB TIME SRB TIME
TOTAL TIME -------------------------------
--------------- ---------------
--------------- SYSTEM SERVICES ADDRESS SPACE
255.755805 3616.539125
3912.294929 DATABASE SERVICES ADDRESS SPACE
843.111144 23804.085334 24647.196478 IRLM
4.462056
1124.705839 1129.167895 DDF ADDRESS SPACE
616.023680 91117.281676
91733.305356
STATISTICS REPORT (SHORT) SQL DML
QUANTITY -------- -------- SELECT
42607.7K INSERT 24061.8K UPDATE
5292.6K DELETE 9605.9K PREPARE
5331.8K DESCRIBE 1861.0K DESC.TBL
0.00 OPEN 10536.5K CLOSE 6623.5K FETCH
170.3M TOTAL 276.2M
SQL DML QUANTITY /SECOND
/THREAD /COMMIT ---------------------------
-------- ------- ------- ------- SELECT
384.6K 4.55 141.51
6.89 INSERT 10127.00
0.12 3.73 0.18 UPDATE
5281.1K 62.42 1943.02 94.63 DELETE
196.00 0.00 0.07
0.00
PREPARE
40863.00 0.48 15.03 0.73 DESCRIBE
7386.00 0.09 2.72
0.13 DESCRIBE TABLE 0.00
0.00 0.00 0.00 OPEN
8671.4K 102.50 3190.38 155.39 CLOSE
8629.3K 102.00 3174.86
154.63 FETCH 14872.9K
175.80 5471.99 266.51
TOTAL
37897.9K 447.96 13.9K
679.10
DYNAMIC SQL STMT QUANTITY /SECOND
/THREAD /COMMIT ---------------------------
-------- ------- ------- ------- PREPARE
REQUESTS 40863.00 0.48 15.03
0.73 FULL PREPARES 7665.00
0.09 2.82 0.14 SHORT PREPARES
40410.00 0.48 14.87 0.72 GLOBAL
CACHE HIT RATIO () 84.06 N/A N/A
N/A
IMPLICIT PREPARES
0.00 0.00 0.00 0.00 PREPARES
AVOIDED 0.00 0.00 0.00
0.00 CACHE LIMIT EXCEEDED 0.00
0.00 0.00 0.00 PREP STMT PURGED
57.00 0.00 0.02 0.00 LOCAL CACHE
HIT RATIO () N/C N/A N/A
N/A
28
Index Proliferation

• Old Indexes Never Die
• The Issues More Indexes
• Longer recovery time
• Slower insert / update / delete performance
• Additional DASD space (uncompressed)
• Longer Reorgs

29
Index Proliferation
--
-- .I.6 CLUSTERING INDEXES WITH POOR CLUSTER
--
DBNAME IX_NAME TB_NAME
FULLKEYCARD CL_RATIO STATS_DATE ---------------
----------------------------------------------
---- DB001101 IAA00101 TAC_POST_CR_REF
29477183. 43 08/11/2006 DB020201
IAA66901 DEP_PICK_DAY_DW 1061081. 53
04/18/2006 DB020101 IAA58401 SRV_STG_RES_DEV
1389347. 55 07/23/2006 . .
. --
-- .I.11 INDEXES WITH
FULLKEYCARD lt 1 OF TABLE CARD --
DBN
AME TSNAME TB_NAME IX_NAME
IDX_FKYCRD TBL_CARD ------------------------
---------------------------------------- DB06
0101 SAA64301 JOUR_TRAN_STG IAA64301
1. 12524221. DB000101 SAA00101
SRV_DW_BAL_TRAN SAA00106 12.
4232191. DB010301 SAA40401 T121_AT_BAL
IAA40402 14. 70278361. . . .

• Develop Cross Reference Reports
• Index to packages and plans
• Index to EXPLAINs
• Begin Here
• Implement index health checks
• Implement cross references
• Regularly review

30
Shrinking Batch Window

• On-Line Day Extending or,
• Batch Schedule Extending Into On-Line Day
• Batch Elapsed Times Longer
• (Accounting Top n Report)
• DBA Job Constraints
• Reorgs, Image Copies, RunStats, etc.
• Application Job Constraints
• Timeouts / Deadlocks, Concurrency, etc.

31
Shrinking Batch Window

• Elapsed Time Resource Constraint
• Begin Here
• Review batch schedule (using tool)
• Trend analysis
• Potential parallelism opportunities
• Create Top n Report for batch cycle

32
Shrinking Batch Window - Study

• PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
  UPDATES DELETES CLASS1 EL.TIME CLASS2 EL.TIME
  GETPAGES SYN.READ
• PLANNAME DISTRS COMMIT FETCHES OPENS
  CLOSES PREPARE CLASS1 CPUTIME CLASS2 CPUTIME
  BUF.UPDT TOT.PREF
• ---------- ------- ------- ------- -------
  ------- ------- -------------- --------------
  -------- --------
•  
• PRODDM15 1 0 0.00 0.00
  0.00 0.00 11653.512404 11517.386423
  53177.4K 35644.00
• DSNTIB71 0 1 9127.3K 1.00
  1.00 3.00 4139.881114 4112.572460
  11302.6K 738.7K
•  
• PRODDX07 1 0 4237.7K 4248.0K
  4214.0K 0.00 13005.808801 12420.802578
  43838.9K 3445.3K
• CEF001BP 0 5745 0.00 0.00
  0.00 0.00 2124.313339 1628.946885
  13421.6K 2405.00
• CBT001BV 3 1 1582.8K 0.00
  0.67 0.00 31044.137728 20812.472110
  111.8M 542.00
• DBT1 0 28620 953.3K 38.1K
  38.1K 0.00 14633.792589 13708.071649
  0.00 13196.67

PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- -------
-------   PRODDM15 1 0 0.00
0.00 0.00 0.00 DSNTIB71 0 1
9127.3K 1.00 1.00 3.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- --------
--------   PRODDM15 11653.512404
11517.386423 53177.4K 35644.00 DSNTIB71
4139.881114 4112.572460 11302.6K 738.7K
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- ------- -------
PRODDX07 1 0 4237.7K 4248.0K
4214.0K 0.00 CEF001BP 0 5745
0.00 0.00 0.00 0.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- --------
-------- PRODDX07 13005.808801
12420.802578 43838.9K 3445.3K CEF001BP
2124.313339 1628.946885 13421.6K 2405.00
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- ------- -------
CBT001BV 3 1 1582.8K 0.00
0.67 0.00 DBT1 0 28620 953.3K
38.1K 38.1K 0.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- --------
-------- CBT001BV 31044.137728
20812.472110 111.8M 542.00 DBT1
14633.792589 13708.071649 0.00 13196.67
33
Death by Random I/O
Accounting SHORT Report PRIMAUTH CLASS1
EL.TIME CLASS2 EL.TIME GETPAGES SYN.READ
PLANNAME CLASS1 CPUTIME CLASS2 CPUTIME BUF.UPDT
TOT.PREF -------------------------
-------------- -------- -------- P0DWDZ30
2657.828009 2629.551153 10826.4K 231.8K
DSNTIB71 906.093016 854.703632 9240.2K
587.3K -----------------------------------------
---------------------------- PROGRAM NAME CL7
ELAP.TIME CL7 CPU TIME CL8 SUSP.TIME CL8
SUSP DSNTIAUL 2629.551149 854.703629
1503.282831 578.8K ------------------------
--------------------------------------------
Accounting LONG Report AVERAGE DB2 (CL.2)
CLASS 3 SUSPENSIONS AVERAGE TIME
AV.EVENT ------------ ----------
-------------------- ------------
-------- ELAPSED TIME 2629.5512
LOCK/LATCH(DB2IRLM) 2.248301 21759.20
NONNESTED 2629.5512 SYNCHRON. I/O
646.292538 231.8K STORED PROC 0.000000
DATABASE I/O 646.292538 231.8K UDF
0.000000 LOG WRITE I/O
0.000000 0.00 TRIGGER 0.000000
OTHER READ I/O 812.470159 324.0K
OTHER WRTE I/O
1.966059 1237.60

• Synchronous I/O Wait
• Use of surrogate keys
• Non-standard clustering
• High (Total) Wait Time
• Begin Here
• Top n - Synchronous I/O Wait
• EXPLAIN cross-reference

Accounting LONG Report (DSNDB07) BP7 BPOOL
ACTIVITY AVERAGE TOTAL ---------------------
-------- -------- BPOOL HIT RATIO ()
52.60 N/A GETPAGES 7975.4K
39876905 BUFFER UPDATES 8006.1K
40030431 SYNCHRONOUS WRITE 0.00
0 SYNCHRONOUS READ 4267.80 21339 SEQ.
PREFETCH REQS 486.4K 2431985 LIST
PREFETCH REQS 0.00 0 DYN.
PREFETCH REQS 0.00 0 PAGES READ
ASYNCHR. 3775.8K 18879044
Accounting LONG Report (Tablespaces) BP10
BPOOL ACTIVITY AVERAGE TOTAL ---------------
------ -------- -------- BPOOL HIT RATIO ()
38.46 N/A GETPAGES
2215.3K 11076745 BUFFER UPDATES 1234.1K
6170329 SYNCHRONOUS WRITE 0.00
0 SYNCHRONOUS READ 97963.00 489815 SEQ.
PREFETCH REQS 92302.40 461512 LIST
PREFETCH REQS 0.00 0 DYN.
PREFETCH REQS 4826.80 24134 PAGES READ
ASYNCHR. 1265.3K 6326306
Accounting LONG Report (Indexes) BP20 BPOOL
ACTIVITY AVERAGE TOTAL ---------------------
-------- -------- BPOOL HIT RATIO ()
63.34 N/A GETPAGES 635.2K
3176216 BUFFER UPDATES 0.00
0 SYNCHRONOUS WRITE 0.00
0 SYNCHRONOUS READ 129.5K 647661 SEQ.
PREFETCH REQS 132.40 662 LIST
PREFETCH REQS 0.00 0 DYN.
PREFETCH REQS 3595.40 17977 PAGES READ
ASYNCHR. 103.4K 516861
34
Strategies for Reorg Stats Hints Rebind

• When and How Often Do You Execute Each?
• Lots of Resources Consumed by
• Reorg ( Copy?)
• RunStats
• How Often Do Access Paths Change?
• Choose Strategy Based on Resources Available
• Begin Here
• Health checks
• EXPLAIN history

35
Strategies for Reorg Stats Hints Rebind

• Data-Driven
• High freespace, non-volatile tables, careful
  COMPRESS, Stats once, few Hints
• Activity-Driven
• Medium freespace, some volatile tables, lots of
  COMPRESS, Stats when things change, some Hints,
  semi-regular ReBinds
• Performance-Driven
• Little freespace, volatile tables, many indexes,
  some COMPRESS, infrequent Stats, few Hints,
  regular ReBinds

36
Capacity Planning

• Resource Trending
• CPU Capacity
• DASD Capacity (and configuration)
• Throughput Capacity
• Forecasting Constraints

37
Where to Begin

• Top n Reports
• Setup
• Initial Review
• Automation
• Health Checks
• Determine Relevance
• Execute, Review Automate
• Tool Review and Education

38
Lock Lyon
Session B11 Case Studies in DB2 for z/OS
Performance Tuning Part 2

• Fifth Third Bancorp
• llyon_at_power-net.net

www.mdug.org Download Presentations