DB2 EXPLAIN: Access Path for Query Optimization

You can use the DB2 EXPLAIN statement to determine the access paths for the SELECT parts of your statements. DB2 EXPLAIN helps you answer questions about Query Performance & Query Optimization; the answers give you the information that you need to make performance improvements. EXPLAIN indicates whether Db2 used an index to access data, whether sorts were performed, whether parallel processing was used, and so on. The information in the plan table can help you when you need to perform the following tasks:

• Determine the access path that Db2 chooses for a query.
• Design databases, indexes, and application programs.
• Determine when to rebind an application.

Three key tables for EXPLAIN are PLAN_TABLE, DSN_STATEMNT_TABLE & DSN_FUNCTION_TABLE.

Steps for DB2 EXPLAIN:

1. Before you can use EXPLAIN, you must create a plan table to hold the results of EXPLAIN. It contains information about access paths for queries that were explained or hints.You can create it by using the below SQL.

   CREATE TABLE [userid].PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;

2. Populate the plan table. You can populate the plan table by executing the SQL statement EXPLAIN. You can also populate a plan table when you bind or rebind a plan or package by specifying the option EXPLAIN(YES). EXPLAIN obtains information about the access paths for all explainable SQL statements in a package or in the DBRMs of a plan.

   EXPLAIN PLAN SET QUERYNO = 1 FOR [your sql statement here]

3. Select information from the plan table. Several processes can insert rows into the same plan table. To understand access paths, you must retrieve the rows for a particular query in an appropriate order. Execute this SQL command to view the Explain information:

   SELECT * 
    FROM PLAN_TABLE 
    WHERE QUERYNO = 1 
    ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ 
    WITH UR;

Look at these fields for important information:

1. PLANNO – Number of steps necessary to process the query indicated in QBLOCKNO. Indicates order in which the steps were executed.
2. METHOD – Indicate joins method used for the step (PLANNO).
   • 0 = First table accessed, continuation of previous table accessed, or not used
   • 1 = Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined
   • 2 = Merge scan join. The present composite table and the new tables are scanned in the order of the join columns and matching rows are joined. 3 = Sorts neededby ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate or an IN predicate. Does not access a new table.
3. ACCESTYPE – Method used to access the table.
   • DI = An intersection of multiple DOCID lists to return final DOCID list.
   • DU = Union of multiple DOCID lists to return the final DOCID list.
   • DX = An XML index scan of the index named in ACCESSNAME to return a DOCID list.
   • E = Direct row using a row change timestamp column.
   • H = Hash access. IF an overflow condition occurs, hash overflow index identified by ACCESSCREATOR and ACCESSNAME is used.
   • HN = Hash access using an IN predicate, or an IN predicate that DB2 generates. If a hash overflow condition occurs, hash overflow index identified in ACCESSCREATOR AND ACCESSNAME is used.
   • IN = Index scan when matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table
   • I = An index (identified in ACCESSCREATOR and ACCESSNAME).
   • I1 = One-fetch index scan
   • M = Multiple index scan (followed by MX, MI, MH, or MU).
   • MH = Hash overflow index named in ACCESSNAME
   • MX = Index scan on index named in ACCESSNAME. When the access method MX follows the access method DX, DI, or DU, the table is accessed by the DOCID index using the DOCID list returned by DX, DI, or DU
   • MI = Intersection of multiple indexes
   • MU = Union of multiple indexes
   • N = Index scan when the matching predicate contains the IN keyword or by an index scan when DB2 rewrites a query using the IN keyword.
   • NR = Range list access.
   • O = Work file scan, as a result of a subquery.
   • P = Dynamic pair-wise index scan
   • R = Table space scan.
   • RW = work file scan of materialized user-defined table function
   • V = Buffers for an INSERT statement within a SELECT.
   • Blank = Not applicable to the current row.
4. MATCHCOLS – Number of index key used for index scan (when ACCESTYPE is I, I1, N, NR, MX, or DX, number of index keys used in an index scan).
5. ACCESSNAME – Name of the index used for index scan (when ACCESTYPE is I, I1, H, MH, N, NR, MX, or DX, name of index).
6. INDEXONLY – If access to an index alone is enough to carry out the step, or if the data, too, must be accessed. Y = Yes; N = No.
7. PREFETCH – Indicates if data pages can be read in advance by prefetch.
   • D = optimizer expects dynamic prefetch
   • S = Pure sequential prefetch
   • L = Prefetch through a page list
   • U = List prefetch with an unsorted RID list
   • Blank = unknown at bind time or no prefetch
8. COLUMN_FN_EVAL – Indicates when aggregate functions are evaluated.
   • R = while data is being read from the table or index
   • S = While performing a sort to satisfy a GROUP BY clause
   • X = While data is read from a table or index, for aggregate functions when an OFFSET clause is specified
   • Y = While performing a sort, for aggregate functions when an OFFSET clause if specified.
   • Blank = After data retrieval after any sorts

Sample EXPLAIN Queries

Query to find Total Cost:
 
 SELECT SUBSTR(PL.COLLID,1,10) AS COLLID, 
        SUBSTR(PL.PROGNAME,1,10) AS PROGNAME, 
        DATE(PL.EXPLAIN_TIME) AS DATE, 
        TIME(PL.EXPLAIN_TIME) AS TIME, 
        COUNT(PL.QUERYNO) AS "STMT COUNT", 
        DEC(SUM(ST.TOTAL_COST),8,2) AS "TOTAL COST" 
 FROM SJD.PLAN_TABLE PL,
      SJD.DSN_STATEMNT_TABLE ST 
 WHERE PL.PROGNAME = ST.PROGNAME
   AND PL.COLLID = ST.COLLID
   AND PL.EXPLAIN_TIME = ST.EXPLAIN_TIME 
   AND PL.QUERYNO = ST.QUERYNO
 GROUP BY PL.COLLID, 
          PL.PROGNAME, 
          PL.EXPLAIN_TIME 
 ORDER BY PL.PROGNAME;
 



Result:
 ————+---------+---------+---------+---------+-------------
 COLLID PROGNAME DATE TIME       STMT COUNT TOTAL COST 
 +---------+---------+---------+---------+-----------------
 MYCOLL MYPACK   05/08/2014 11.19.38   5    10.10
 MYCOLL MYPACK   05/08/2014 17.36.17   8    19.11
 





Query to Compare Old & New Cost:

 SELECT ST1.QUERYNO AS QUERYNO, 
        COALESCE(DEC(ST1.TOTAL_COST,8,2),0) AS "OLD COST",   
        COALESCE(DEC(ST2.TOTAL_COST,8,2),0) AS "NEW COST" 
 FROM SJD.DSN_STATEMNT_TABLE ST1 FULL JOIN
      SJD.DSN_STATEMNT_TABLE ST2
   ON ST1.QUERYNO = ST2.QUERYNO
 WHERE ST1.COLLID = 'MYCOLL' 
   AND ST2.COLLID = 'MYCOLL'
   AND ST1.PROGNAME = 'MYPACK'
   AND ST2.PROGNAME = 'MYPACK'
   AND DATE(ST1.EXPLAIN_TIME) = '2014-05-08' 
   AND TIME(ST1.EXPLAIN_TIME) = '17.36.17' 
   AND DATE(ST2.EXPLAIN_TIME) = '2014-05-15' 
   AND TIME(ST2.EXPLAIN_TIME) = '13.05.14' 
   AND ST1.TOTAL_COST <> ST2.TOTAL_COST
 ORDER BY QUERYNO;
 



Result:
 ---------+---------+---------+---------+----- 
 QUERYNO OLD COST NEW COST 
 ---------+---------+---------+---------+-----
 353         .15       .11
 360        8.07     16.24
 





Query to find Total Cost and Explain Time:

 SELECT QUERYNO, 
        TOTAL_COST, 
        EXPLAIN_TIME 
 FROM SJD.DSN_STATEMNT_TABLE
 WHERE PROGNAME = 'ID14SQP' 
 AND COLLID = 'ID14SQL'
 ORDER BY TOTAL_COST DESC, 
          EXPLAIN_TIME DESC; 
 




Result:
 -----+---------+---------+---------+---------+---------+---- 
 QUERYNO TOTAL_COST               EXPLAIN_TIME 
 -----+---------+---------+---------+---------+---------+----
  88     +0.5616997729372477E+05  2014-11-02-18.03.04.993751 
 131     +0.1453335050780900E+01  2014-11-02-18.03.04.993751 
 131     +0.1453335050780900E+01  2014-10-27-16.55.51.641367
 



 
 SELECT QUERYNO,
        COALESCE(DEC(TOTAL_COST,8,0),0) AS "COST", 
        EXPLAIN_TIME
 FROM SJD.DSN_STATEMNT_TABLE 
 WHERE COLLID = ‘ID14SQL'
   AND PROGNAME = ‘ID14SQP' 
 ORDER BY EXPLAIN_TIME DESC;
 



Result:
 ---------+---------+---------+---------+---------+ 
 QUERYNO   COST          EXPLAIN_TIME 
 ---------+---------+---------+---------+---------+
 88        434758        2014-11-02-18.03.04.993751
 88         23184        2014-10-27-16.55.51.641367

Read DB2 blogs : Click Here IBM DB2 Manual :Click Here