static sql and access path review tips and tricks paul walters sallie mae inc....

Static SQL and Access Path ReviewTips and Tricks

Paul WaltersSallie 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

• Statistics and System Changes• Application Changes• Best Practices• Plan Stability DB2 V9

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

Basic Explain

O

DSNWFQB##DSNBFQB##DSNVT##/V9

QUERYNO

Explain Information by Object

Explain Information by Object

A

SELECT * FROM SYSIBM.SYSDUMMY1WHERE 1 = 2

Explain information by Access

Explain information by Access

A Complete Picture

Program Execution FunctionCatalog Statistic

PT

Plan Tables (Most familiar)

• 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

DB2 V9 addsPARENT_PLANNO

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

DSN_FILTER_TABLE/DSN_PREDICAT_TABLE

DSN_FILTER_TABLE/DSN_PREDICAT_TABLE

DSN_PREDICAT_TABLE

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

DSN_DETCOST_TABLE

DSN_DETCOST_TABLE

OT

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.

SQL Statements and Source Code

ObjectsObject Structure and Buffer Pool Assignments

StatisticsDB2 Statistics and Real Time Statistics

Package Execution/Function

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

Managing Change: Statistics and System Changes

• Impacts on Access Path Selection

• Identification Model a Control set of structures

Off Production Copy Production DBRMS Bind Before and After Maintenance Review Changes

PROCMSPROCSU

Release GuideHold Data

Managing Change: Statistics and System Changes

I001 No ChangeW001 C<P StepsW002 C>P StepsW005 ChangeW003 C<P StmtsW004 C>P StmtsW006 New Package

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

PERFORMANCE

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 ChangeW001 C<P StepsW002 C>P StepsW003 C<P StmtsW004 C>P StmtsW005 ChangeW006 New Package

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

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

Best Practices

• Explain(YES)

• Regular Review

• Know Your Applications

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

,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 StepsW002 C>P StepsW003 C<P StmtsW004 C>P StmtsW006 New Package

(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)

Total Steps and Statements from the Current and Previous Explain output

,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

,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

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)

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

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

Session: E05Static SQL and Access Path ReviewTips and Tricks

Paul WaltersSallie Mae Inc.

Paul.A.Walters@Salliemae.com