Transcript Copy PPT - Aetna - db2commerce.com

MON_GET_PKG_CACHE_STMT
Tips for Finding Problem SQL
08.01.12
http://db2commerce.com
CONFIDENTIAL; © 2012 ROSETTA. All rights reserved.
MON_GET Table Functions
Introduced in 9.7
Lightweight monitoring interface
IBM’s strategic direction for monitoring
Access data with SQL
No reset – start when database is activated
Can emulate reset with this methodology:
• http://www.ibm.com/developerworks/data/library/techarticle/dm1009db2monitoring1/index.html?ca=drs-
Consider extending the reset methodology with history tables
2
MON_GET_PKG_CACHE_STMT
Information from the package cache
Data on what is currently in the package cache
Can be exceptionally large if PCKCACHESZ is set to AUTOMATIC
Data from SNAPSHOT FOR DYNAMIC SQL and more
Includes data on static SQL
3
Using the MON_GET_PKG_CACHE_STMT
Table Function
Parameters passed to MON_GET_PKG_CACHE_STMT:
• section_type
• executable_id
• search_args
• member
Example FROM clause:
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D',
NULL, NULL, -2)) AS T
4
SQL Example #1:
WITH SUM_TAB (SUM_RR) AS (
SELECT FLOAT(SUM(ROWS_READ))
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T)
SELECT
SUBSTR(STMT_TEXT,1,20) AS STATEMENT,
ROWS_READ,
DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
ROWS_RETURNED,
CASE WHEN ROWS_RETURNED > 0
THEN DECIMAL(FLOAT(ROWS_READ)/FLOAT(ROWS_RETURNED),10,2)
ELSE -1 END AS READ_EFFICIENCY,
NUM_EXECUTIONS
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T,
SUM_TAB
ORDER BY ROWS_READ DESC
FETCH FIRST 10 ROWS ONLY WITH UR;
5
Query Output
STATEMENT
ROWS_READ
PCT_TOT_RR
ROWS_RET
READ_EFF
#_EXEC
SELECT T1.ORDIADJUST
5125312
59.75
198
25885.41
2526
SELECT T1.MARKFORDEL
515936
6.01
32246
16.00
32246
SELECT T1.CATGROUP_I
411133
4.79
411133
1.00
411133
SELECT T1.CATENTRY_I
408720
4.76
408720
1.00
104
Select srchattr.srch
360162
4.19
40125
8.97
321
SELECT T0.PX_PROMOTI
203140
2.36
0
-1.00
4239
SELECT T1.TIMECREATE
128140
1.49
44240
2.89
110600
SELECT JURST_ID FROM
119340
1.39
109
1094.86
306
SELECT stl.IDENTIFIE
110528
1.28
23896
4.62
1173
98280
1.14
98280
1.00
273
SELECT INSRULE.INSRU
6
SQL Example #2:
WITH SUM_TAB (SUM_CPU) AS (
SELECT FLOAT(SUM(TOTAL_CPU_TIME))
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T)
SELECT
SUBSTR(STMT_TEXT,1,20) AS STATEMENT,
TOTAL_CPU_TIME,
DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) AS
PCT_TOT_CPU,
DECIMAL(FLOAT(TOTAL_CPU_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS
AVG_CPU_TIME_PER_EXE,
NUM_EXECUTIONS
FROM
TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T,
SUM_TAB
ORDER BY TOTAL_CPU_TIME DESC
FETCH FIRST 10 ROWS ONLY WITH UR;
7
Query Output
STATEMENT
TOT_CPU_TIME
AVG_CPU_ NUM_EXEC
TIME_PER_EXE
64.81
442845
SELECT-T1.CATGROUP_I
28704143
PCT_TOT
_CPU
27.07
SELECT-T1.TIMECREATE
27725396
26.15
241.92
114605
SELECT-T0.PX_PROMOTI
2361848
2.22
540.09
4373
SELECT-T1.ORDIADJUST
2002939
1.88
708.50
2827
SELECT-T1.MARKFORDEL
1978817
1.86
58.46
33848
INSERT-INTO-DBAMON09
1602159
1.51
89008.83
18
SELECT-T0.PX_PROMOTI
1485336
1.40
528.96
2808
SELECT-T1.CATGROUP_I
1181837
1.11
78.99
14961
SELECT-CATFILTER.CAT
1120726
1.05
165.10
6788
SELECT-CATFILTER.CAT
995825
0.93
43.18
23060
8
Vote for Ember!
Blog: http://db2commerce.com
Twitter: @ember_crooks
LinkedIn: http://www.linkedin.com/pub/ember-crooks/8/a9b/25a/
E-mail: [email protected]
Useful links on this topic:
Emulating Monitor Reset:
http://www.ibm.com/developerworks/data/library/techarticle/dm1009db2monitoring1/index.html?ca=drsInfo Center page on MON_GET:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu
w.sql.rtn.doc/doc/c0053963.html
My developerWorks article on mining the package cache:
http://www.ibm.com/developerworks/data/library/techarticle/dm1211packagecache/index.html
9