1/11用友股份-LE支持服务业务本部技术方案--《SQL效率问题-动态采样参数》建立日期：2014-01-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2014-00072/11文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2014-01-20雷鹏技术支持部V1.0版本领域模块备注3/11目录SQL效率问题-动态采样参数...................................................................................4一、系统环境............................................................................................................4二、问题描述............................................................................................................4三、检查分析............................................................................................................4四、解决方法..........................................................................................................114/11SQL效率问题-动态采样参数注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！一、系统环境oracle11g,window2012二、问题描述科目余额表勾选未记账凭证，错误凭证后查询速度变慢，未勾选则查询非常快。三、检查分析此数据库存在多个问题，1、数据库存在本身是32位的，sga1G左右，buffer_cache命中率在94%。——更换64位数据库。5/112、sql本身未走最优执行计划。select/*+0.2319186739155601*/gl_voucher.pk_glorgbook,gl_detail.pk_accsubj,sum(gl_detail.debitquantity)debitquantitysum,sum(gl_detail.creditquantity)creditquantitysum,sum(gl_detail.debitamount)debitamountsum,sum(gl_detail.creditamount)creditamountsum,sum(gl_detail.fracdebitamount)fracdebitamountsum,sum(gl_detail.fraccreditamount)fraccreditamountsum,sum(gl_detail.localdebitamount)localdebitamountsum,sum(gl_detail.localcreditamount)localcreditamountsumfromgl_detailgl_detail,gl_vouchergl_voucher,tmptabsubjORAwheregl_detail.pk_accsubj=tmptabsubjORA.pk_accsubjandgl_voucher.year='2013'andgl_voucher.free1>='01'andgl_voucher.free1<='12'andgl_voucher.pk_glorgbook='0001V810000000000BQ1'andgl_detail.pk_glorgbook='0001V810000000000BQ1'andgl_detail.dr=0andgl_voucher.discardflag='N'andgl_voucher.dr=0andgl_voucher.voucherkind<>255andgl_voucher.pk_manager='N/A'andgl_detail.pk_voucher=gl_voucher.pk_vouchergroupbygl_voucher.pk_glorgbook,gl_detail.pk_accsubjorderbygl_voucher.pk_glorgbook,gl_detail.pk_accsubj原因gl_voucher，gl_detail表的dr=1的垃圾数据太多，dr=1的占全部的一半以上。6/113、在清理完dr=1的垃圾数据后，sql执行计划已经改变，执行时间从原来的60秒减少到20秒。但执行计划仍然不是最优。调整optimizer_dynamic_sampling,由4调整到2，执行时间减少到4秒左右。参考：原sql：select/*+0.2319186739155601*/gl_voucher.pk_glorgbook,gl_detail.pk_accsubj,sum(gl_detail.debitquantity)debitquantitysum,sum(gl_detail.creditquantity)creditquantitysum,sum(gl_detail.debitamount)debitamountsum,sum(gl_detail.creditamount)creditamountsum,sum(gl_detail.fracdebitamount)fracdebitamountsum,sum(gl_detail.fraccreditamount)fraccreditamountsum,sum(gl_detail.localdebitamount)localdebitamountsum,sum(gl_detail.localcreditamount)localcreditamountsumfromgl_detailgl_detail,gl_vouchergl_voucher,tmptabsubjORAwheregl_detail.pk_accsubj=tmptabsubjORA.pk_accsubjandgl_voucher.year='2013'andgl_voucher.free1>='01'andgl_voucher.free1<='12'andgl_voucher.pk_glorgbook='0001V810000000000BQ1'andgl_detail.pk_glorgbook='0001V810000000000BQ1'andgl_detail.dr=0andgl_voucher.discardflag='N'andgl_voucher.dr=0andgl_voucher.voucherkind<>255andgl_voucher.pk_manager='N/A'andgl_detail.pk_voucher=gl_voucher.pk_vouchergroupbygl_voucher.pk_glorgbook,gl_detail.pk_accsubjorderbygl_voucher.pk_glorgbook,gl_detail.pk_accsubj7/11正在执行的sql的执行计划查看方法：selects.sid,s.serial#,w.event,s.LAST_CALL_ET,s.STATUS,s.CLIENT_INFO,q.SQL_TEXT,q.HASH_VALUEfromv$session_waitw,v$sessions,v$sqlqwherew.SID=s.SIDandq.HASH_VALUE=s.SQL_HASH_VALUEandw.eventnotlike'%messagefromclient%'orderbylast_call_etdesc;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR('793785125',0))用nmc查看，sql执行时间在20秒左右执行计划：1HASH_VALUE3745760608,childnumber02--------------------------------------3select/*+0.5745992196903358*/gl_voucher.pk_glorgbook,4gl_detail.pk_accsubj,sum(gl_detail.debitquantity)debitquantitysum,5sum(gl_detail.creditquantity)creditquantitysum,sum(6gl_detail.debitamount)debitamountsum,sum(gl_detail.creditamount)7creditamountsum,sum(gl_detail.fracdebitamount)fracdebitamountsum,8sum(gl_detail.fraccreditamount)fraccreditamountsum,sum(9gl_detail.localdebitamount)localdebitamountsum,sum(10gl_detail.localcreditamount)localcreditamountsumfromgl_detail11gl_detail,gl_vouchergl_voucher,tmptabsubjORAwhere12gl_detail.pk_accsubj=tmptabsubjORA.pk_accsubjandgl_voucher.year=13'2013'andgl_voucher.free1>='01'andgl_voucher.free1<='12'and14gl_voucher.pk_glorgbook='0001V810000000000BQ1'and15gl_detail.pk_glorgbook='0001V810000000000BQ1'andgl_detail.dr=016andgl_voucher.discardflag='N'andgl_voucher.dr=0and8/1117gl_voucher.voucherkind<>255andgl_voucher.pk_manager='N/A'and18gl_detail.pk_voucher=gl_voucher.pk_vouch1920Planhashvalue:35822014802122-------------------------------------------------------------------------------------------------23|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|24-------------------------------------------------------------------------------------------------25|0|SELECTSTATEMENT||||814(100)||26|1|SORTGROUPBY||341|62403|814(1)|00:00:10|27|*2|HASHJOIN||341|62403|813(1)|00:00:10|28|*3|HASHJOIN||2032|222K|795(1)|00:00:10|29|4|TABLEACCESSFULL|TMPTABSUBJORA|910|20020|7(0)|00:00:01|30|*5|TABLEACCESSBYINDEXROWID|GL_DETAIL|92730|8150K|787(1)|00:00:10|31|*6|INDEXRANGESCAN|I_GL_DETAIL_4|8061||16(0)|00:00:01|32|*7|TABLEACCESSBYINDEXROWID|GL_VOUCHER|15102|1047K|18(0)|00:00:01|33|*8|INDEXSKIPSCAN|I_GL_VOUCHER_6|46||6(0)|00:00:01|34-------------------------------------------------------------------------------------------------3536PredicateInformation(identifiedbyoperationid):37---------------------------------------------------38392-access("GL_DETAIL"."PK_VOUCHER"="GL_VOUCHER"."PK_VOUCHER")403-access("GL_DETAIL"."PK_ACCSUBJ"="TMPTABSUBJORA"."PK_ACCSUBJ")415-filter("GL_DETAIL"."DR"=0)426-access("GL_DETAIL"."PK_GLORGBOOK"='0001V810000000000BQ1')9/11437-filter(("GL_VOUCHER"."DR"=0AND"GL_VOUCHER"."DISCARDFLAG"='N'AND44"GL_VOUCHER"."VOUCHERKIND"<>255))458-access("GL_VOUCHER"."PK_GLORGBOOK"='0001V810000000000BQ1'AND46"GL_VOUCHER"."YEAR"='2013'AND"GL_VOUCHER"."FREE1">='01'AND47"GL_VOUCHER"."PK_MANAGER"='N/A'AND"GL_VOUCHER"."FREE1"<='12')48filter("GL_VOUCHER"."PK_MANAGER"='N/A')4950Note51-----52-dynamicsamplingusedforthisstatement(level=4)53调整动态采样因子：optimizer_dynamic_sampling=2执行计划有变化，用nmc查看执行时间也降低到4秒左右。1HASH_VALUE2554892977,childnumber02--------------------------------------3select/*+0.5410913264031737*/gl_voucher.pk_glorgbook,4gl_detail.pk_accsubj,sum(gl_detail.debitquantity)debitquantitysum,5sum(gl_detail.creditquantity)creditquantitysum,sum(6gl_detail.debitamount)debitamountsum,sum(gl_detail.creditamount)7creditamountsum,sum(gl_detail.fracdebitamount)fracdebitamountsum,8sum(gl_detail.fraccreditamount)fraccreditamountsum,sum(9gl_detail.localdebitamount)localdebitamountsum,sum(10gl_detail.localcreditamount)localcreditamountsumfromgl_detail11gl_detail,gl_vouchergl_voucher,tmptabsubjORAwhere12gl_detail.pk_accsubj=tmptabsubjORA.pk_accsubjandgl_voucher.year=13'2013'andgl_voucher.free1>='01'andgl_voucher.free1<='12'and14gl_voucher.pk_glorgbook='0001V810000000000BQ1'and15gl_detail.pk_glorgbook='0001V810000000000BQ1'andgl_detail.dr=016andgl_voucher.discardflag='N'andgl_voucher.dr=0and10/1117gl_voucher.voucherkind<>255andgl_voucher.pk_manager='N/A'and18gl_detail.pk_voucher=gl_voucher.pk_vouch1920Planhashvalue:4266972042122--------------------------------------------------------------------------------------------------23|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|24--------------------------------------------------------------------------------------------------25|0|SELECTSTATEMENT||||26(100)||26|1|SORTGROUPBY||1|171|26(8)|00:00:01|27|*2|HASHJOIN||1|171|25(4)|00:00:01|28|3|NESTEDLOOPS||||||29|4|NESTEDLOOPS||4|596|17(0)|00:00:01|30|*5|TABLEACCESSBYINDEXROWID|GL_VOUCHER|4|284|11(0)|00:00:01|31|*6|INDEXSKIPSCAN|I_GL_VOUCHER_6|14||7(0)|00:00:01|32|*7|INDEXRANGESCAN|I_GL_DETAIL_1|4||1(0)|00:00:01|33|*8|TABLEACCESSBYINDEXROWID|GL_DETAIL|1|78|2(0)|00:00:01|34|9|TABLEACCESSFULL|TMPTABSUBJORA|1051|23122|7(0)|00:00:01|35--------------------------------------------------------------------------------------------------3637PredicateInformation(identifiedbyoperationid):38---------------------------------------------------39402-access("GL_DETAIL"."PK_ACCSUBJ"="TMPTABSUBJORA"."PK_ACCSUBJ")415-filter(("GL_VOUCHER"."DR"=0AND"GL_VOUCHER"."DISCARDFLAG"='N'AND42"GL_VOUCHER"."VOUCHERKIND"<>255))11/11436-access("GL_VOUCHER"."PK_GLORGBOOK"='0001V810000000000BQ1'AND44"GL_VOUCHER"."YEAR"='2013'AND"GL_VOUCHER"."FREE1">='01'AND45"GL_VOUCHER"."PK_MANAGER"='N/A'AND"GL_VOUCHER"."FREE1"<='12')46filter("GL_VOUCHER"."PK_MANAGER"='N/A')477-access("GL_DETAIL"."PK_VOUCHER"="GL_VOUCHER"."PK_VOUCHER")488-filter(("GL_DETAIL"."DR"=0AND"GL_DETAIL"."PK_GLORGBOOK"='0001V810000000000BQ1'))4950Note51-----52-dynamicsamplingusedforthisstatement(level=2)四、解决方法清理垃圾数据，调整动态采用因子为2。在外表行数不多，内表有索引的条件下，nestloop的效率会比较hashjoin要高。
