2019-03 EXPLAIN data review

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
  1. PLAN_TABLE
  2. DSN_DETCOST_TABLE
  3. DSN_FILTER_TABLE
  4. DSN_PREDICAT_TABLE
  5. New Standard Explain SQL

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:

ONECOMPROWSThe number of rows qualified after applying
local predicates.
IMLEAFThe 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).
RDSROWThe number of data manager rows returned
(after all stage 1 predicates are applied).
IXSCAN_SKIP_DUPSWhether duplicate index key values
are skipped during an index scan.
  • Y Duplicate key values are skipped
  • N Duplicate key values are not skipped
IXCAN_SKIP_SCREENWhether key ranges that are disqualified
by index screening predicates are
skipped during an index scan.
  • Y Disqualified key ranges are skipped
  • N Key ranges are not skipped
EARLY_OUTWhether fetching from the table stops
after the first qualified row.
  • Y Internal fetching stops after the first
    qualified row
  • N Internal fetching continues after the first
    qualified row
BLOCK_FETCH 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.

STAGEThe stage that the predicate was evaluated.
MATCHING, SCREENING, PAGERANGE,
STAGE1, or STAGE2.
PUSHDOWNWhether the predicate was pushed down.
  • I for the Index Manager evaluates it
  • D for the Data Manager evaluates it
  • Blank means no push down was used

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_PREDIf 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!
  • Blank Db2 did not add the predicate
  • B for bubble up
  • C for correlation
  • J for join
  • K for LIKE for expression-based index
  • L for localization
  • P for push down
  • R for page range
  • S for simplification
  • T for transitive closure

CLAUSE

Is this a SELECT, HAVING, ON, or WHERE clause?
ORIGIN  Where did it come from?
  • C for a column mask
  • R for a Row Permission
  • U specified by User
  • Blank generated by Db2

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