DB2执行计划的创建使用和更新.doc

我们发现某个SQL语句执行很慢时，可以通过查看它的访问计划来定位原因，如是否执行了合适的索引、是否采用了正确的连接方法等。但是我们发现很多用户对访问计划的生成和解释工具的使用存在很多疑惑，本文通过一些实例来解释具体的用法，希望对大家有所帮助。- 如果没有建立过执行计划表，要先建立执行计划表（连接数据库并执行脚本文件EXPLAIN.DDL）db2home=/tw1_db2home/db2inst1db2 connect to dssdwdb2 -tvf $db2home/sqllib/misc/EXPLAIN.DDL (在当前数据库中会生成9张解释表，采集的解释数据会存放到这些表中。)- 解释表 EXPLAIN_ARGUMENT 包含所用各独立操作符的独特特征（如果存在的话）。 EXPLAIN_INSTANCE 包含所解释的 SQL 语句的源的基本信息，还有关于进行解释的环境的信息。（EXPLAIN_INSTANCE 表是所有解释信息的主要控制表。其它解释表中的各行数据显式地链接到该表中的各行。） EXPLAIN_OBJECT 包含关于为 SQL 语句生成的访问计划所需的数据对象的信息。 EXPLAIN_OPERATOR 包含 SQL 编译器为满足 SQL 语句而需的所有操作符。 EXPLAIN_PREDICATE 包含确定特定操作符应用哪些谓词的相关信息。 EXPLAIN_STATEMENT 包含在得到不同级别的解释信息时存在的 SQL 语句文本。用户输入的原始 SQL 语句存储在该表中，另外还有 DB2 Optimizer 用于选择满足 SQL 语句的访问计划的版本。（后一种版本可能与原始版本的语句略有差异，因为 SQL Precompiler 可能已通过额外的谓词重写和/或增强了该语句。） EXPLAIN_STREAM 包含关于各单独操作符和数据对象之间存在的输入输出数据流的信息。（数据对象本身显示于 EXPLAIN_OBJECT 表中，而数据流中涉及的操作符可在 EXPLAIN_OPERATOR 表中找到。）- 开始和关闭执行计划模式db2 connect to dssdwdb2 set current explain mode explain （这点和Oracle里的10046及sql_trace一样，此过程中的sql执行将被trace）db2 set current explain mode no （这里和ORACLE的sql_trace false, event off一样了）- 查找存储过程所对应的程序包的名称SELECT CHAR(PROCSCHEMA,20) AS PROCSCHEMA, - 模式名称 CHAR(PROCNAME,20) AS PROCNAME, - 存储过程名称 CHAR(B.BNAME,20) AS PKGNAME - 绑定包名称FROM SYSCAT.PROCEDURES A JOIN SYSCAT.ROUTINEDEP B ON A.SPECIFICNAME = B.ROUTINENAMEWHERE PROCSCHEMA=KF2 AND PROCNAME=EXPLAINPLAN_TESTWITH UR;- 查看执行计划(1) db2expln -d 数据库名 -c 模式名称 -p 绑定包名称 -s 0 -g -t -o 输出文件 如: db2 connect to dssdw db2 set current explain mode explain db2expln -d dssdw -c KF2 -p P0560833 -s 0 -g -t -o explainplan.out (-s 0 是指分析所有的SQL命令 -g 是指给出存取计划的图形输出(用字符模拟的) -t 是指同时打印到终端屏幕) db2 set current explain mode no(2) db2exfmt -d 数据库名 -g TIC -w -1 -n % -s % -# 0 -o 输出文件 如: db2 connect to dssdw db2 set current explain mode explain db2 -tvf db2 set current explain mode no db2exfmt -d dssdw -g TIC -w -1 -n % -s % -# 0 -o explainplan.out cat explainplan.out- 查看执行计划的另外两种方式：1. db2expln -d dssdw -u username password -g -t -q statement2. db2 set current explain mode explaindb2 statementsdb2 set current explain mode nodb2exfmt -d dssdw -u username password -w -l -s % -n % -o explain.outcat explain.out - 执行计划的解读db2exfmt o Table Operatorsdb2exfmt TBSCAN - Table Scandb2exfmt IXSCAN - Index Scandb2exfmt FETCH - Fetch from Tabledb2exfmtdb2exfmt o Joinsdb2exfmt MSJOIN - Merge Scan Joindb2exfmt NLJOIN - Nested Loop Joindb2exfmt HSJOIN - Hash Joindb2exfmtdb2exfmt o Aggregationdb2exfmt GRPBY - Group Bydb2exfmt SUM - Sumdb2exfmt AVG - Averagedb2exfmt MIN - Minimumdb2exfmt MAX - Maximundb2exfmt etc.db2exfmtdb2exfmt o Temp / Sortdb2exfmt TEMP - Insert into temp tabledb2exfmt SORT - Sortdb2exfmtdb2exfmt o Special Operationsdb2exfmt IXAND - Index ANDingdb2exfmt RIDSCA - Index ORing or List Prefetchdb2exfmt IXA - Star Schema Bitmap Indexingdb2exfmt BTQ - Broadcast Table Queuedb2exfmt DTQ - Directed Table Queuedb2exfmt MBTQ - Merge Broadcast Table Queuedb2exfmt MDTQ - Merge Directed Table Queuedb2exfmt LTQ - Local Table Queue, for Intra-partition parallelism+-+-+-+| Join Strategy | Inner Table | Outer Table |+-+-+-+| Collocated Join | Temporary Table | Temporary Table |+-+-+-+| Directed Inner Join | Temporary Table | Hashed Table Queue | Directed Outer Join | Hashed Table Queue | Temporary Table | Directed Inner and Outer Join | Hashed Table Queue | Hashed Table Queue |+-+-+-+| Broadcast Inner Join | Temporary Table | Broadcast Table Queue | Boradcast Outer Join | Broadcast Table Queue | Temporary Table |+-+-+-+附:BTQ Broadcast Table Queue broadcasts data to several partitions.DELETE Deletes rows from a table.DTQ Directed Table Queue transfers data to a specific partition.EISCAN Scans a user-defined index to produce a reduced stream of rows.FETCH Fetches columns from a table using spe-cific record identifier.FILTER Represents the application of residual predicates.GRPBY Groups rows by common values of desig-nated columns or functions.HSJOIN Represents a hash join, where two or more tables are hashed on join columns.INSERT Inserts rows into a table.IXAND ANDs together the row identifiers (RIDs) from two or more index scans.IXSCAN Scans an index of a table with optional start/stop conditions, producing an ordered stream of rows.LTQ Local Table Queue. Transfers data between local agents.LMTQ Local Merge Table Queue. Merges data transferred between local agents.MBTQ Merging Broadcast Table Queue.MDTQ Merging Directed Table queue.MSJOIN Represents a merge join, where both outer and inner tables must be in join-predicate order.NLJOIN Represents a nested loop join that accesses an inner table once for each row of the outer table.RETURN Represents the return of data from the query to the user.RIDSCAN Scans a list of row identifiers (RIDs) obtained from one or more indexes.RPD For nonrelational wrappers, it shows the simulated SQL operation that the nonrela-tional wrapper will be asked to perform.SHIP Retrieves data from a remote database source. Used in federated systems.SORT Sorts rows in the order of specified col-umns, and optionally eliminates duplicate entries.TBSCAN Retrieves rows by reading all required data directly from the data pages.TEMP Stores data in a temporary table to be read back out (possibly multiple times).TQUEUE Transfers table data between agents.UNION Concatenates streams of rows from multi-ple tables.UNIQUE Eliminates rows with duplicate values, for specified columns.UPDATE Updates rows in a table.- DB2修改执行计划和ORACLE数据库一样，DB2数据库里面也是通过优化器来分析你的SQL，生成它认为最优的执行计划(Access Plan)。DB2的优化器实际上是一个标准规则集合，一般来说我们只要告诉DB2要检索什么，而不是如何检索。 那么DB2的优化器是根据什么来判断SQL的最优存取路径呢？DB2的优化器是基于成本的优化器，也就是CBO(Cost Based Optmizer)。也就是说DB2 优化器会应用查询成本公式，该公式对每条可能的存取路径的四个因素进行评估和权衡：CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的 SQL 语句。那么我们来简单看一下DB2的优化器的工作流程：1. DB2的优化器，在接收到SQL语句后，会首先校验SQL的语法，确保是正确的SQL2. 根据当前的系统环境信息，生成最优的执行计划来优化SQL语句3. 把SQL翻译成计算机指令语言，并执行这个优化后的SQL4. 返回结果，或者存储它们，以便将来的执行在我们看来，DB2 系统目录中统计信息是让DB2优化器正确工作的一个非常重要的依据。这些统计信息向优化器提供了与正在被优化的 SQL 语句将要访问的表状态相关的信息。这些信息主要包括：Table-包括表的记录数、PAGE、PCTFREE以及COMPRESS等信息，相关的系统视图是：sysstat.tables、syscat.tablesColumns包括COLUMNS的数量、长度、分布特征以及COMPRESS等信息，相关的系统视图是：sysstat.columns、syscat. columnsIndex-包括是否存在索引、索引的组织(叶子页的数量和级别的数量)、索引键的离散值的数量以及是否群集索引， 相关的系统视图是：sysstat.indexes、syscat. indexes其他的还有分区/节点组信息和表空间的信息如何及时更新这些信息呢？保证DB2优化器正确的工作，在DB2里面提供了两个办法: RUNSTATS与REOGCHKRunstats这个命令的功能主要就是收集数据库对象的状态信息，这对数据库使用合理的ACCESS PLAN是至关重要的。一般来说，以下几种情况下面，我们需要用runstats来收集统计信息：1. 在给表创建一个index后，我们最好做一次runstat。这 个情况也是大家经常忽略的。很多时候大家在给表增加了一个index后，分析执行计划，发现没有变化，觉得很奇怪。其实这个时候，你需要做一次 runstats，就可以了。在8.2里面，DB2做了很好的改进，可以避免这个问题，在创建index的时候，可以立即更新你的信息。2. 在对table做了一次reorg后，记得要做一次runstats。因为对表做reorg，会修改表的很多信息，比如高水位等，所以做一次runstats，可以更新统计信息。3. 当你的表里面的数据发生了比较大的变化，一般来说，大约表里面的数据量的10%-20%发生了变化，就应该作一次runstats。这 些变化包括删除，修改，插入。对于一些非常大的表，比方在数据仓库的项目里面，某些事实表非常巨大。这个时候，完整的对一个大表作runstats可能花 费时间相当大，DB2 8.1里面支持我们对这些大表作抽样，比方说只对20%的数据作runstats，这样的话，一般来说也能保证得到正确的执行计划。当然首先要确保这个表 里面的数据最好分布比较均匀。4. 当你在分区(DPF)数据库里面使用了REDISTRIBUTE DATABASE PARTITION GROUP这个命令，那么就需要用runstats来收集新的统计信息。RUNSTATS命令的语法如下：如果表名为DB2INST1.STAFF，表上有索引，则可以用下面的例子完成RUNSTATS命令：db2 runstats on table db2inst1.staff with distribution and detailed indexes all在实际的项目里面，对于变化比较大的表，需要我们定时对数据库做runstats，一般来说runstats和reorg可以结合起来做，首先对表作reorg，然后做runstats，最后REBIND数据库根据最新的统计信息生成合适的统计计划。值得注意的是，如果我们要处理的表数据量是快速变化的，比如在电信移动行业，需要在月末进行处理的汇总表。在不长的时间范围内数据量变化特别大，从而使 得RUNSTATS 得到的统计信息不准确，原因是这些统计信息只是某个时间点的信息。您可以用这条语句来把表修改为volatile。alter table table_name volatile cardinality这样优化器将考虑使用索引扫描而不是表扫描。无论统计信息如何，优化器将使用索引扫描而不是使用表扫描。IBM的文档里面还提供了REORGCHK这个命令，可以根据统计公式计算表是否需要重整。比如可以分为对系统表和用户表两部分分别进行REORGCHK：1) 针对系统表进行REORGCHKdb2 reorgchk update statistics on table system2) 针对用户表进行REORGCHKdb2 reorgchk update statistics on table user需要注意的是，如果数据库中数据量比较大，这些操作一般所需时间比较长，所以尽量安排在数据库比较空闲的时候做。db2 update db cfg using AUTO_MAINT off AUTO_TBL_MAINT off AUTO_RUNSTATS off在DB2 8.2里面数据库可以自动进行统计信息收集，不过这样的动作还是会带来额外的负载，一般情况下面可以关掉，只在我们需要的时候运行就可以了。LOADLoad这个工具是DB2里面一个非常强大的数据迁移工具。一般用作大批量的数据插入。因为Load操作不记日志，所以效率非常好。笔者曾经在 RS6000平台上面实现50-60m/s的速度Load数据。在这里我想讨论的是在DB2数据库里面如何用load来影响你的catalog视图的统计 信息。在Load的时候使用statisti