1 / 36

Static SQL and Access Path Review Tips and Tricks

Static SQL and Access Path Review Tips and Tricks. Paul Walters Sallie Mae Inc. Paul.A.Walters@Salliemae.com Session Code: E05 May 12, 2010 • 08:30 a.m. – 9:30a.m. Platform: DB2 zOS. Agenda . Managing Change Data to Review Plan Tables Objects Statistics SQL Statements Key Indicators

melvyn
Download Presentation

Static SQL and Access Path Review Tips and Tricks

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. Static SQL and Access Path ReviewTips and Tricks Paul Walters Sallie Mae Inc. Paul.A.Walters@Salliemae.com Session Code: E05 May 12, 2010 • 08:30 a.m. – 9:30a.m. Platform: DB2 zOS

  2. Agenda • Managing Change • Data to Review • Plan Tables • Objects • Statistics • SQL Statements • Key Indicators • Statistics and System Changes • Application Changes • Best Practices • Plan Stability DB2 V9

  3. Brief History • How did we get here? Managing Change – Types of Changes & Preserving Performance • DB2 Changes • ZPARM Changes • Statistics Changes • DDL Changes • Application Changes Plan Tables to the Rescue E

  4. Basic Explain DSNWFQB## DSNBFQB## DSNVT##/V9 QUERYNO O

  5. Explain Information by Object

  6. Explain Information by Object A

  7. Explain information by Access SELECT * FROM SYSIBM.SYSDUMMY1 WHERE 1 = 2

  8. Explain information by Access

  9. A Complete Picture Program Execution Function Catalog Statistic PT

  10. Plan Tables (Most familiar) DB2 V9 adds PARENT_PLANNO • PLAN_TABLE • Contains most of the access path selection information that was gathered when the package was bound with EXPLAIN(YES). • DSN_STATEMNT_TABLE • Contains COST_CATEGORY, REASON AND costing information PROCSU, PROCMS. The costing numbers are ESTIMATED and can very widely with changes to DB2. DB2 V9 adds a TOTAL_COST

  11. Plan Tables (Continued) • DSN_FILTER_TABLE • Contains information on what stage the predicate is processed (MATCHING, SCREENING, STAGE1 and STAGE2). • DSN_PREDICAT_TABLE • Contains FILTERFACTOR, BOOLEAN_TERM indicator and the actual predicate text. • DSN_DETCOST_TABLE • Contains detailed cost information (COMPCOST) at each step of the access path (mini plan), an estimate on the number of rows that will be returned after the local predicates are applied (ONECOMPROW) and an estimate of rows returned from DM and RDS (DMROWS,RDSROW). Q

  12. DSN_FILTER_TABLE/DSN_PREDICAT_TABLE

  13. DSN_FILTER_TABLE/DSN_PREDICAT_TABLE

  14. DSN_PREDICAT_TABLE • DSN_PREDICAT_TABLE and SYSCOLUMNS and SYSKEYS provide information helpful for index review

  15. DSN_DETCOST_TABLE

  16. DSN_DETCOST_TABLE OT

  17. Plan Tables (Continued) • DSN_VIRTUAL_INDEXES • This table allows for the creation or deletion of indexes virtually. This enables a query to be explained with the virtual change. • 10 other EXPLAIN Tables • The additional tables support Explain statements from the dynamic statement cache, sort operations or other Explain details.

  18. SQL Statements and Source Code

  19. Objects Object Structure and Buffer Pool Assignments Statistics DB2 Statistics and Real Time Statistics Package Execution/Function

  20. Managing Change: Statistics and System Changes • DB2 V9 Enhancements that require rebinds: • Puffing of the runtime structures – especially with RELEASE(COMMIT) • Required to reestablish SPROCs (with no rebind a 0-10% performance penalty ) • Virtual Storage Constraint Relief • Global Query Optimization • Sort Avoidance with Distinct and Group By You bought it might as well drive it

  21. Managing Change: Statistics and System Changes Release Guide Hold Data • Impacts on Access Path Selection • Identification • Model a Control set of structures Off Production • Copy Production DBRMS • Bind Before and After Maintenance • Review Changes PROCMS PROCSU

  22. Managing Change: Statistics and System Changes I001 No Change W001 C<P Steps W002 C>P Steps W005 Change W003 C<P Stmts W004 C>P Stmts W006 New Package

  23. PERFORMANC E Managing Change: Statistics and System Changes • Managing Rebinds During Upgrades • Wait until the upgrade is stabilized • Rebind the safe and improved packages with PLANMGMT • Execute RUNSTATS – focus on heavily used objects, watch for changes in CLUSTERRATIOF, use “_HIST” tables • Check Access Path Changes with new statistics • Rebind Previously Untouched Packages • Rebind packages bound before new statistics were collected

  24. Managing Change: Application changes • New/Changed Packages • New programs Require top down review • Changed Programs – look for new objects and/or access path changes • New/Changed Objects I001 No Change W001 C<P Steps W002 C>P Steps W003 C<P Stmts W004 C>P Stmts W005 Change W006 New Package

  25. Plan Stability DB2 V9 • Provides a backup of a access path that can be used for fallback • Enabled Globally with ZPARM PLNMGNT or at bind time with PLNMGNT option. • Information Stored in SPT01 and SYSPACKDEP • Rebind Switch • Each Copy is Separately Invalidated • Only Current information is stored in SYSPACKAGE • Dependant versions recorded in SYSPACKDEP(DTYPE P,O) • Can only replace the entire package • How Stale is the Original Copy • DB2 Hints are still Relevant

  26. Other DB2 V9 Changes • Virtual Indexes – available via APAR in V8 (PK46687) • Unicode Plan Tables (PK85068) • New Explain Sub-Query Parent Query Block • Visual Explain is Deprecated in DB2 V9 • Optimization Service Center (OSC) Deprecated in the next Release of DB2 • IBM Data Studio is the replacement for OSC

  27. Best Practices • Explain(YES) • Regular Review • Know Your Applications

  28. WITH STMT1(COLLID,PROGNAME,BIND_TIME) AS (SELECT COLLID,NAME,MAX(BINDTIME) FROM SYSIBM.SYSPACKAGE WHERE LOCATION = ' ' AND BINDTIME > CURRENT TIMESTAMP - 84 HOURS AND TYPE <> 'T' AND EXPLAIN = 'Y' AND (COLLID LIKE 'PLSF%') GROUP BY COLLID,NAME ) ,STMT2(COLLID,PROGNAME,BIND_TIME) AS (SELECT S1.COLLID,S1.PROGNAME, COALESCE(MAX(PT.BIND_TIME),'0001-01-01-00.00.00.000000') FROM PDBA0.PLAN_TABLE AS PT, STMT1 AS S1 WHERE PT.COLLID = S1.COLLID AND PT.PROGNAME = S1.PROGNAME AND PT.BIND_TIME < S1.BIND_TIME AND PT.OPTHINT = ' ' GROUP BY S1.COLLID,S1.PROGNAME) Identify Packages BINDTIME with Explain Yes and The previous BIND_TIME from the Plan_Table

  29. ,STMT_REV(COLLID,PROGNAME,BIND_TIME,CSCNT,CPCNT, PSCNT,PPCNT,WARNING) AS (SELECT COLLID,PROGNAME,BIND_TIME ,SUM(CSCNT) AS CSCNT ,SUM(CPCNT) AS CPCNT ,SUM(PSCNT) AS PSCNT ,SUM(PPCNT) AS PPCNT ,CASE WHEN SUM(PSCNT) = 0 THEN 'W006' WHEN SUM(CSCNT) > SUM(PSCNT) THEN 'W004' WHEN SUM(CSCNT) < SUM(PSCNT) THEN 'W003' WHEN SUM(CPCNT) > SUM(PPCNT) THEN 'W002' WHEN SUM(CPCNT) < SUM(PPCNT) THEN 'W001' ELSE NULL END AS WARNING FROM First Cut W001 C<P Steps W002 C>P Steps W003 C<P Stmts W004 C>P Stmts W006 New Package

  30. Total Steps and Statements from the Current and Previous Explain output (SELECT S1.COLLID,S1.PROGNAME,S1.BIND_TIME ,COUNT(DISTINCT QUERYNO) AS CSCNT,COUNT(*) AS CPCNT ,0 AS PSCNT ,0 AS PPCNT FROM STMT1 AS S1, PDBA0.PLAN_TABLE AS PT WHERE PT.PROGNAME = S1.PROGNAME AND PT.COLLID = S1.COLLID AND PT.BIND_TIME = S1.BIND_TIME AND PT.OPTHINT = ' ' GROUP BY S1.COLLID,S1.PROGNAME,S1.BIND_TIME UNION ALL SELECT S1.COLLID,S1.PROGNAME,S1.BIND_TIME ,0 AS CSCNT ,0 AS CPCNT ,COUNT(DISTINCT QUERYNO) AS PSCNT ,COUNT(*) AS PPCNT FROM STMT1 AS S1, STMT2 AS S2, PDBA0.PLAN_TABLE AS PT WHERE PT.PROGNAME = S2.PROGNAME AND PT.COLLID = S2.COLLID AND S1.PROGNAME = S2.PROGNAME AND S1.COLLID = S2.COLLID AND PT.BIND_TIME = S2.BIND_TIME AND PT.OPTHINT = ' ' GROUP BY S1.COLLID,S1.PROGNAME,S1.BIND_TIME ) AS DATA GROUP BY COLLID,PROGNAME,BIND_TIME)

  31. ,CDATA(COLLID,PROGNAME,BIND_TIME, SEQ ,QUERYNO,QBLOCKNO,PLANNO,MIXOPSEQ,DATA) AS (SELECT S1.COLLID,S1.PROGNAME,S1.BIND_TIME, CSEQ.SEQ ,QUERYNO ,QBLOCKNO ,PLANNO ,MIXOPSEQ, {COLUMN1 CONCAT COLUMN2….} FROM STMT1 AS S1, PDBA0.PLAN_TABLE AS PT, TABLE (SELECT COUNT(*)+1 AS SEQ FROM PDBA0.PLAN_TABLE AS PT1 WHERE PT.PROGNAME = PT1.PROGNAME AND PT.COLLID = PT1.COLLID AND PT.BIND_TIME = PT1.BIND_TIME AND PT.OPTHINT = PT1.OPTHINT AND DIGITS(PT.QUERYNO) CONCAT DIGITS(PT.QBLOCKNO) CONCAT DIGITS(PT.PLANNO) CONCAT DIGITS(PT.MIXOPSEQ) > DIGITS(PT1.QUERYNO) CONCAT DIGITS(PT1.QBLOCKNO) CONCAT DIGITS(PT1.PLANNO) CONCAT DIGITS(PT1.MIXOPSEQ) ) AS CSEQ WHERE PT.PROGNAME = S1.PROGNAME AND PT.COLLID = S1.COLLID AND PT.BIND_TIME = S1.BIND_TIME AND PT.OPTHINT = ' ') Select the Current Explain Data with a New Sequence Number

  32. ,PDATA(COLLID,PROGNAME, SEQ ,QUERYNO ,QBLOCKNO ,PLANNO ,MIXOPSEQ, DATA) AS (SELECT S2.COLLID,S2.PROGNAME, PSEQ.SEQ ,QUERYNO ,QBLOCKNO ,PLANNO ,MIXOPSEQ , {COLUMN1 CONCAT COLUMN2….} FROM STMT2 AS S2, PDBA0.PLAN_TABLE AS PT, TABLE (SELECT COUNT(*)+1 AS SEQ FROM PDBA0.PLAN_TABLE AS PT1 WHERE PT.PROGNAME = PT1.PROGNAME AND PT.COLLID = PT1.COLLID AND PT.BIND_TIME = PT1.BIND_TIME AND PT.OPTHINT = PT1.OPTHINT AND DIGITS(PT.QUERYNO) CONCAT DIGITS(PT.QBLOCKNO) CONCAT DIGITS(PT.PLANNO) CONCAT DIGITS(PT.MIXOPSEQ) > DIGITS(PT1.QUERYNO) CONCAT DIGITS(PT1.QBLOCKNO) CONCAT DIGITS(PT1.PLANNO) CONCAT DIGITS(PT1.MIXOPSEQ) ) AS PSEQ WHERE PT.PROGNAME = S2.PROGNAME AND PT.COLLID = S2.COLLID AND PT.BIND_TIME = S2.BIND_TIME AND PT.OPTHINT = ' ') Select the Previous Explain Data with a New Sequence Number

  33. SELECT CHAR(C.COLLID,10) AS COLLID ,CHAR(C.PROGNAME,10) AS PROGNAME C.BIND_TIME, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN C.SEQ ELSE 0 END AS SEQ, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN 'W005' ELSE SR.WARNING END AS WARNING ,SR.CSCNT,SR.CPCNT, SR.PSCNT,SR.PPCNT, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN C.QUERYNO ELSE 0 END AS QUERYNO, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN C.QBLOCKNO ELSE 0 END AS QBLOCKNO, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN C.PLANNO ELSE 0 END AS PLANNO, CASE WHEN SR.WARNING IS NULL AND C.DATA <> P.DATA THEN C.MIXOPSEQ ELSE 0 END AS MIXOPSEQ FROM CDATA AS C LEFT OUTER JOIN PDATA AS P ON P.COLLID = C.COLLID AND P.PROGNAME = C.PROGNAME AND P.SEQ = C.SEQ INNER JOIN STMT_REV AS SR ON SR.COLLID = C.COLLID AND SR.PROGNAME = C.PROGNAME AND SR.BIND_TIME = C.BIND_TIME WHERE (C.DATA <> P.DATA OR WARNING IS NOT NULL)

  34. UNION SELECT CHAR(C.COLLID,10) AS COLLID ,CHAR(C.PROGNAME,10) AS PROGNAME, C.BIND_TIME ,0 AS SEQ, 'I001' AS WARNING ,SR.CSCNT,SR.PSCNT, SR.CPCNT,SR.PPCNT , 0 AS QUERYNO, 0 AS QBLOCKNO, 0 AS PLANNO, 0 AS MIXOPSEQ FROM CDATA AS C INNER JOIN STMT_REV AS SR ON SR.COLLID = C.COLLID AND SR.PROGNAME = C.PROGNAME AND SR.BIND_TIME = C.BIND_TIME WHERE WARNING IS NULL AND NOT EXISTS (SELECT 1 FROM PDATA AS P WHERE P.COLLID = C.COLLID AND P.PROGNAME = C.PROGNAME AND P.SEQ = C.SEQ AND P.DATA <> C.DATA) ORDER BY 3 DESC FOR FETCH ONLY WITH UR UNION the Remainder/Unchanged Packages

  35. Reference Material • Redbooks • Data Integrity with DB2 for z/OS • DB2 9 for z/OS Performance Topics • Manuals • DB2 V9 Performance Monitoring and Tuning Guide • Web • DB2 for z/OS Exchange at IBM.COM   • WWW.IDUG.ORG

  36. Session: E05Static SQL and Access Path ReviewTips and Tricks Paul Walters Sallie Mae Inc.Paul.A.Walters@Salliemae.com

More Related