Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1745795
  • 博文数量: 335
  • 博客积分: 4690
  • 博客等级: 上校
  • 技术积分: 4341
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-08 21:38
个人简介

无聊之人--除了技术,还是技术,你懂得

文章分类

全部博文(335)

文章存档

2016年(29)

2015年(18)

2014年(7)

2013年(86)

2012年(90)

2011年(105)

分类: DB2/Informix

2012-05-15 21:05:06

所谓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
TM0NL0
TMX1IX1Y1
TMX1IX2Y2
TMI0N3


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






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.

QUERYNOA 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.

PROGNAMEThe 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.

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

METHODA 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


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

TIMESTAMPUsually, 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

MIXOPSEQThe 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_DEGREEThe 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_TYPEThe 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_MEMBERThe 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

阅读(3271) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~