1 / 39

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

Monitoring SQL Performance Using the V8 EXPLAIN STMTCACHE and the DB2 9 EXPLAIN MONITORED STMTS. Outline. Activating statement cache monitoring Detecting exceptions Obtaining access paths Activating profile monitoring (DB2 9) Keeping a performance database. Turning on the Cache .

darshan
Download Presentation

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

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. 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 Statement cache EDM pool DBM1 EDMSTMTC=nnnn (KB) • 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 Statement cache owner.DSN_STATEMENT_CACHE_TABLE • 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 CREATE TABLE DSN_STATEMENT_CACHE_TABLE (STMT_ID INTEGER NOT NULL, STMT_TOKEN VARCHAR(240) , COLLID VARCHAR(128) NOT NULL, PROGRAM_NAME VARCHAR(128) NOT NULL, INV_DORPALT CHAR(1) NOT NULL, INV_REVOKE CHAR(1) NOT NULL, INV_LRU CHAR(1) NOT NULL, INV_RUNSTATS CHAR(1) NOT NULL, CACHED_TS TIMESTAMP NOT NULL, USERS INTEGER NOT NULL, COPIES INTEGER NOT NULL, LINES INTEGER NOT NULL, PRIMAUTH VARCHAR(128) NOT NULL, CURSQLID VARCHAR(128) NOT NULL, BIND_QUALIFER VARCHAR(128) NOT NULL, BIND_ISO CHAR(2) NOT NULL, BIND_CDATA CHAR(1) NOT NULL, BIND_DYNRL CHAR(1) NOT NULL, BIND_DEGRE CHAR(1) NOT NULL, BIND_SQLRL CHAR(1) NOT NULL, BIND_CHOLD CHAR(1) NOT NULL, STAT_TS TIMESTAMP NOT NULL, STAT_EXEC INTEGER NOT NULL, STAT_GPAG INTEGER NOT NULL, STAT_SYNR INTEGER NOT NULL, STAT_WRIT INTEGER NOT NULL, STAT_EROW INTEGER NOT NULL, STAT_PROW INTEGER NOT NULL, STAT_SORT INTEGER NOT NULL, STAT_INDX INTEGER NOT NULL, STAT_RCSN INTEGER NOT NULL, STAT_PGRP INTEGER NOT NULL, STAT_ELAP FLOAT NOT NULL, STAT_CPU FLOAT NOT NULL, STAT_SUS_SYNIO FLOAT NOT NULL, STAT_SUS_LOCK FLOAT NOT NULL, STAT_SUS_SWIT FLOAT NOT NULL, STAT_SUS_GLCK FLOAT NOT NULL, STAT_SUS_OTHR FLOAT NOT NULL, STAT_SUS_OTHW FLOAT NOT NULL, STAT_RIDLIMT INTEGER NOT NULL, STAT_RIDSTOR INTEGER NOT NULL, EXPLAIN_TS TIMESTAMP NOT NULL, SCHEMA VARCHAR(128) NOT NULL, STMT_TEXT CLOB(2M) NOT NULL, STMT_ROWID ROWID NOT NULL GENERATED ALWAYS ) IN database-name.table-space-name CCSID EBCDIC; Identification Invalidation Usage Bind information Statistics Statement text 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 > 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"."SYSINDEXES" 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"."SYSINDEXES" 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 QUERYNO=nnn 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. -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

  26. Tables that receive data DSN_STATEMENT_RUNTIME_INFO DSN_OBJECT_RUNTIME_INFO PLAN TABLES

  27. CPU time overhead of monitoring

  28. 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

  29. DSN_STATEMENT_RUNTIME_INFO

  30. DSN_OBJECT_RUNTIME_INFO

  31. Keeping a performance database Hourly Daily Monthly

  32. “Scrubbing” the SQL text SELECT * FROM “SYSTABLES” WHERE CARDF < 0.0 AND NAME LIKE ‘DSN%’ SELECT * FROM “SYSTABLES” WHERE CARDF < . AND NAME LIKE

  33. Computing a text hash Scrubbed text + Qualifier SELECT * FROM “SYSTABLES” WHERE CARDF < . AND NAME LIKE SYSIBM 2302380

  34. 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)

  35. Spikes

  36. Trends

  37. Summary • Explain stmtcache all • Explain stmtcache stmid= • Explain plan for • Explain monitored stmts • Merge • Covariance

  38. 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)

  39. 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

More Related