DB2数据库慢SQL性能优化详解

z7369

　　一、抓取批量耗时最久的SQL：
　　2、执行次数最多的TOP10SQL"
　　db2 "select substr(stmt_text,1,500) as stmt_text,NUM_EXECUTIONS,TOTAL_USR_CPU_TIME,TOTAL_EXEC_TIME,SORT_OVERFLOWS from sysibmadm.snapdyn_sql order by NUM_EXECUTIONS desc fetch first 10 rows only"|grep -iv selected
　　3、平均执行时间最长的TOP10SQL"
　　db2  "select substr(stmt_text,1,500) as stmt_text,average_execution_time_s,num_executions from sysibmadm.top_dynamic_sql order by average_execution_time_s desc fetch first 10 rows only"|grep -iv selected
　　4、排序最多的TOP10SQL"
　　db2 "select substr(stmt_text,1,500) as stmt_text,stmt_sorts,SORT_OVERFLOWS,TOTAL_USR_CPU_TIME,NUM_EXECUTIONS from sysibmadm.snapdyn_sql order by stmt_sorts desc fetch first 10 rows only"|grep -iv selected
　　5、通过以上SQL获得最耗时的SQL信息如下：
　　update  tbhisfeyehz  t set client_num=( select count(distinct a.in_client_no) from vsharedetail a, tbbranch b, tbtainfo c  where a.ta_code=c.ta_code and a.open_branch=b.branch_no  and substr(b.internal_branch,1,length(Rtrim(t.internal_branch))) =  t.internal_branch  and a.client_type=t.client_type and a.prd_code=t.prd_code and a.ta_code=t.ta_code and c.prd_type=t.prd_type and a.tot_vol > 0.0001  and a.ta_code=?)  where exists(select 1 from vsharedetail a, tbbranch b, tbtainfo c where a.ta_code=c.ta_code and a.open_branch=b.branch_no  and substr(b.internal_branch,1,length(Rtrim(t.internal_branch))) =  t.internal_branch  and a.client_type=t.client_type and a.prd_code=t.prd_code and a.ta_code=t.ta_code and c.prd_type=t.prd_type and a.tot_vol > 0.0001 and a.ta_code=?)  and internal_branch in(select internal_branch from tbbranch where branch_level?)  and sum_flag='0clear
　　三、为了在测试环境复现该执行计划我们需要从生产导出该SQL用到的所有基表的统计信息还原到测试环境下去。
　　1、导出统计信息：
　　db2look -d ifm30 -u ifm40 -m -t tbhisfeyehz -o tbhisfeyehz.sql
　　db2look -d ifm30 -u ifm40 -m -t tbsharedetail1 -o tbsharedetail1.sql
　　db2look -d ifm30 -u ifm40 -m -t tbsharedetail2 -o tbsharedetail2.sql
　　db2look -d ifm30 -u ifm40 -m -t tbsharedetail3 -o tbsharedetail3.sql
　　db2look -d ifm30 -u ifm40 -m -t tbsharedetail4 -o tbsharedetail4.sql
　　db2look -d ifm30 -u ifm40 -m -t tbsharedetail5 -o tbsharedetail5.sql
　　db2look -d ifm30 -u ifm40 -m -t tsys_branch    -o tsys_branch.sql
　　db2look -d ifm30 -u ifm40 -m -t tbtainfo       -o tbtainfo.sql
　　2、生效到测试环境：
　　db2 -tvf  tbsharedetail1.sql
　　db2 -tvf  tbsharedetail2.sql
　　db2 -tvf  tbsharedetail3.sql
　　db2 -tvf  tbsharedetail4.sql
　　db2 -tvf  tbsharedetail5.sql
　　db2 -tvf  tsys_branch.sql
　　db2 -tvf  tbtainfo.sql
　　91pron free videos
　　db2 -tvf  tbhisfeyehz.sql
　　四、分析SQL执行计划，根据执行计划分析关键执行成本之处的SQL计划并做出调整计划
　　db2 connect to ifm30
　　db2 set current explain mode explain
　　db2 -tvf optim.sql
　　db2 set current explain mode no
　　db2exfmt -d ifm30 -l -o
　　五、使用优化器顾问程序给出的建议进行优化
　　db2advis -d ifm30 -i optim3.sql -m i -l -1 -t 0 -o product.txt
　　六、优化相关基表
　　1、根据优化引擎建议新建唯一索引在对应基表之上
　　-- index[1],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210621430" ON "IFM40   "."TBSHAREDETAIL4"
　　("TOT_VOL" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[2],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210623270" ON "IFM40   "."TBSHAREDETAIL3"
　　("TOT_VOL" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[3],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210625230" ON "IFM40   "."TBSHAREDETAIL2"
　　("TOT_VOL" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[4],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210627190" ON "IFM40   "."TBSHAREDETAIL1"
　　("TOT_VOL" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[5],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210629150" ON "IFM40   "."TBSHAREDETAIL5"
　　("TOT_VOL" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[6],    0.013MB
　　CREATE UNIQUE INDEX "IFM40   "."IDX1501210631080"
　　ON "IFM40   "."TBTAINFO" ("TA_CODE" ASC) INCLUDE ("PRD_TYPE")
　　ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[7],    0.013MB
　　CREATE INDEX "IFM40   "."IDX1501210632520" ON "IFM40   "."TBHISFEYEHZ"
　　("SUM_DATE" ASC, "SUM_FLAG" ASC, "PRD_TYPE" ASC, "TA_CODE"
　　ASC, "CLIENT_NUM" ASC, "INTERNAL_BRANCH" ASC, "CLIENT_TYPE"
　　ASC, "PRD_CODE" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[8],    0.036MB
　　CREATE INDEX "IFM40   "."IDX1501210632500" ON "IFM40   "."TSYS_BRANCH"
　　("BRANCH_PATH" ASC, "BRANCH_LEVEL" ASC) ALLOW REVERSE
　　SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　-- index[9],    0.024MB
　　CREATE UNIQUE INDEX "IFM40   "."IDX1501210631010"
　　ON "IFM40   "."TSYS_BRANCH" ("BRANCH_CODE" ASC) INCLUDE
　　("BRANCH_PATH") ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
　　COMMIT WORK ;
　　2、批量统计信息更新
　　使用如下shell脚本批量优化
　　cat tblist|while read a
　　db2 connect to ifm30 >>/dev/null
　　do
　　if [ $a  ' ' ];
　　then
　　db2 "runstats on table ifm40.${a} with distribution and detailed indexes all"
　　else
　　exit
　　fi
　　done
　　3、业务验证，通过重新批量测试，由原来的30min降低到2min优化效果明显