DB2 for Linux, UNIX, and Windows 查询性能故障诊断

V9.7 中的改进

从 IBM® DB2 for Linux®, UNIX®, and Windows® V9.7 Fix Pack 1 开始,DB2 数据库管理器新增了两种全新的高级查询调优特性:片段解释(section explain)和片段实绩(section actuals)。本文将通过具体的示例逐一介绍如何使用这些特性来调试查询计划,以及如何充分利用 DB2 最具成本优势的优化器。

Samir Kapoor, DB2 UDB 高级支持分析师, IBM

Samir Kapoor 的照片Samir Kapoor 是一位 IBM 认证的 DB2 UDB 高级技术专家。Samir 目前在 DB2 UDB Advanced Support - Down system division(DSD)团队工作,他具有引擎领域的深入知识。



Ani Patel, DB2 高级支持专家, IBM

Ani Patel 的照片Ani Patel 目前在 IBM 多伦多实验室工作,是一名 2 级 DB2 高级支持分析师。他从事 DB2 支持已超过 7 年之久。他擅长的领域包括存储、备份、恢复和日志记录。他拥有加拿大多伦多瑞尔森大学计算机工程学士学位。



Tao Wang, DB2 高级技术支持, IBM

作者照片:Tao WangTao Wang 是一位 IBM 认证高级数据库管理员 - DB2 for Linux, UNIX, and Windows。Tao 目前任职于 DB2 高级支持 - Down System Division (DSD) 团队,他在引擎方面有很深的造诣。



2011 年 8 月 09 日

免费下载:IBM® DB2® Express-C 9.7.2 免费版 或者 DB2® 9.7 for Linux®, UNIX®, and Windows® 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

简介

从 V9.7 Fix Pack 1 开始,DB2 for Linux, UNIX, and Windows 数据库管理器就支持直接从一个运行时片段的内容执行一个 explain。这个功能就是所谓的片段解释。访问计划操作者也可以在一个片段执行时收集运行时统计信息。这些统计信息称为片段实绩。片段实绩使您能够查看访问计划的真实基数与估计基数的对比。

在本文中,我们将通过一些实例逐一介绍如何使用各种方法获得片段解释信息。此外,我们还列举了一个例子来详细说明如何收集片段实绩信息,以了解实绩基数与优化器所估算的基数之间的差别。最后,我们讨论了 db2support 实用工具 用于收集优化器相关信息的一些改进。在您为 IBM DB2 支持部门收集优化器相关信息时,这是很有用的。

更多关于片段解释和片段实绩的信息,请访问 DB2 信息中心(请参阅参考资料)。本文主要通过示例来分步解释此功能的使用方法。


创建环境和测试数据库

在开始之前,我们需要创建一个测试环境,其中一个查询需要几分钟才能够完成。在我们的测试环境中,示例中使用的这个查询的执行时间会接近 2 分钟。此外,我们使用的是 AIX 的一个单分区环境:

清单 1. OS 版本
Operating system is: AIX 64BIT 5.3.0.0

dblevel 命令的输出显示了我们在测试中使用的 DB2 版本。

清单 2. db2level
Server is running EE.
DB21085I  Instance "skapoor" uses "64" bits and DB2 code release "SQL09073"
with level identifier "08040107".
Informational tokens are "DB2 v9.7.0.3", "s101006", "IP23212", and Fix Pack
"3a". Product is installed at "/home/skapoor/sqllib".

空间需求

在重新创建本文所介绍的所有测试用例时,需要 20 GB 的磁盘空间用于存储数据和日志文件。其中大约 13 GB 的空间是分配给日志文件的,而其余空间则留给数据。测试使用循环日志记录方式,一共分配了 100 个主日志。

清单 3. 针对日志的数据库配置
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150

您可以通过简单修改脚本而减少日志文件所需要的磁盘空间,以便将数据填充到事实表中。我们将在后面对此进行介绍。

配置

影响查询执行计划的主要数据库配置参数设置如下:

清单 4. 配置
Parallelism:          None
CPU Speed:            3.857478e-07
Comm Speed:           0
Buffer Pool size:     17083
Sort Heap size:       1241
Database Heap size:   2277
Lock List size:       7020
Maximum Lock List:    97
Average Applications: 1
Locks Available:      217900
SQL Type:             Dynamic
Optimization Level:   5
Blocking:             Block All Cursors
Isolation Level:      Cursor Stability
STMTHEAP:             8192

测试的主查询

下面是我们用来测试本文所讨论的各种方法的查询。这个查询会执行一个外连接操作,以便比较从 1 月份至 11 月份的 10 个特殊商店的销售信息。

清单 5. 主查询 [Query1.sql]
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
     D.MONTH AS MONTH, 
     S.STORE_ID AS STORE_ID,
     S.DISTRICT AS DISTRICT,
     S.REGION AS REGION,
     SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F1,
     SKAPOOR.DATE_DIM D,
     SKAPOOR.PRODUCT_DIM P,
     SKAPOOR.STORE_DIM S
     
  WHERE
     P.MODEL LIKE '%model%' AND
     F1.DATE_ID=D.DATE_ID AND
     F1.PRODUCT_ID=P.PRODUCT_ID AND
     F1.STORE_ID=S.STORE_ID AND
     F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND
     F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND  
     D.MONTH = 1 

  GROUP BY
     S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
     
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT 
     D1.MONTH AS MONTH,
     S1.STORE_ID AS STORE_ID,
     S1.DISTRICT AS DISTRICT,
     S1.REGION AS REGION,
     SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F2,
     SKAPOOR.DATE_DIM D1,
     SKAPOOR.PRODUCT_DIM P1,
     SKAPOOR.STORE_DIM S1

  WHERE
     P1.MODEL LIKE '%model%' AND
     F2.DATE_ID=D1.DATE_ID AND
     F2.PRODUCT_ID=P1.PRODUCT_ID AND
     F2.STORE_ID=S1.STORE_ID AND
     F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND
     F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND  
     D1.MONTH=11

  GROUP BY
     S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 

SELECT 
     A.*, 
     B.*
FROM
     TMP1 A LEFT OUTER JOIN TMP2 B ON
       (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

对于这个查询来说,可以证明 DATE_ID 和 STORE_ID 上建立的索引是很有用的。但是为了演示这里介绍的功能,我们先不在事实表的 DATE_ID 和 STORE_ID 列上建立索引,这样查询就会执行很长一段时间 — 例如,在我们的测试环境中执行时间接近 2 分钟。

设置数据库

第 1 步: 我们假设已经用默认表空间创建了一个数据库,同时创建下面的事实表和维度表。您可以将模式名称从 SKAPOOR 修改为适合您环境使用的名称。如果您调整了名称,那么一定要同时修改清单 5 的查询,使之调用正确的模式名称。您可以将下面的语句复制到一个文件 TEST1.ddl 中,然后使用下面的方式执行它:

db2 -tvf TEST1.ddl -z test1.log
清单 6. 设置数据库
-- Replace <path> with your desired path with 20 GB of disk space.
create db section on <path>;

CREATE TABLE "SKAPOOR "."SALES_FACT"  (
    "DATE_ID" DATE , 
    "PRODUCT_ID" INTEGER , 
    "STORE_ID" INTEGER , 
    "QUANTITY" INTEGER , 
    "PRICE" INTEGER , 
    "TRANSACTION_DETAILS" CHAR(100) )   
   
CREATE TABLE "SKAPOOR "."DATE_DIM"  (
    "DATE_ID" DATE NOT NULL , 
    "MONTH" INTEGER , 
    "QUARTER" INTEGER , 
    "YEAR" INTEGER )   ;
    
ALTER TABLE "SKAPOOR "."DATE_DIM" 
 ADD PRIMARY KEY
  ("DATE_ID");


CREATE TABLE "SKAPOOR "."PRODUCT_DIM"  (
    "PRODUCT_ID" INTEGER NOT NULL , 
    "PRODUCT_DESC" CHAR(20) , 
    "MODEL" CHAR(10) , 
    "MAKE" CHAR(10) )   ;


ALTER TABLE "SKAPOOR "."PRODUCT_DIM" 
 ADD PRIMARY KEY
  ("PRODUCT_ID");


CREATE TABLE "SKAPOOR "."STORE_DIM"  (
    "STORE_ID" INTEGER NOT NULL , 
    "LOCATION" CHAR(15) , 
    "DISTRICT" CHAR(15) , 
    "REGION" CHAR(15) )  ;

ALTER TABLE "SKAPOOR "."STORE_DIM" 
 ADD PRIMARY KEY
  ("STORE_ID");


COMMIT WORK;

CONNECT RESET;

第 2 步:创建了表之后,您就可以按照下面的方式将数据插入到 3 个维度表中,并且修改模式以符合您的环境:

清单 7. 使用数据填充 DATE_DIM 表
db2 -td@ -vf date_insert.txt -z date_insert.log
清单 8. 使用数据填充 PRODUCT_DIM 表
db2 -td@ -vf product_insert.txt -z product_insert.log
清单 9. 使用数据填充 STORE_DIM 表
db2 -td@ -vf store_insert.txt -z store_insert.log

在 DATE_DIM 表中填充 2010 年全年 365 天的数据。

清单 10. 文件 date_insert.txt 的内容
connect to SECTION@

begin atomic
  declare cnt INT default 1;
  declare dat DATE default '01/01/2010';
  declare yer INT default 2010;
  declare quart INT default 1;

while (cnt <= 365) do    
    if (int(dat + cnt DAYS)/100) between 201001 and 201003 then
           set quart=1;
    elseif (int(dat + cnt DAYS)/100) between 201004 and 201006 then
           set quart=2;
    elseif (int(dat + cnt DAYS)/100) between 201007 and 201009 then
           set quart=3;
    elseif (int(dat + cnt DAYS)/100) between 201010 and 201012 then
           set quart=4;    
    end if;
    
    insert into SKAPOOR.DATE_DIM values (
 dat + cnt DAYS,
 (int(dat + cnt DAYS)/100) - 201000,
 quart,
 yer
    );
       
    set cnt=cnt+1;
end while;

end@

connect reset@

在 PRODUCT_DIM 表中填入 60,000 条产品信息。

清单 11. 文件 product_insert.txt 的内容
connect to SECTION@

drop sequence seq1@
drop sequence seq2@

create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@

begin atomic
   declare cnt INT default 1;

   while (cnt < 60001) do
 insert into SKAPOOR.PRODUCT_DIM values (
     nextval for SEQ2,
     'product desc' concat char(nextval for SEQ1),
            'model ' concat char(integer(rand()*1000)),
     'maker ' concat char(integer(rand()*500))
 );
  set cnt=cnt+1;
     end while;
end@

drop sequence seq1@
drop sequence seq2@

connect reset@

在 STORE_DIM 表中填入 201 个商店的信息。

清单 12. 文件 store_insert.txt 的内容
connect to SECTION@

drop sequence seq2@

create sequence seq2 as integer start with 0 increment by 1@

begin atomic
    declare cnt INT default 1;

    while (cnt < 202) do
       insert into SKAPOOR.STORE_DIM values (
     nextval for SEQ2,
     'location' concat char(integer(rand()*500)),
     'district' concat char(integer(rand()*10)),
     'region' concat char(integer(rand()*5))
       );
       set cnt=cnt+1;

    end while;
end@

drop sequence seq2@

connect reset@

第 3 步:将数据插入 SALES_FACT 表。将模式名称修改为符合您环境的名称。在我们的测试环境中,将数据插入事实表大约需要一个半小时。

清单 13. 使用数据填充 SALES_FACT 表
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log
清单 14. 文件 sales_fact_insert.ddl 的内容
connect to SECTION@

VALUES (CURRENT TIMESTAMP)@

begin atomic

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2010';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
         (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
          select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
          INT(RAND()*200 + 1), RESERVE, U_ID + 1
          from   v
          where  U_ID < 60000)
     select date_id, product_id, store_id, quantity, price, transaction_details from v;

     set cnt1 = cnt1 + 1;
     set cnt  = cnt + 1;
   end while;

end@

VALUES (CURRENT TIMESTAMP)@

connect reset@

注意:在清单 14 中,SALES_FACT 表是通过单独一个事务填充完成的,这需要占用大量的磁盘空间来记录日志。为了减少日志记录的影响,您可以创建一个存储过程,并分步提交插入数据。

清单 15. 填充 SALES_FACT 表的替代方法
connect to SECTION@

VALUES (CURRENT TIMESTAMP)@

-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging

create procedure salesFactPopulate()
specific salesFactPopulate
language sql

begin

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2010';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
    (
       values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
      union all
       select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
              INT(RAND()*200 + 1), RESERVE, U_ID + 1
         from v
        where U_ID < 60000
    )
    select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;

    commit work;

    set cnt1 = cnt1 + 1;
    set cnt=cnt+1;

   end while;

end@

-- populate the SALES_FACT table
invoke salesFactPopulate@

VALUES (CURRENT TIMESTAMP)@

connect reset@

第 4 步:我们需要对查询进行 explain(解释),以查看 DB2 查询优化器所选择的访问计划。

因此,我们使用 explain 工具,它需要先建立 EXPLAIN 表。我们可以通过以下步骤来创建 EXPLAIN 表:

  1. 将目录修改为您的 sqllib/misc 目录所在的位置。在我们的测试环境中,它是 “$HOME/sqllib/misc”。
  2. 连接到 SECTION:执行 db2 connect
  3. 执行 db2 -tvf EXPLAIN.DDL

第 5 步:在事实表和 3 个维度表之间建立正确的引用完整性约束。

第 5.1 步:Alter(修改)事实表,创建正确的维度表外键(FK)联系。

清单 16. 在 SALES_FACT 表中创建外键约束和索引
db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log
清单 17. 文件 alter_sales_fact.txt 的内容
CONNECT TO SECTION;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CONNECT RESET;

第 5.2 步:收集所有表的统计信息。

优化器是依靠统计信息来计算可选查询执行计划(QEP)的开销,同时选择出最优的计划。在继续之前,我们需要收集一些统计信息。

清单 18. 收集所有表的统计信息
db2 -tvf runstats.ddl -z runstats.log
清单 19. 文件 runstats.ddl 的内容
CONNECT TO SECTION;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;

第 5.3 步:explain 和运行完整查询。

explain 这个查询,我们需要重复前面的步骤:

db2 connect to SECTION
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset

其中 QUERY1.SQL 的内容只包含清单 5 的查询,分号结尾。

这个查询执行计划可以使用 db2exfmt 工具查看:

db2exfmt -d SECTION -g TIC -w -1 -n % -s % -# 0 -o test1.txt

要测定查询的初始性能,我们需要按如下方式使用 db2batch

注意:在执行这些测试时,测试系统是空闲的,测试时没有运行其他任何活动。

使用 db2stop 强制停止 DB2,然后使用 db2start 重新启动 DB2。保证查询执行时间接近 2 分钟左右。您可以使用 db2batch 获得所用时间信息,命令如下:

db2batch -d SECTION -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt

在我们的环境中,测定结果如下:

* Prepare Time is:       7.278206 seconds
* Execute Time is:     107.729436 seconds
* Fetch Time is:         0.000102 seconds
* Elapsed Time is:     115.007744 seconds (complete)

片段解释

相对于使用 explain 语句的传统解释而言,片段解释的优势是 explain 会重新编译所解释的语句。如果编译环境或表统计信息在 explain 语句执行时已经发生改变,那么编译器可能会生成一个不同的访问计划。

然而,片段解释总是会提供执行时的确切访问计划,因为访问计划是从可执行的片段直接重新创建的。片段解释在功能上与 db2expln 类似,但是在详细程度上更接近于 explain 语句。

片段解释的功能是通过一组存储过程实现的。这些存储过程会接受输入参数,用于定位一个片段(无论是在内存中、目录中,还是由事件监视器捕捉到或直接作为输入提供的),并且执行 explain,按照与 explain 语句相似的方式填充解释表。存储过程会输出之前在解释表中填充的 explain 实例的关键字段。

这些关键字段可作为现有解释格式化工具的输入,例如 db2exfmt,它会从解释表提取信息,然后用一种格式化输出显示。

下面的过程可以执行一个片段解释:

  1. EXPLAIN_FROM_SECTION
  2. EXPLAIN_FROM_ACTIVITY
  3. EXPLAIN_FROM_DATA
  4. EXPLAIN_FROM_CATALOG

现在让我们在一个详细的例子中了解以上每一个过程。

EXPLAIN_FROM_SECTION

将可执行 ID 和位置作为输入,其中位置是使用内存中的包缓存和包缓存事件监视器名称指定的。这个过程会搜索指定位置的片段。

可执行 ID 可以唯一且一致地标识一个片段。可执行 ID 是数据服务器为所执行的每一个片段生成的一个不透明的二进制令牌。这个可执行 ID 会作为该片段的查询监视数据的输入,并用于执行一个片段解释。

使用包缓存或包缓存事件监视器 explain(解释)一个语句

语法:

>>-EXPLAIN_FROM_SECTION--(--executable_id--,--section_source_type
 >--section_source_name--,--member--,--explain_schema--,->
 >--explain_requester--,--explain_time--,--source_name--,-->
 >--source_schema--,--source_version--)->

executable_id— 唯一地标识一个将要 explain 的片段。如果为空,则返回 SQL2032。

section_source_type— 类型为 CHAR(1) 的输入参数,它是用来指定将要 explain 的片段的来源。有效值包括:

  • M — 片段是从内存中的包缓存获得的。
  • P — 片段是从包缓存事件监视器获得的。

为了查找用于 explain 这个片段的 executable_id,我们将在这个例子中使用表函数 mon_get_pkg_cache_stmt

示例:

这里我们需要两个会话。在第一个会话中(称为 SESSION1),我们将执行这个查询。在第二个会话中(SESSION2),我们将先执行命令来获取 executable_id,然后运行命令从片段获取一个解释。

SESSION1

我们首先在第一个会话中(本例中是 SESSION1)发出主查询,这样我们就能够得到运行时片段解释的目标。

SESSION2

在查询运行时,在另一个会话中(这里是 SESSION2)连接数据库,然后查找与该语句关联的 executable_id。具体如下:

1. 获取正在运行的查询的可执行 ID。

db2 "select executable_id, varchar(stmt_text,50) as stmt_text 
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-1)) as T"

EXECUTABLE_ID                                                        
STMT_TEXT
------------------------------------------------------------------- 
-------------------------------------------------
x'0000000100000000000000000000000100000000000220110412082630627501' 
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1

SQL0445W  Value "WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1" 
has been truncated.  SQLSTATE=01004

注意:可执行 ID 在每一个正在执行的查询中是唯一的,在您的环境中可能与本例中有所差别。

2. 通过调用 explain_from_section 过程收集 explain 信息:

db2 "call explain_from_section (x'000000010000000000000000000000010000
0000000220110412082630627501','M',NULL,0,'SKAPOOR',?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-04-12-09.16.24.384509

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

存储过程输出参数 EXPLAIN_REQUESTER、EXPLAIN_TIME、SOURCE_NAME、SOURCE_SCHEMASOURCE_VERSION 是由用于查询解释表的片段的信息的键组成的。在任何现有的解释工具(例如,db2exfmt)中都可以使用这些参数对从片段中检索到的 explain 信息进行格式化。

3. 生成 explain 输出(参见 下载)。

db2exfmt -d sample -1 -o exfmt_explain_from_section.out

Exfmt.out contents:
******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.3
SOURCE_NAME:       SQLC2H21
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2011-04-12-09.16.24.384509
EXPLAIN_REQUESTER: SKAPOOR

4) 查看 exfmt_explain_from_section.out 的内容。您会注意到有一个新的片段添加到计划中了,如下所示:

Explain level:    Explain from section

这表示 explain 是用于解释查询的。下面是这个计划的节选:

               Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                10
              TBSCAN
              (   2)
            1.55994e+06
                NA
                |
                10
              SORT
              (   3)
            1.55994e+06
                NA  
                |
                10
             HSJOIN<
             (   4)
           1.55994e+06
               NA
             ........

片段解释和 explain 语句输出的区别

执行一个片段解释所得到的结果与执行 explain 语句所收集的结果类似。片段解释和解释表中 explain 语句的输出与由 db2exfmt 实用工具生成的输出有一些差别。

出于某些性能方面的原因,我们没有收集 I/O 开销,也没有收集信息中心中讨论的的其他一些统计信息。请访问信息中心进一步了解未收集的字段。

EXPLAIN_FROM_ACTIVITY

在某些时候,用户可能希望捕捉过去执行的查询的访问计划。为此,用户可以使用 WLM_CAPTURE_ACTIVITY_IN_PROGRESS API(将在 EXPLAIN_FROM_DATA 小节中深入讨论)来捕捉正在运行的作业或创建一个活动监视器和 WLM_SET_CONN_ENV,收集活动信息和捕捉片段解释信息。

在这个例子中,我们将使用 WLM_SET_CONN_ENV 来收集活动数据。

这种片段 explain 过程的语法如下:

>>-EXPLAIN_FROM_ACTIVITY---------------------------------------->

>--(--appl_id--,--uow_id--,--activity_id--,--activity_evmon_name-->,--explain_schema-->

>--,--explain_requester--,--explain_time--,--source_name--,--source_schema-->

>--,--source_version--)->
表 1. 参数具体信息
名称方向数据类型
appl_id输入VARCHAR(64)
uow_id输入INTEGER
activity_id输入INTEGER
activity_evmon_name输入VARCHAR(128)
explain_schema输入VARCHAR(128)
explain_requester输出VARCHAR(128)
explain_time输出TIMESTAMP
source_name输出VARCHAR(128)
source_schema输出VARCHAR(128)
source_version输出VARCHAR(64)

这个存储过程的最后 5 个参数是输出参数。这些输出参数可用于在解释表中定位该片段的信息。请注意,这个过程在解释表的插入操作之后不会执行一条 COMMIT 命令。调用者必须在调用这个过程后执行 COMMIT 来保存 explain 信息。用户需要具有 EXPLAIN_FROM_ACTIVITYEXECUTE 权限和解释表的 INSERT 权限。

执行下面的步骤,通过使用 EXPLAIN_FROM_ACTIVITY 过程捕捉访问计划:

  1. 创建活动监视器。
  2. 设置 Workload Manager 环境。
  3. 启用活动监视器。
  4. 运行查询。
  5. 查询语句活动监视器表,获取 appl_id、uow_id 和 activity_id 的信息。
  6. 调用 EXPLAIN_FROM_ACTIVITY 填充解释表。
  7. 使用 db2exfmt 工具生成访问计划。

注意:

  • 在继续进行本示例之前,您必须先创建解释表。同样,您可以使用目录 ~sqllib/misc/ 中的 EXPLAIN.DDL 脚本来创建解释表。

a) 连接数据库。

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) 创建活动事件监视器。

db2 "CREATE EVENT MONITOR DB2ACTIVITIES FOR ACTIVITIES \
WRITE TO TABLE ACTIVITY (TABLE ACTIVITY_DB2ACTIVITIES IN \
USERSPACE1 PCTDEACTIVATE 100), ACTIVITYSTMT (TABLE \
ACTIVITYSTMT_DB2ACTIVITIES IN USERSPACE1 PCTDEACTIVATE 100), \
ACTIVITYVALS (TABLE ACTIVITYVALS_DB2ACTIVITIES IN USERSPACE1 \
PCTDEACTIVATE 100), CONTROL (TABLE CONTROL_DB2ACTIVITIES IN \
USERSPACE1 PCTDEACTIVATE 100) AUTOSTART"
DB20000I  The SQL command completed successfully.

c) 设置 WLM 环境。

 db2 "call wlm_set_conn_env(null,'<collectactdata>with details, section and \
 	values</collectactdata><collectsectionactuals>base</collectsectionactuals>')"

  Return Status = 0

请注意,在这个例子中,片段实绩的收集也是启用的,这个方面将在 片段实绩 小节中进一步解释。

d) 激活事件监视器。

db2 set event monitor db2activities state 1
DB20000I  The SQL command completed successfully.

e) 运行查询:

db2 "WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS ( \
	SELECT \
		D.MONTH AS MONTH, \
		S.STORE_ID AS STORE_ID, \
		S.DISTRICT AS DISTRICT, \
		S.REGION AS REGION, \
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT \
	FROM \
		SKAPOOR.SALES_FACT F1, \
		SKAPOOR.DATE_DIM D, \
		SKAPOOR.PRODUCT_DIM P, \
		SKAPOOR.STORE_DIM S \
	WHERE \
		P.MODEL LIKE '%model%' AND \
		F1.DATE_ID=D.DATE_ID AND \
		F1.PRODUCT_ID=P.PRODUCT_ID AND \
		F1.STORE_ID=S.STORE_ID AND \
		F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND \
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND \
		D.MONTH = 1 \
	GROUP BY \
		S.STORE_ID,\
		S.DISTRICT,\
		S.REGION,D.MONTH \
) , \
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS ( \
	SELECT \
		D1.MONTH AS MONTH, \
		S1.STORE_ID AS STORE_ID, \
		S1.DISTRICT AS DISTRICT, \
		S1.REGION AS REGION, \
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT \
	FROM \
		SKAPOOR.SALES_FACT F2, \
		SKAPOOR.DATE_DIM D1, \
		SKAPOOR.PRODUCT_DIM P1, \
		SKAPOOR.STORE_DIM S1 \
	WHERE \
		P1.MODEL LIKE '%model%' AND \
		F2.DATE_ID=D1.DATE_ID AND \
		F2.PRODUCT_ID=P1.PRODUCT_ID AND \
		F2.STORE_ID=S1.STORE_ID AND \
		F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND \
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND \
		D1.MONTH=11 \
	GROUP BY \
		S1.STORE_ID,\
		S1.DISTRICT,\
		S1.REGION,\
		D1.MONTH\
) \
SELECT \
	A.*, B.* \
FROM \
	TMP1 A LEFT OUTER JOIN \
	TMP2 B ON (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT) \
ORDER BY \
	A.AMOUNT_1 DESC, \
	B.AMOUNT_11 DESC"

f) 停用事件监视器。

db2 set event monitor db2activities state 0
DB20000I  The SQL command completed successfully.

g) 从 ACTIVITYSTMT_DB2ACTIVITIES 表中查询 appl_id、uow_idactivity_id

db2 "select substr(appl_id,1,30), uow_id, activity_id, substr(stmt_text,1,15) \
from ACTIVITYSTMT_DB2ACTIVITIES where stmt_text like 'WITH TMP1%'"

1                              UOW_ID      ACTIVITY_ID          4
------------------------------ ----------- -------------------- ---------------
*LOCAL.skapoor.110518172020             49                    1 WITH TMP1 (MONT

  1 record(s) selected.

h) 调用 explain_from_activity 过程填充解释表:

db2 "call explain_from_activity('*LOCAL.skapoor.110518172020', 49, 1, \
'DB2ACTIVITIES','SKAPOOR',?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-05-18-14.52.37.654737

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

i) 使用 db2exfmt 生成访问计划。在这个例子中,我们将插入前面步骤 h) 的存储过程输出所生成的 EXPLAIN_TIME— 例如:

db2exfmt -d SECTION -1 -w 2011-05-18-14.52.37.654737 -o exfmt_explain_from_activity.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_data.out.
Executing Connect Reset -- Connect Reset was Successful.

您可以进一步检查当前工作目录中的文件 exfmt_explain_from_activity.out 的输出。

EXPLAIN_FROM_DATA

EXPLAIN_FROM_DATA 存储过程会使用可执行 ID、片段和语句文本作为输入参数填充解释表。输入片段信息(可执行 ID、片段和语句文本)可获取自各种来源,例如:

  • 活动事件监视器
  • 包缓存事件监视器
  • 目录表

与其他片段解释过程类似,它会将解释输出存储到解释表中,以便使用任何现有的 EXPLAIN 工具进行处理,如 db2exfmt。可选的输入或输出参数 explain_schema 可用于指定保存 EXPLAIN 信息的解释表的模式。如果这个参数不指定值或者指定为 NULL,那么它首先会查找当前授权 ID 下的解释表,然后再在 SYSTOOLS 模式下查找。

这个片段解释过程的语法如下所示:

>>-EXPLAIN_FROM_DATA-------------------------------------------->
>--(--section--,--stmt_text--,--executable_id--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name-->
>--,--source_schema--,--source_version--)->
表 2. 参数详细信息
名称方向数据类型
section输入BLOB(134M)
stmt_text输入CLOB(2M)
executable_id输入VARCHAR(32)
explain_schema输入/输出VARCHAR(128)
explain_requester输出VARCHAR(128)
explain_time输出TIMESTAMP
source_name输出VARCHAR(128)
source_schema输出VARCHAR(128)
source_version输出VARCHAR(64)

这个存储过程的最后 5 个参数是输出参数。这些输出参数可用于在解释表中定位该片段的解释信息。请注意,这个过程在解释表的插入操作之后不会执行一条 COMMIT 命令。调用者必须在调用这个过程后执行 COMMIT 来保存解释信息。用户需要具有 EXPLAIN_FROM_DATAEXECUTE 权限和解释表的 INSERT 权限。

让我们通过一个例子介绍如何使用这个过程来生成片段解释信息。使用这个过程生成片段解释有 5 个步骤。生成 explain 数据的方法对于调试一个运行时间很长的查询是很有用的。

  1. 准备使用上面提到的三种过程之一来获取关于正在运行的 SQL 的信息。在本例中,我们将使用活动事件监视器,它将向我们提供必要的输入参数。
  2. 运行您希望为之收集片段解释的查询。
  3. 使用 WLM_CAPTURE_ACTIVITY_IN_PROGRESS 捕捉示例查询的活动信息。
  4. 使用第 3 步所收集的信息来运行 EXPLAIN_FROM_DATA 存储过程以生成解释信息。
  5. 使用 db2exfmt 工具对第 3 步生成的解释数据进行格式化。

请注意,在开始这个例子之前,您必须先创建解释表。您可以使用目录 ~sqllib/misc/ 中的 EXPLAIN.DDL 脚本来创建解释表。此外,在这个例子中您需要启动两个会话:SESSION1 和 SESSION2。

在 SESSION1 中

第 1 步— 创建活动事件监视器,为我们感兴趣的查询的存储过程收集输入参数。

a) 连接数据库。

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) 创建活动事件监视器。

db2 "CREATE EVENT MONITOR mymon FOR ACTIVITIES WRITE TO TABLE AUTOSTART"
DB20000I  The SQL command completed successfully.

c) 激活事件监视器。

db2 "SET EVENT MONITOR mymon STATE 1"
DB20000I  The SQL command completed successfully.

d) 使用下面的命令确定当前连接的应用句柄(Application Handle)。在这个输出中,第三列就是应用句柄。应用句柄将在第 3 步用来收集必要的信息,以便捕捉正在运行的活动。

db2 "LIST APPLICATIONS"

Auth Id  Application    Appl.      Application Id                DB       # of
         Name           Handle                                   Name    Agents
-------- -------------- ---------- ----------------------------- -------- -----
SKAPOOR  db2bp          58         *LOCAL.skapoor.110510181205   SECTION  1

在上面的输出中,应用句柄是 58。

第 2 步— 运行 Query1.sql 的主查询。这个运行时间较长的查询能够保证您及时完成第 3 步。

在 SESSION2 中

第 3 步— 当查询运行时,我们要使用 WLM_CAPTURE_ACTIVITY_IN_PROGRESS 过程来捕捉查询的活动信息。

a) 连接数据库。

db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) 通过替换第 1 步中找到的应用句柄,使用下面的查询来查找 UOW ID 和 Activity ID:

db2 "select APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID from  
TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(NULL, -2)) as 
WLOACTS where APPLICATION_HANDLE=58" 

APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID
-------------------- ----------- -----------
                  58           3           1

  1 record(s) selected.

c) 在下面的查询中替换之前的步骤中找到的信息,从而捕捉主查询的活动:

db2 "CALL WLM_CAPTURE_ACTIVITY_IN_PROGRESS(58,3,1)"   

Return Status = 0

第 4 步— 创建一个包含以下内容的 exp_data_sp.sql 文件。请相应地替换 UOW_ID、ACTIVITY_IDEXPLAIN_SCHEMA。这个 SQL 文件将为示例查询使用 EXPLAIN_FROM_DATA,通过活动事件监视器记录的片段信息生成解释数据。

SET SERVEROUTPUT ON;

BEGIN
  DECLARE EXECUTABLE_ID VARCHAR(32) FOR BIT DATA; --
  DECLARE SECTION BLOB(134M); --
  DECLARE STMT_TEXT CLOB(2M); --
  DECLARE EXPLAIN_SCHEMA VARCHAR(128); --

  DECLARE EXPLAIN_REQUESTER VARCHAR(128); --
  DECLARE EXPLAIN_TIME TIMESTAMP; --
  DECLARE SOURCE_NAME VARCHAR(128); --
  DECLARE SOURCE_SCHEMA VARCHAR(128); --
  DECLARE SOURCE_VERSION VARCHAR(128); --

  SET EXPLAIN_SCHEMA = 'SKAPOOR'; --

  SELECT A.SECTION_ENV, A.STMT_TEXT, A.EXECUTABLE_ID INTO
         SECTION, STMT_TEXT, EXECUTABLE_ID
  FROM ACTIVITYSTMT_MYMON A
  WHERE UOW_ID=3 and ACTIVITY_ID=1 and substr(stmt_text,1,80) like ('WITH TMP1%'); --

  CALL EXPLAIN_FROM_DATA( SECTION,
                          STMT_TEXT,
                          EXECUTABLE_ID,
                          EXPLAIN_SCHEMA,
                          EXPLAIN_REQUESTER,
                          EXPLAIN_TIME,
                          SOURCE_NAME,
                          SOURCE_SCHEMA,
                          SOURCE_VERSION ); --

  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_REQUESTER = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_REQUESTER ); --
  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_TIME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_TIME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_NAME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_NAME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_SCHEMA = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_SCHEMA ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_VERSION = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_VERSION ); --
END;

a) 按以下方式运行这个 SQL 文件:

db2 -tvf exp_data_sp.sql

DB20000I  The SQL command completed successfully.

EXPLAIN_REQUESTER = SKAPOOR
EXPLAIN_TIME = 2011-05-10-14.28.34.119918
SOURCE_NAME = SQLC2H22
SOURCE_SCHEMA = NULLID
SOURCE_VERSION =

第 5 步— 使用 db2exfmt 工具为第 4 步中使用 EXPLAIN_TIME 生成的解释数据生成解释格式化数据。

db2exfmt -d SECTION -1 -w 2011-05-10-14.28.34.119918 -o exfmt_explain_from_data.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_data.out.
Executing Connect Reset -- Connect Reset was Successful.

解释输出会保存在工作目录的 exfmt_explain_from_data.out 文件中。这个文件包含了关于这个示例查询的访问计划和其他详细信息。

EXPLAIN_FROM_CATALOG

EXPLAIN_FROM_CATALOG 过程将使用目录表中的片段内容生成有关一条语句的解释信息。这些解释信息会存储在解释表中,它可以由解释格式化实用工具进一步加以处理,如 db2exfmt。可选的输入或输出参数 explain_schema 可用于指定保存解释信息的解释表的模式。如果这个参数没有指定值或者指定为 NULL,那么它首先会查找当前授权 ID 下的解释表,然后再在 SYSTOOLS 模式下查找。

EXPLAIN_FROM_CATALOG 过程的语法如下所示:

>>-EXPLAIN_FROM_CATALOG----------------------------------------->
>--(--pkgschema--,--pkgname--,--pkgversion--,--sectno--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name-->
>--,--source_schema--,--source_version--)->
表 3. 参数详细信息
名称方向数据类型
pkgschema输入VARCHAR(128)
pkgname输入VARCHAR(128)
pkgversion输入VARCHAR(64)
sectno输入SMALLINT
explain_schema输入/输出VARCHAR(128)
explain_requester输出VARCHAR(128)
explain_time输出TIMESTAMP
source_name输出VARCHAR(128)
source_schema输出VARCHAR(128)
source_version输出VARCHAR(64)

这个存储过程的最后 5 个参数是输出参数。这些输出参数可用于定位解释表中该片段的解释信息。请注意,这个过程在解释表的插入操作之后不会执行一条 COMMIT 命令。调用者必须在调用这个过程后执行 COMMIT 来保存解释信息。用户需要具有 EXPLAIN_FROM_DATAEXECUTE 权限和解释表的 INSERT 权限。

让我们通过一个例子介绍如何使用这个过程来生成片段解释信息。使用这个过程生成片段解释有 5 个步骤。这种生成 explain 数据的方法对于调试一个将包存储在目录表中的静态查询是很有用的。

  1. 创建一个 SQC 文件来执行主查询。
  2. 准备和绑定第 1 步得到的 SQC 文件。
  3. 从目录表中查找主查询的包和片段信息。
  4. 使用第 3 步查找到的包和片段信息,调用 EXPLAIN_FROM_CATALOG 过程为主查询生成解释信息。
  5. 使用 db2exfmt 工具对第 4 步所生成的解释数据进行格式化。

注意: 在继续本例的后续步骤之前,您必须先创建解释表。您可以使用目录 ~sqllib/misc/ 中的 EXPLAIN.DDL 脚本来创建解释表。

第 1 步— 创建一个包含以下内容的 SQC 文件,并将它保存为 expcat.sqc。

struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
        short month;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c2 CURSOR FOR WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS ( 
	SELECT 
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID, 
		S.DISTRICT AS DISTRICT, 
		S.REGION AS REGION, 
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT 
	FROM 
		SKAPOOR.SALES_FACT F1, 
		SKAPOOR.DATE_DIM D, 
		SKAPOOR.PRODUCT_DIM P, 
		SKAPOOR.STORE_DIM S 
	WHERE 
		P.MODEL LIKE '%model%' AND 
		F1.DATE_ID=D.DATE_ID AND 
		F1.PRODUCT_ID=P.PRODUCT_ID AND 
		F1.STORE_ID=S.STORE_ID AND 
		F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND 
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND 
		D.MONTH = 1 
	GROUP BY 
		S.STORE_ID,
		S.DISTRICT,
		S.REGION,D.MONTH 
) , 
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS ( 
	SELECT 
		D1.MONTH AS MONTH, 
		S1.STORE_ID AS STORE_ID, 
		S1.DISTRICT AS DISTRICT, 
		S1.REGION AS REGION, 
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT 
	FROM 
		SKAPOOR.SALES_FACT F2, 
		SKAPOOR.DATE_DIM D1, 
		SKAPOOR.PRODUCT_DIM P1, 
		SKAPOOR.STORE_DIM S1 
	WHERE 
		P1.MODEL LIKE '%model%' AND 
		F2.DATE_ID=D1.DATE_ID AND 
		F2.PRODUCT_ID=P1.PRODUCT_ID AND 
		F2.STORE_ID=S1.STORE_ID AND 
		F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND 
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND 
		D1.MONTH=11 
	GROUP BY 
		S1.STORE_ID,
		S1.DISTRICT,
		S1.REGION,
		D1.MONTH
) 
SELECT 
	A.*, B.* 
FROM 
	TMP1 A LEFT OUTER JOIN 
	TMP2 B ON (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT) 
ORDER BY 
	A.AMOUNT_1 DESC, 
	B.AMOUNT_11 DESC;
EXEC SQL OPEN c2;
EXEC SQL FETCH c2 INTO :month;


while (sqlca.sqlcode !=100)
{
        EXEC SQL FETCH c2 INTO :month;
}
EXEC SQL CLOSE c2;

第 2 步— 准备和绑定 expcat.sqc 文件,它会为主查询创建包。

a) 连接数据库。

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) 准备 expcat.sqc 文件。这会在工作目录中创建一个绑定文件 expcat.bnd。

 db2 prep expcat.sqc bindfile

LINE    MESSAGES FOR expcat.sqc
------  -------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "0" warnings.

c) 绑定前一步生成的 expcat.bnd 文件。

db2 bind expcat.bnd explain all

LINE    MESSAGES FOR expcat.bnd
------  ------------------------------------------------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.

第 3 步— 查找 EXPLAIN_FROM_CATALOG 过程所需要的包和片段输入信息。

db2 "SELECT pkgschema, pkgname, version, Sectno FROM SYSCAT.STATEMENTS \
WHERE TEXT like ('%WITH TMP1 (MONTH_1%')"

PKGSCHEMA   PKGNAME    VERSION  SECTNO 
----------- ---------- -------- ------ 
SKAPOOR     EXPCAT                   1 
                                       
  1 record(s) selected.

第 4 步— 使用第 3 步得到的包和片段信息,调用 EXPLAIN_FROM_CATALOG 过程生成主查询的解释信息。

db2 "call explain_from_catalog('SKAPOOR', 'EXPCAT','', 1, 'SKAPOOR', ?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-05-24-20.12.45.788476

  Parameter Name  : SOURCE_NAME
  Parameter Value : EXPCAT

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

第 5 步— 使用 db2exfmt 工具为第 4 步使用 EXPLAIN_TIME 生成的解释数据生成解释格式化数据。

db2exfmt -d SECTION -1 -w 2011-05-24-20.12.45.788476 -o exfmt_explain_from_catalog.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_catalog.out.
Executing Connect Reset -- Connect Reset was Successful.

解释输出会保存在工作目录下的 exfmt_explain_from_catalog.out 文件中。


片段实绩

从 V9.7 Fix Pack 1 开始,我们就可以在一个片段执行时为访问计划操作者收集运行时统计信息。这些统计信息称为片段实绩。通过这种方法,我们就能够为访问操作者收集实际基数。

解释实用工具输出能够很方便地显示实际基数(片段实绩)和估计的基数计划值,供您进行比较。这种比较的结果可能会指向一些过时的统计信息 — 例如,优化器用来选择一个错误访问计划的统计信息。我们可以使用 RUNSTATS 命令来进一步更新这些统计信息,然后使用得到的最新访问计划来重新尝试这个应用。只有在片段解释已执行并已使用活动事件监视器捕捉到一个片段时,才能获得片段实绩。

下面是收集片段实绩的步骤:

  1. 启用片段实绩
  2. 为活动创建工作负载和事件监视器
  3. 为关注的语句收集片段实绩
  4. 确定数据的应用、UOW 和 活动 ID
  5. 将数据解释到解释表中
  6. 运行 db2exfmt 生成访问计划
  7. 查看输出

下面我们开始查看一个例子。

1. 使用下面的命令启用片段实绩:

db2 update db cfg for SECTION using SECTION_ACTUALS BASE

注意: 您还可以使用片段解释小节中介绍的 WLM_SET_CONN_ENV 存储过程 — 例如:

 db2 "call wlm_set_conn_env(null,'>collectactdata<with details, section and \
 		 values>/collectactdata<>collectsectionactuals<\
         base>/collectsectionactuals<')"

2. 创建工作负载和事件监视器

connect to SECTION;
create workload MYWORKLOAD current client_acctng('MYWORKLOAD') 
   service class sysdefaultuserclass collect activity data on all  
   database partitions with details, section;

下面我们创建一个工作负载来收集活动信息,并指定关键字 SECTION 来收集片段实绩信息。

create event monitor MYMON for activities write to table ;

这条语句会创建一个 ACTIVITYSTMT_MYMON 表,并获取语句信息。

3. 收集所关注语句的片段实绩

call wlm_set_client_info(null, null, null, 'MYWORKLOAD', null); 
set event monitor MYMON state 1; 
-- Execute the main query used in this article.
set event monitor MYMON state 0; 
call wlm_set_client_info(null, null, null, null, null);

CURRENT CLIENT_ACCTNG(或 CLIENT ACCTNG)特殊寄存器包含了为这个连接指定的客户端信息的帐号字符串。寄存器的数据类型为 VARCHAR(255)。这个寄存器的默认值是一个空字符串。

WLM_SET_CLIENT_INFO 过程会设置与 DB2® 服务器当前连接相关的客户端信息。下面我们会使用这个过程来设置客户端的工作负载信息。

4. 定位数据的应用、UOW 和活动 ID。

由于片段实绩使用 explain_from_activity 存储过程来填充解释表,所以我们需要确定 appl_id、uow_id、 activity_id。这可以通过记录在 activitystmt_mymon 事件监视器表中的活动信息收集。

SELECT appl_id, uow_id, activity_id, substr(stmt_text,1,80) as stmt FROM
ACTIVITYSTMT_MYMON where substr(stmt_text,1,50) like ('WITH TMP1%')

APPL_ID                     UOW_ID  ACTIVITY_ID  STMT
--------------------------  ------- -----------  ----------- 
*LOCAL.skapoor.110412133000 7    1    WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1

  1 record(s) selected.

5. 调用 EXPLAIN_FROM_ACTIVITY 填充解释表:

call explain_from_activity ('*LOCAL.skapoor.110412133000 ',7,1,'MYMON',
'SKAPOOR',?,?,?,?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-04-12-09.34.44.082039

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

6. 运行 db2exfmt 生成访问计划(参见 下载)。

db2exfmt –d section -1 –o exfmt_section_actuals.out

Screen Output:
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in eexfmt_section_actuals.out.
Executing Connect Reset -- Connect Reset was Successful.

7. 查看 exfmt_section_actuals.out 文件中的输出信息。

Explain level:    Explain from section


Snippet of the output:


      Rows 
  Rows Actual
     RETURN
     (   1)
      Cost 
      I/O 
       |
      10    <- Estimated cardinality
      10    <- Actual cardinality
    TBSCAN
    (   2)
  1.55994e+06 
      NA 
      |
      10 
      10 
     SORT  
   (   3)
  1.55994e+06 
      NA 
      |
      10 
      10 
    HSJOIN<
    (   4)
  1.55994e+06 
      NA 
..

db2support 命令的改进

除了片段解释特性,V9.7 还引入了一些相关的 db2support 改进。大多数这类改进都是与收集活动事件监视数据相关的。这些选项的语法如下所示。请参考 DB2 在线信息中心的内容全面了解使用这些选项的 db2support 命令。

>--+------------------------------------------------------------------------------+-->
  -| event-monitor-options |----------------------------------------------------+  
   '- -aem--+---------------------------------+--+------------------------------+-'  
            '- -compenv--compilation-env-file-'  '- -tbspname--table-space-name-'

event-monitor-options

|-- -actevm--event-monitor-name-- -appid--application-id-------->

>-- -uowid--uow-id-- -actid--activity-id------------------------

下面这两个例子介绍了我们可以如何使用新的 db2support 选项来收集活动事件监视信息。请注意,本文中使用的数据库名称是 SECTION。

1. db2support –d section –sf badquery.sql –aem

将您希望用于收集活动事件监视信息的查询保存到一个名称为 badquery.sql 的文件中。除了收集第 2 级(-cl 2)的优化器信息之外,这个命令还会调用 db2caem。这个调用会创建一个活动事件监视器,并收集详细的活动信息,包括在 badquery.sql 中指定的片段解释和片段实绩。

2. db2support -d section -actevm mymon –appid *LOCAL.skapoor.110510181205 -uowid 3 –actid 1

在这个例子中,已经有一个名为 mymon 的活动事件监视器。这条命令将使用这个已有的事件监视器来收集所需要的活动监视数据。这条命令不会创建新的活动监视器。而是会收集所有相关的活动信息,包括应用 ID、UOW ID 和活动 ID 的特定组合的片段解释和片段实绩。

这些命令都会在最后得到的 db2support.zip 文件的 OPTIMIZER 目录中创建一个 DB2CAEM 目录。在 DB2CAEM 文件夹中,您将会看到另一个名称为 DB2CAEM_<timestamp_of_collection> 的文件夹,其中 <timestamp_of_collection>db2support 执行 db2caem 的时间。在我们的例子中,这个目录名称为 DB2CAEM_2011-05-10-17.45.04.967082。在这个文件夹下,您将会看到以下内容:

  • EXPORTS — 这个目录包含从 db2support 命令使用的活动事件监视器表中导出的数据。
  • db2caem.exfmt — 这是一个使用 db2exfmt 工具收集的片段解释格式。这个解释信息是通过 db2support 命令使用的活动事件监视器生成的。
  • db2caem_options.in — 这个文件包含了通过 db2support 命令调用的 db2caem 命令时,所使用的准确命令和选项。

活动事件监视器所引入的 db2support 命令改进提供了一种更简单的方法来为查询收集片段解释信息。这其中包括片段数据、值、实绩等,这对于研究一个查询的性能问题是至关重要的。


结束语

在研究查询执行计划的问题时,片段解释和片段实绩是非常强大的特性。利用这些特性,即可显著缩短处理一个性能较差的查询时的分析时间。


下载

描述名字大小
格式示例exfmt_section_examples.zip15KB

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=751573
ArticleTitle=DB2 for Linux, UNIX, and Windows 查询性能故障诊断
publish-date=08092011