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