You can use the explain facility to examine
the access plan for a specific statement
as it will actually run (or
as it was run) by generating the access plan from the section for
the statement itself. By contrast, using the EXPLAIN statement creates
the access plan by recompiling the statement. The resulting access
plans from each of these two methods of creating access plans can
be different. For example, if the statement in a section was compiled,
say, 2 hours ago, then the access plan it uses might be different
than the one produced by running the EXPLAIN statement against the
statement.
If you have activity event monitor information available,
you can generate the access plan for the section after it has run
using the EXPLAIN_FROM_ACTIVITY procedure. (If section actuals are
being collected, you can also view this information along with the
estimates generated by the explain facility in the access plan. See Capturing and accessing section actuals for
more information.)
If there is no activity event monitor
information available for the statement, you can use the EXPLAIN_FROM_SECTION
procedure to generate the access plan for the statement as it will
run based on the section stored in the package cache. This topic shows
how to use EXPLAIN_FROM_SECTION to view access plan information for
a statement based on section information in the package cache.
Before you begin
This task assumes that you have already created the explain
tables required by the explain facility.
About this task
In this topic, assume that you want to use the explain facility
to examine the most CPU-intensive statement in the package cache.
Procedure
The first part of this procedure shows how to identify
the most CPU-intensive statement. Then, it shows how to use the EXPLAIN_FROM_SECTION
procedure to view the access plan information for that statement as
it will actually run.
- Identify the statement that using the most processor time:
SELECT SECTION_TYPE,
CASE
WHEN SUM(NUM_COORD_EXEC_WITH_METRICS) > 0 THEN
SUM(TOTAL_CPU_TIME)/SUM(NUM_COORD_EXEC_WITH_METRICS)
ELSE
0
END as AVG_CPU_TIME,
EXECUTABLE_ID,
VARCHAR(STMT_TEXT, 200) AS TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
WHERE T.NUM_EXEC_WITH_METRICS <> 0 AND STMT_TYPE_ID LIKE 'DML%'
GROUP BY SECTION_TYPE, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 200)
ORDER BY AVG_CPU_TIME DESC
The preceding SQL is written to avoid division by 0 when
calculating the average processor time across members. It also examines
DML statements only, since the explain facility does not operate on
DDL statements. The results of this query are as
follows:
SECTION_TYPE AVG_CPU_TIME EXECUTABLE_ID TEXT
------------ -------------------- ------------------------------------------------------------------- --------------------------------------------------
D 250000 x'01000000000000005F0000000000000000000000020020101108135629359000' select cust_last_name, cust_cc_number, cust_intere
SQL0445W Value "SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2TableMainte"
has been truncated. SQLSTATE=01004
D 15625 x'01000000000000001B0000000000000000000000020020101108135017625000' UPDATE SYSTOOLS.HMON_ATM_INFO SET STATS_LOCK = 'N'
D 15625 x'0100000000000000200000000000000000000000020020101108135018296001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET ATM.STATS
D 15625 x'0100000000000000210000000000000000000000020020101108135018312001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET STATS_FLA
D 7812 x'0100000000000000150000000000000000000000020020101108135016984000' SELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHER
D 0 x'0100000000000000160000000000000000000000020020101108135017156000' SELECT TRIGNAME FROM SYSCAT.TRIGGERS WHERE TABNAM
D 0 x'0100000000000000190000000000000000000000020020101108135017484000' SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME='
D 0 x'01000000000000001A0000000000000000000000020020101108135017500000' SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME='
D 0 x'01000000000000001C0000000000000000000000020020101108135017750002' SELECT CREATOR, NAME, CTIME FROM SYSIBM.SYSTABLES
D 0 x'01000000000000001D0000000000000000000000020020101108135017828001' SELECT CREATE_TIME FROM SYSTOOLS.HMON_ATM_INFO WHE
D 0 x'01000000000000001E0000000000000000000000020020101108135018000001' DELETE FROM SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NO
D 0 x'01000000000000001F0000000000000000000000020020101108135018093000' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2T
D 0 x'0100000000000000220000000000000000000000020020101108135018328001' SELECT IBM.TID, IBM.FID FROM SYSIBM.SYSTABLES AS I
D 0 x'0100000000000000230000000000000000000000020020101108135018343001' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2C
14 record(s) selected with 1 warning messages printed.
- Based on the output of the preceding query, use the EXPLAIN_FROM_SECTION
procedure to generate explain information from the section for the
most CPU-intensive statement:
CALL EXPLAIN_FROM_SECTION (x'01000000000000005F0000000000000000000000020020101108135629359000' ,'M', NULL, 0, NULL, ?, ?, ?, ?, ? )
The output of the EXPLAIN_FROM_SECTION
procedure is as follows:
Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : DB2DOCS
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : DB2DOCS
Parameter Name : EXPLAIN_TIME
Parameter Value : 2010-11-08-13.57.52.984001
Parameter Name : SOURCE_NAME
Parameter Value : SQLC2H21
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
- You can now examine the explain information, either by
examining the explain tables using SQL, or using the db2exfmt command
to format the information for easier reading. For example,
running db2exfmt -d gsdb -e db2docs -w 2010-11-08-13.57.52.984001
-n SQLC2H21 -s NULLID -t -#0 against the explain information
collected from the previous step generates the following output:
Connecting to the Database.
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.2
SOURCE_NAME: SQLC2H21
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2010-11-08-13.57.52.984001
EXPLAIN_REQUESTER: DB2DOCS
Database Context:
----------------
Parallelism: None
CPU Speed: 8.029852e-007
Comm Speed: 100
Buffer Pool size: 21418
Sort Heap size: 6590
Database Heap size: 1196
Lock List size: 21386
Maximum Lock List: 97
Average Applications: 1
Locks Available: 663821
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 0
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select cust_last_name, cust_cc_number, cust_interest_code
from gosalesct.cust_crdt_card C, gosalesct.cust_customer D,
gosalesct.cust_interest E
where C.cust_code=d.cust_code AND c.cust_code=e.cust_code
group by d.cust_last_name, c.cust_cc_number, e.cust_interest_code
order by d.cust_last_name ASC, c.cust_cc_number DESC, e.cust_interest_code
ASC
Optimized Statement:
-------------------
SELECT Q5.CUST_LAST_NAME AS "CUST_LAST_NAME", Q5.CUST_CC_NUMBER AS
"CUST_CC_NUMBER", Q5.CUST_INTEREST_CODE AS "CUST_INTEREST_CODE"
FROM
(SELECT Q4.CUST_LAST_NAME, Q4.CUST_CC_NUMBER, Q4.CUST_INTEREST_CODE
FROM
(SELECT Q2.CUST_LAST_NAME, Q3.CUST_CC_NUMBER, Q1.CUST_INTEREST_CODE
FROM GOSALESCT.CUST_INTEREST AS Q1, GOSALESCT.CUST_CUSTOMER AS Q2,
GOSALESCT.CUST_CRDT_CARD AS Q3
WHERE (Q3.CUST_CODE = Q1.CUST_CODE) AND (Q1.CUST_CODE = Q2.CUST_CODE))
AS Q4
GROUP BY Q4.CUST_INTEREST_CODE, Q4.CUST_CC_NUMBER, Q4.CUST_LAST_NAME) AS
Q5
ORDER BY Q5.CUST_LAST_NAME, Q5.CUST_CC_NUMBER DESC, Q5.CUST_INTEREST_CODE
Explain level: Explain from section
Access Plan:
-----------
Total Cost: 1255.29
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
31255
GRPBY
( 2)
1255.29
NA
|
31255
TBSCAN
( 3)
1249.02
NA
|
31255
SORT
( 4)
1242.74
NA
|
31255
^HSJOIN
( 5)
1134.96
NA
/---------+---------\
31255 31255
HSJOIN TBSCAN
( 6) ( 9)
406.871 716.136
NA NA
/------+-------\ |
31255 31255 31255
TBSCAN IXSCAN TABLE: GOSALESCT
( 7) ( 8) CUST_CUSTOMER
235.505 159.488 Q2
NA NA
| |
31255 -1
TABLE: GOSALESCT INDEX: SYSIBM
CUST_CRDT_CARD SQL101108113609000
Q3 Q1
⋮
Objects Used in Access Plan:
---------------------------
Schema: SYSIBM
Name: SQL101108113609000
Type: Index
Last statistics update: 2010-11-08-13.29.58.531000
Number of rows: -1
Number of buffer pool pages: -1
Distinct row values: Yes
Tablespace name: GOSALES_TS
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: 32
Container extent page count: 32
Index clustering statistic: 1.000000
Index leaf pages: 37
Index tree levels: 2
Index full key cardinality: 31255
Base Table Schema: GOSALESCT
Base Table Name: CUST_INTEREST
Columns in index:
CUST_CODE(A)
CUST_INTEREST_CODE(A)
Schema: GOSALESCT
Name: CUST_CRDT_CARD
Type: Table
Last statistics update: 2010-11-08-11.59.58.531000
Number of rows: 31255
Number of buffer pool pages: 192
Distinct row values: No
Tablespace name: GOSALES_TS
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Schema: GOSALESCT
Name: CUST_CUSTOMER
Type: Table
Last statistics update: 2010-11-08-11.59.59.437000
Number of rows: 31255
Number of buffer pool pages: 672
Distinct row values: No
Tablespace name: GOSALES_TS
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Base Table For Index Not Already Shown:
---------------------------------------
Schema: GOSALESCT
Name: CUST_INTEREST
Time of creation: 2010-11-08-11.30.28.203002
Last statistics update: 2010-11-08-13.29.58.531000
Number of rows: 31255
Number of pages: 128
Number of pages with rows: 124
Table overflow record count: 0
Indexspace name: GOSALES_TS
Tablespace name: GOSALES_TS
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: GOSALES_TS
(The preceding output has had several lines removed for
presentation purposes.)
What to do next
Analyze the explain output to see where there are opportunities
to tune the query.
DB2 Version 10.1 for Linux, UNIX, and Windows