EXPLAIN-PLAN_TABLE

所谓plan_table 就是DB2 优化器根据catalog里面收集到表的统计信息以及应用程序里面的sql源代码生成的一个所谓的访问DB2 中数据的最优方法，记住是最优的，但是这个最优是DB2 优化器认为最优的，也有可能很差。应用程序通过 bind将这些访问表的方法记录在plan_tale中，当应用程序执行的时候，DB2就根据这个表中的方法进行处理。这是这不是绝对的，程序在实际执行的时候，不一定走plan_table中的访问路径，这时对程序进行调优的话，你就要通过PE来进行实时查看，程序到底采用的何种访问路径。

下面通过白皮书里面的一个程序来说明。

Both of the following examples have these indexes: IX1 on T(C1) and IX2 on T(C2).

Example: Suppose that you issue the following SELECT statement:

SELECT * FROM T WHERE C1 = 1 AND C2 = 1;

DB2 processes the query by performing the following steps:

1. DB2 retrieves all the qualifying record identifiers (RIDs) where C1=1, by using index IX1.
2. DB2 retrieves all the qualifying RIDs where C2=1, by using index IX2. The intersection of these lists is the final set of RIDs.
3. DB2 accesses the data pages that are needed to retrieve the qualified rows by using the final RID list.

The plan table for this example is shown in .

Table 166. PLAN_TABLE output for example with intersection (AND) operator

TNAME	ACCESS- TYPE	MATCH- COLS	ACCESS- NAME	INDEX- ONLY	PREFETCH	MIXOP- SEQ
T	M	0		N	L	0
T	MX	1	IX1	Y		1
T	MX	1	IX2	Y		2
T	MI	0		N		3

这样就比较容易理解，至于其它复杂的访问路径，不同的访问类型来进行区分，下面给出了该表的定义以及各个字段的定义

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) FOR BIT DATA 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_QBLOCKNO 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, |CTEREF SMALLINT NOT NULL WITH DEFAULT, |STMTTOKEN VARCHAR(240))

Plan table column descriptions:  explains the columns in PLAN_TABLE. The explanations apply both to rows resulting from the execution of an EXPLAIN statement and to rows resulting from a bind or rebind.

Each row in a plan table describes a step in the execution of a query or subquery in an explainable statement. The column values for the row identify, among other things, the query or subquery, the tables involved, and the method used to carry out the step.

QUERYNO

A number intended to identify the statement being explained. For a row produced by an EXPLAIN statement, specify the number in the QUERYNO clause. For a row produced by non-EXPLAIN statements, specify the number using the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE and DELETE statement syntax. Otherwise, DB2 assigns a number based on the line number of the SQL statement in the source program. When the values of QUERYNO are based on the statement number in the source program, values greater than 32767 are reported as 0. However, in a very long program, the value is not guaranteed to be unique. If QUERYNO is not unique, the value of TIMESTAMP is unique.

PROGNAME

The name of the program or package containing the statement being explained. Applies only to embedded EXPLAIN statements and to statements explained as the result of binding a plan or package. Blank if not applicable.

PLANNO

The number of the step in which the query indicated in QBLOCKNO was processed. This column indicates the order in which the steps were executed.

METHOD

A number (0, 1, 2, 3, or 4) that indicates the join method used for the step:0First table accessed, continuation of previous table accessed, or not used.1Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined.2Merge scan join. The present composite table and the new table are scanned in the order of the join columns, and matching rows are joined.3Sorts needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate, or an IN predicate. This step does not access a new table.4Hybrid join. The current composite table is scanned in the order of the join-column rows of the new table. The new table is accessed using list prefetch.

TNAME

|The name |of a table, materialized query table, created or declared temporary |table, materialized view, or materialized table expression. The value is blank if METHOD is 3. The column can also contain the name of a table in the form DSNWFQB(qblockno). DSNWFQB(qblockno) is used to represent the intermediate result of a UNION ALL or an outer join that is materialized. If a view is merged, the name of the view does not appear

ACCESSTYPE

The method of accessing the new table: I    By an index (identified in ACCESSCREATOR and ACCESSNAME) I1   By a one-fetch index scan M   By a multiple index scan (followed by MX, MI, or MU) MI      By an intersection of multiple indexes MU    By a union of multiple indexes MX     By an index scan on the index named in ACCESSNAME N    By an index scan when the matching predicate contains the IN keyword R   By a table space scan RW  By a work file scan of the result of a materialized user-defined table function| V     By buffers for an INSERT statement within a SELECTblankNot applicable to the current row

MATCHCOLS	For ACCESSTYPE I, I1, N, or MX, the number of index keys used in an index scan; otherwise, 0.
ACCESSCREATOR	For ACCESSTYPE I, I1, N, or MX, the creator of the index; otherwise, blank.
ACCESSNAME	For ACCESSTYPE I, I1, N, or MX, the name of the index; otherwise, blank.
INDEXONLY	Whether access to an index alone is enough to carry out the step, or whether data too must be accessed. Y=Yes; N=No.

TIMESTAMP

Usually, the time at which the row is processed, to the last .01 second. If necessary, DB2 adds .01 second to the value to ensure that rows for two successive queries have different values.

PREFETCH

|Whether data |pages are to be read in advance by prefetch:|##S#Pure sequential prefetch##L#Prefetch through a page list##D#Possible candidate for dynamic prefetch##blank#Unknown or no prefetch

MIXOPSEQ

The sequence number of a step in a multiple index operation.1, 2, ... nFor the steps of the multiple index procedure (ACCESSTYPE is MX, MI, or MU.)0For any other rows (ACCESSTYPE is I, I1, M, N, R, or blank.)

ACCESS_DEGREE

The number of parallel tasks or operations activated by a query. This value is determined at bind time; the actual number of parallel operations used at execution time could be different. This column contains 0 if there is a host variable.

JOIN_TYPE

The type of join:FFULL OUTER JOINLLEFT OUTER JOINSSTAR JOINblankINNER JOIN or no joinRIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that JOIN_TYPE contains L.

GROUP_MEMBER

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.

BIND_TIME

#For static SQL statements, the time at #which the plan or package for this statement or query block was bound. #For cached dynamic SQL statements, the time at which the which the #statement entered the cache. For static and cached dynamic SQL statements, #this is a full-precision timestamp value. For non-cached dynamic SQL #statements, this is the value contained in the TIMESTAMP column of #PLAN_TABLE appended by 4 zeroes.

QBLOCKNO

|A number |that identifies each query block within a query. The value of the |numbers are not in any particular order, nor are they necessarily |consecutive