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执行速度恢复正常。
后记：
该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 特权
要收集特定列的统计信息：
- 连接至 sales 数据库。
- 取决于您的需求，在 DB2® 命令行执行下列其中一个命令：
- 要收集列 zip 和 ytdtotal 的分布统计信息：
RUNSTATS ON TABLE sales.customers WITH DISTRIBUTION ON COLUMNS (zip, ytdtotal) - 要收集相同列的分布统计信息，但调整分布缺省值：
RUNSTATS ON TABLE sales.customers WITH DISTRIBUTION ON COLUMNS (zip, ytdtotal NUM_FREQVALUES 50 NUM_QUANTILES 75) - 要收集索引为 custidx1 和 custidx2 的列的分布统计信息：
RUNSTATS ON TABLE sales.customer ON KEY COLUMNS - 要仅收集表中特定列 zip 和 ytdtotal 以及包含 region 和 territory 的列组的列统计信息：
RUNSTATS ON TABLE sales.customers ON COLUMNS (zip, (region, territory), ytdtotal) - 假定已使用带 STATISTICS 选项的 LOAD 命令收集了非 XML 列的统计信息。要用 XML 列 miscinfo 的统计信息补充非 XML 统计信息：
RUNSTATS ON TABLE sales.customers ON COLUMNS (miscinfo)
- 要仅收集表中非 XML 列的列统计信息（EXCLUDING XML COLUMNS 选项优先于可能指定 XML 列的所有其他子句）：
RUNSTATS ON TABLE sales.customers EXCLUDING XML COLUMNS
- 要收集列 zip 和 ytdtotal 的分布统计信息：
还可以使用“控制中心”来收集分布统计信息。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13165828/viewspace-615332/ ，如需转载，请注明出处，否则将追究法律责任。
