db2 index信息更新引起的性能问题 --分布统计信息

问题描述： 用户反馈应用系统某个常用的操作变慢，该操作对应的SQL语句如下：

select distinct a.*,w.IM_WORK_TRAY_NAME as work_tray_name,i.IM_POLNO from SA.TBL_Work_Tray w right join SA.TBL_Work_Task_Define a on a.IM_LAST_WORK_TRAY=w.IM_WORK_TRAY_ID,SA.TBL_image_extend e,SA.TBL_IMAGE i where a.IM_Case_NO=e.IM_CASE_NO and e.IM_Mainindex=i.im_mainindex   and i.IM_IS_DEL<>'Y' and a.IM_Task_Begin='Y' and a.IM_task_status <> 'C'  and (a.IM_Lock_Flag = 'N' or (a.IM_Lock_Flag = 'Y' and LOWER(a.IM_Lock_User) = 'alice_xu')) and a.IM_WORK_TRAY_ID='19' and a.IM_Company_code='1'  order by a.IM_LOCK_flag desc,a.IM_LAST_UPDATE_TIME asc fetch first 100 rows only ;

该语句正常情况执行效率如下：

Summary of Results
==================
                Elapsed             Agent CPU         Rows      Rows
Statement #     Time (s)            Time (s)          Fetched   Printed
1                     0.039               0.000         0         0

Arith. mean     0.039               0              
Geom.  mean     0.039               0              

访问计划

Access Plan:
-----------
        Total Cost:                 128.908
        Query Degree:                1

                                                     Rows
                                                    RETURN
                                                    (   1)
                                                     Cost
                                                      I/O
                                                      |
                                                   0.0573019
                                                    TBSCAN
                                                    (   2)
                                                    128.908
                                                    10.0158
                                                      |
                                                   0.0573019
                                                    SORT   
                                                    (   3)
                                                    128.908
                                                    10.0158
                                                      |
                                                    1.0669
                                                    NLJOIN
                                                    (   4)
                                                    128.906
                                                    10.0158
                                           /-----------+----------\
                                      2.1338                        0.5
                                      NLJOIN                      IXSCAN
                                      (   5)                      (  11)
                                      46.6005                     38.5834
                                      3.61438                        3
                              /----------+----------\               |
                        0.377296                    5.65552     3.55689e+06
                         NLJOIN                     IXSCAN    INDEX: DB2INST1
                         (   6)                     (  10)   IDX8051911324200
                         33.7159                    38.5872
                         2.61438                       3
                  /---------+---------\               |
            0.614244                     1        3.80777e+06
             FETCH                    IXSCAN    INDEX: DB2INST1
             (   7)                   (   9)    IDXMAIN_EXTEND
             33.6961                 0.0197809
             2.61438                     0
           /----+---\                   |
     0.614244       674825              60
      IXSCAN    TABLE: SA         INDEX: DB2INST1
      (   8)   TBL_WORK_TASK_DE  IDX8051911322300
      25.7988
         2
        |
      674825
  INDEX: DB2INST1
IDX8051911313000

目前的执行效率：
Summary of Results
==================
                Elapsed             Agent CPU         Rows      Rows
Statement #     Time (s)            Time (s)          Fetched   Printed
1                     3.969               3.363         0         0

Arith. mean     3.969               3.36           
Geom.  mean     3.969               3.36      

目前执行计划：


Access Plan:
-----------
        Total Cost:                 175756
        Query Degree:                1

                           Rows
                          RETURN
                          (   1)
                           Cost
                            I/O
                            |
                          8259.28
                          TBSCAN
                          (   2)
                          175756
                          64164.7
                            |
                            100
                          SORT   
                          (   3)
                          175756
                          64164.7
                            |
                          8259.28
                          HSJOIN
                          (   4)
                          175745
                          64164.7
           /-----------------+-----------------\
    6.50478e+06                                8267.55
      IXSCAN                                   NLJOIN
      (   5)                                   (   6)
      109853                                   65367.1
      59080.7                                  5084.01
        |                              /----------+----------\
    6.5113e+06                    1530.97                    5.4002
  INDEX: DB2INST1                 HSJOIN                     IXSCAN
IDX8051911324200                 (   7)                     (  15)
                                  6322.43                    38.5876
                                  491.095                       3
                           /---------+---------\               |
                      1530.97                    65        7.75465e+06
                      FETCH                    IXSCAN    INDEX: DB2INST1
                      (   8)                   (  14)    IDXMAIN_EXTEND
                      6322.17                 0.0579678
                      491.095                     0
                    /----+---\                   |
               1754.96     1.37667e+06           65
               RIDSCN    TABLE: SA         INDEX: DB2INST1
               (   9)   TBL_WORK_TASK_DE  IDX8051911322300
               135.242
               9.99367
           /------+-----\
      1742.63           12.3484
      SORT              SORT   
      (  10)            (  12)
      49.4921           85.7504
      3.69747           6.2962
        |                 |
      1742.63           12.3484
      IXSCAN            IXSCAN
      (  11)            (  13)
      48.695            85.7484
      3.69747           6.2962
        |                 |
    1.37667e+06       1.37667e+06
  INDEX: DB2INST1   INDEX: DB2INST1
IDX8051911424700  IDX8051911313000

通过对比SQL访问计划，可以得出SQL语句选择访问计划原因导致SQL突然变慢。

解决方法：查看该语句涉及的表和INDEX的统计信息，统计信息是最近更新的，

对该index进行列分布的统计信息的更新，该SQL执行速度恢复正常。

后记：

开发人员对该语句中的index进行  RUNSTATS ON TABLE  for index 更新，

该runstat更新会替换列分布的统计信息，导致访问计划变化。

 

在 DB2 优化器中使用分布统计信息:

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0606fechner/

 

 

收集特定列的分布统计信息

为了提高 RUNSTATS 和后续查询方案分析的效率，可以仅收集查询在 WHERE、GROUP BY 和类似子句中使用的列的分布统计信息。还可以收集关于列的组合组的基数统计信息。优化器使用这种信息来在它为引用组中列的查询估计选择性时检测列相关。

在下列步骤中，假定数据库为 sales 并包含表 customers，带有索引 custidx1 和 custidx2。

先决条件

必须连接至包含表和索引的数据库并具有下列其中一个权限级别：

• sysadm
• sysctrl
• sysmaint
• dbadm
• 对表的 CONTROL 特权

注:

RUNSTATS 仅收集执行该命令的数据库分区上的表的统计信息。将此数据库分区的 RUNSTATS 结果推广到其他数据库分区。如果执行 RUNSTATS 的数据库分区不包含表的一部分，则将请求发送到数据库分区组中持有表的该部分的第一个数据库分区。

过程

要收集特定列的统计信息：

1. 连接至 sales 数据库。
2. 取决于您的需求，在 DB2® 命令行执行下列其中一个命令：
   • 要收集列 zip 和 ytdtotal 的分布统计信息：

   • 要收集相同列的分布统计信息，但调整分布缺省值：

   • 要收集索引为 custidx1 和 custidx2 的列的分布统计信息：

   • 要仅收集表中特定列 zip 和 ytdtotal 以及包含 region 和 territory 的列组的列统计信息：

   • 假定已使用带 STATISTICS 选项的 LOAD 命令收集了非 XML 列的统计信息。要用 XML 列 miscinfo 的统计信息补充非 XML 统计信息：

   • 要仅收集表中非 XML 列的列统计信息（EXCLUDING XML COLUMNS 选项优先于可能指定 XML 列的所有其他子句）：

还可以使用“控制中心”来收集分布统计信息。

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-615332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-615332/