Analyzing High Cost SQL Using new 9.7 Monitoring Facilities

I can identify any SQL that is causing a problem by using many different techniques. One I want to share here is the use of the MONREPORT reporting module and EXPLAIN_FROM_SECTION stored procedure. First to identify high cost SQL I run the MONREPORT.PKGCACHE reporting module and review each of the report sections for SQL of interest. See Below example:

C:\Users\Administrator>db2 connect to migamprd

Database Connection Information

Database server        = DB2/NT64 9.7.6
SQL authorization ID   = ADMINIST…
Local database alias   = MIGAMPRD
C:\Users\Administrator>db2 call monreport.pkgcache > mrgame16.txt

Cat or otherwise read the contents of mrgame16.txt and I was interested in the first Statement in

terms of most CPU used.

Result set 1

————–

TEXT

——————————————————————————–

——————————————————————————–

Monitoring report – package cache

——————————————————————————–

Database:                          MIGAMPRD

Generated:                         01/06/2015 08:42:27

— Command options —

CACHE_INTERVAL:                    All statements

SECTION_TYPE:                      Dynamic and static SQL

MEMBER:                            All

================================================================================

Part 1 – Summaries by ‘top’ metrics

Top 10 statements by TOTAL_CPU_TIME

——————————————————————————–

#   TOTAL_       STMT_TEXT

CPU_TIME

—  ———–  —————————————————————

1   9709892236   select      a.ACC_NUM as AccountNumber,      b.CASINO_ID,

Next, I went to the bottom of the report and retrieved the executable ID for this statement.

================================================================================

Part 2 – EXECUTABLE_IDs for statements in Part 1

#   EXECUTABLE_ID

—  —————————————————————————-

1   x’01000000000000001DB603000000000000000000020020150104135354228000′

2   x’0100000000000000CF1E03000000000000000000020020150101090403025000′

3   x’010000000000000024B603000000000000000000020020150104135522743001′

4   x’0100000000000000590000000000000007000000010020141024003001986004′

5   x’0100000000000000BC1E03000000000000000000020020150101090017480000′

6   x’0100000000000000001D03000000000000000000020020150101073549623000′

7   x’0100000000000000F31C03000000000000000000020020150101073511060001′

I then passed the executable ID to the EXPLAIN_FROM_SECTION strored procedure as follows:

C:\Users\Administrator>db2 call explain_from_section (x’01000000000000001DB603000000000000000000020020150104135354228000′,’M’, NULL, 0, NULL, ?, ?, ?, ?, ? )

Value of output parameters
————————–
Parameter Name  : EXPLAIN_SCHEMA
Parameter Value : ADMINISTRATOR

Parameter Name  : EXPLAIN_REQUESTER
Parameter Value : ADMINISTRATOR

Parameter Name  : EXPLAIN_TIME
Parameter Value : 2015-01-06-09.11.02.227000

Parameter Name  : SOURCE_NAME
Parameter Value : SYSSH200

Parameter Name  : SOURCE_SCHEMA
Parameter Value : NULLID

Parameter Name  : SOURCE_VERSION
Parameter Value :

Return Status = 0

To see the actual access path used at run time I then formatted the explain tables as follows:

C:\Users\Administrator>db2exfmt -d migamprd -e administrator -w 2015-01-06-09.11.02.227000 -n SYSSH200 -s NULLID -t -#0 |more
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material – Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

I’m omitting some extraneous data here…..

Connect to Database Successful.
Enter section number (0 for all, Default 0) ==>
Connecting to the Database.
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material – Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.6
SOURCE_NAME:       SYSSH200
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2015-01-06-09.11.02.227000
EXPLAIN_REQUESTER: ADMINISTRATOR

Database Context:
—————-
Parallelism:          None
CPU Speed:            3.306410e-007
Comm Speed:           0
Buffer Pool size:     5100628
Sort Heap size:       16000
Database Heap size:   25000
Lock List size:       80000
Maximum Lock List:    22
Average Applications: 1
Locks Available:      563200

Package Context:
—————
SQL Type:           Dynamic
Optimization Level: 5
Blocking:           Block All Cursors
Isolation Level:    Cursor Stability

—————- STATEMENT 1  SECTION 4 —————-
QUERYNO:       1
QUERYTAG:
Statement Type:   Select
Updatable:        No
Deletable:        No
Query Degree:     1

Original Statement:
——————
select a.ACC_NUM as AccountNumber, b.CASINO_ID,
ltrim(rtrim(upper(a.GIVEN_NAME))) as firstName,
ltrim(rtrim(upper(a.FAMILY_NAME))) as lastName,
ltrim(rtrim(upper(DECRYPT_CHAR(a.ECONTACT, :L0)))) as email,
ltrim(rtrim(upper(a.ADDRESS))) as address,
ltrim(rtrim(upper(a.CITY))) as city,
ltrim(rtrim(upper(a.STATE_PROV))) as state,
ltrim(rtrim(upper(a.COUNTRY))) as countryCode,
ltrim(rtrim(upper(a.ZIP))) as zip, a.DAY_PHONE as DayPhone, a.DOB as
birthday, s.SEGMENT_CODE as GameSegment, a.DATE_ACTIVATED as
DateActivated, b.CONVERSION_DATE as DateConverted,
case
when b.ACC_STATUS = :L1
then :L2
else :L3 end as AccountStatus, REAL_CHIPS as RealBalance, PROMO_CHIPS as
PromoBalance, a.GENDER, b.PASSWORD as password,
case
when b.ACC_STATUS = :L4
then c.REASON_ID
else null end as REASON_ID, b.REAL_PLAYER, a.UPDATE_TS as LastUpdated,
ltrim(rtrim(ip.IP)) as IP
from DB2ADMIN.CLIENT as a inner join DB2ADMIN.CLIENT_ACC as b on a.ACC_NUM =
b.ACC_NUM left outer join DB2ADMIN.CLIENT_SEGMENT as s on a.ACC_NUM =
s.ACC_NUM left outer join
(select ACC_NUM, LOCK, REASON_ID, ACTION, row_number() over(partition by
ACC_NUM
order by LOCK desc) as rankLock
from
(select ACC_NUM, ACTION, LOCK, REASON_ID
from DB2ADMIN.ACC_BLOCKING as a) as b) as c on a.ACC_NUM = c.ACC_NUM
and c.rankLock = :L5 and c.ACTION = :L6 left outer join
(select ACC_NUM, IP, row_number() over(partition by ACC_NUM
order by TIME) as rankIP
from
(select ACC_NUM, IP, TIME
from DB2ADMIN.LOGIN_TRACE
where coalesce(IP,:L7) != :L8
union
select ACC_NUM, IP, LOCK as TIME
from DB2ADMIN.PROXY_LOGIN_TRACE
where coalesce(IP,:L9) != :L10) as a) as ip on a.ACC_NUM = ip.ACC_NUM
and ip.rankIP = :L11
where a.ACC_NUM != :L12 and a.COUNTRY is not null and (
COALESCE(b.CONVERSION_DATE,:L13) >= (current timestamp) – :L14
minutes)
Optimized Statement:
——————-
SELECT Q20.ACC_NUM AS “ACCOUNTNUMBER”, Q20.CASINO_ID AS “CASINO_ID”,
LTRIM(RTRIM(UPPER(Q20.GIVEN_NAME))) AS “FIRSTNAME”,
LTRIM(RTRIM(UPPER(Q20.FAMILY_NAME))) AS “LASTNAME”,
LTRIM(RTRIM(UPPER(DECRYPT_CHAR(Q20.ECONTACT, :L0)))) AS “EMAIL”,
LTRIM(RTRIM(UPPER(Q20.ADDRESS))) AS “ADDRESS”,
LTRIM(RTRIM(UPPER(Q20.CITY))) AS “CITY”,
LTRIM(RTRIM(UPPER(Q20.STATE_PROV))) AS “STATE”,
LTRIM(RTRIM(UPPER(Q20.COUNTRY))) AS “COUNTRYCODE”,
LTRIM(RTRIM(UPPER(Q20.ZIP))) AS “ZIP”, Q20.DAY_PHONE AS “DAYPHONE”,
Q20.DOB AS “BIRTHDAY”, Q20.SEGMENT_CODE AS “GAMESEGMENT”,
Q20.DATE_ACTIVATED AS “DATEACTIVATED”, Q20.CONVERSION_DATE AS
“DATECONVERTED”,
CASE
WHEN (Q20.ACC_STATUS = :L1)
THEN :L2
ELSE :L3 END AS “ACCOUNTSTATUS”, Q20.REAL_CHIPS AS “REALBALANCE”,
Q20.PROMO_CHIPS AS “PROMOBALANCE”, Q20.GENDER AS “GENDER”,
Q20.PASSWORD AS “PASSWORD”,
CASE
WHEN (Q20.ACC_STATUS = :L4)
THEN Q20.REASON_ID
ELSE NULL END AS “REASON_ID”, Q20.REAL_PLAYER AS “REAL_PLAYER”, Q20.UPDATE_TS
AS “LASTUPDATED”, LTRIM(RTRIM(Q20.$C0)) AS “IP”
FROM
(SELECT Q19.$C1, Q10.UPDATE_TS, Q10.GENDER, Q10.DATE_ACTIVATED, Q10.DOB,
Q10.DAY_PHONE, Q10.ZIP, Q10.STATE_PROV, Q10.CITY, Q10.ADDRESS,
Q10.ECONTACT, Q10.FAMILY_NAME, Q10.GIVEN_NAME, Q10.COUNTRY,
Q10.ACC_NUM, Q10.REASON_ID, Q10.SEGMENT_CODE, Q10.REAL_PLAYER,
Q10.PASSWORD, Q10.PROMO_CHIPS, Q10.REAL_CHIPS, Q10.ACC_STATUS,
Q10.CASINO_ID, Q10.CONVERSION_DATE
FROM
(SELECT Q9.REASON_ID, Q5.UPDATE_TS, Q5.GENDER, Q5.DATE_ACTIVATED,
Q5.DOB, Q5.DAY_PHONE, Q5.ZIP, Q5.STATE_PROV, Q5.CITY,
Q5.ADDRESS, Q5.ECONTACT, Q5.FAMILY_NAME, Q5.GIVEN_NAME,
Q5.COUNTRY, Q5.ACC_NUM, Q5.SEGMENT_CODE, Q5.REAL_PLAYER,
Q5.PASSWORD, Q5.PROMO_CHIPS, Q5.REAL_CHIPS, Q5.ACC_STATUS,
Q5.CASINO_ID, Q5.CONVERSION_DATE
FROM
(SELECT Q1.SEGMENT_CODE, Q4.UPDATE_TS, Q4.GENDER, Q4.DATE_ACTIVATED,
Q4.DOB, Q4.DAY_PHONE, Q4.ZIP, Q4.STATE_PROV, Q4.CITY,
Q4.ADDRESS, Q4.ECONTACT, Q4.FAMILY_NAME, Q4.GIVEN_NAME,
Q4.COUNTRY, Q4.ACC_NUM, Q4.REAL_PLAYER, Q4.PASSWORD,
Q4.PROMO_CHIPS, Q4.REAL_CHIPS, Q4.ACC_STATUS, Q4.CASINO_ID,
Q4.CONVERSION_DATE
FROM DB2ADMIN.CLIENT_SEGMENT AS Q1 RIGHT OUTER JOIN
(SELECT Q3.REAL_PLAYER, Q3.PASSWORD, Q3.PROMO_CHIPS,
Q3.REAL_CHIPS, Q3.ACC_STATUS, Q3.CASINO_ID,
Q3.CONVERSION_DATE, Q2.UPDATE_TS, Q2.GENDER,
Q2.DATE_ACTIVATED, Q2.DOB, Q2.DAY_PHONE, Q2.ZIP,
Q2.STATE_PROV, Q2.CITY, Q2.ADDRESS, Q2.ECONTACT,
Q2.FAMILY_NAME, Q2.GIVEN_NAME, Q2.COUNTRY, Q2.ACC_NUM
FROM DB2ADMIN.CLIENT AS Q2, DB2ADMIN.CLIENT_ACC AS Q3
WHERE Q2.COUNTRY IS NOT NULL AND (Q2.ACC_NUM <> :L12) AND
(Q2.ACC_NUM = Q3.ACC_NUM) AND ($C1 <=
COALESCE(Q3.CONVERSION_DATE, $C0))) AS Q4 ON (Q1.ACC_NUM
<> :L12) AND (Q4.ACC_NUM = Q1.ACC_NUM)) AS Q5 LEFT OUTER
JOIN
(SELECT Q8.ACC_NUM, Q8.REASON_ID, Q8.ACTION, Q8.$C3
FROM
(SELECT Q7.ACC_NUM, Q7.REASON_ID, Q7.ACTION, ROW_NUMBER() OVER
(PARTITION BY Q7.ACC_NUM ORDER BY Q7.LOCK DESC)
FROM
(SELECT Q6.ACC_NUM, Q6.LOCK, Q6.REASON_ID, Q6.ACTION
FROM DB2ADMIN.ACC_BLOCKING AS Q6) AS Q7) AS Q8) AS Q9 ON
(Q9.ACTION = :L6) AND (Q9.$C3 = :L5) AND (Q5.ACC_NUM =
Q9.ACC_NUM)) AS Q10 LEFT OUTER JOIN
(SELECT Q18.$C0, Q18.$C1, Q18.$C2
FROM
(SELECT Q17.$C0, Q17.$C1, ROW_NUMBER() OVER (PARTITION BY Q17.$C0
ORDER BY Q17.$C2)
FROM
(SELECT Q16.$C0, Q16.$C1, Q16.$C2
FROM
(SELECT DISTINCT Q15.$C0, Q15.$C1, Q15.$C2
FROM
(SELECT Q11.ACC_NUM, Q11.IP, Q11.LOCK
FROM DB2ADMIN.PROXY_LOGIN_TRACE AS Q11
WHERE (COALESCE(Q11.IP, :L9) <> :L10)
UNION ALL
SELECT Q13.ACC_NUM, Q13.IP, Q13.TIME
FROM DB2ADMIN.LOGIN_TRACE AS Q13
WHERE (Q13.IP <> :L8)) AS Q15) AS Q16) AS Q17) AS Q18) AS Q19
ON (Q19.$C2 = :L11) AND (Q10.ACC_NUM = Q19.$C0)) AS Q20

Explain level:    Explain from section

Access Plan:
———–
Total Cost:             331169
Query Degree:           1

Rows
RETURN
(   1)
Cost
I/O
|
168272
>MSJOIN
(   2)
331144
NA
/————+————\
168272                       0.309234
>MSJOIN                       FILTER
(   3)                        (  14)
96128                        234955
NA                            NA
/———+———-\                  |
168272                 0.00119964         189385
TBSCAN                   FILTER           FILTER
(   4)                   (  11)           (  15)
94872.4                  1230.75          234955
NA                       NA               NA
|                        |                |
168272                    734.7        4.73462e+006
SORT                     FILTER           TBSCAN
(   5)                   (  12)           (  16)
94858.5                  1230.75          232639
NA                       NA               NA
|                        |                |
168272                    36735        4.73462e+006
>^HSJOIN                  IXSCAN           SORT
(   6)                    (  13)           (  17)
94654.9                  1205.74          231496
NA                       NA               NA
/——–+——–\               |                |
168272                612432          -1          4.73462e+006
^HSJOIN               IXSCAN    INDEX: DB2ADMIN      UNION
(   7)                (  10)   GTS1_ACC_BLOCKING     (  18)
75551.1               19003.4         Q6             217249
NA                    NA                             NA
/—–+——\              |                      /——-+——-\
504815           204144         -1                 143279           4.59135e+006
TBSCAN           TBSCAN   INDEX: DB2ADMIN          IXSCAN              TBSCAN
(   8)           (   9)       GTS9CS               (  19)              (  20)
42809.8          32655.5        Q1                 7639.33             209610
NA               NA                                NA                  NA
|                |                                 |                   |
612433           612433                              -1             4.59136e+006
TABLE: DB2ADMIN  TABLE: DB2ADMIN                   INDEX: DB2ADMIN     TABLE: DB2ADMIN
CLIENT         CLIENT_ACC                  GTS1_PROXY_LOGIN_TRACE    LOGIN_TRACE
Q2               Q3                                Q11                 Q13
As a result I have the actual runtime access plan that is used as well as the reoptimized SQL. Notice the table scan on the client table. This would be my next are to drill down into using the plan details of the db2exfmt output which I have omitted due to size and what I want to show you in a subsequent post. This method produces the actual access path used which is helpful but it does not contain the values for the host variable which is really what I want to see if actuals would affect the access path selection. In the next posting I will show how to obtain the actual data processed versus host variables. Stay tuned….