Monitoring SQL Performance Using the V8 EXPLAIN STMTCACHE and the DB2 9 EXPLAIN MONITORED STMTS

1
Monitoring SQL Performance Using the V8 EXPLAIN
STMTCACHE and the DB2 9 EXPLAIN MONITORED STMTS
2
Outline

• Activating statement cache monitoring
• Detecting exceptions
• Obtaining access paths
• Activating profile monitoring (DB2 9)
• Keeping a performance database

3
Turning on the Cache

• ZPARM CACHEDYN defaults to YES for DB2 V8.
• Dynamic statement cache
• An EDM pool in which DB2 saves prepared SQL
  statements for sharing among different
  threads, plans, and packages.
• Saves re-preparation cost.
• Eligible statements SELECT UPDATE INSERT
  DELETE MERGE
• Not statements in plans or packages bound with
  REOPT(ALWAYS).
• Conditions for sharing
• Identical statements
• Same authorization ID or role
• Same bind options
• Same values of special registers
• Static SQL (DB2 9 only)

4
Dynamic SQL Statement CachingMeasuring Cache
Effectiveness
Statement Pool Full Failures Should be 0 Increase
Statement Pool Size if not
Global Cache Hit Ratio Shoot for 90
Local Cache Hit Ratio Specific for Applications
bound with KEEPDYNAMIC(YES)
Statement Discarded Shoot for 0 Increase MAXKEEPD
5
Extracting Information from the Dynamic Statement
Cache

• Execution statistics for dynamic SQL statements
• Turn on collection with Monitor trace IFCID 318
• Begins collecting statistics and accumulates them
  for the length of time the monitor trace is on
• Stop Monitor trace resets all statistics
• 2-4 overhead per dynamic SQL statement stored in
  the cache
• Recommended approach
• Run the trace only when actively monitoring the
  cache
• Use EXPLAIN STMTCACHE to externalize data for
  evaluation

6
-START TRACE(MON) IFCID(318)

• DEST(GTF) for DB2 9
• PLAN(planname)
• AUTHID(authorization_id)

7
CPU time overhead of trace
8
DSN_STATEMENT_CACHE_TABLE
owner.DSN_STATEMENT_CACHE_TABLE
9
Query cache_table to get stmt_id

• SELECT STMT_ID, STAT_EXEC AS EXEC,
• DEC(STAT_ELAP,5,2) AS ELAPSED,
• DEC(STAT_CPU,5,2) AS CPU,
  
• SUBSTR(STMT_TEXT,1,80)
  
• FROM DSN_STATEMENT_CACHE_TABLE
  
• WHERE STAT_CPU gt 1.0
  
• ORDER BY STAT_CPU DESC
  

10
Most expensive statements

• STMT_ID EXEC ELAPSED CPU
• 18846 1 55.65 2.36 SELECT
• 18830 1 48.07 2.07 SELECT
• 18847 1 49.19 1.76 SELECT
• 18618 0 3.90 1.49 select

11
Use DSNREXX to retrieve the SQL

• sqlstmt "select stmt_text from
  dsn_statement_cache_table where stmt_id "
  stmt_id
• "execsql prepare s1 from sqlstmt"
  
• "execsql open c1"
  
• "execsql fetch c1 into stmt_text"
  
• "execsql close c1"
  
• call fmtsql(stmt_text)
  

12
Formatted SQL text

• SELECT A.
  
• FROM "SYSIBM"."SYSCOLUMNS" A , "SYSIBM"."SYSINDEXE
  S" B, "SYSIBM"."SYSKEYS" C
  
• WHERE C.IXCREATOR 'NSU911C1'
  
• AND C.IXNAME 'DXRSSX0'
  
• AND C.COLSEQ 1
  
• AND B.CREATOR C.IXCREATOR
  
• AND B.NAME C.IXNAME
  
• AND A.TBCREATOR B.TBCREATOR
  
• AND A.TBNAME B.TBNAME
  
• AND A.COLNO C.COLNO FOR FETCH ONLY
  

13
EXPLAIN STMTCACHE STMTID nnn
PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE
14
Query plan_table

• Select qblockno, planno, method, accesstype,
  tname, accessname, matchcols
• From plan_table
• Where queryno 18846
• Order by qblockno, planno, mixopseq

15
Access path steps

• QB PL ME AC TB IX MC
• 1 1 0 I SYSINDEXES DSNDXX01 2
• 1 2 1 I SYSCOLUMNS DSNDCX01 2
• 1 3 1 R SYSKEYS 0

16
Review the SQL text

• SELECT A.
  
• FROM "SYSIBM"."SYSCOLUMNS" A , "SYSIBM"."SYSINDEXE
  S" B, "SYSIBM"."SYSKEYS" C
  
• WHERE C.IXCREATOR 'NSU911C1'
  
• AND C.IXNAME 'DXRSSX0'
  
• AND C.COLSEQ 1
  
• AND B.CREATOR C.IXCREATOR
  
• AND B.NAME C.IXNAME
  
• AND A.TBCREATOR B.TBCREATOR
  
• AND A.TBNAME B.TBNAME
  
• AND A.COLNO C.COLNO FOR FETCH ONLY
  

17
What if?

• SYSIBM.SYSKEYS has index on IXCREATOR, IXNAME,
  COLNAME
• AND A.COLNO C.COLNO
• AND A.NAME C.COLNAME

18
EXPLAIN PLAN SET QUERYNOnnn FOR sql-statement
PLAN_TABLE DSN_STATEMNT_TABLE DSN_FUNCTION_TABLE
DSN_PREDICAT_TABLE DSN_DETCOST_TABLE 9 OTHER
TABLES
19
New access path steps

• QB PL ME AC TB IX MC
• 1 1 0 I SYSINDEXES DSNDXX01 2
• 1 2 1 I SYSCOLUMNS DSNDCX01 2
• 1 3 1 I SYSKEYS DSNDKX01 3
• Service Units 502 2

20
Summary of method

• Explain stmtcache all
• Select stat_cpu, stmt_id from dsn_statement_cache_
  table
• Select stmt_text from dsn_statement_cache_table
  where stmt_id ?
• Explain stmtcache stmtid ?
• Select from plan_table where queryno ?
• Explain plan set queryno xxx for
• Select from plan_table where queryno xxx

21
Profile monitoring

• DB2 9 introduced
• Input tables control monitoring
• Lossless pushout
• Static SQL too
• But no wildcards

22
SYSIBM.DSN_PROFILE_TABLE
23
SYSIBM.DSN_PROFILE_ATTRIBUTES
24
Function keywords
25
ATTRIBUTE2

• eexplain
• oobject info
• sstatement info
• e eeooosss 00000eos
• 1 11000111 00000101
• 1 C 7 0 5
• 116485

26
-START PROFILE
Monitors profiles where PROFILE_ENABLED Y
-STOP TRACE(MON) DEST(GTF)
-DIS PROFILE DSNT753I DIA1 DSNT1DSP DISPLAY
PROFILE REPORT FOLLOWS STATUS ON TIMESTAMP
2008-09-25-21.21.57.407471 PUSHOUTS 3578 OUT OF
10000 DISPLAY PROFILE REPORT COMPLETE. DSN9022I
-DB9A DSNT1DSP 'DISPLAY PROFILE' NORMAL COMPLETION
27
Tables that receive data
DSN_STATEMENT_RUNTIME_INFO
DSN_OBJECT_RUNTIME_INFO
PLAN TABLES
28
CPU time overhead of monitoring
29
EXPLAIN MONITORED STMTS SCOPE

• AUTHID authid IPADDR nn.nn.nn.nn
• PLAN planname
• COLLECTION collid PACKAGE package

EXPLAIN MONITORED STMTS SCOPE PLAN p1'
COLLECTION 'c1' PACKAGE 'pak1'
DSN_STATEMENT_RUNTIME_INFO sqlid.PLAN_TABLE sqlid.
DSN_STATEMNT_TABLE sqlid.DSN_FUNCTION_TABLE
30
DSN_STATEMENT_RUNTIME_INFO
31
DSN_OBJECT_RUNTIME_INFO
32
Keeping a performance database
Hourly Daily
Monthly
33
Scrubbing the SQL text

• SELECT FROM SYSTABLES
• WHERE CARDF lt 0.0 AND NAME LIKE DSN
• SELECT FROM SYSTABLES WHERE CARDF lt . AND
  NAME LIKE

34
Computing a text hash

• Scrubbed text Qualifier
• SELECT FROM SYSTABLES WHERE CARDF lt . AND
  NAME LIKE SYSIBM
• 2302380

35
Updating the performance database

• MERGE INTO DAILY
  
• USING ( VALUES(?,?,?,?,?,?,?) ) AS T ( HASH,
  PROG, EXEC, CPU, TS, SCHEMA, TEXT)
  
  
• ON (DAILY. STMT_HASH T.HASH)
  
  
• WHEN MATCHED THEN UPDATE SET DAILY.STAT_EXEC
  DAILY.STAT_EXEC T.EXEC, DAILY.STAT_CPU
  DAILY.STAT_CPU T.CPU
• WHEN NOT MATCHED THEN INSERT VALUES ( T.HASH,
  T.PROG, T.EXEC, T.CPU, T.TS, T.SCHEMA, T.TEXT)
  

36
Spikes
37
Trends
38
Summary

• Explain stmtcache all
• Explain stmtcache stmid
• Explain plan for
• Explain monitored stmts
• Merge
• Covariance

39
Summary

• It is all about to tame the dynamic SQLs
• DB2 v8 DB2 9 a lot more to offer
• Special registers
• SQLESETI/JDBC/RRS Signon to set client variables
• ACCUMAC/ACCUMID to reduce SMF records
• IFCID 350
• RUNSTATS REPORT/UPDATE NO to invalidate dynamic
  SQL cache
• ZPARM EDMSTMTC
• START TRACE, DSNRLMT, REOPT(AUTO)

40
Acknowledgements

• Thanks to
• Kevin Baker (BMC) and Mike Perry (BMC) for
  providing active support in putting this topic
  together.
• IBM Redbooks on this topic were especially
  helpful in researching this presentation,
  including
• DB2 for z/OS and OS/390 Squeezing the Most Out
  of Dynamic SQL
• DB2 for z/OS Performance Monitoring and Tuning
  Guide
• IBM DB2 9 for z/OS New Tools for Query
  Optimization
• There are numerous documents that discuss SQL in
  general and dynamic SQL in particular, including
• DB2 technical publications
• Technical articles by numerous DB2 Subject Matter
  Experts
• IDUG List Server Archives
• What Every DBA Should Know About Dynamic SQL,
  Suresh Sane, DST Systems
• IDUG 2006 Europe
• Demystifying the DB2 Dynamic Statement Cache,
  Bill Arledge, BMC Software
• IDUG 2007 North America