1/18用友股份-LE支持服务业务本部技术方案--《资源消耗问题汇总》建立日期：2013-07-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-00932/18文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-7-01郜耿志产品支持部V1.0版本领域模块备注3/18目录资源消耗问题汇总........................................................................................................4消耗资源过多且影响较大的问题...............................................................................4消耗资源过多且执行频繁的SQL................................................................................9其他SQL.......................................................................................................................134/18资源消耗问题汇总消耗资源过多且影响较大的问题1.问题1由图可知，p_default分区，占据了GlobalCacheBufferBusy的98%，所占比重相当高。问题分析：通过查询可知，p_default分区包含70个公司左右，数据量如下图：优化方案：bd_invmandoc表和bd_cumandoc表进行splitpartition操作5/182.问题2由图可知，占据大量内存，每个SQL大约消耗2M左右内存，语句如下：select*fromzz_hr_tbminfwherepsncode='1301190055'andtbmyear=2013andtbmmonth=06优化方案：改用绑定变量6/183.问题3存在问题：该操作中，共出现selectsum(casearap_djzb.djdlwhen'ys'thenbbyewhen'sk'then0-bbyewhen'yf'thenbbyeend)fromarap_djzb,arap_djfbwherearap_djfb.vouchid(+)=arap_djzb.vouchidand(arap_djzb.dwbm='1109'andarap_djfb.hbbm='0001A110000000763G3L'andarap_djfb.wldx=1andarap_djzb.dr=0andarap_djfb.dr=0)这个类似的SQL422次，其中唯一不同的就是红色标注出的值建议使用in查询，改为一个SQL，如下：selecthbbm,sum(casedjdlwhen'ys'thenbbyewhen'sk'then0-bbyewhen'yf'thenbbyeend)from(selectarap_djzb.djdldjdl,arap_djfb.bbyebbye,arap_djfb.hbbmhbbmfromarap_djzb,arap_djfbwherearap_djfb.vouchid(+)=arap_djzb.vouchid7/18and(arap_djzb.dwbm='1109'andarap_djfb.hbbmin('0001ZZ10000000000106',……'0001A1100000007UA9YV')andarap_djfb.wldx=1andarap_djzb.dr=0andarap_djfb.dr=0))agroupbyhbbm4.问题4由图可知，图中标红的SQL，消耗过多的资源，耗时非常长久，其对应SQL如下：selectpk_finindex,billcode,pk_sys,pk_proc,pk_busitype,businame,busidate,operator,dap_finindex.pk_corp,flag,currency,money,dapcomment,gl_voucher.pk_prepared,settlemode,docnum,docdate,pk_vouchentry,deltag,sign,pk_mergebatch,mergebatchcode,errmsg,checkstate,pk_rtvouch,gl_voucher.novoucherno,gl_voucher.prepareddatevoucherdate,billtypename,procmsg,sm_user.user_name,a.user_namepsnname,currtypename,gl_voucher.pk_vouchertypevouchertype,gl_voucher.totaldebittotaldebit,gl_voucher.totalcredittotalcredit,bd_vouchertype.vouchtypenamevouchtypename,dap_finindex.destsystem,dap_rtvouch.assinofromdap_finindex,sm_usera,bd_billtype,bd_currtype,gl_voucher,sm_user,bd_vouchertype,dap_rtvouchwheredap_finindex.operator=a.cUserId(+)anddap_finindex.pk_proc=bd_billtype.pk_billtypecode(+)anddap_finindex.currency=bd_currtype.pk_currtype(+)anddap_finindex.pk_vouchentry=gl_voucher.pk_voucher(+)andgl_voucher.pk_prepared=sm_user.cUserId(+)andgl_voucher.pk_vouchertype=bd_vouchertype.pk_vouchertype(+)anddap_finindex.pk_rtvouch=dap_rtvouch.pk_voucher(+)and(flag>=4and((pk_sys=:1andpk_proc=:2))anddap_finindex.busidate>=:3andgl_voucher.prepareddate>=:4anddap_finindex.currency=:5anddap_finindex.busidate<=:6andgl_voucher.prepareddate<=:7and(gl_voucher.drisnullorgl_voucher.dr=0)anddap_finindex.destsystem=0anddap_finindex.pk_glorg=:8anddap_finindex.pk_glbook=:9)orderbydap_finindex.billcode,voucherdate,vouchtypename,voucherno8/18优化方案：去除shared_pool中错误的执行计划，通过Outline固定正确的执行计划原始执行计划：优化后执行计划：9/18消耗资源过多且执行频繁的SQL问题SQL1.由图可知，图中标红的SQL，占据了绝大部分的逻辑读，其对应SQL如下：selecta.cbillid,a.fdispatchflag,a.vbillcode,a.dbilldate,a.vproducebatch,a.vbatch,a.nplanedmny,a.nassistnum,a.nsimulatemny,a.nplanedprice,a.vnote,a.nnumber,a.nprice,a.nmoney,a.nassistnum,a.bwithdrawalflag,a.pk_corp,a.crdcenterid,a.cwarehouseid,a.cbiztypeid,a.ccustomvendorid,a.cdeptid,a.cagentid,a.cemployeeid,a.cinventoryid,a.cprojectid,a.coperatorid,a.cdispatchid,a.castunitid,cb.bomname,a.cbilltypecode,10/18a.vsourcebillcode,a.vdef1,a.vdef2,a.vdef3,a.vdef4,a.vdef5,a.vdef6,a.vdef7,a.vdef8,a.vdef9,a.vdef10,a.vdef11,a.vdef12,a.vdef13,a.vdef14,a.vdef15,a.vdef16,a.vdef17,a.vdef18,a.vdef19,a.vdef20,a.bdef1,a.bdef2,a.bdef3,a.bdef4,a.bdef5,a.bdef6,a.bdef7,a.bdef8,a.bdef9,a.bdef10,a.bdef11,a.bdef12,a.bdef13,a.bdef14,a.bdef15,a.bdef16,a.bdef17,a.bdef18,a.bdef19,a.bdef20,a.vfree1,a.vfree2,a.vfree3,a.vfree4,a.vfree5,a.csumrtvouchid,a.cbill_bidfromv_ia_inoutledgera,(selectc.invcodebomcode,c.invnamebomname,d.pk_invmandocpk_invmandocfrombd_invbasdocc,bd_invmandocdwherec.pk_invbasdoc=d.pk_invbasdoc)cb,bd_busitypee,bd_measdocrwherea.vbomcode=cb.pk_invmandoc(+)anda.cbiztypeid=e.pk_busitype(+)anda.castunitid=r.pk_measdoc(+)and((a.iauditsequence>0)and(a.cdeptidisnullora.cdeptidin(((selectdistinctpower.resource_data_idfromsm_power_deptdocpower,sm_useru,sm_user_roleurolewhereu.cuserid='D83F315D5F063160E043'andurole.pk_role=power.pk_roleandpower.orgtypecode=1and((u.cuserid=urole.cuseridandurole.pk_corp='1409'and(iscommon_powerin('y','Y')orpower.pk_org='1409')))))))anda.cbilltypecodenotin('I0','I1','IB','IE','IF','IG')and(a.badjusteditemflag='N'ora.cbilltypecode<>'I9')anda.pk_corp='1409'anda.dbilldate>='2013-04-01'anda.dbilldate<='2013-04-30')orderbycasewheniauditsequence=-1then999999999elseiauditsequenceend优化方案：bd_invmandoc表添加PK_CORP查询条件优化前执行计划：解决办法：添加pk_corp字段，改后SQL：selecta.cbillid,……fromv_ia_inoutledgera,11/18(selectc.invcodebomcode,c.invnamebomname,d.pk_invmandocpk_invmandocfrombd_invbasdocc,bd_invmandocdwherec.pk_invbasdoc=d.pk_invbasdocandd.pk_corp='1391')cb,bd_busitypee,bd_measdocrwherea.vbomcode=cb.pk_invmandoc(+)anda.cbiztypeid=e.pk_busitype(+)anda.castunitid=r.pk_measdoc(+)and((a.crdcenterid='1391A11000000000CSOD')and(a.cdeptidisnullora.cdeptidin(((selectdistinctpower.resource_data_idfromsm_power_deptdocpower,sm_useru,sm_user_roleurolewhereu.cuserid='0001V510000000004F4N'andurole.pk_role=power.pk_roleandpower.orgtypecode=1and((u.cuserid=urole.cuseridandurole.pk_corp='1391'and(iscommon_powerin('y','Y')orpower.pk_org='1391')))))))anda.cbilltypecodenotin('I0','I1','IB','IE','IF','IG')and(a.badjusteditemflag='N'ora.cbilltypecode<>'I9')anda.pk_corp='1391'anda.dbilldate>='2013-04-01'anda.dbilldate<='2013-04-30')orderbycasewheniauditsequence=-1then999999999elseiauditsequenceend;优化后执行计划：12/18问题SQL2.由图可知，图中标红的SQL，占据了绝大部分的逻辑读，且根据监控，该SQL频繁执行，其对应SQL如下：SELECTrdosum.pk_corp,cwarehouseid,cinventoryid,nterminnum,nterminastnum,ntermingrossnum,ntermoutnum,ntermoutastnum,ntermoutgrossnumFROM(SELECTpk_corp,cwarehouseid,cinventoryid,SUM(nvl(nterminnum1,0.0))nterminnum,SUM(nvl(nterminastnum1,0.0))nterminastnum,SUM(nvl(ntermoutnum1,0.0))ntermoutnum,SUM(nvl(ntermoutastnum1,0.0))ntermoutastnum,SUM(ntermingrossnum1)ntermingrossnum,SUM(ntermoutgrossnum1)ntermoutgrossnumFROM((SELECTkp.pk_corp,kp.cwarehouseid,kp.cinventoryid,SUM(ninnum)nterminnum1,SUM(ninassistnum)nterminastnum1,0.0ntermoutnum1,0.0ntermoutastnum1,nvl(sum(ningrossnum),0.0)ntermingrossnum1,0.0ntermoutgrossnum1FROMic_keep_detail4kpWHERE(1=1)ANDkp.dbizdate>='2012-02-01'ANDkp.dbizdate<='2012-02-29'AND(cbilltypecodein(selectcbilltypecodefromic_billtypewherebmonthflag='Y'andctransactiontype<>'INITIAL'))andkp.pk_corpin('1289')AND(1=1)and(1=1)and(1=1)and(cwarehouseid='1289A11000000001LTSW')GROUPBYkp.pk_corp,kp.cwarehouseid,kp.cinventoryidHAVING(SUM(ninnum)ISNOTNULL))UNIONALL(SELECTkp.pk_corp,kp.cwarehouseid,kp.cinventoryid,0.0nterminnum1,0.0nterminastnum1,SUM(noutnum)ntermoutnum1,SUM(noutassistnum)ntermoutastnum1,0.0ntermingrossnum1,nvl(sum(noutgrossnum),0.0)ntermoutgrossnum1FROMic_keep_detail4kpWHERE(1=1)ANDkp.dbizdate>='2012-02-01'ANDkp.dbizdate<='2012-02-29'AND(cbilltypecodein(selectcbilltypecodefromic_billtypewherebmonthflag='Y'andctransactiontype<>'INITIAL'))andkp.pk_corpin('1289')AND(1=1)and(1=1)and(1=1)and(cwarehouseid='1289A11000000001LTSW')GROUPBYkp.pk_corp,kp.cwarehouseid,kp.cinventoryidHAVING(SUM(noutnum)ISNOTNULL)))rdoGROUPBYpk_corp,cwarehouseid,cinventoryid)rdosumORDERBYcwarehouseid,cinventoryid优化方案：13/18执行计划有误，重新收集IC_GENERAL_B表统计信息analyzetableIC_GENERAL_BcomputestatisticsforcolumnsDBIZDATEsize254;优化后该SQL从几十分钟到数S其他SQL5.SQL1:selectdistincta.invcode,a.invname,a.invspec,a.invtype,m.measname,p.pk_invmandocfrombd_invbasdoca,bd_measdocm,bd_invclb,bd_producep,ia_bill_bwherea.pk_measdoc=m.pk_measdocand(a.pk_invcl=b.pk_invclanda.pk_invbasdoc=p.pk_invbasdocandp.pk_corp='1536'andp.pk_calbody='1536A110000000001BU9'andp.pk_invmandoc=ia_bill_b.cinventoryidandia_bill_b.fpricemodeflag=5)orderbyinvcode优化前执行计划：解决办法：添加pk_corp字段，改后SQL：selectdistincta.invcode,a.invname,a.invspec,14/18a.invtype,m.measname,p.pk_invmandocfrombd_invbasdoca,bd_measdocm,bd_invclb,bd_producep,ia_bill_bwherea.pk_measdoc=m.pk_measdocand(a.pk_invcl=b.pk_invclanda.pk_invbasdoc=p.pk_invbasdocandp.pk_corp='1536'andp.pk_calbody='1536A110000000001BU9'andp.pk_invmandoc=ia_bill_b.cinventoryidandia_bill_b.pk_corp='1536'andia_bill_b.fpricemodeflag=5)orderbyinvcode;优化后执行计划：6.SQL2：selectinvcodefrombd_invbasdoca,bd_invmandocb,bd_producecwherea.pk_invbasdoc=b.pk_invbasdocandb.pk_invmandoc=c.pk_invmandocandb.pk_corp='1160'andc.pk_calbody='1160781000000001FSCM'and(c.sfpchs='N')orderbya.invcode;15/18解决办法：createindexi_bd_produce_jszc01onbd_produce(sfpchs)nologgingparallel;alterindexi_bd_produce_jszc01noparallel;7.SQL3:selectpo_praybill_b.crowno,……po_praybill_b.pk_purcorpfrompo_praybill_b,po_order_b,po_praybill,po_orderwherepo_praybill_b.pk_purcorp=po_order_b.pk_corpandpo_praybill_b.cpraybillid=po_praybill.cpraybillidandpo_praybill_b.cpraybill_bid=po_order_b.cupsourcebillrowidandpo_praybill_b.cpraybillid=po_order_b.cupsourcebillidandpo_order_b.corderid=po_order.corderidandpo_praybill_b.dr=0andpo_praybill_b.pk_corpin('1053')and(po_order.dr=0ANDnvl(po_order.bislatest,'Y')='Y')and(po_order_b.dr=0ANDpo_order_b.iisactive<>3)16/18andpo_order_b.blargess='N'and((po_praybill_b.cmangidin(selectpk_invmandocfrombd_invmandoc,bd_invbasdocwherebd_invmandoc.pk_invbasdoc=bd_invbasdoc.pk_invbasdocandbd_invbasdoc.invnamelike'%卡套%')))andpo_praybill_b.cpraybillidin('1053A11000000007NTSK','1053A11000000007WALL','1053A11000000006ZBYD','1053A110000000094U2V','1053A11000000007AY68','1053A11000000008HHOU','1053A11000000006K9FF','1053A110000000099K9W')orderbypo_praybill_b.cpraybill_bid;原始执行计划：处理办法：改子查询为内联视图，添加pk_corp字段优化后SQL：selectpo_praybill_b.crowno,……po_praybill_b.pk_purcorpfrompo_praybill_b,po_order_b,po_praybill,po_order,(selectpk_invmandocfrombd_invmandoc,bd_invbasdoc17/18wherebd_invmandoc.pk_invbasdoc=bd_invbasdoc.pk_invbasdocandbd_invbasdoc.invnamelike'%卡套%'andbd_invmandoc.pk_corpin('1053'))twherepo_praybill_b.pk_purcorp=po_order_b.pk_corpandpo_praybill_b.cpraybillid=po_praybill.cpraybillidandpo_praybill_b.cpraybill_bid=po_order_b.cupsourcebillrowidandpo_praybill_b.cpraybillid=po_order_b.cupsourcebillidandpo_order_b.corderid=po_order.corderidandpo_praybill_b.dr=0andpo_praybill_b.pk_corpin('1053')and(po_order.dr=0ANDnvl(po_order.bislatest,'Y')='Y')and(po_order_b.dr=0ANDpo_order_b.iisactive<>3)andpo_order_b.blargess='N'andpo_praybill_b.cmangid=t.pk_invmandocandpo_praybill_b.cpraybillidin('1053A11000000007NTSK','1053A11000000007WALL','1053A11000000006ZBYD','1053A110000000094U2V','1053A11000000007AY68','1053A11000000008HHOU','1053A11000000006K9FF','1053A110000000099K9W')orderbypo_praybill_b.cpraybill_bid;优化后执行计划：对应补丁：请购效率优化0507.rar18/188.SQL4：SELECTDISTINCThead.bassetcard,……head.vuserdef9FROMic_general_hhead,ic_general_bbodywherehead.cgeneralhid=body.cgeneralhidand(head.dr=0ANDbody.dr=0ANDhead.cbilltypecode='4D'and(body.ccorrespondcode='CR1207160103'ANDbody.bonroadflag='N')andhead.pk_corp='1042'and(1=1)AND((1=1)))优化前执行计划：--解决办法：--添加索引createindexi_ic_general_b_jszc01onic_general_b(ccorrespondcode)nologgingparallel5;alterindexi_ic_general_b_jszc01noparallel;优化后执行计划：
