1/9用友股份-LE支持服务业务本部技术方案--《NC57结账操作缓慢问题解决案例》建立日期：2013-10-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-01222/9文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2013-10-20李楠技术支持部V1.0版本领域模块备注NC573/9目录NC57结账操作缓慢问题解决案例..........................................................................4一.系统环境...............................................................................................................4二.问题描述.............................................................................................................4三.检查分析.............................................................................................................4四.解决方法.............................................................................................................94/9NC57结账操作缓慢问题解决案例一.系统环境NC57Oracle二.问题描述结账操作大约40分钟没出来三.检查分析Sql语句如下：SELECTDISTINCT(voucher.pk_voucher),voucher.pk_glorgbook,voucher.prepareddate,voucher.NO,voucher.pk_vouchertype,voucher.pk_prepared,voucher.totalcredit,voucher.totaldebit,voucher.explanationFROMgl_vouchervoucherINNERJOINgl_detaildetailONvoucher.pk_voucher=detail.pk_voucherANDvoucher.pk_glorgbook='00011110000000003XIS'ANDdetail.pk_glorgbook='00011110000000003XIS'INNERJOINbd_accsubjsubjONdetail.pk_accsubj=subj.pk_accsubjINNERJOINgl_reconcilesubjrecsubjON(recsubj.isonlyinnercust='Y'and(subj.subjcodeLIKETRIM(recsubj.anothersubjcode)||'%'ORsubj.subjcodeLIKETRIM(recsubj.onesubjcode)||'%')ANDsubj.pk_glorgbook='00011110000000003XIS')INNERJOINbd_cumandocmanONdetail.pk_glorgbook='00011110000000003XIS'ANDman.pk_corp='1020'5/9INNERJOINgl_freevaluefreeONdetail.assid=free.freevalueidINNERJOINbd_cubasdocbasONfree.checkvalue=bas.pk_cubasdocANDbas.pk_cubasdoc=man.pk_cubasdocANDfree.checktypeIN('00010000000000000071','00010000000000000072','00010000000000000073')WHEREvoucher.pk_glorgbook='00011110000000003XIS'ANDdetail.pk_glorgbook='00011110000000003XIS'AND((man.cooperateflag=recsubj.isonlyinnercustANDrecsubj.isonlyinnercust='Y'ANDbas.pk_corp1ISNOTNULLANDbas.custprop<>0ANDbas.pk_corp1IN(SELECTorg.pk_entityorgFROMbd_glorgorgINNERJOINbd_glorgbookotherONother.pk_glorg=org.pk_glorgINNERJOINbd_glorgbookSELFONSELF.pk_glorgbook='00011110000000003XIS'WHERE(recsubj.reconcilebookISNULLANDother.TYPE=0)OR(recsubj.reconcilebookISNOTNULLANDSELF.prop=recsubj.reconcilebookANDother.prop=recsubj.reconcilebook))))ANDman.pk_corp='1020'AND((voucher.prepareddate>=recsubj.startdateANDrecsubj.startdateISNOTNULL)OR(recsubj.startdateISNULL))andvoucher.pk_glorgbookin('00011110000000003XIS')andvoucher.free1='07'andvoucher.year='2013'andvoucher.pk_sourcepkisnullandvoucher.dr=0andvoucher.discardflag='N'andvoucher.voucherkind<>2andvoucher.errmessageisnullanddetail.busireconnoisnullandnotexists(selectpk_detailselffromgl_reconoccursubwheredetail.pk_detail=gl_reconoccursub.pk_detailself)andnotexists(selectpk_detailotherfromgl_reconoccursubwheredetail.pk_detail=gl_reconoccursub.pk_detailother)6/9orderbyvoucher.PK_glorgbook------Planhashvalue:896375273------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|668|141(2)|00:00:02||1|HASHUNIQUE||1|668|141(2)|00:00:02||*2|HASHJOIN||1|668|140(1)|00:00:02||3|NESTEDLOOPSANTI||1|590|136(0)|00:00:02||4|NESTEDLOOPSANTI||1|569|135(0)|00:00:02||5|NESTEDLOOPS||1|548|134(0)|00:00:02||6|NESTEDLOOPS||1|498|132(0)|00:00:02||7|NESTEDLOOPS||3|1047|126(0)|00:00:02||8|NESTEDLOOPS||1|213|73(0)|00:00:01||9|NESTEDLOOPS||1|150|67(0)|00:00:01||10|NESTEDLOOPS||1|122|64(0)|00:00:01||11|NESTEDLOOPS||1|86|63(0)|00:00:01||12|NESTEDLOOPS||1|60|62(0)|00:00:01||13|TABLEACCESSBYINDEXROWID|BD_GLORGBOOK|1|34|1(0)|00:00:01||*14|INDEXUNIQUESCAN|PK_BD_GLORGBOOK|1||0(0)|00:00:01||*15|TABLEACCESSBYINDEXROWID|BD_CUBASDOC|1|26|61(0)|00:00:01|7/9|*16|INDEXFULLSCAN|I_BD_CUBASDOC_5|99||1(0)|00:00:01||17|TABLEACCESSBYINDEXROWID|BD_GLORG|1|26|1(0)|00:00:01||*18|INDEXRANGESCAN|I_BD_GLORG|1||0(0)|00:00:01||19|TABLEACCESSBYINDEXROWID|BD_GLORGBOOK|1|36|1(0)|00:00:01||*20|INDEXRANGESCAN|I_BD_GLORGBOOK_1|1||0(0)|00:00:01||*21|TABLEACCESSBYINDEXROWID|BD_CUMANDOC|1|28|3(0)|00:00:01||*22|INDEXRANGESCAN|I_BD_CUMANDOC_1|1||2(0)|00:00:01||23|INLISTITERATOR|||||||24|TABLEACCESSBYINDEXROWID|GL_FREEVALUE|2|126|6(0)|00:00:01||*25|INDEXRANGESCAN|I_GL_FREEVALUE2|2||4(0)|00:00:01||*26|TABLEACCESSBYINDEXROWID|GL_DETAIL|2|272|53(0)|00:00:01||*27|INDEXRANGESCAN|I_GL_DETAIL_3|59||3(0)|00:00:01||*28|TABLEACCESSBYINDEXROWID|GL_VOUCHER|1|149|2(0)|00:00:01||*29|INDEXUNIQUESCAN|PK_GL_VOUCHER|1||1(0)|00:00:01||*30|TABLEACCESSBYINDEXROWID|BD_ACCSUBJ|1|50|2(0)|00:00:01||*31|INDEXUNIQUESCAN|PK_BD_ACCSUBJ|1||1(0)|00:00:01||*32|INDEXRANGESCAN|I_RECSUB_OTHERD|10467|214K|1(0)|00:00:01||*33|INDEXRANGESCAN|I_RECSUB_SELFD|13423|275K|1(0)|00:00:01||*34|TABLEACCESSFULL|GL_RECONCILESUBJ|39|3042|3(0)|00:00:01|------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------8/92-access("MAN"."COOPERATEFLAG"="RECSUBJ"."ISONLYINNERCUST")filter(("VOUCHER"."PREPAREDDATE">="RECSUBJ"."STARTDATE"AND"RECSUBJ"."STARTDATE"ISNOTNULLOR"RECSUBJ"."STARTDATE"ISNULL)AND("SUBJ"."SUBJCODE"LIKETRIM("RECSUBJ"."ANOTHERSUBJCODE")||'%'OR"SUBJ"."SUBJCODE"LIKETRIM("RECSUBJ"."ONESUBJCODE")||'%')AND("RECSUBJ"."RECONCILEBOOK"ISNULLAND"OTHER"."TYPE"=0OR"SELF"."PROP"="RECSUBJ"."RECONCILEBOOK"AND"OTHER"."PROP"="RECSUBJ"."RECONCILEBOOK"AND"OTHER"."PROP"ISNOTNULLAND"RECSUBJ"."RECONCILEBOOK"ISNOTNULLAND"SELF"."PROP"ISNOTNULL))14-access("SELF"."PK_GLORGBOOK"='00011110000000003XIS')15-filter("BAS"."CUSTPROP"<>0)16-filter("BAS"."PK_CORP1"ISNOTNULL)18-access("BAS"."PK_CORP1"="ORG"."PK_ENTITYORG")20-access("OTHER"."PK_GLORG"="ORG"."PK_GLORG")21-filter("MAN"."COOPERATEFLAG"='Y')22-access("MAN"."PK_CORP"='1020'AND"BAS"."PK_CUBASDOC"="MAN"."PK_CUBASDOC")25-access("FREE"."CHECKVALUE"="BAS"."PK_CUBASDOC"AND("FREE"."CHECKTYPE"='00010000000000000071'OR"FREE"."CHECKTYPE"='00010000000000000072'OR"FREE"."CHECKTYPE"='00010000000000000073'))26-filter("DETAIL"."PK_GLORGBOOK"='00011110000000003XIS'AND"DETAIL"."BUSIRECONNO"ISNULL)27-access("DETAIL"."ASSID"="FREE"."FREEVALUEID")filter("DETAIL"."ASSID"ISNOTNULL)28-filter("VOUCHER"."FREE1"='07'AND"VOUCHER"."DR"=0AND"VOUCHER"."PK_GLORGBOOK"='00011110000000003XIS'AND"VOUCHER"."YEAR"='2013'AND"VOUCHER"."ERRMESSAGE"ISNULLAND"VOUCHER"."PK_SOURCEPK"ISNULLAND"VOUCHER"."VOUCHERKIND"<>2AND"VOUCHER"."DISCARDFLAG"='N')29-access("VOUCHER"."PK_VOUCHER"="DETAIL"."PK_VOUCHER")30-filter("SUBJ"."PK_GLORGBOOK"='00011110000000003XIS')31-access("DETAIL"."PK_ACCSUBJ"="SUBJ"."PK_ACCSUBJ")32-access("DETAIL"."PK_DETAIL"="GL_RECONOCCURSUB"."PK_DETAILOTHER")33-access("DETAIL"."PK_DETAIL"="GL_RECONOCCURSUB"."PK_DETAILSELF")34-filter("RECSUBJ"."ISONLYINNERCUST"='Y')9/9该sql的执行计划看起来不错，cost和执行时间都理想，但是实际执行时间非常长。原因在于这种内连接很多的sql，连接顺序对于性能的影响非常大。从执行计划中可见到该连接从几个小表开始，而gl_voucher,gl_detail这两个大表在最后，这会导致过滤性很差。四.解决方法为该语句增加hint/*+leading(voucher,detail)*/。通过leading可以改变多表关联时的执行次序，将最大的两个表放在前面执行可大幅提高语句的执行效率。
