Access Paths and the PLAN_TABLE

Access Paths and the PLAN_TABLE

EXPLAIN populates the PLAN_TABLE with access path information. You can use the DDL in Listing 23.1 to create a PLAN_TABLE.

NOTE

The PLAN_TABLE will be created in the default database ( DSNDB04 ) and STOGROUP ( SYSDEFLT ) in a DB2-generated table space, unless a database and a table space are created for the PLAN_TABLE and they are referenced in the IN clause of the CREATE TABLE statement. Avoid using the default database and table space.

DB2 supports many different formats for the PLAN_TABLE . The other formats exist to provide support for PLAN_TABLE s built under older versions of DB2 that did not support all the current columns. In general, though, you should use all of the columns available for the version of DB2 that you are using in order to obtain as much information from EXPLAIN as possible.

If a PLAN_TABLE already exists, you can use the LIKE clause of CREATE TABLE to create PLAN_TABLE s for individual users based on a master PLAN_TABLE . Having a PLAN_TABLE for the following users is a good idea:

• Every DB2 application programmer. This way, they can analyze and evaluate the access paths chosen for the SQL embedded in their application programs.

• Every individual owner of every production DB2 plan. This way, an EXPLAIN can be run on production DB2 packages and plans.

• Every DBA and system programmer. This way, they can analyze access paths for ad hoc and dynamic SQL statements.

Changes for DB2 Versions 7 and 8

The PARENT_QBLOCK and TABLE_TYPE columns were added to the PLAN_TABLE as of DB2 Version 7.

Listing 23.1. DDL to Create the PLAN_TABLE

 CREATE TABLE  userid  .PLAN_TABLE (  QUERYNO              INTEGER        NOT NULL,  QBLOCKNO             SMALLINT       NOT NULL,  APPLNAME             CHAR(8)        NOT NULL,  PROGNAME             VARCHAR(128)   NOT NULL,  PLANNO               SMALLINT       NOT NULL,  METHOD               SMALLINT       NOT NULL,  CREATOR              VARCHAR(128)   NOT NULL,  TNAME                VARCHAR(128)   NOT NULL,  TABNO                SMALLINT       NOT NULL,  ACCESSTYPE           CHAR(2)        NOT NULL,  MATCHCOLS            SMALLINT       NOT NULL,  ACCESSCREATOR        VARCHAR(128)   NOT NULL,  ACCESSNAME           VARCHAR(128)   NOT NULL,  INDEXONLY            CHAR(1)        NOT NULL,  SORTN_UNIQ           CHAR(1)        NOT NULL,  SORTN_JOIN           CHAR(1)        NOT NULL,  SORTN_ORDERBY        CHAR(1)        NOT NULL,  SORTN_GROUPBY        CHAR(1)        NOT NULL,  SORTC_UNIQ           CHAR(1)        NOT NULL,  SORTC_JOIN           CHAR(1)        NOT NULL,  SORTC_ORDERBY        CHAR(1)        NOT NULL,  SORTC_GROUPBY        CHAR(1)        NOT NULL,  TSLOCKMODE           CHAR(3)        NOT NULL,  TIMESTAMP            CHAR(16)       NOT NULL,  REMARKS              VARCHAR(762)   NOT NULL,  PREFETCH             CHAR(1)        NOT NULL WITH DEFAULT,  COLUMN_FN_EVAL       CHAR(1)        NOT NULL WITH DEFAULT,  MIXOPSEQ             SMALLINT       NOT NULL WITH DEFAULT,  VERSION              VARCHAR(64)    NOT NULL WITH DEFAULT,  COLLID               VARCHAR(128)   NOT NULL WITH DEFAULT,  ACCESS_DEGREE        SMALLINT,  ACCESS_PGROUP_ID     SMALLINT,  JOIN_DEGREE          SMALLINT,  JOIN_PGROUP_ID       SMALLINT,  SORTC_PGROUP_ID      SMALLINT,  SORTN_PGROUP_ID      SMALLINT,  PARALLELISM_MODE     CHAR(1),  MERGE_JOIN_COLS      SMALLINT,  CORRELATION_NAME     VARCHAR(128),  PAGE_RANGE           CHAR(1)        NOT NULL WITH DEFAULT,  JOIN_TYPE            CHAR(1)        NOT NULL WITH DEFAULT,  GROUP_MEMBER         CHAR(8)        NOT NULL WITH DEFAULT,  IBM_SERVICE_DATA     VARCHAR(254)   NOT NULL WITH DEFAULT,  WHEN_OPTIMIZE        CHAR(1)        NOT NULL WITH DEFAULT,  QBLOCK_TYPE          CHAR(6)        NOT NULL WITH DEFAULT,  BIND_TIME            TIMESTAMP      NOT NULL WITH DEFAULT,  OPTHINT              VARCHAR(128)   NOT NULL WITH DEFAULT,  HINT_USED            VARCHAR(128)   NOT NULL WITH DEFAULT,  PRIMARY_ACCESSTYPE   CHAR(1)        NOT NULL WITH DEFAULT,  PARENT_QBLOCK        SMALLINT       NOT NULL WITH DEFAULT,  TABLE_TYPE           CHAR(1),  TABLE_ENCODE         CHAR(1)        NOT NULL WITH DEFAULT,  TABLE_SCCSID         SMALLINT       NOT NULL WITH DEFAULT,  TABLE_MCCSID         SMALLINT       NOT NULL WITH DEFAULT,  TABLE_DCCSID         SMALLINT       NOT NULL WITH DEFAULT,  ROUTINE_ID           INTEGER        NOT NULL WITH DEFAULT ) IN  database.tablespace  ; 

The TABLE_ENCODE , TABLE_SCCSID , TABLE_MCCSID , TABLE_DCCSID , and ROUTINE_ID columns were added to the PLAN_TABLE as of DB2 Version 8. Also, many columns were extended to support long names , as follows :

• PROGNAME was modified from CHAR(8) to VARCHAR(128) .

• CREATOR was modified from CHAR(8) to VARCHAR(128) .

• TNAME was modified from CHAR(18) to VARCHAR(128) .

• ACCESSCREATOR was modified from CHAR(8) to VARCHAR(128) .

• ACCESSNAME was modified from CHAR(18) to VARCHAR(128).

• COLLID was modified from CHAR(18) to VARCHAR(128) .

• CORRELATION_NAME was modified from CHAR(18) to VARCHAR(128) .

• OPTHINT was modified from CHAR(8) to VARCHAR(128) .

• HINT_USED was modified from CHAR(8) to VARCHAR(128) .

The final V8 modification is to the REMARKS column, which was extended from VARCHAR(254) to VARCHAR(762) .

Querying the PLAN_TABLE

After you issue the EXPLAIN command on your SQL statements, the next logical step is to inspect the results. Because EXPLAIN places the access path information in a DB2 table, you can use an SQL query to retrieve this information, as follows:

 SELECT   QUERYNO, QBLOCKNO, QBLOCK_TYPE, APPLNAME, PROGNAME, PLANNO,          METHOD, CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE, MATCHCOLS,          ACCESSNAME, INDEXONLY, SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN,          SORTN_ORDERBY, SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ,          SORTC_JOIN, SORTC_ORDERBY, SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP,          PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, COLLID, VERSION, \          ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE, JOIN_PGROUP_ID,          PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,          PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, BIND_TIME, HINT_USED,          PRIMARY_ACCESSTYPE, PARENT_QBLOCK, TABLE_TYPE, TABLE_ENCODE,          TABLE_SCCSID, TABLE_MCCSID, TABLE_DCCSID, ROUTINE_ID FROM  ownerid  .PLAN_TABLE ORDER BY APPLNAME, COLLID, VERSION, PROGNAME, TIMESTAMP DESC,          QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ; 

A common method of retrieving access path data from the PLAN_TABLE is to use QMF or a GUI-based query tool to format the results of a simple SELECT statement. This way, you can organize and display the results of the query in a consistent and manageable fashion.

It is crucial that the TIMESTAMP column be in descending order. Because EXPLAIN s are executed as a result of the BIND command, access path data is added to the PLAN_TABLE with a different timestamp. The old data is not purged from the PLAN_TABLE each time an EXPLAIN is performed. If you specify the descending sort option on the TIMESTAMP column, you can ensure that the EXPLAIN data in the report is sorted in order from the most recent to the oldest access path for each SQL statement in the PLAN_TABLE . Sorting this way is important if the PLAN_TABLE s you are working with are not purged.

If you want to retrieve information placed in the PLAN_TABLE for a single SQL statement, you can issue the following query:

 SELECT   QUERYNO, QBLOCKNO, QBLOCK_TYPE, PLANNO, METHOD, TNAME,          ACCESSTYPE, JOIN_TYPE, MATCHCOLS, ACCESSNAME, INDEXONLY,          SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,          SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN,          SORTC_ORDERBY, SORTC_GROUPBY, TSLOCKMODE, PREFETCH,          COLUMN_FN_EVAL, MIXOPSEQ, ACCESS_DEGREE, ACCESS_PGROUP_ID,          JOIN_DEGREE, JOIN_PGROUP_ID, PARALLELISM_MODE,          MERGE_JOIN_COLS, CORRELATION_NAME, PAGE_RANGE, GROUP_MEMBER,          WHEN_OPTIMIZE, BIND_TIME, HINT_USED, PRIMARY_ACCESSTYPE,          PARENT_QBLOCK, TABLE_TYPE, TABLE_ENCODE, TABLE_SCCSID,          TABLE_MCCSID, TABLE_DCCSID, ROUTINE_ID FROM  ownerid  .PLAN_TABLE ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ; 

The preceding eliminates from the query the package and plan information, as well as the name of the table creator. Throughout the remainder of this chapter, I present PLAN_TABLE information for several types of SQL statements. Variants of this query are used to show the PLAN_TABLE data for each EXPLAIN statement.

The PLAN_TABLE Columns

Now that you have some basic PLAN_TABLE queries to assist you with DB2 performance monitoring, you can begin to EXPLAIN your application's SQL statements and analyze their access paths. But remember, because the access path information in the PLAN_TABLE is encoded, you must have a type of decoder to understand this information. This information is provided in Table 25.1. A description of every column of the PLAN_TABLE is provided.

Table 25.1. PLAN_TABLE Columns

Column	Description
QUERYNO	Indicates an integer value assigned by the user issuing the EXPLAIN , or by DB2. Enables the user to differentiate between EXPLAIN statements.
QBLOCKNO	Indicates an integer value enabling the identification of blocks within a query (for example, subselects or SELECT s in a union).
APPLNAME	Contains the plan name for rows inserted as a result of running BIND PLAN specifying EXPLAIN(YES) . Contains the package name for rows inserted as a result of running BIND PACKAGE with EXPLAIN(YES) . Otherwise, contains blanks for rows inserted as a result of dynamic EXPLAIN statements.
PROGNAME	Contains the name of the program in which the SQL statement is embedded. If a dynamic EXPLAIN is issued from QMF, this column contains DSQIESQL .
PLANNO	Contains an integer value indicating the step of the plan in which QBLOCKNO is processed (that is, the order in which plan steps are undertaken).
METHOD	Contains an integer value identifying the access method used for the given step:
		First table accessed (can also indicate an outer table or a continuation of the previous table accessed)
	1	Nested loop join
	2	Merge scan join
	3	Independent sort; Sort happens as a result of ORDER BY , GROUP BY , SELECT DISTINCT , a quantified predicate, or an IN predicate (the step does not access a new table)
	4	Hybrid join
CREATOR	Indicates the creator of the table identified by TNAME or is blank when METHOD equals 3 .
TNAME	Indicates the name of the table, MQT, temporary table (created or declared), materialized view, or materialized table expression being accessed; or blank when METHOD equals 3 .
TABNO	IBM use only.
ACCESSTYPE	Indicates the method of accessing the table:
	I	Indexed access
	I1	One-fetch index scan
	R	Table space scan
	RW	Work file scan
	T	Sparse index access (star join)
	V	Buffers for an INSERT within a SELECT
	N	Index access with an IN predicate
	D	Direct row access (by a ROWID column)
	M	Multiple index scan
	MX	Specification of the index name for multiple index access
	MI	Multiple index access by RID intersection
	MU	Multiple index access by RID union
	blank	Not applicable to current row
MATCHCOLS	Contains an integer value with the number of index columns used in an index scan when ACCESSTYPE is I , I1 , N , or MX . Otherwise, contains .
ACCESSCREATOR	Indicates the creator of the index when ACCESSTYPE is I , I1 , N , or MX . Otherwise, it is blank.
ACCESSNAME	Indicates the name of the index used when ACCESSTYPE is I , I1 , N , or MX . Otherwise, it is blank.
INDEXONLY	A value of Y indicates that access to the index is sufficient to satisfy the query. N indicates that access to the table space is also required.
SORTN_UNIQ	A value of Y indicates that a sort must be performed on the new table to remove duplicates.
SORTN_JOIN	A value of Y indicates that a sort must be performed on the new table to accomplish a merge scan join. Or a sort is performed on the RID list and intermediate table of a hybrid join.
SORTN_ORDERBY	A value of Y indicates that a sort must be performed on the new table to order rows.
SORTN_GROUPBY	A value of Y indicates that a sort must be performed on the new table to group rows.
SORTC_UNIQ	A value of Y indicates that a sort must be performed on the composite table to remove duplicates.
SORTC_JOIN	A value of Y indicates that a sort must be performed on the composite table to accomplish a join (any type).
SORTC_ORDERBY	A value of Y indicates that a sort must be performed on the composite table to order rows.
SORTC_GROUP	A value of Y indicates that a sort must be performed on the composite table to group rows.
TSLOCKMODE	Contains the lock level applied to the new table, its table space, or partitions. If the isolation level can be determined at BIND time, the values can be as follow:
	IS	Intent share lock
	IX	Intent exclusive lock
	S	Share lock
	U	Update lock
	X	Exclusive lock
	SIX	Share with intent exclusive lock
	N	No lock ( UR isolation level)
	If the isolation level cannot be determined at BIND time, the lock mode values can be as follow:
	NS	For UR , no lock; for CS , RS , or RR , an S -lock.
	NIS	For UR , no lock; for CS , RS , or RR , an IS -lock.
	NSS	For UR , no lock; for CS or RS , an IS -lock; for RR, an S -lock.
	SS	For UR , CS , or RS , an IS -lock; for RR , an S -lock.
TIMESTAMP	Indicates the date and time the EXPLAIN for this row was issued. This internal representation of a date and time is not in DB2 timestamp format.
REMARKS	Contains a 254-byte character string for commenting EXPLAIN results.
PREFETCH	Contains an indicator of which type of prefetch will be used:
	S	Sequential prefetch can be used.
	L	List prefetch can be used.
	D	Dynamic prefetch expected.
	blank	Prefetch is not used, or prefetch use is unknown.
COLUMN_FN_EVAL	Indicates when the column function is evaluated:
	R	Data retrieval time
	S	Sort time
	blank	Unknown (runtime division)
MIXOPSEQ	Contains a small integer value indicating the sequence of the multiple index operation.
VERSION	Contains the version identifier for the package.
COLLID	Contains the collection ID for the package.
ACCESS_DEGREE	Indicates the number of parallel tasks utilized by the query. For statements containing host variables , this column is set to . (Although this column is set at bind time, it can be re-determined at execution time.)
ACCESS_PGROUP_ID	Contains a sequential number identifying the parallel group accessing the new table. (Although this column is set at bind time, it can be re-determined at execution time.)
JOIN_DEGREE	Indicates the number of parallel tasks used in joining the composite table with the new table. For statements containing host variables, this column is set to . (Although this column is set at bind time, it can be re-determined at execution time.)
JOIN_PGROUP_ID	A sequential number identifying the parallel group joining the composite table to the new table. (Although this column is set at bind time, it can be re-determined at execution time.)
SORTC_PGROUP_ID	Contains the parallel group identifier for the parallel sort of the composite table.
SORTN_PGROUP_ID	Contains the parallel group identifier for the parallel sort of the new table.
PARALLELISM_MODE	Indicates the type of parallelism used at bind time:
	I	Query I/O parallelism
	C	Query CPU parallelism
	X	Query sysplex parallelism
	null	No parallelism, or mode will be determined at runtime
MERGE_JOIN_COLS	Indicates the number of columns joined during a merge scan join ( METHOD = 2 ).
CORRELATION_NAME	Indicates the correlation name for the table or view specified in the statement. Blank if no correlation name. A correlation name is an alternate name for a table, view, or inline view. It can be specified in the FROM clause of a query and in the first clause of an UPDATE or DELETE statement. For example, D is the correlation name in the following clause:
	FROM DSN8810.DEPT D
PAGE_RANGE	Indicates whether the table qualifies for page range table space scans in which only a subset of the available partitions are scanned:
	Y	Yes
	blank	No
JOIN_TYPE	Indicates the type of join being implemented:
	F	Full outer join
	L	Left outer join (or a converted right outer join)
	S	Star join
	blank	Inner join (or no join)
GROUP_MEMBER	Indicates the member name of the DB2 that executed EXPLAIN . The column is blank if the DB2 subsystem was not in a data sharing environment when EXPLAIN was executed.
IBM_SERVICE_DATA	IBM use only.
WHEN_OPTIMIZE	Specifies when the access path was determined:
	Blank	At BIND time
	B	At BIND time, but will be reoptimized at runtime (bound with REOPT(VARS) )
	R	At runtime (bound with REOPT(VARS) )
QBLOCK_TYPE	Indicates the type of SQL operation performed for the query block:
	SELECT	SELECT
	SELUPD	SELECT with FOR UPDATE OF
	INSERT	INSERT
	UPDATE	UPDATE
	UPDCUR	UPDATE WHERE CURRENT OF CURSOR
	DELETE	DELETE
	DELCUR	DELETE WHERE CURRENT OF CURSOR
	CORSUB	Correlated subselect
	NCOSUB	Non-correlated subselect
	TABLEX	Table expression
	TRIGGER	WHEN clause on a TRIGGER
	UNION	UNION
	UNIONA	UNION ALL
BIND_TIME	Indicates the time the plan or package for the statement or query block was bound.
OPTHINT	A string used to identify this row as an optimization hint for DB2. DB2 will use this row as input when choosing an access path.
HINT_USED	If an optimization hint is used, the hint identifier is put in this column (that is, the value of OPTHINT ).
PRIMARY_ACCESSTYPE	Indicates if direct row access will be attempted:
	D	DB2 will try to use direct row access. At runtime, if DB2 cannot use direct row access, it uses the access path described in ACCESSTYPE .
	blank	DB2 will not try to use direct row access.
PARENT_QBLOCKNO	Indicates the QBLOCKNO of the parent query block.
TABLE_TYPE	Indicates the type of table, as follows:
	B	Buffers for an INSERT within a SELECT
	F	Table function
	M	Materialized query table (MQT)
	Q	Temporary intermediate result table (not materialized)
	T	Table
	W	Work file
	null	Query uses GROUP BY , ORDER BY , or DISTINCT , which requires an implicit sort
TABLE_ENCODE	Indicates the encoding scheme for the table, as follows:
	A	ASCII
	E	ENCDIC
	U	Unicode
	M	Multiple CCSID sets
TABLE_SCCSID	The SBCS CCSID value of the table; if TABLE_ENCODE is set to M , this value is .
TABLE_MCCSID	The mixed CCSID value of the table; if TABLE_ENCODE is set to M , this value is .
TABLE_DCCSID	The DBCS CCSID value of the table; if TABLE_ENCODE is set to M , this value is .
ROUTINE_ID	IBM use only.

Recall from Chapter 20, "DB2 Behind the Scenes," the access strategies that DB2 can choose in determining the access path for a query. Understanding how these access path strategies relate to the PLAN_TABLE columns is useful. The following sections provide a synopsis of the strategies and how to recognize them based on particular PLAN_TABLE columns.

The specific type of operation to which the PLAN_TABLE row applies is recorded in the QBLOCK_TYPE column. This column, in conjunction with the ACCESSTYPE column, can be used to identify the specific operations taken to satisfy each portion of the query. Table space scans are indicated by ACCESSTYPE being set to R . For a partitioned table space scan in which specific partitions can be skipped , ACCESSTYPE is set to R and PAGE_RANGE is set to Y . Index scans are indicated by ACCESSTYPE being set to any other value except a space.

When PREFETCH is set to S , sequential prefetch can be used; when it is set to L , list prefetch can be used. Even if the PREFETCH column is not set to L or S , however, prefetch can still be used at execution time. Whether sequential detection is used cannot be determined from the PLAN_TABLE because it is specified for use only at execution time.

If an index is used to access data, it is identified by creator and name in the ACCESSCREATOR and ACCESSNAME columns. A direct index lookup cannot be determined from the PLAN_TABLE alone . In general, a direct index lookup is indicated when the MATCHCOLS column equals the same number of columns in the index and the index is unique. For a non-unique index, this same PLAN_TABLE row can indicate a matching index scan. This additional information must be retrieved from the DB2 Catalog.

A non-matching index scan is indicated when the MATCHCOLS=0 . The INDEXONLY column is set to Y for index-only access, or to N when the table space data pages must be accessed in addition to the index information. Index screening cannot be determined by looking at the PLAN_TABLE data, but if MATCHCOLS is less than the number of columns in the index key index screening is possible.

A one-fetch index access is used when ACCESSTYPE equals I1 . This type of access is used when a single row can be used to resolve a MIN or MAX function. And, multiple-index access can be determined by the existence of M , MX , MI , or MU in the ACCESSTYPE column.

Clustered and non-clustered index access cannot be determined using the PLAN_TABLE . You will need to query the DB2 Catalog to determine whether each index is clustered. Also, index lookaside is generally available when DB2 indexes are used (but is not shown in the PLAN_TABLE ).

A parallel query is indicated by values in ACCESS_DEGREE indicating the number of parallel streams to be invoked. It is the number of parallel tasks that BIND deems optimal. The degree can be decreased at runtime. The type of parallelism (I/O, CPU, or Sysplex) is recorded in the PARALLELISM_MODE column. Parallel tasks are grouped into parallel groups as indicated by the value(s) in ACCESS_PGROUP_ID . JOIN_DEGREE and JOIN_PGROUP_ID are populated when tables are joined in parallel.

For the different join methods , the METHOD column is set to 1 for a nested loop join, 2 for a merge scan join, or 4 for a hybrid join. If the METHOD column is 3 , it indicates a sort operation on data from a previous step.

Now that you know what to look for, you can examine some sample access paths.

Sample Access Paths

The primary objective of EXPLAIN is to provide a means by which an analyst can "see" the access paths chosen by DB2. This section provides some EXPLAIN examples showing the SQL statement, rows from a PLAN_TABLE that were the result of an EXPLAIN being run for that SQL statement, and an analysis of the output. Based on the results of the EXPLAIN , you might decide that a better access path is available for that SQL statement. This process involves tuning, which is discussed in Part V, "DB2 Performance Tuning." This section concentrates solely on showing the EXPLAIN results for different types of accesses .

PLAN_TABLE rows for various types of accesses follow. You can use them as a guide to recognizing access path strategies in the PLAN_TABLE . Italicized column data is unique to the access path strategy being demonstrated. (For example, in the first row shown, the R in the TYP column is italicized, indicating that a table space scan is used.)