A quick history of EXPLAIN and a new standard Db2 EXPLAIN Query to bring out the best and most underused columns.
EXPLAIN has been with us from nearly the start of Db2 (DB2 V2R1 when the b was B!) and, over the years, the number of Explain tables has mushroomed up to 20 in Db2 12. Not *all* of this data is really useful but there are bits and pieces that are well worth adding to your standard repertoire of explain queries!
| Table of contents |
PLAN_TABLE
This can be used to see in which release the PLAN_TABLE was created:
V2.1 – 25 column format
V2.2 – 28 column format
V2.3 – 30 column format
V3.1 – 34 column format
V4.1 – 43 column format
V5.1 – 46 column format
V6.1 – 49 column format
V7.1 – 51 column format
V8.1 – 58 column format
V9.1 – 59 column format
10 – 64 column format ending with MERGN
11 – 66 Column format ending with EXPANSION_REASON
12 – 67 column format ending with PER_STMT_ID
What could be interesting here?? Well what about:
PREFETCH D, S, L, U, or Blank?
- D for Dynamic
- S for pure Sequential
- L for through a page list
- U for unsorted RID list
- Blank for Unknown or no prefetch
COLUMN_FN_EVAL R, S, X, Y, or Blank?
- R for while data is read
- S for while performing a sort
- X for while data is read but using OFFSET
- Y for while performing a sort but using OFFSET
- Blank for after data retrieval and any sort
PAGE_RANGE Y or Blank.
- Y for yes the table qualifies for page range screening
so that only the needed partitions are scanned - Blank for no
PRIMARY_ACCESSTYPE D, P, S, T, Blank. Is direct row access attempted first:
- D it tries to use direct row access with a rowid column. If it cannot do this it uses the access path that is described in the ACCESSTYPE column
- P it uses a DPSI and a part-level operation to access the data
- S it uses sparse index access for a sideways table reference
- T the base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed
- Blank it does not try to use direct row access by using a rowid column or sparse index access for a work file.
DSN_DETCOST_TABLE
There are a ton of interesting columns here but most are “IBM internal only”, however, these are available for our enjoyment:
| ONECOMPROWS | The number of rows qualified after applying local predicates. |
| IMLEAF | The number of index leaf pages scanned by Data Manager. |
| IMFF | The filter factor of matching predicates only. |
| IMFFADJ | The filter factor of matching and screening |
| DMROWS | The number of data manager rows returned (after all stage 1 predicates are applied). |
| RDSROW | The number of data manager rows returned (after all stage 1 predicates are applied). |
| IXSCAN_SKIP_DUPS | Whether duplicate index key values are skipped during an index scan.
|
| IXCAN_SKIP_SCREEN | Whether key ranges that are disqualified by index screening predicates are skipped during an index scan.
|
| EARLY_OUT | Whether fetching from the table stops after the first qualified row.
|
| BLOCK_FETCH | Y or N Was block fetch used? |
DSN_FILTER_TABLE
Sometimes it is really interesting to see when the predicate is applied and whether or not it could be pushed down.
| STAGE | The stage that the predicate was evaluated. MATCHING, SCREENING, PAGERANGE, STAGE1, or STAGE2. |
| PUSHDOWN | Whether the predicate was pushed down.
|
DSN_PREDICAT_TABLE
Here lives the really good stuff that most people do not use! The Bubble Up is normally an SQL coding error and the T is “forgetfulness”…
| ADDED_PRED | If this column is non-blank it means that Db2 has rewritten the query to some extent. This is not good as it adds CPU to the process. In my opinion any “added predicate” should already be coded correctly in the SQL!
|
CLAUSE | Is this a SELECT, HAVING, ON, or WHERE clause? |
| ORIGIN | Where did it come from?
|
New Standard Explain SQL
Just adding these to your standard Explain query like this:
SET CURRENT SQLID = 'BOXWELL' ;
SELECT SUBSTR(P.PROGNAME, 1 , 8 ) AS PROGNAME
,SUBSTR(DIGITS(P.QUERYNO), 6, 5) AS LINE
,SUBSTR(DIGITS(P.QBLOCKNO), 4, 2) AS QNO
,SUBSTR(DIGITS(P.PLANNO), 4, 2) AS PNO
,SUBSTR(DIGITS(P.MIXOPSEQ), 4, 2) AS SQ
,SUBSTR(DIGITS(P.METHOD), 5, 1) AS M
,SUBSTR(P.TNAME, 1, 18) AS TABLE_NAME
,P.ACCESSTYPE AS A
,P.PRIMARY_ACCESSTYPE AS PA
,SUBSTR(DIGITS(P.MATCHCOLS), 4, 2) AS CS
,SUBSTR(P.ACCESSNAME, 1, 12) AS INDEX
,P.INDEXONLY AS IO
,SUBSTR(CASE SORTN_UNIQ WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_JOIN WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END
, 1 , 4) AS UJOG
,SUBSTR(CASE SORTC_UNIQ WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_JOIN WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END
, 1 , 4) AS UJOGC
,P.PREFETCH AS P
,P.COLUMN_FN_EVAL AS CE
,P.PAGE_RANGE AS PR
,P.QBLOCK_TYPE AS TYPE
,P.MERGE_JOIN_COLS AS MJC
,S.PROCMS AS MS
,S.PROCSU AS SU
,D.EARLY_OUT AS EO
,D.BLOCK_FETCH AS BF
,F.ORDERNO AS ON
,F.PREDNO AS PN
,F.STAGE AS STAGE
,F.PUSHDOWN AS PD
,R.TYPE AS TYPE
,R.ADDED_PRED AS AP
,R.CLAUSE AS CLAUSE
,R.ORIGIN AS OR
,R.REDUNDANT_PRED AS RP
,R.TEXT AS TRANSFORMED_PREDICATE
FROM PLAN_TABLE P
FULL OUTER JOIN
DSN_STATEMNT_TABLE S
ON P.QUERYNO = S.QUERYNO
AND P.APPLNAME = S.APPLNAME
AND P.PROGNAME = S.PROGNAME
AND P.COLLID = S.COLLID
AND P.GROUP_MEMBER = S.GROUP_MEMBER
AND P.SECTNOI = S.SECTNOI
AND P.VERSION = S.VERSION
AND P.EXPLAIN_TIME = S.EXPLAIN_TIME
FULL OUTER JOIN
DSN_DETCOST_TABLE D
ON P.QUERYNO = D.QUERYNO
AND P.APPLNAME = D.APPLNAME
AND P.PROGNAME = D.PROGNAME
AND P.COLLID = D.COLLID
AND P.GROUP_MEMBER = D.GROUP_MEMBER
AND P.SECTNOI = D.SECTNOI
AND P.VERSION = D.VERSION
AND P.EXPLAIN_TIME = D.EXPLAIN_TIME
AND P.QBLOCKNO = D.QBLOCKNO
AND P.PLANNO = D.PLANNO
FULL OUTER JOIN
DSN_FILTER_TABLE F
ON P.QUERYNO = F.QUERYNO
AND P.APPLNAME = F.APPLNAME
AND P.PROGNAME = F.PROGNAME
AND P.COLLID = F.COLLID
AND P.GROUP_MEMBER = F.GROUP_MEMBER
AND P.SECTNOI = F.SECTNOI
AND P.VERSION = F.VERSION
AND P.EXPLAIN_TIME = F.EXPLAIN_TIME
AND P.QBLOCKNO = F.QBLOCKNO
AND P.PLANNO = F.PLANNO
FULL OUTER JOIN
DSN_PREDICAT_TABLE R
ON F.QUERYNO = R.QUERYNO
AND F.APPLNAME = R.APPLNAME
AND F.PROGNAME = R.PROGNAME
AND F.COLLID = R.COLLID
AND F.GROUP_MEMBER = R.GROUP_MEMBER
AND F.SECTNOI = R.SECTNOI
AND F.VERSION = R.VERSION
AND F.EXPLAIN_TIME = R.EXPLAIN_TIME
AND F.QBLOCKNO = R.QBLOCKNO
AND F.PREDNO = R.PREDNO
WHERE 1 = 1
AND P.QUERYNO IN (1 , 2 )
ORDER BY 1 , 2 , 3 , 4 , 5 , 24 , 25
;
Here I limit it to just the QUERYNO 1 and 2 as these were the numbers used for the EXPLAIN command:
EXPLAIN ALL SET QUERYNO = 1 FOR
SELECT INSTANCE, CLONE
FROM SYSIBM.SYSTABLESPACE A
WHERE ( SELECT B.TSNAME
FROM SYSIBM.SYSTABLES B
,SYSIBM.SYSINDEXES C
WHERE C.CREATOR = ?
AND C.NAME = ?
AND C.TBCREATOR = B.CREATOR
AND C.TBNAME = B.NAME ) = A.NAME
AND A.DBNAME = ?
;
EXPLAIN ALL SET QUERYNO = 2 FOR
SELECT A.INSTANCE, A.CLONE
FROM SYSIBM.SYSTABLESPACE A
,SYSIBM.SYSTABLES B
,SYSIBM.SYSINDEXES C
WHERE C.CREATOR = ?
AND C.NAME = ?
AND C.TBCREATOR = B.CREATOR
AND C.TBNAME = B.NAME
AND A.DBNAME = ?
AND A.NAME = B.TSNAME
AND A.DBNAME = B.DBNAME
WITH UR
;
--------+--------+------+--------+---------+---------+--------+------+---
PROGNAME LINE QNO PNO SQ M TABLE_NAME A PA CS INDEX IO UJOG UJOGC
--------+--------+------+--------+---------+---------+--------+------+---
DSNESM68 00001 01 01 00 0 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00001 01 01 00 0 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00001 02 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00001 02 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00001 02 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00001 02 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00002 01 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 03 00 1 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00002 01 03 00 1 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNE610I NUMBER OF ROWS DISPLAYED IS 13
--+---------+---------+---------+---------+---------+---------+--------
P CE PR TYPE MJC MS SU EO BF ON
--+---------+---------+--------+---------+---------+---------+---------
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
NCOSUB ------ 1 2 N N 1
NCOSUB ------ 1 2 N N 2
NCOSUB ------ 1 2 N N 1
NCOSUB ------ 1 2 N N 2
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
SELECT ------ 1 2 N N 3
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
---------+---------+---------+---------+---------+---------+---------+-----
PN STAGE PD TYPE AP CLAUSE OR RP TRANSFORMED_PREDICATE
---------+---------+---------+---------+---------+---------+---------+-----
3 MATCHING EQUAL WHERE U N "A"."DBNAME"=(EXPR)
2 MATCHING EQUAL WHERE U N "A"."NAME"=(SELECT "B"."TSNAME"
5 MATCHING EQUAL WHERE U N "C"."CREATOR"=(EXPR)
6 MATCHING EQUAL WHERE U N "C"."NAME"=(EXPR)
7 MATCHING EQUAL WHERE U N "C"."TBCREATOR"="B"."CREATOR"
8 MATCHING EQUAL WHERE U N "C"."TBNAME"="B"."NAME"
2 MATCHING EQUAL WHERE U N "C"."CREATOR"=(EXPR)
3 MATCH EQUAL WHERE U N "C"."NAME"=(EXPR)
4 MATCH EQUAL WHERE U N "C"."TBCREATOR"="B"."CREATOR"
5 MATCHING EQUAL WHERE U N "C"."TBNAME"="B"."NAME"
9 STAGE1 EQUAL T WHERE N "B"."DBNAME"=(EXPR)
6 MATCHING EQUAL WHERE U N "A"."DBNAME"=(EXPR)
7 MATCHING EQUAL WHERE U N "A"."NAME"="B"."TSNAME"
It shows you a lot more data than the normal bare bones Explain PLAN_TABLE data especially here the TYPE, STAGE and ADDED_PREDICATE (AP Column).
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect