1/17用友股份-LE支持服务业务本部技术方案--《SQL优化案例分析》建立日期：2013-10-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-01392/17文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2013-10-20郜耿志技术支持部V1.0版本领域模块备注3/17目录SQL优化案例分析....................................................................................................4一、问题1................................................................................................................4二、问题2................................................................................................................7三、问题3..............................................................................................................10四、问题4..............................................................................................................11五、问题5..............................................................................................................14六、问题6..............................................................................................................164/17SQL优化案例分析一、问题1问题背景：原始16G物理内存，客户扩容到64G，SGA参数无法调大，调整lgpg_regions与lgpg_size参数后，SGA参数可以正常调大问题分析：怀疑只是分配了大内存，没有使用，检查svmon可知，大内存果然没有使用5/17使用16M内存页面1).在操作系统执行如下命令：#vmo-pov_pinshm=1#vmo-pomaxpin%=80（根据实际需要来设置）#chusercapabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATEoracle（使用lsuser-acapabilitiesoracle命令可以查看oracle用户设置的此属性）#vmo-polgpg_size=16777216-olgpg_regions=64（16777216=16M*64=1024M内存，lgpg_size根据实际的内存和数据库实例SGA的大小来设置）Settinglgpg_sizeto16777216innextbootfileSettinglgpg_regionsto64innextbootfileWarning:althoughthechangeconcernsadynamic...optimallyrestorethesesettingsatreboot（建议做bosboot,并且重新启动操作系统）Settinglgpg_sizeto16777216Settinglgpg_regionsto64#bosboot-a6/17注意：OracleDatabase10.2.0.4及其以上的版本必须完成chusercapabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATEoracle命令对oracle操作系统用户属性的设置，如果不设置可能导致出现如下文章中的问题：http://space.itpub.net/23135684/viewspace-631065运行下面的命令可以去除16M的页面设置：#vmo-polgpg_size=0-olgpg_regions=02).需要修改oracle用户的profile文件#exportORACLE_SGA_PGSZ=16MOracle10.2.0.4,需要设置这个参数，参见metalinkDOCID:NOTE:728444.13).需要修改数据库参数执行以下的SQL语句，主要设置LOCK_SGA为true：SQL>altersystemsetpre_page_sga=truescope=spfile;SQL>altersystemsetlock_sga=truescope=spfile;将LOCK_SGA设置为TRUE会触发Oracle数据库实例使用LargePageMemory，设置之后重启数据库实例。使用16M内存页面之前<注意红色标注出的地方>：7/17使用16M内存页面之后<注意红色标注出的地方>：二、问题2SQL>selectdistinctcmp_settlement.pk_settlement,2cmp_settlement.pk_tradetype,3cmp_settlement.busi_billdate,4cmp_settlement.billcode,5cmp_settlement.ts6fromcmp_detail,cmp_settlement7wherecmp_settlement.pk_settlement=cmp_detail.pk_settlement8and(cmp_detail.pk_corp='1402'andcmp_settlement.pk_corp='1402'and8/179cmp_settlement.pk_tradetype<>'DR'andcmp_settlement.dr=0and10cmp_detail.dr=0andcmp_settlement.busistatus=3and11(cmp_settlement.def19isnullAND12cmp_detail.pk_currtype='00010000000000000001'AND13cmp_detail.def3like'%PP011107000148%'))14orderbycmp_settlement.busi_billdate,15cmp_settlement.billcode;--原始执行计划9/17通过执行计划，未能看出为哈逻辑读和物理读如此高，通过trace文件分析，如下图所示：--所做优化：createindexi_cmp_settlement_jszc02oncmp_settlement(PK_CORP,SETTLESTATUS,busistatus)parallel4;alterindexi_cmp_settlement_jszc02noparallel;dropindexI_CMPSETTLE_JSZC_01;--优化后的trace文件，如下图所示：10/17三、问题3selectdistinctbd_cubasdoc.custcode,……frombd_cubasdoc,bd_cumandocwherebd_cumandoc.pk_cubasdoc=bd_cubasdoc.pk_cubasdocand((bd_cumandoc.pk_corp='1985'AND(bd_cumandoc.custflag='0'ORbd_cumandoc.custflag='1'ORbd_cumandoc.custflag='2')andbd_cumandoc.def3='11')and(bd_cumandoc.sealflagisnullAND(frozenflag='N'orfrozenflagisnull))and(bd_cubasdoc.pk_areacl='0001A1100000000ABJM4'))orderbybd_cubasdoc.custcode;--原始执行计划：---所做优化：11/17analyzetablebd_cubasdoccomputestatisticsfortableforcolumnspk_areaclsize254;--优化后执行计划：四、问题4#sqlstatck:totalcosttime=230097totalnumberofSql=1totalconnectionused=1#TheSql0:SELECTf.settlecentername公司名称,banktypename银行类别,substr(b.billdate,1,7)月份,sum(b.receive)金额,count(*)笔数FROMcmp_busibilla,(SELECTDISTINCTpk_corppk_corp1,unitcodesettlecenternameFROMbd_corp)f,cmp_detailb,bd_bankaccbasc,bd_banktyped,cmp_settlementewhere(a.dwbm=f.pk_corp1)and(a.djdl='sj'ANDnvl(b.settlestatus,'-1')='5'12/17ANDa.vouchid=e.pk_busibillANDb.pk_settlement=e.pk_settlementANDb.pk_account=c.pk_bankaccbasANDc.pk_banktype=d.pk_banktypeANDsubstr(b.billdate,1,7)='2013-08')groupbyf.settlecentername,banktypename,substr(b.billdate,1,7)orderbyf.settlecentername,banktypename,substr(b.billdate,1,7)costtime=230097readnum=0status=running--解决办法：办法一：创建索引：createindexi_cmp_detail_jszc02oncmp_detail(substr(billdate,1,7))nologgingparallel2;alterindexi_cmp_detail_jszc02noparallel;优化后执行计划：13/17办法二：修改SQL--优化后执行计划14/17五、问题5SELECTdistincta.zyx22contract,a.zy,a.bzbm,a.shrq,a.paydate,a.payflag,a.djzt,f.webnodecode,a.djlxbm,a.ts,a.djdl,a.spzt,a.pk_jkbx,a.djbh,e.djlxmc,a.totalybje,a.hkybjehkybje,b.user_nameshr,bb.psnnamejkbxr,c.unitname,cc.unitnamezfdwbm,a.djrq,g.custnamecustnameFROMer_bxzba,bd_psndocbb,er_djlxe,bd_billtypef,bd_cubasdocg,sm_userb,bd_corpc,bd_corpccwherebb.pk_psndoc=a.jkbxrande.djlxbm=a.djlxbmANDe.dwbm='1001'anda.djlxbm=f.pk_billtypecodeanda.hbbm=15/17g.pk_cubasdoc(+)andb.cuserid(+)=a.shrandc.pk_corp(+)=a.pk_corpandcc.pk_corp(+)=a.zfdwbmand(((pk_jkbxin('1001AL10000000015QT6','1001AL10000000015PJK','1001AL10000000015FEU','1001AL10000000015FFC','1001AL10000000015QA3',……'1001AL10000000000MA6')))ANDnvl(a.dr,0)=0AND1=1ANDqcbz<>'Y')ORDERBYa.tsDESC注：红色……省略若干行优化前执行计划：解决办法：in中条件，改为临时表--优化后执行计划（耗时不到1S）16/17六、问题617/17通过查看执行计划可知：红色标注处rows为1，有可能统计信息被收集，查看该表的统计信息：由结果可知，统计信息被收集，临时表的统计信息不应被收集，故删除统计信息，如下图：--优化方案：删除统计信息删除后执行那个业务正常，效率从以前的10分钟提高到2S
