Chinaunix首页 | 论坛 | 博客
  • 博客访问: 64016
  • 博文数量: 4
  • 博客积分: 655
  • 博客等级: 上士
  • 技术积分: 150
  • 用 户 组: 普通用户
  • 注册时间: 2006-02-23 22:10
文章分类

全部博文(4)

文章存档

2010年(2)

2009年(2)

我的朋友

分类:

2009-09-22 14:39:47

 
问题描述: 用户反馈应用系统某个常用的操作变慢,该操作对应的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更新会替换列分布的统计信息,导致访问计划变化。
 
 
 
 
阅读(1316) | 评论(0) | 转发(0) |
0

上一篇:没有了

下一篇:DB2启动故障案例一

给主人留下些什么吧!~~