DB2优化工具使用

DB2优化工具使用DB2提供了多种数据库优化工具，包括db2advis（设计顾问程序）、Visual Explain、db2exfmt、db2expln数据库优化工作最头疼的事情是什么？个人认为:一．没有具体的量化指标(这里指执行sql的开销,包括CPU、磁盘I/O等的开销)，来判断优化的效果。

程序员只能凭sql语句的执行时间和以往的经验来判断优化是否成功。

二．数据库查询优化器的优化结果是否理想，由于sql语句是要经过数据库引擎的查询优化器对sql语句进行重整优化，这一过程是黑盒的，我们无法了解，比如查询条件是否下推？我建立的索引是否被用到？sql语句执行的是全表扫描还是索引扫描？这些怎么确定是一个关键问题。

三．对于业务系统需求经常变更的应用系统，怎么从全局角度把握数据库的优化，在运行阶段可能做过优化工作，但是随着业务的变更，以前做的优化工作变的无效了，怎么重新优化？IBM为我们提供的这几款工具为使我们能够对这些开销的具体数据有全面的了解，了解查询优化器优化后的结果，全局角度了解目前业务系统的优化状况，从而制定优化目标。

以上是个人的一点感受，下面切入正题，go.一． db2advis（设计顾问程序）的使用设计顾问程序有命令行模式的db2advis，也有图形化界面的设计顾问程序。

需要注意的是图形化界面的设计顾问程序依赖于db2advis，如果没有建立过执行计划表的话，先要建立执行计划表，需要在相应数据库里执行 EXPLAIN.DDL,命令如下db2cmd 进入DB2CLPdb2 connect to <dbname> user <username> using <password> db2 -tf $INSTHOME\sqllib\misc\EXPLAIN.DDL如下图:执行完后会在数据库中看到建立的执行计划表接着就是在DB2CLP中执行db2advis命令,db2advis -d <dbname> -n <schema name>–m I –s "sql stmt" –t <time> -a <username>/<password> -o <filename>这里解释一下上面的参数–d 数据库名称或数据库别名–m 执行优化的类型(I 索引 M MQT物化查询表 C 多维集群 P 分区)-n schema name-a 数据库用户名和密码-s 要执行的sql-o 将结果保存到文件中 (注意-o只给出了优化方案,并没给出开销和性能提升的百分比,可以不用这个而在最后加上 >xxx.txt 来包结果输出到文本中)详细的参数可以在DB2CLP中输入 db2advis 命令查看以下是一个执行的例子:db2advis -d cs -n db2inst1 -m I -s "selectbp.id,bc.brand_code,cb.brand_name,bbp.id as b_id,sbp.id as s_id, sbp.plan_amount1 as ssg,sc.sg as sg ,sbp.is_normal1s_is1,bbp.plan_amount1 as bsg, sbp.plan_amount2 as szg,sc.zg as zg ,sbp.is_normal2 s_is2,bbp.plan_amount2 as bzg,sbp.plan_amount3 as sxl,sc.xl as xl ,sbp.is_normal3s_is3,bbp.plan_amount3 as bxl, sbp.plan_amount4 assscfe,sc.scfe as scf ,sbp.is_normal4 s_is4,bbp.plan_amount4 as bscfe,case when coalesce(h.OCT_LOWER_LIMIT,0)>0 or coalesce(h.OCT_UPPER_LIMIT,0)>0 then case whencoalesce(sc.sg,0)<h.OCT_LOWER_LIMIT or coalesce(sc.sg,0)> h.OCT_UPPER_LIMIT then '否' else '是' end whencoalesce(h.OCT_LOWER_LIMIT,0)>0 then case whencoalesce(sc.sg,0)<h.OCT_LOWER_LIMIT then '否' else '是' end else '是' end as sg_is_good,case whencoalesce(h.REP_LOWER_LIMIT,0)>0 orcoalesce(h.REP_UPPER_LIMIT,0)>0 then case whencoalesce(sc.zg,0)<h.REP_LOWER_LIMIT or coalesce(sc.zg,0)> h.REP_UPPER_LIMIT then '否' else '是' end whencoalesce(h.REP_LOWER_LIMIT,0)>0 then case whencoalesce(sc.zg,0)<h.REP_LOWER_LIMIT then '否' else '是' end else '是' end as zg_is_good,case whencoalesce(h.SALE_LOWER_LIMIT,0)>0 orcoalesce(h.SALE_UPPER_LIMIT,0)>0 then case whencoalesce(sc.xl,0)<h.SALE_LOWER_LIMIT or coalesce(sc.xl,0)> h.SALE_UPPER_LIMIT then '否' else '是' end whencoalesce(h.SALE_LOWER_LIMIT,0)>0 then case whencoalesce(sc.xl,0)<h.SALE_LOWER_LIMIT then '否' else '是' end else '是' end as xl_is_good,case whencoalesce(h.MARKER_LOWER_LIMIT,0)>0 orcoalesce(h.MARKER_UPPER_LIMIT,0)>0 then case whencoalesce(sc.scfe,0)<h.MARKER_LOWER_LIMIT orcoalesce(sc.scfe,0)> h.MARKER_UPPER_LIMIT then '否' else '是' end when coalesce(h.MARKER_LOWER_LIMIT,0)>0 then case when coalesce(sc.scfe,0)<h.MARKER_LOWER_LIMIT then '否'else '是' end else '是' end asscf_is_good,cb.BRAND_TRADEMARK,i.count2,rate2,decimal(hhh.a _amt,18,2),iii.zyrate,bbp.plan_sghs,bbp.plan_zghs Fromdb2inst1.work135_person_plan bp left join ( selectv.person_code,mb.brand_code fromdb2inst1.v_work135_manager_person_relation v left joindb2inst1.work135_person_plan p on _code = _code and p.person_code = v.manager_code left joindb2inst1.work135_sale_attention_brand mb on mb.plan_id = p.id where _code='11652901' and v.type_code='SALE' andv.person_code='29000092' and plan_type=30 and year=2011 and month=12 union select cb.person_code,cb.brand_code fromdb2inst1.work135_sale_attention_brand cb where cb.plan_id = '1000011121900003733' ) bc on bp.person_code =bc.person_code left join db2inst1.work135_brand_plan bbp on bbp.plan_id = bp.id and bbp.brand_code = bc.brand_code left join db2inst1.work135_person_plan sp on sp.person_code = bp.person_code and sp.year = 2011 and sp.month = 11 and sp.plan_type = bp.plan_type left joindb2inst1.work135_brand_plan sbp on sp.id = sbp.plan_id and sbp.brand_code = bc.brand_code left joindb2inst1.WORK135_BRAND_MONTH_ACCOUNT sc on sc.person_code =bp.person_code and sc.brand_code = bc.brand_code and sc.year = 2011 and sc.month = 11 left join _brand cb on cb.brand_code = bc.brand_code left joinDB2INST1.V_WORK135_MANAGER_PERSON_RELATION g onbp.PERSON_CODE=g.PERSON_CODE left joinDB2INST1.WORK135_THRESHOLD_brand h onh.PERSON_CODE=g.MANAGER_CODE and bc.brand_code=h.BRAND_CODE left join (select cust_mgr_code,count(distinct a.custom_id) count2 from _customer a left joinDB2INST1.custom_regie_info b on a.custom_id=b.custom_id where current_status not in (4,13) and a.cust_mgr_code is not null and a.cust_mgr_code<>'' and a.cust_mgr_code not like '未知' GROUP BY cust_mgr_code) i on bp.PERSON_CODE=i.cust_mgr_code left join(select cust_mgr_code,brand_code,count(distinct custom_id) rate2 from (selectcust_mgr_code,brand_code,a.custom_id fromDB2INST1.custom_month_account a left join_customer c on a.custom_id=c.custom_id left join DB2INST1.custom_regie_info d on d.custom_id=c.custom_id where a.year= 2011 and a.month = 11 and sale_amount<>0 andcurrent_status not in (4,13)) a group bycust_mgr_code,brand_code) ggg onggg.cust_mgr_code=bc.person_code andggg.brand_code=bc.brand_code left join (selectcust_mgr_code,brand_code,sum(sale_amount) a_amt from (select brand_code,custom_id,sale_amount fromDB2INST1.custom_month_account where year=2011 and month=11) a left join (select * from _customer wherecust_mgr_code is not null and cust_mgr_code<>'' andcust_mgr_code not like '未知') c on a.custom_id=c.custom_id left join DB2INST1.custom_regie_info d ond.custom_id=c.custom_id and sale_amount<>0 and current_status not in (4,13) and c.cust_mgr_code='29000092' group bycust_mgr_code,brand_code) hhh onhhh.cust_mgr_code=bc.person_code andhhh.brand_code=bc.brand_code left join (selecta.cust_mgr_code,brand_code,decimal(decimal(a_amt,18,6)/b_amt*100,18,2) zyrate from (select cust_mgr_code,a.brand_code,sum(sale_amount)a_amt,category_id from DB2INST1.custom_month_account a left join _customer c on a.custom_id=c.custom_id left join DB2INST1.custom_regie_info d on d.custom_id=c.custom_id left join (select a.category_id,brand_code fromDB2INST1.Brand_Category_Set a left joinDB2INST1.Brand_Category b on a.category_id=b.category_id where org_code='11650001') e on a.brand_code=e.brand_code where a.year =2011 and a.month=11 and sale_amount<>0 and current_status not in (4,13) and cust_mgr_code is not null and cust_mgr_code<>'' and cust_mgr_code not like '未知' group by cust_mgr_code,a.brand_code,category_id) a left join (select cust_mgr_code,sum(sale_amount) b_amt,category_id fromDB2INST1.custom_month_account a left join_customer c on a.custom_id=c.custom_id left join DB2INST1.custom_regie_info d on d.custom_id=c.custom_id left join (select a.category_id,brand_code fromDB2INST1.Brand_Category_Set a left joinDB2INST1.Brand_Category b on a.category_id=b.category_id where org_code='11650001') e on a.brand_code=e.brand_code where a.year =2011 and a.month=11 and sale_amount<>0 and current_status not in (4,13) and cust_mgr_code is not null and cust_mgr_code<>'' and cust_mgr_code not like '未知' group by cust_mgr_code,category_id) b ona.cust_mgr_code=b.cust_mgr_code anda.category_id=b.category_id) iii oniii.cust_mgr_code=bc.person_code andiii.brand_code=bc.brand_code where bp.id ='1000011121900003733' and bc.brand_code is not null and bc.person_code='29000092'" -a db2inst1/db2inst1 >1.TXT执行完成后的结果如下图Timeron为执行这个sql语句的总开销,下面是给出的优化建议. 这个太长,下面给个其它语句的执行结果主要列出的是:建议的索引、推荐的索引、没有用到的索引建立建议的索引，对推荐的索引做RUNSTATS来收集信息以便数据库查询优化器来做优化，没用的索引要根据整体情况来看。