发挥 DB2 优化器的最大功效

分析存取计划以改进查询执行性能

IBM® DB2® SQL 优化器所选择的存取计划可能会对 SQL 语句的执行性能产生重大影响。在本文中,您将通过 DB2 用户遇到的基于实际场景的示例,了解如何分析存取计划、优化查询,来提高查询执行性能。

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

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



Vincent Corvinelli, DB2 优化器开发人员, IBM

Vincent Corvinelli 是 IBM Toronto Lab 的 DB2 Query Optimizer Development 团队的一名顾问软件开发人员。



2011 年 1 月 17 日

简介

本文将通过分析 DB2 SQL Optimizer(后面也称为优化器)所生成的存取计划来介绍一些改进 SQL 语句执行性能的方法。它包括了 DB2 支持和开发所使用的场景,它们会说明问题是如何诊断和解决的,包括如何使用 DB2 Version 9.7 的 RUNSTATS 工具、REOPT 绑定选项和 EXPLAIN 选项来提高性能,通过改进优化器的基数估算。

场景 1: 使用 RUNSTATS 更新过期的统计资源

在这个场景中,相对于开发系统,生产系统上的查询性能非常差。为了提高查询性能,您需要分析存取计划并运行 RUNSTATS 来对所有过时的统计资料进行更新。

清单 1. 原始语句
-- Note: VW is a view
select *
from  
  db2inst1.vw v, 
  db2inst1.t1 t1
where 
  v.x = t1.x
清单 2. 优化后的语句Optimized statement
SELECT ...
FROM 
  DB2INST1.T4 AS Q1, 
  DB2INST1.T3 AS Q2, 
  DB2INST1.T2 AS Q3, 
  DB2INST1.T1 AS Q4
WHERE 
  (Q1.T2ID = Q2.T2ID) AND 
  (Q3.T4ID = Q1.T4ID) AND
  (Q1.Y = 'Y') AND 
  (Q1.Z = 'N') AND
  (Q3.X = Q4.X) AND 
  (Q3.Y IS NULL )
清单 3. 查询存取计划
                                              9.906 
                                              NLJOIN 
                                              (   2) 
                                              11476.2 
                                               2892 
                                  /--------------+--------------\
                             247.66                             0.04 
                             NLJOIN                             FETCH  
                             (   3)                             (   8) 
                             11333.4                            100.125 
                              2888                              4.00412 
                      /---------+--------\                     /---+--\
                 0.0033                   74416              1      712084 
                 NLJOIN                  TBSCAN           IXSCAN  TABLE: DB2INST1      
                 (   4)                  (   7)           (   9)        T2 
                0.0199984                11333.4          75.0188 
                   52                     2888               3 
            /-------+------\               |                |
       0.0208              0.16          74416           712084 
       IXSCAN              IXSCAN    TABLE: DB2INST1   INDEX: DB2INST1     
       (   5)              (   6)         T1                IDX_1 
      0.0104877          0.00951078 
          0                 1
         |                  |
         13                100 
   INDEX: DB2INST1     INDEX: DB2INST1 
        IDX_T4              IDX_T3

分析这个存取计划的第一步是查看优化器所得到的记录估算数值。如果估算值误差很大,那么基数估算很可能成为选择非最优存取计划的一个主要因素。小于一个记录的估算值一般是没有价值的,除非操作符是嵌套循环联合的内部(右)输入(称为 NLJOIN)。内部输入基数是一个平均外部估算,而小于一个记录的估算值情况是很普遍的。而且,在分析存取计划时,您应该采取从下到上的方法。

清单 3 所示的存取计划显示了一个少于一条记录的基数估算,即操作符 IXSCAN (5) 的估算值为 0.0208。这个操作符并不在 NLJOIN 的内部输入中,所以这是进一步观察的一个很好的候选值。

清单 4. 应用到 IXSCAN(5) 的谓词
  Predicates:
  ----------
  4) Start Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            0.04
     Predicate Text:
     --------------
     (Q1.Y = 'Y')
     
  4) Stop Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            0.04
     Predicate Text:
     --------------
     (Q1.Y = 'Y')
     
  5) Start Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            0.04
     Predicate Text:
     --------------
     (Q1.Z = 'N')
     
  5) Stop Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            0.04
     Predicate Text:
     --------------
     (Q1.Z = 'N')

当进一步检查 清单 4 中应用到 IXSCAN (5) 的谓词细节时,您会发现显示为 P1P2 的两个谓词,以及过滤因数 FF

  • P1: Q1.Y = 'Y' FF=0.04
  • P2: Q1.Z = 'N' FF=0.04

P1P2 的过滤因数都是 0.04,所以假定它们是独立的,那么 IXSCAN(5) 的基数估算是按照以下方式计算的。

CARD_AT_IXSCAN(5) = FF(P1) * FF(P2) * INPUT_CARD

其中 INPUT_CARD13,它表示进入 IXSCAN(5) 的记录数。结果,基数是按照以下方式估算出来的:

CARD_AT_IXSCAN(5) = 0.04 * 0.04 * 13 
                  = 0.0208

当计算这些谓词的过滤因数时,优化器可以利用有效的字段分布统计来解释不一致的分布式数据。或者,如果数据是一致分布的,那么优化器就可以使用这个字段基数(后面也称为 colcard)。最坏的情况是,如果没有统计信息,那么优化器会根据表的大小来编造统计信息。然后它会在优化器所统计的每一个表的 db2exfmt 输出的 Extended Diagnostic Information 部分添加警告信息。

清单 5. 扩展的诊断信息
Diagnostic Identifier:  1
Diagnostic Details:     EXP0045W. The table named "DB2INST1.T4" has fabricated
                        statistics.  This can lead to poor cardinality and predicate
                        filtering estimates.  The size of the table changed significantly
                        since the last time the RUNSTATS command was run.

清单 5 中的信息显示,从统计过期开始,优化器就编造统计信息。如果统计信息显示的记录数小于 UpdateDeleteInsert (UDI) 计数,那么所编造的统计信息会保存到内部打包的描述符中。如果新编造的统计信息显示的记录数大于 UDI 记数,那么这个编造的统计信息将会一直保存到它被 RUNSTATS 重置。所编造的统计信息的变化可能导致选择的查询执行计划发生变化。

清单 6 显示有两个 count(*) 查询被提交,用于进一步确认谓词之前和之后的真实基数估算。

清单 6. 用于确认实际计算的 COUNT(*) 查询
SELECT COUNT(*) FROM "DB2INST1 "."T4";

RESULT:  13 ROWS

SELECT COUNT(*) FROM "DB2INST1 "."T4" AS Q1 
WHERE 
  (Q1.Y = 'Y') AND
  (Q1.Z = 'N');

RESULT:  13 ROWS

count(*) 查询显示实际的基数在谓词之前和之后都是 13。与估算的基数 0.0208 相比较,估算误差是 13/0.0208 = 625 的一个因子。这是非常重要的,因为优化器在 NLJOIN (4) 估算的值小于 1 行,因此预计仅试探 NLJOIN 内部一次即可。然而,上面收集的计数是不正确的,预计联合起来能至少达到 625 * CARD_EST_NLJOIN(4) =~ 2 行。这样,您便可以预期试探 NLJOIN (3) 内部两次,如清单 3 所示。NLJOIN (3) 内部是对 TBSCAN (7) 进行一次完整的表扫描,虽然必须试探超过一次的存取方式的执行可能并不是最优的。

其他方法

从 Version 9.5 开始,您可以启用自动语句统计来避免出现过时的统计资料,所以手动收集统计信息可能并不总是一个好的方法。

解决方案:您应该在表 DB2INST1.T4 上执行 RUNSTATS 收集最新的统计信息。清单 7 显示的是基于更新的统计所选择的存取计划,以及根据您要求执行的查询。

清单 7. RUNSTATS 执行后的存取计划
                                     6191.41 
                                     HSJOIN 
                                     (   2) 
                                     11948.8 
                                      2892 
                   /--------------------+-------------------\
               74416                                         2.08 
              NLJOIN                                        HSJOIN 
              (   3)                                        (   7) 
              11945.4                                      0.0277668 
               2892                                            0 
          /------+------\                              /-------+------\
      74416                1                        13                   4 
     TBSCAN             FETCH                     IXSCAN              IXSCAN 
     (   4)             (   5)                    (   8)              (   9) 
     11333.4            100.125                  0.0156447           0.0112472 
      2888              4.00412                      0                   0 
       |               /---+---\                    |                   |
      74416          1         712084               13                   4 
 TABLE: DB2INST1   IXSCAN   TABLE: DB2INST1     INDEX: DB2INST1   INDEX: DB2INST1 
      T1           (   6)        T2                  IDX_T4            IDX_T3
                  75.0188 
                     3 
                    |
                  712084 
              INDEX: DB2INST1      
                   IDX_1

这两个谓词的过滤因子在更新的统计资料上被估算为 1

清单 8. RUNSTATS 执行后的两个谓词的谓词过滤因子
  Predicates:
  ----------
  3) Start Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            1
     Predicate Text:
     --------------
     (Q1.Z = 'N')
     
  3) Stop Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            1
     Predicate Text:
     --------------
     (Q1.Z = 'N')
     
  4) Start Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            1
     Predicate Text:
     --------------
     (Q1.Y = 'Y')
     
  4) Stop Key Predicate
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            1
     Predicate Text:
     --------------
     (Q1.Y = 'Y')

使用修改后的过滤因子,优化器将 IXSCAN(8) 的基数估算为

  FF(P1) * FF(P2) * INPUT_CARD = 1 * 1 * 13 = 13

场景 2:收集分布式统计以改进查询执行性能

在这个场景中,优化器使用一个未优化索引存取计算查询存取计划。

清单 9. 原始语句
  select  ... 
  from db2inst1.name n1
  where 
       ...
       n1.lastname like ‘BAKER%' and 
       n1.firstname like ‘LENNY%'
清单 10. 优化后的语句
Optimized Statement:
-------------------
SELECT ...
FROM DB2INST1.NAME AS Q1
WHERE ... AND (Q1.LASTNAME LIKE ‘LENNY%') 
          AND (Q1.FIRSTNAME LIKE ‘BAKER%')
清单 11. 查询计划的片断
           0.40474
           FETCH
           (   3)
            37.86
           126.234
          /---+---\
     139.289    9.3475e+08
     RIDSCN   TABLE: DB2INST1
     (   4)       NAME
     22.7192        Q1
        4
       |
     139.289
     SORT
     (   5)
     22.7187
        4
       |
     139.289
     IXSCAN
     (   6)
     22.6784
        4
       |
   9.3475e+08
 INDEX: DB2INST1
    INDX3
       Q1

FIRSTNAME "LENNY"DB2INST1.NAME 中出现了 1.14 百万次。但是,对于 清单 9 中的查询,优化器会选择一个 DB2INST1.INDX3 索引扫描存取,它带有只包含 FIRSTNAME 域的键。清单 11 显示了优化器是如何对这个索引存取计算得到一个非常低的开销估算值 IXSCAN(6)。表中还定义了另一个名为 DB2INST1.INDX1 的索引,它具有一个同时包含 LASTNAMEFIRSTNAME 的键。

清单 12. 统计明细
NUM_FREQVALUES  100
NUM_QUANTILES    50

RUNSTATS ON TABLE DB2INST1.NAME WITH DISTRIBUTION AND DETAILED INDEXES ALL

Table Cardinality:

  UPDATE SYSSTAT.TABLES
  SET CARD=934750120,
    NPAGES=17905266,
    FPAGES=17905266,
    OVERFLOW=0,
    ACTIVE_BLOCKS=0
  WHERE TABNAME = 'NAME' AND TABSCHEMA = 'DB2INST1    ';

Column Cardinalities:

  COLNAME     COLCARD
  ----------  -------
  LASTNAME    6856495
  FIRSTNAME   3829720

Indexes and their associated statistics:

  INDEX  KEY         NLEAF   NLEVELS SEQ PAGES CLUSTER FACTOR
  ------ ----------- ------- ------- --------- --------------
  INDX1  LASTNAME    3829525 5       3828184   0.084789 
         FIRSTNAME
         NAME_MIDDLE
         NUMKEY

  INDX2  LASTNAME    4790072 5       4788313   0.089595
         SIGNIN

  INDX3  FIRSTNAME   2596262 5       2595551   0.075572
         BIRTH

清单 12 的索引统计显示在字段 CLUSTER FACTOR 的所有索引上表聚集的性能都是非常差的。如果索引扫描级别上的数据流没有明显减少,那么随机 I/O 可能会导致大量数据页的产生,这会严重影响性能。

第一步是要确定这个估算值的准确性,因为 清单 11IXSCAN (6) 操作符的输出基数相对于 934 行输入基数来说是非常小的。

清单 13. 应用于 IXSCAN (6) 的谓词明细
Predicates:
----------
6) Stop Key Predicate
        Comparison Operator:            Less Than or Equal (<=)
        Subquery Input Required:        No
        Filter Factor:                  0.59615
        Predicate Text:
        --------------
        (Q1.FIRSTNAME <= 'LENNY........................')
7) Start Key Predicate
        Comparison Operator:            Less Than or Equal (<=)
        Subquery Input Required:        No
        Filter Factor:                  0.40385
        Predicate Text:
        --------------
        ('LENNY........................' <= Q1.FIRSTNAME)

Input Streams:
-------------
        1) From Object DB2INST1.INDX3
                Estimated number of rows:       9.3475e+08
                Number of columns:              2
                Subquery predicate ID:          Not Applicable
                Column Names:
                ------------
                +Q1.FIRSTNAME(A)+Q1.$RID$

Output Streams:
--------------
        2) To Operator #5
                Estimated number of rows:       139.289
                Number of columns:              1
                Subquery predicate ID:          Not Applicable
                Column Names:
                ------------
                +Q1.FIRSTNAME(A)

查询优化的重写阶段会为语句中每一个 LIKE 谓词生成相同的范围谓词,并通过将生成的范围谓词作为 start/stop 键应用到索引扫描中,从而提高查询性能。这个范围谓词将用于搜索一定范围的值,从以 LENNY 为前缀的最小字符串,到以 LENNY 为前缀的最大字符串。LIKE 谓词的过滤因子如下所示:

  • P4: n1.lastname like 'LENNY%' FF=1.49012e-07
  • P5: n1.firstname like 'BAKER%' FF=0.00290581

当在 RUNSTATS 命令中使用 WITH DISTRIBUTION 子句时,在字段上收集的分位数统计给优化器提供了准确估算范围谓词的过滤因子所需要的信息。对于这种情况,表 1 显示了对 LASTNAMEFIRSTNAME 感兴趣的的谓词数量。

表 1. 谓词字段数量
字段序列号值计数
LASTNAME1'ACKLAND'1
2'BAKER'20656377
3'BARKLEY'38993587
FIRSTNAME29'LARRY'538016212
30'LIN'557252038

清单 14 的图表很好地说明了范围谓词过滤因子估算值的计算过程。

清单 14. 范围谓词的 FF 计算
 |                              | 'LENNY.....' <= Q1.FIRSTNAME = FF2               | 
 |                              |------------------------------------------------->|
 |                              | FF(RANGE)         |                              |
 |                              |  = FF1 + FF2 – 1  |                              |
 |<-------------------------------------------------|                              |
 | Q1.FIRSTNAME <= 'LENNY.....' = FF1               |                              |
 |<------------------------------------------------------------------------------->|
 |                           FULL RANGE OF VALUES IN FIRSTNAME                     |

当分位统计完成时,LIKE 谓词的过滤因子的计算是通过单独为每一个范围(FF1,FF2)估算过滤因子计算得来的,并且将两个谓词之间的差别确定为:

  FF(RANGE) = (FF1 + FF2) - 1.

结果,FIRSTNAME LIKE 'LENNY%' 谓词的过滤因子被计算为:

  FF1 = FF('LENNY......' <= Q1.FIRSTNAME) = 0.40385
  FF2 = FF(Q1.FIRSTNMAE <= 'LENNY......') = 0.59615

  FF(RANGE) = FF1 + FF2 – 1 = 0.59615 + 0.40385 – 1 = 0

这表示所估算的过滤因子为 0,它是使用分位统计的线性插值计算得来的,但是优化器将频率值统计作为一个下边界对这个过滤因子进行进一步界定。

采取与 场景 1 相同的过程,基数估算就可以使用 count(*) 进行验证。

清单 15. 用于确认实际计数的 COUNT(*) 查询
select count(*) 
  from db2inst1.name n8 
 where n1.lastname like 'BAKER%'
 
  RESULT: 2739268 

select count(*) 
  from db2inst1.name n8 
 where n1.firstname like 'LENNY%' 

  RESULT: 1161991 

select count(*) 
  from db2inst1.name n1 
 where n1.firstname like 'LENNY%' AND 
       n1.lastname like 'BAKER%'

  RESULT: 3853

这些计算显示为 LASTNAME 估算的过滤因子是正确的,但是 FIRSTNAME 的则是估算不足的。从 清单 13 您可以发现:

  FF(n1.lastname like 'BAKER%') = 0.00290581

因此,从下面可以看到,在应用这个谓词之后所估算的基数是非常准确的。

  INPUT_CARD * FF(n1.lastname like 'BAKER%') = 934750120 * 0.00290581 = 2,716,206.25

对于 FIRSTNAME,优化器只估算了 139.289 行,但是实际的计数是 1,161,991。这个估算误差是很明显的。

为什么优化器会低估这个基数?对于范围谓词,优化器考虑使用分位统计方法来估算过滤因子。从 表 1 所列的统计数字上看,在 29 和 30 之间位置上包含了 557,252,038 – 538,016,212 = 19,235,826 行值位于 LARRYLIN 之间的记录。下面显示的频率值是这个范围内的 2.29 百万行记录之一。

UPDATE SYSSTAT.COLDIST
SET COLVALUE='LESTER',
    VALCOUNT=2295040
WHERE COLNAME = 'FIRSTNAME' AND TABNAME = 'NAME'
      AND TABSCHEMA = 'DB2INST1    '
      AND TYPE      = 'F'  AND SEQNO     =  58;

这个值只占整个范围的一小部分,而其中还有 1700 万其他未包含在内的记录。清单 16 包含了表示这些未知值的图形。

清单 16. FIRSTNAME 的分位组
  LARRY                          LESTER                           LIN
    |<-------UNKNOWN------->|<----2.29M---->|<------UNKNOWN------>|
... +--------------+--------+---------------+---------------------+ ...
    |              |        |                                     |
  538016212          LENNY?                                 557252038
  (SEQNO 29)                                                (SEQNO 30)

解决方案:由于 LARRYLIN 之间的不同值可能有非常多,如果您能够减少数量范围,那么就可以减小过滤因子估算的误差。一开始,您只收集到 50 个值,所以如果您收集更多的值,那么优化器估算值的误差应该可以减小,并且应该可以使您获得一个优化的存取计划。

清单 17. 增加数据之后的存取计划片断
     511.05 
     IXSCAN
     (   3)
     11111.8
     11242.9
       |
   9.3475e+08
 INDEX: DB2INST1
    INDX1
       Q1

将在 FIRSTNAME 收集的数量增加到 200 可以显著减小基数估算的误差,从而优化器可以选择一个能实现最优执行性能的索引存取计划。


场景 3:使用 REOPT 改进查询执行性能

在这种情况中,您需要在应用程序中运行一个静态查询,它需要 11 分钟才能完成。但是,如果查询是从命令行(CLP)提交的,那么它会在 10 秒钟内完成。为什么这两种方法执行相同的查询会产生如此大的差别呢?

清单 18. 从静态包中使用主机变量进行查询
  SELECT ...
  FROM 
    DB2INST1.T1 A, 
    DB2INST1.T2 B, 
    DB2INST1.T3 C, 
  WHERE 
    A.T1ID = B.T2ID    and
    B.T2ID = C.T2ID  and 
    B.X = :HV00001  :HI00001   and 
    C.Y <> ‘10’    and 
    A.X in ('00')        and 
    A.Y in ('000','001','005','006') and    
    A.Z between :HV00002  :HI00002 and :HV00003 :HI00003
清单 19. 从 CLP 中使用线性值进行查询
  SELECT ...
  FROM 
    DB2INST1.T1 A, 
    DB2INST1.T2 B, 
    DB2INST1.T3 C, 
  WHERE 
    A.T1ID = B.T1ID    and
    B.T2ID = C.T2ID  and 
    B.X = 'CAD'  and 
    C.Y <> ‘10’    and 
    A.X in ('00')        and 
    A.Y in ('000','001','005','006') and    
    A.Z between ‘2007-10-01' and '2007-10-30'

当优化器编译一个在 WHERE 子句中包含参数标记、特殊标记或主机变量等谓词的查询时,它并不知道所计算的过滤因子的实际值。对于等价的谓词,它使用的是 COLCARD 统计,而对于范围谓词,它使用的是 HIGH2KEYLOW2KEY 统计。在本场景中,您收集了分布式统计信息,所以优化器能够为值跨度很大的字段数据估算出一个更准确的基数,而在从 CLP 提交时它们会在查询中使用实际的线性值。

解决方案:按照以下方法使用 REOPT ALWAYS 选项绑定包:

  db2 BIND <filename> ... REOPT ALWAYS ...

此外,我们也可以考虑使用 REOPT ONCE 选项,但是如果数据跨度很大,那么计划的多种变化可能要求进行性能优化。通过使用 REOPT ONCE,如果您不给它提供一组有代表性的值,那么它就没有任何优势。下面是使用 REOPT 时应遵循的一些准则。

  • 不一致的数据分布
  • 收集分布式统计信息
  • REOPT ALWAYS:不考虑编译时间
  • REOPT ONCE:如果考虑编译时间,第一组值上的 REOPT-imizing 可能会产生一个适合于所有范围值的计划

您可以使用 db2pd 工具来确认是否使用了 REOPT,如 清单 20 所示。

清单 20. 使用 db2pd 确认已使用 REOPT
db2pd –db <dbname> -REOPT

Database Partition 0 -- Database PCTMS00D -- Active -- Up 15 days 00:28:27 -- Date ...
Dynamic Cache Reoptimization:
Dynamic SQL Statements:
Address    AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x2E787090 41     94       1      4       5      5      SELECT ...
                                                        FROM
                                                          DB2INST1.T1 A,
                                                          DB2INST1.T1 B,
                                                          DB2INST1.T1 C,
                                                        WHERE
                                                          A.T1ID = B.T1ID and
                                                          B.T2ID = C.T2ID and
                                                          B.X = :HV00001 :HI00001 and
                                                          C.Y <> ‘10’ and
                                                          A.X in ('00') and
                                                          A.Y in ('000','001','005','006')
                                                           and
                                                          A.Z between :HV00002 :HI00002
                                                           and
                                                               :HV00003 :HI00003

NumVar 值表示查询的变化数,而 StmtUID 值则用于映射这些变化,如 清单 21 所示。

清单 21. 动态 SQL 变化
Address    AnchID StmtUID EnvID VarID NumRef Typ ... Time                       ...
0x2F1175E0 41     94      2     4     1      6       2008-11-06-16.34.25.040149
0x2F0E7C50 41     94      2     3     1      6       2008-11-06-16.30.42.854376 
0x2F2E6A60 41     94      2     2     1      6       2008-11-06-13.15.17.701584
0x2F2D61C0 41     94      2     1     1      6       2008-11-06-12.48.36.476402

清单 21 显示了同一个查询在不同时间执行的不同变化。由于性能原因,当您使用 REOPT ALWAYS 时,清单 22 中的空白输出所表示的值在内部是不会被记录的。

清单 22. REOPT 值不会出现在 REOPT ALWAYS 中
Address    AnchID StmtUID EnvID VarID NumRef Typ ... Time                       ...

场景 4:收集字段组统计信息以改进查询执行性能

在这种情况中,优化器在计算一个查询存取计划时会低估基数,从而导致查询需要执行 2 个小时。

清单 23. 原始语句
SELECT ...
FROM 
  T1,
  T2, 
  T3, 
  T4 
WHERE ... AND
  T1.ACCT_NUM  = T2.ACCT_NUM AND 
  T2.ACCT_NUM  = T3.ACCT_NUM AND 
  T2.ID_NUM    = T3.ID_NUM AND 
  T1.ACCT_NUM  = T4.ACCT_NUM AND 
  T1.REP_NUM   = T4.REP_NUM AND
  T4.ACCT_NUM  = T3.ACCT_NUM AND 
  T4.ID_NUM    = T3.ID_NUM AND 
  T4.ACCT_IND  = T3.ACCT_IND AND 
  ('1800-01-01-00.00.00.000000' < T1.DATE_MOD) AND
  T1.COMM IN ('A', 'D') ...
清单 24. 查询存取计划
                                     55.8297 
                                     NLJOIN 
                                     (   2) 
                                     226198 
                                     66138.3 
                           /------------+-----------\
                      55.8297                          1 
                      NLJOIN                        FETCH  
                      (   3)                        (  10) 
                      221047                        92.2672 
                      65932.4                       3.68909 
                    /----+---\                     /---+---\
               57.0648      0.978356          3.05367    3.81189e+06 
               TBSCAN        IXSCAN           IXSCAN   TABLE: DB2INST1  
               (   4)        (   9)           (  11)        T3 
               218193        50.0315          50.0303 
               65818.2          2                2 
                 |             |                |
               57.0648     1.06432e+06      3.81189e+06 
               SORT      INDEX: DB2INST1   INDEX: DB2INST1  
               (   5)        T2_IDX             T3_IDX 
               218193 
               65818.2 
                 |
               57.0648
               HSJOIN 
               (   6) 
               218193 
               65818.2 
           /------+-----\
    6.69807e+06       1.15013e+06 
      TBSCAN            TBSCAN 
      (   7)            (   8) 
      60191.6           33264.5 
       33540             17057 
        |                 |
    6.69807e+06       3.3602e+06 
  TABLE: DB2INST1    TABLE: DB2INST1  
       T4                 T1

清单 23 中的查询包含了多个相同的关于联合中各表的联合谓词。

Group 1:
  P2:   T2.ACCT_NUM = T3.ACCT_NUM 
  P3:   T2.ID_NUM   = T3.ID_NUM 

Group 2:
  P4:   T1.ACCT_NUM = T4.ACCT_NUM 
  P5:   T1.REP_NUM  = T4.REP_NUM

Group 3:
  P6:   T4.ACCT_NUM = T3.ACCT_NUM 
  P7:   T4.ID_NUM   = T3.ID_NUM 
  P8:   T4.ACCT_IND = T3.ACCT_IND

清单 24 中的存取计划显示 HSJOIN (6) 中的基数明显小于联合的两个输入。清单 25 中的详细谓词部分来自于 Group 2 集,它们显示为 P4P5。而量词 Q1Q4 分别对应于表 T4T1

清单 25. 应用到 HSJOIN(6) 的谓词
  2) Predicate used in Join
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            2.7307e-07
     Predicate Text:
     --------------
     (Q4.ACCT_NUM = Q1.ACCT_NUM) <--- P4
     
  6) Predicate used in Join
     Relational Operator:      Equal (=)
     Subquery Input Required:  No
     Filter Factor:            2.71267e-05
     Predicate Text:
     --------------
     (Q1.REP_NUM = Q4.REP_NUM)   <--- P5

这个散列联合所估算的基数是作为输入基数的乘积和每一个联合谓词的过滤因子计算的,如下所示。

  HSJOIN_CARD = 2.7307e-07 * 2.71267e-05 * 6.69807e+06  * 1.15013e+06 =~ 57.0648

下面的 count(*) 查询可用于计算这个联合基数估算值的误差,如下所示。

  SELECT COUNT(*)
  FROM
    T4 AS Q1,
    T1 AS Q4
  WHERE
   (Q1.REP_NUM = Q4.REP_NUM) AND
   (Q4.ACCT_NUM = Q1.ACCT_NUM) AND
   ('1800-01-01-00.00.00.000000' < Q4.DATE_MOD) AND
   Q4.COMM IN ('A', 'D');

  RESULT:  1,155,273 rows

字段组统计

这里的讨论是假定您熟悉字段组统计及其相关概念的。您可以这里查找关于字段分级统计的更详细信息 “理解 DB2 中的字段组统计”。

T1 中的两个本地谓词的每个过滤因子都进行了验证和确认。由于两个表之间可能有两个或两个以上的同等联合谓词,所以字段组统计也可作为一种解决方案,而且这个联合的基数估算的误差是非常大的,所以这两个联合谓词可能在统计上是相关联的。最简单的方法是同时收集两个表的字段组统计信息,但是只需要收集一个父表字段组,所以可以采取以下步骤来确定联合父表的字段组统计。

表 2. 字段统计
T1T4
ColumnColcardHigh2keyLow2keyColcardHigh2keyLow2key
ACCT_ NUM3357997'JERE2''00003'3662064'V222S''00003'
REP_ NUM32341'95517''63135'36864'99999''63135'

数据库分区特性

在一个 DPF 环境中,确定父表及子表的方式有很多限制。请参考 “理解 DB2 中的字段组统计“ 了解更详细的信息。

表 2 的字段组统计中,T4 是联合的父表,因为 ACCT_NUMREP_NUMCOLCARD 大于 T1 对应的 COLCARD 统计信息,而每一个字段的 HIGH2KEY 更大一些,但是 LOW2KEY 是相同的(要求是 less than or equal to)。结果,优化器将使用(T4.ACCT_NUM, T4.REP_NUM)的字段组统计来计算两个联合谓词之间的统计相关性。

解决方案:收集 T4 的字段组统计信息。如果 T4 中已经定义了一个带有将(ACCT_NUM, REP_NUM)作为开头字段的键的索引(两个字段的顺序是无关的),那么这个索引的 FIRST2KEYCARD 统计应该是已经存在,优化器将用它来计算统计相关性。在本场景中,这种索引可能还不存在,所以 RUNSTATS 命令是使用所包含的字段组执行的,如下所示。

  RUNSTATS ON TABLE DB2INST1.T4 
           ON ALL COLUMNS AND COLUMNS ((ACCT_NUM, REP_NUM)) WITH DISTRIBUTION 
           AND SAMPLED DETAILED INDEXES ALL

清单 26 说明了收集字段组统计信息是如何改进优化器所计算的基数估算值的,它能产生一个更好的执行存取计划。

清单 26. 收集字段组统计之后的存取计划
                              1.6838e+06 
                                HSJOIN
                                (   2) 
                                420201 
                                170275 
                    /--------------+--------------\
             1.72106e+06                      1.06671e+06 
               HSJOIN                            HSJOIN 
               (   3)                             (   6) 
               248948                             170618 
                69854                             100421 
           /------+-----\                     /------+-----\
    6.70386e+06       1.20558e+06      3.81669e+06       1.06671e+06 
      TBSCAN            TBSCAN           TBSCAN            IXSCAN 
      (   4)            (   5)           (   7)            (   8) 
      60245.9           33859.8          154124            15675.8 
       33569             17359            91530             8891 
        |                 |                |                 |
    6.70386e+06       3.42018e+06      3.81669e+06       1.06671e+06 
TABLE: DB2INST1    TABLE: DB2INST1   TABLE: DB2INST1    INDEX:DB2INST
     T4                 T1                T3                 T2_IDX

场景 5:在分析存取计划时使用 Section Actuals 来改进查询性能

确定正确分组的 count(*) 查询可能是很困难且很耗费时间的,特别是对于大型查询更是如此。您可以使用一个从 DB2 Version 9.7 Fix Pack 1 开始出现的名为 Section Actuals 的新特性来解决这个问题。

在本场景中,您可以使用 Section Actuals 重复 场景 1 的步骤,以证明其用处。您可以采取以下步骤来了解 Section Actuals

  1. 启用 Section Actuals
  2. 创建工作量管理器和事件监视器
  3. 收集相关语句的 Section Actuals
  4. 确定数据的 Application、UOW 和 Activity ID
  5. 将数据输入到 EXPLAIN 表中
  6. 运行 db2exfmt 产生存取计划
  7. 检查输出

步骤 1:启用 Section Actuals

Section Actuals 限制

如果启用了统计模板(auto_stats_prof)自动生成,Section Actuals 就无法启用。如果试图启用,它就返回一个 SQLCODE -5153 错误。

您必须将 section_actuals 数据库配置参数按照以下方式设置到 BASE,从而显式地启用 Section Actuals

  db2 update db cfg for <dbname> using section_actuals base

在启用后,我们就可以使用 EXPLAIN_FROM_ACTIVITY 过程来捕捉信息。

步骤 2:创建工作量管理器和事件监视器

您必须创建一个工作量管理器和事件监视器来使用 Section Actuals。您可以采取下面的方式创建一个工作量管理器,但是您也可以使用默认的工作量管理器。

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

  grant usage on workload MYWORKLOAD to public; 

  create event monitor MYMON for activities write to table;

步骤 3:收集相关语句的 Section Actuals

  delete from ACTIVITYSTMT_MYMON; 
  call wlm_set_client_info(null, null, null, 'MYWORKLOAD', null); 
  set event monitor MYMON state 1;

  -- a subset of the statement from scenario 1
  SELECT * FROM DB2INST1.T4 AS Q1 
   WHERE (Q1.Y = 'Y')
     AND (Q1.Z = 'N')

  set event monitor MYMON state 0; 
  call wlm_set_client_info(null, null, null, null, null);

步骤 4:确定数据的 Application、UOW 和 Activity ID

  select appl_id, uow_id, activity_id, substr(stmt_text,1,80) as stmt 
    from ACTIVITYSTMT_MYMON ;

APPL_ID                         UOW_ID   ACTIVITY_ID       STMT
--------------------------      -------  ----------------  -----------------------------
*LOCAL.DB2.100530150552              20                 1  SELECT * FROM DB2INST1.T4 
                                                                           AS Q1
                                                           WHERE (Q1.Y = 'Y') 
                                                             AND (Q1.Z = 'N')

步骤 5:将数据输入到 EXPLAIN 表中

我们调用 explain_from_activity 过程来将有关的语句输入到 EXPLAIN 表中。首先是三个输入 APPL_ID, UOW_ID, ACTIVITY_ID,它们是根据 步骤 4 输出中有关的语句确定的。第四个输入是 步骤 3 中设置的事件监视器的标识符,第五个输入是 EXPLAIN 表的模式名称。在这个例子中,EXPLAIN 表的模式是 VCORVINE

  call explain_from_activity ('*LOCAL.DB2.100530150552',
                              20,1,'MYMON','VCORVINE',?,?,?,?,?)

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

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : VCORVINE

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2010-05-30-11.21.34.250000

  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 <dbname> -1 –o ex_activity.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 ex_activity.out.
Executing Connect Reset -- Connect Reset was Successful.

步骤 7:检查输出

下面是 ex_activity.outdb2exfmt 输出的存取计划的一部分。

       Rows
    Rows Actual
      RETURN
      (   1)
       Cost
        I/O
         |
        ...
       0.0208  <-- estimated number of rows
        13    <-- actual number of rows
       IXSCAN     
       (   5)
      0.0104877
         NA
         |
         13 
         NA
   INDEX: DB2INST1 
        IDX89

这个存取计划显示每一个操作符的估算及实际的记录数。Section Actuals 是从 Explain 部分派生的,所以并非包含所有的数据,像查询中总是有一个 EXPLAIN。这是使用 NA 符号在存取计划图中标记出来的。例如,IXSCAN(5) 并不包含 I/O 开销值,实际的基本表基数也是没有收集的。


结束语

分析存取计划以优化查询和改进查询执行性能是一个非常有价值的技能。通过使用本文所讨论的工具和技术来确定和修正优化器所估算的基数误差,您可以实现最优的存取计划和提高其性能,

参考资料

学习

获得产品和技术

讨论

条评论

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=608372
ArticleTitle=发挥 DB2 优化器的最大功效
publish-date=01172011