用友股份-LE服务支持部技术方案--《Sql效率优化》建立日期：2013-04-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-00322/12文档控制创建记录审阅人姓名所属部门职位审阅签字发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-04-01蔡平昕V1.03目录Sql效率优化........................................................................................................................4一、系统环境:.......................................................................................................4二、问题现象：....................................................................................................4三、问题分析：....................................................................................................4四、解决方法:.......................................................................................................5五、附录：............................................................................................................5附录一：...............................................................................................................5附录二：...............................................................................................................7附录三：...............................................................................................................7附录四:................................................................................................................104Sql效率优化一、系统环境:环境：linux+NC5.5WAS中间件Oracle数据库二、问题现象：某项目月结慢，查看问题原因。三、问题分析：1.发现某个sql语句慢，执行计划参考附录一，发现统计信息有问题。2.统计信息更新后发现cost更高了，感觉执行计划有问题。所以创建索引和修改bitmap关闭参数。（附录二、附录三）altersystemset"_b_tree_bitmap_plans"=false;5createindexi_ia_monthinout_jszc01onia_monthinout(cinventoryid,caccountyear,caccountmonth)nologging;3.重新查看执行计划并执行sql发现速度可以接受在2-3秒能跑完。（附录四）四、解决方法:统计信息、隐含参数、以及索引的效率会提升sql效率。五、附录：附录一：SQL_ID4mtgwkhx787ap,childnumber0-------------------------------------insertintoia_monthledger(btryflag,caccountmonth,caccountyear,cinventoryid,cmonthledgerid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,ninmny,ninnum,ninvarymny,nmonthprice,noutmny,noutnum,noutvarymny,nplanedprice,nvariancerate,pk_corp,ts,vbatch)selectbtryflag,'01','2013',cinventoryid,keyid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,null,null,null,null,null,null,null,nplanedprice,null,pk_corp,'2013-04-0110:45:21',vbatchfrom(selectig.*,rownumanumfromia_monthledgerigwhereig.caccountyear='2012'andig.caccountmonth='12'andig.frecordtypeflag=3andig.pk_corp='1002'and(ig.nabmny<>0orig.nabnum<>0orig.nabvarymny<>60)andig.dr=0andnotexists(select1fromia_monthinoutimwhereim.crdcenterid=ig.crdcenteridandim.cinventoryid=ig.cinventoryidandnvl(im.vbatch,'NULL')=nvl(ig.vbatch,'NULL')andim.caccountyear='201Planhashvalue:2776252480--------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------------------|0|INSERTSTATEMENT|||||1595K(100)|||*1|HASHJOIN||630M|130G|7376K|1595K(1)|05:19:05||2|VIEW||160K|5490K||256(3)|00:00:04||3|COUNT||||||||4|TABLEACCESSFULL|T_IA_NUM001|160K|3451K||256(3)|00:00:04||5|VIEW||392K|70M||1580K(1)|05:16:03||6|COUNT||||||||*7|FILTER||||||||*8|TABLEACCESSBYINDEXROWID|IA_MONTHLEDGER|392K|40M||11630(1)|00:02:20||*9|INDEXRANGESCAN|I_IA_ML_MONTHCORP|414K|||1632(2)|00:00:20||*10|TABLEACCESSBYINDEXROWID|IA_MONTHINOUT|1|74||4(0)|00:00:01||*11|INDEXRANGESCAN|I_IA_MONTHINOUT_CO|1|||3(0)|00:00:01|--------------------------------------------------------------------------------------------------------------7PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("A"."ANUM"="B"."BNUM")7-filter(ISNULL)8-filter((("IG"."NABNUM"<>0OR"IG"."NABMNY"<>0OR"IG"."NABVARYMNY"<>0)AND"IG"."DR"=0))9-access("IG"."CACCOUNTMONTH"='12'AND"IG"."CACCOUNTYEAR"='2012'AND"IG"."PK_CORP"='1002'AND"IG"."FRECORDTYPEFLAG"=3)10-filter(("IM"."CINVENTORYID"=:B1AND"IM"."CRDCENTERID"=:B2ANDNVL("IM"."VBATCH",'NULL')=NVL(:B3,'NULL')AND"IM"."DR"=0))11-access("IM"."PK_CORP"='1002'AND"IM"."CACCOUNTYEAR"='2013'AND"IM"."CACCOUNTMONTH"='01')Note------dynamicsamplingusedforthisstatement附录二：begindbms_stats.gather_table_stats(user,'ia_monthinout',estimate_percent=>null,degree=>4,method_opt=>'forallindexedcolumnssize254');end;附录三：SQL_ID9v5y3mvxjwj82,childnumber0-------------------------------------insertintoia_monthledger(btryflag,caccountmonth,caccountyear,cinventoryid,cmonthledgerid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,ninmny,ninnum,8ninvarymny,nmonthprice,noutmny,noutnum,noutvarymny,nplanedprice,nvariancerate,pk_corp,ts,vbatch)selectbtryflag,'01','2013',cinventoryid,keyid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,null,null,null,null,null,null,null,nplanedprice,null,pk_corp,'2013-04-0113:32:15',vbatchfrom(selectig.*,rownumanumfromia_monthledgerigwhereig.caccountyear='2012'andig.caccountmonth='12'andig.frecordtypeflag=3andig.pk_corp='1002'and(ig.nabmny<>0orig.nabnum<>0orig.nabvarymny<>0)andig.dr=0andnotexists(select1fromia_monthinoutimwhereim.crdcenterid=ig.crdcenteridandim.cinventoryid=ig.cinventoryidandnvl(im.vbatch,'NULL')=nvl(ig.vbatch,'NULL')andim.caccountyear='201Planhashvalue:1978850226-------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|-------------------------------------------------------------------------------------------------------------------|0|INSERTSTATEMENT|||||9798K(100)|||*1|HASHJOIN||128M|26G|7376K|9798K(2)|32:39:38||2|VIEW||160K|5490K||256(3)|00:00:04||3|COUNT||||||||4|TABLEACCESSFULL|T_IA_NUM001|160K|3451K||256(3)|00:00:04||5|VIEW||80129|14M||9794K(2)|32:38:54||6|COUNT||||||||*7|FILTER|||||||9|*8|TABLEACCESSBYINDEXROWID|IA_MONTHLEDGER|80129|8451K||3376(1)|00:00:41||*9|INDEXRANGESCAN|I_IA_ML_MONTHCORP|120K|||475(2)|00:00:06||*10|TABLEACCESSBYINDEXROWID|IA_MONTHINOUT|1|74||124(2)|00:00:02||11|BITMAPCONVERSIONTOROWIDS||||||||12|BITMAPAND||||||||13|BITMAPCONVERSIONFROMROWIDS||||||||*14|INDEXRANGESCAN|I_IA_MONTHINOUT_CI|300|||4(0)|00:00:01||15|BITMAPCONVERSIONFROMROWIDS||||||||*16|INDEXRANGESCAN|I_IA_MONTHINOUT_CR|300|||118(1)|00:00:02|-------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("A"."ANUM"="B"."BNUM")7-filter(ISNULL)8-filter((("IG"."NABNUM"<>0OR"IG"."NABMNY"<>0OR"IG"."NABVARYMNY"<>0)AND"IG"."DR"=0))9-access("IG"."CACCOUNTMONTH"='12'AND"IG"."CACCOUNTYEAR"='2012'AND"IG"."PK_CORP"='1002'AND"IG"."FRECORDTYPEFLAG"=3)10-filter(("IM"."CACCOUNTYEAR"='2013'AND"IM"."CACCOUNTMONTH"='01'ANDNVL("IM"."VBATCH",'NULL')=NVL(:B1,'NULL')AND"IM"."PK_CORP"='1002'AND"IM"."DR"=0))14-access("IM"."CINVENTORYID"=:B1)16-access("IM"."CRDCENTERID"=:B1)Note-----10-dynamicsamplingusedforthisstatement附录四:SQL_ID4frhscrfz9gvb,childnumber0-------------------------------------insertintoia_monthledger(btryflag,caccountmonth,caccountyear,cinventoryid,cmonthledgerid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,ninmny,ninnum,ninvarymny,nmonthprice,noutmny,noutnum,noutvarymny,nplanedprice,nvariancerate,pk_corp,ts,vbatch)selectbtryflag,'01','2013',cinventoryid,keyid,crdcenterid,dr,fpricemodeflag,frecordtypeflag,nabmny,nabnum,nabprice,nabvarymny,null,null,null,null,null,null,null,nplanedprice,null,pk_corp,'2013-04-0114:05:19',vbatchfrom(selectig.*,rownumanumfromia_monthledgerigwhereig.caccountyear='2012'andig.caccountmonth='12'andig.frecordtypeflag=3andig.pk_corp='1002'and(ig.nabmny<>0orig.nabnum<>0orig.nabvarymny<>0)andig.dr=0andnotexists(select1fromia_monthinoutimwhereim.crdcenterid=ig.crdcenteridandim.cinventoryid=ig.cinventoryidandnvl(im.vbatch,'NULL')=nvl(ig.vbatch,'NULL')andim.caccountyear='201Planhashvalue:643633413------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|------------------------------------------------------------------------------------------------------------------|0|INSERTSTATEMENT|||||323K(100)|||*1|HASHJOIN||124M|25G|7120K|323K(1)|01:04:42|11|2|VIEW||155K|5301K||255(2)|00:00:04||3|COUNT||||||||4|TABLEACCESSFULL|T_IA_NUM001|155K|3332K||255(2)|00:00:04||5|VIEW||80129|14M||319K(1)|01:04:00||6|COUNT||||||||*7|FILTER||||||||*8|TABLEACCESSBYINDEXROWID|IA_MONTHLEDGER|80129|8451K||3376(1)|00:00:41||*9|INDEXRANGESCAN|I_IA_ML_MONTHCORP|120K|||475(2)|00:00:06||*10|TABLEACCESSBYINDEXROWID|IA_MONTHINOUT|1|74||4(0)|00:00:01||*11|INDEXRANGESCAN|I_IA_MONTHINOUT_JSZC01|1|||3(0)|00:00:01|------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("A"."ANUM"="B"."BNUM")7-filter(ISNULL)8-filter((("IG"."NABNUM"<>0OR"IG"."NABMNY"<>0OR"IG"."NABVARYMNY"<>0)AND"IG"."DR"=0))9-access("IG"."CACCOUNTMONTH"='12'AND"IG"."CACCOUNTYEAR"='2012'AND"IG"."PK_CORP"='1002'AND"IG"."FRECORDTYPEFLAG"=3)10-filter(("IM"."CRDCENTERID"=:B1ANDNVL("IM"."VBATCH",'NULL')=NVL(:B2,'NULL')AND"IM"."PK_CORP"='1002'AND"IM"."DR"=0))11-access("IM"."CINVENTORYID"=:B1AND"IM"."CACCOUNTYEAR"='2013'AND"IM"."CACCOUNTMONTH"='01')12Note------dynamicsamplingusedforthisstatement
