1 / 36

DB2 Performance & Tuning with iStrobe

DB2 Performance & Tuning with iStrobe . Frank Schuler. Strobe MF Application Performance Management Field Technical Support. September 20, 2012. DB2 Performance & Tuning with iStrobe. Strobe & iStrobe CICS Case Study Batch Case Study DB2 / DDF Case Study

ince
Download Presentation

DB2 Performance & Tuning with iStrobe

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Performance & Tuning with iStrobe Frank Schuler Strobe MF Application Performance Management Field Technical Support September 20, 2012

  2. DB2 Performance & Tuning with iStrobe • Strobe & iStrobe • CICS Case Study • Batch Case Study • DB2 / DDF Case Study • Consider DB2 System Services

  3. Strobe Measurement Pgm Execution Add Request The Strobe Process Measuring iStrobe StrobeProfile StrobeReporter Sample Dataset Reporting

  4. What Strobe Sees UserPrograms Library Routines CPU CICS STROBE drills down through all the layers of your OS/390 address space to pinpoint specific areas of resource demand Database & IO Subsystems OS/390 Operating System Database Management Systems I/O Subsystems I/O I/O Devices Data sets

  5. What Strobe Sees UserPrograms CPU Library Routines CICS Database and IO Subsystems STROBE drills down through all the layers of your OS/390 address space to pinpoint specific areas of resource demand OS/390 Operating System Database Management Systems I/O Subsystems I/O I/O Devices Data sets

  6. Analysis - iStrobe • Browser-based product for • Navigating performance reports • Analyzing and improving performance • Supplies tips for improving performance • Module HELP database supplies descriptions and performance hints for system services • SQL Analysis Feature makes recommendations for improving SQL performance

  7. iStrobe Automates the Strobe Profile Report Interpretation Process • iStrobe improves Strobe ease of use by providing a GUI home page look and feel for interpreting profile reporting • Look for the biggest number or the longest line • “Click until you click no more” (+ expands on current page) • Jump to next report via ( Report Icon) • 5 clicks or less to the root cause

  8. Traditional Strobe Text Based Reports

  9. iStrobe Automates Strobe Interpretation and Analysis

  10. iStrobe – Initial Panel

  11. CICS Case Study 12

  12. iStrobe 4.2 – Measurement Session Data - CICS

  13. iStrobe 4.2 – Program CPU Usage CA Detector

  14. iStrobe 4.2 – DB2 Activity by Query

  15. iStrobe 4.2 – SQL Analysis

  16. iStrobe 4.2 – DB2 Explain Expanded

  17. iStrobe 4.2 – DB2 Catalog Statistics

  18. Analysis Summary • XIE1RULE: 206K Rows / 5.1K Pages • XPKCLM_Rule(1) 277K Rows / 3.2K Pages • Non-Matching Index Scan • Consider adding an index to Table ITS.RULE to change the Non-Matching Index Scan to a Matching Index Only Scan as follows: • Column 1 – PRFX_CD • 2 – STS_QUAL_CD • 3 – STS_CD • 4 – LOC_PLAN_CD • 5 – PLAN_PROFL_UID • Get Pages & CPU reduced

  19. Batch 20

  20. iStrobe 4.2 – Measurement Session Data - Batch

  21. iStrobe 4.2 – Program CPU Usage CA Detector

  22. iStrobe 4.2 – DB2 Activity by Query

  23. iStrobe 4.2 – DB2 Explain

  24. iStrobe 4.2 – DB2 Catalog Statistics

  25. Analysis Summary • ACSIS.OPEN: 259K Rows / 5.5K Pages • There are 259,000 rows on this table but the cursor is opened nearly 5 million times. Resulting in a large number of duplicates. • Recommend checking programmatically to see if the program will be accessing the same row(s) and not make the open again. • Table ACSIS.OPEN. PCTROWCOMP is 0.0 • Consider turning DB2 Compression on if the NPAGES is greater than 500 and the DB2 compression utility DSN1COMP indicates the compression ratio would be greater than 30%

  26. DDF 27

  27. iStrobe 4.2 – Measurement Session Data - DDF

  28. iStrobe 4.2 – DB2 Stored Procedure SQL Activity

  29. iStrobe 4.2 – DB2 Explain

  30. iStrobe 4.2 – DB2 Catalog Statistics

  31. Analysis Summary • XI18S1 6.5M Rows / 303K Pg • Non-Matching Index Scan • Consider adding an index to Table TXT18S_BILL_VALIDATION to change the Non-Matching Index Scan to a Matching Index Only Scan as follows: • Column 1 – ACCOUNT_ID • 2 – CIRCUIT_ID • 3 – PON • 4 – USOC • 5 – JURISDICTION_CD • 6 – BILL_DATE  • Get Pages & CPU reduced

  32. Consider what DB2 System Services is doing on behalf of your SQL as part of your analysis 34

  33. Database Services Address Space • Checks SQL statements • Processes Stage 2 predicates • Performs sort and optimization Relational Data System (RDS) Data Manager (DM) • Processes Stage 1 predicates • Processes indexable predicates • Interfaces with IRLM Buffer Manager (BM) • Manages bufferpools • Moves data to and from DASD DB2 Data

  34. DB2 Address Space Reporting #PUP ** PROGRAM USAGE BY PROCEDURE ** .SYSTEM SYSTEM SERVICES .DB2 DB2 SYSTEM SERVICES MODULE SECTION FUNCTION % CPU TIME MARGIN OF ERROR 6.84% NAME NAME SOLO TOTAL 00 8.00 16.00 DSNBBM DSNB1GET RETRIEVE REQUESTED PAGE 3.41 3.41 ***** DSNBBM DSNB1REL PAGE RELEASE ROUTINE .49 .49 * DSNECP10 DSNAPRH PGM REQUEST APPL INTERF 1.46 1.46 ** DSNECP10 DSNECP21 PARSE DSN COMMAND .49 .49 * DSNGEDM DATA MGT DBD/SKCT RTNS .49 .49 * DSNIDM DSNICSMP CHK MAP PAGE CONSISTENC .49 .49 * DSNIDM DSNICUBD ADD AN OBJECT TO CUB DY .49 .49 * DSNIDM DSNIMSAR RELEASE ALL MSA RESOURC 1.46 1.46 ** DSNIDM DSNINXTP READ SPECFD PAG USNG MS .49 .49 * DSNIDM DSNIOST2 SET ON CUB DEF ON IX FA .98 .98 ** DSNIDM DSNIPSBL LOC AN EXISTING PSCB .49 .49 * DSNIDM DSNIRNXT FETCH NEXT ROW TO PROG 14.15 14.15 ****************** DSNIDM DSNIRPRV TABLE SPACE SCAN BACKWA .49 .49 * DSNXGRDS RDS ACCESS MODULE GENER .32 .32 (subsequent lines omitted) Strobe will identify the DB2 services utilized by the SQL

  35. DB2 Address Space Reporting #PUP ** PROGRAM USAGE BY PROCEDURE ** .SYSTEM SYSTEM SERVICES .DB2 DB2 SYSTEM SERVICES MODULE SECTION FUNCTION % CPU TIME MARGIN OF ERROR 6.84% NAME NAME SOLO TOTAL 00 8.00 16.00 DSNBBM DSNB1GET RETRIEVE REQUESTED PAGE 3.41 3.41 ***** DSNBBM DSNB1REL PAGE RELEASE ROUTINE .49 .49 * DSNECP10 DSNAPRH PGM REQUEST APPL INTERF 1.46 1.46 ** DSNECP10 DSNECP21 PARSE DSN COMMAND .49 .49 * DSNGEDM DATA MGT DBD/SKCT RTNS .49 .49 * DSNIDM DSNICSMP CHK MAP PAGE CONSISTENC .49 .49 * DSNIDM DSNICUBD ADD AN OBJECT TO CUB DY .49 .49 * DSNIDM DSNIMSAR RELEASE ALL MSA RESOURC 1.46 1.46 ** DSNIDM DSNINXTP READ SPECFD PAG USNG MS .49 .49 * DSNIDM DSNIOST2 SET ON CUB DEF ON IX FA .98 .98 ** DSNIDM DSNIPSBL LOC AN EXISTING PSCB .49 .49 * DSNIDM DSNIRNXT FETCH NEXT ROW TO PROG 14.15 14.15 ****************** DSNIDM DSNIRPRV TABLE SPACE SCAN BACKWA .49 .49 * DSNXGRDS RDS ACCESS MODULE GENER .32 .32 (subsequent lines omitted) Strobe will identify the DB2 services utilized by the SQL

  36. Thank You

More Related