1/13用友股份-LE支持服务业务本部技术方案--《NC61反结账操作速度慢》建立日期：2013-07-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-00682/13文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-7-01冯建民产品支持部V1.0版本领域模块备注3/13目录反结账操作速度慢........................................................................................................4一、系统环境................................................................................................................4二、问题描述................................................................................................................4三、检查分析................................................................................................................4四、解决方法..............................................................................................................134/13NC61反结账操作速度慢一、系统环境NC61；NC中单件；ORACLE11G；二、问题描述重新安装数据库后，反结账操作时间特别长三、检查分析监控发现有问题的SQL语句：updateia_generalnabglsetts='2013-07-0315:00:36',nmonthprice=null,(nabmny,nabnum,nabvarymny,nabplanedmny,nabglobalmny,nabgroupmny,nabprice)=(selectgl.nabmny-dl.nabmny,gl.nabnum-dl.nabnum,gl.nabvarymny-dl.nabvarymny,gl.nabplanedmny-dl.nabplanedmny,gl.nabglobalmny-dl.nabglobalmny,gl.nabgroupmny-dl.nabgroupmny,casewhengl.nabnum-dl.nabnum=0thennullelse5/13round((gl.nabmny-dl.nabmny)/(gl.nabnum-dl.nabnum),6)endfrom(selectdl.pk_org,dl.cinventoryid,dl.ccalcrangeid,sum(nvl(casewhendl.fdispatchflag=0thendl.nnumelse-dl.nnumend,0))nabnum,sum(nvl(casewhendl.fdispatchflag=0thendl.nmnyelse-dl.nmnyend,0))nabmny,sum(nvl(casewhendl.fdispatchflag=0thendl.nvarymnyelse-dl.nvarymnyend,0))nabvarymny,sum(nvl(casewhendl.fdispatchflag=0thendl.nplanedmnyelse-dl.nplanedmnyend,0))nabplanedmny,sum(nvl(casewhendl.fdispatchflag=0thendl.nglobalmnyelse-dl.nglobalmnyend,0))nabglobalmny,sum(nvl(casewhendl.fdispatchflag=0thendl.ngroupmny6/13else-dl.ngroupmnyend,0))nabgroupmnyfromia_detailledgerdl,TEMP_PUBAPP_2IDtempwheredl.cbilltypecodenotin('IG','IF','IE')and(dl.fintransitflagin(-1,0))anddl.fdatagetflagnotin(8,12,11,13)anddl.iauditsequence>=0anddl.dr=0anddl.pk_group='0001O1100000000004R2'anddl.pk_book='1002O110000000000HM6'anddl.pk_org='1002O110000000000HMT'anddl.cinventoryid=temp.id1anddl.ccalcrangeid=temp.id2anddl.caccountperiod='2013-06'groupbydl.pk_org,dl.cinventoryid,dl.ccalcrangeid)dlwheregl.dr=0andgl.pk_org=dl.pk_organdgl.cinventoryid=dl.cinventoryidandgl.ccalcrangeid=dl.ccalcrangeidandgl.pk_group='0001O1100000000004R2'andgl.pk_book='1002O110000000000HM6')wheregl.dr=0andgl.pk_group='0001O1100000000004R2'andgl.pk_book='1002O110000000000HM6'and(gl.pk_org,gl.cinventoryid,gl.ccalcrangeid)in(selectdl.pk_org,dl.cinventoryid,dl.ccalcrangeidfrom(selectdl.pk_org,dl.cinventoryid,dl.ccalcrangeid,sum(nvl(casewhendl.fdispatchflag=0thendl.nnumelse-dl.nnumend,0))nabnum,sum(nvl(casewhendl.fdispatchflag=0thendl.nmnyelse-dl.nmnyend,7/130))nabmny,sum(nvl(casewhendl.fdispatchflag=0thendl.nvarymnyelse-dl.nvarymnyend,0))nabvarymny,sum(nvl(casewhendl.fdispatchflag=0thendl.nplanedmnyelse-dl.nplanedmnyend,0))nabplanedmny,sum(nvl(casewhendl.fdispatchflag=0thendl.nglobalmnyelse-dl.nglobalmnyend,0))nabglobalmny,sum(nvl(casewhendl.fdispatchflag=0thendl.ngroupmnyelse-dl.ngroupmnyend,0))nabgroupmnyfromia_detailledgerdl,TEMP_PUBAPP_2IDtempwheredl.cbilltypecodenotin('IG','IF','IE')and(dl.fintransitflagin(-1,0))anddl.fdatagetflagnotin(8,12,11,13)anddl.iauditsequence>=0anddl.dr=0anddl.pk_group='0001O1100000000004R2'anddl.pk_book='1002O110000000000HM6'anddl.pk_org='1002O110000000000HMT'anddl.cinventoryid=temp.id1anddl.ccalcrangeid=temp.id2anddl.caccountperiod='2013-06'groupbydl.pk_org,dl.cinventoryid,dl.ccalcrangeid)dl);8/13------------------------------1Planhashvalue:229156647223-------------------------------------------------------------------------------------------------4|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|5-------------------------------------------------------------------------------------------------6|0|UPDATESTATEMENT||1|188|34267(1)|00:06:52|7|1|UPDATE|IA_GENERALNAB|||||8|2|NESTEDLOOPS||||||9|3|NESTEDLOOPS||1|188|4286(1)|00:00:52|10|4|VIEW||1|36|4284(1)|00:00:52|11|5|SORTGROUPBY||1|238|4284(1)|00:00:52|12|*6|HASHJOIN||1|238|4283(1)|00:00:52|13|7|TABLEACCESSFULL|TEMP_PUBAPP_2ID|1|44|2(0)|00:00:01|14|*8|TABLEACCESSFULL|IA_DETAILLEDGER|8773|1662K|4281(1)|00:00:52|15|*9|INDEXRANGESCAN|I_IA_GEN_INV|1||1(0)|00:00:01|16|*10|TABLEACCESSBYINDEXROWID|IA_GENERALNAB|1|152|2(0)|00:00:01|17|11|VIEW||1|114|4283(1)|00:00:52|18|12|SORTGROUPBY||1|238|4283(1)|00:00:52|19|*13|FILTER||||||20|*14|HASHJOIN||1|238|4283(1)|00:00:52|21|15|TABLEACCESSFULL|TEMP_PUBAPP_2ID|1|44|2(0)|00:00:01|22|*16|TABLEACCESSFULL|IA_DETAILLEDGER|1|194|4280(1)|00:00:52|9/1323-------------------------------------------------------------------------------------------------2425PredicateInformation(identifiedbyoperationid):26---------------------------------------------------27286-access("DL"."CINVENTORYID"="TEMP"."ID1"AND"DL"."CCALCRANGEID"="TEMP"."ID2")298-filter("DL"."IAUDITSEQUENCE">=0AND"DL"."DR"=0AND30"DL"."CACCOUNTPERIOD"='2013-06'AND"DL"."PK_BOOK"='1002O110000000000HM6'AND31"DL"."PK_ORG"='1002O110000000000HMT'AND"DL"."CBILLTYPECODE"<>'IG'AND32"DL"."CBILLTYPECODE"<>'IF'AND"DL"."CBILLTYPECODE"<>'IE'AND33"DL"."PK_GROUP"='0001O1100000000004R2'AND("DL"."FINTRANSITFLAG"=(-1)OR34"DL"."FINTRANSITFLAG"=0)AND"DL"."FDATAGETFLAG"<>8AND"DL"."FDATAGETFLAG"<>12AND35"DL"."FDATAGETFLAG"<>11AND"DL"."FDATAGETFLAG"<>13)369-access("GL"."PK_ORG"="DL"."PK_ORG"AND"GL"."CINVENTORYID"="DL"."CINVENTORYID"37AND"GL"."CCALCRANGEID"="DL"."CCALCRANGEID")3810-filter("GL"."PK_BOOK"='1002O110000000000HM6'AND"GL"."DR"=0AND39"GL"."PK_GROUP"='0001O1100000000004R2')4013-filter(:B1='1002O110000000000HM6'AND:B2='0001O1100000000004R2'AND41'1002O110000000000HMT'=:B3AND:B4=0)4214-access("DL"."CINVENTORYID"="TEMP"."ID1"AND"DL"."CCALCRANGEID"="TEMP"."ID2")4316-filter("DL"."CINVENTORYID"=:B1AND"DL"."IAUDITSEQUENCE">=0AND"DL"."DR"=0AND44"DL"."CACCOUNTPERIOD"='2013-06'AND"DL"."PK_BOOK"='1002O110000000000HM6'AND45"DL"."PK_ORG"='1002O110000000000HMT'AND"DL"."CBILLTYPECODE"<>'IG'AND46"DL"."CBILLTYPECODE"<>'IF'AND"DL"."CBILLTYPECODE"<>'IE'AND47"DL"."PK_GROUP"='0001O1100000000004R2'AND("DL"."FINTRANSITFLAG"=(-1)OR10/1348"DL"."FINTRANSITFLAG"=0)AND"DL"."FDATAGETFLAG"<>8AND"DL"."FDATAGETFLAG"<>12AND49"DL"."FDATAGETFLAG"<>11AND"DL"."FDATAGETFLAG"<>13AND"DL"."CCALCRANGEID"=:B2)5051Note52-----53-dynamicsamplingusedforthisstatement(level=2)查看统计信息：SQL>selecta.table_name,A.AVG_ROW_LEN,A.AVG_SPACE,a.last_analyzed,a.num_rows,a.blocks2fromuser_tablesa3wherea.table_namein4('IA_DETAILLEDGER');TABLE_NAMEAVG_ROW_LENAVG_SPACELAST_ANALYZEDNUM_ROWSBLOCKS------------------------------------------------------------------------------------IA_DETAILLEDGER96702013-07-022210359115711解决方法：SQL>createindexidx_IA_DETAILLEDGER_0001onIA_DETAILLEDGER(CINVENTORYID);Indexcreated最终执行计划：SQL>selecta.table_name,A.AVG_ROW_LEN,A.AVG_SPACE,a.last_analyzed,a.num_rows,a.blocks2fromuser_tablesa3wherea.table_namein11/134('IA_DETAILLEDGER');TABLE_NAMEAVG_ROW_LENAVG_SPACELAST_ANALYZEDNUM_ROWSBLOCKS------------------------------------------------------------------------------------IA_DETAILLEDGER96702013-07-022210359115711SQL>createindexidx_IA_DETAILLEDGER_0001onIA_DETAILLEDGER(CINVENTORYID);IndexcreatedPlanhashvalue:3157913670--------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------------------|0|UPDATESTATEMENT||1|188|98(9)|00:00:02||1|UPDATE|IA_GENERALNAB||||||2|NESTEDLOOPS|||||||3|NESTEDLOOPS||1|188|14(8)|00:00:01||4|VIEW||1|36|12(9)|00:00:01||5|SORTGROUPBY||1|238|12(9)|00:00:01||6|NESTEDLOOPS|||||||7|NESTEDLOOPS||1|238|11(0)|00:00:01||8|TABLEACCESSFULL|TEMP_PUBAPP_2ID|1|44|2(0)|00:00:01||*9|INDEXRANGESCAN|IDX_IA_DETAILLEDGER_0001|8||2(0)|00:00:01||*10|TABLEACCESSBYINDEXROWID|IA_DETAILLEDGER|2|388|9(0)|00:00:01|12/13|*11|INDEXRANGESCAN|I_IA_GEN_INV|1||1(0)|00:00:01||*12|TABLEACCESSBYINDEXROWID|IA_GENERALNAB|1|152|2(0)|00:00:01||13|VIEW||1|114|11(0)|00:00:01||14|SORTGROUPBY||1|238|11(0)|00:00:01||*15|FILTER|||||||16|NESTEDLOOPS|||||||17|NESTEDLOOPS||1|238|11(0)|00:00:01||18|TABLEACCESSFULL|TEMP_PUBAPP_2ID|1|44|2(0)|00:00:01||*19|INDEXRANGESCAN|IDX_IA_DETAILLEDGER_0001|8||2(0)|00:00:01||*20|TABLEACCESSBYINDEXROWID|IA_DETAILLEDGER|1|194|9(0)|00:00:01|--------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------9-access("DL"."CINVENTORYID"="TEMP"."ID1")10-filter("DL"."IAUDITSEQUENCE">=0AND"DL"."DR"=0AND"DL"."CACCOUNTPERIOD"='2013-06'AND"DL"."PK_BOOK"='1002O110000000000HM6'AND"DL"."PK_ORG"='1002O110000000000HMT'AND"DL"."CBILLTYPECODE"<>'IG'AND"DL"."CBILLTYPECODE"<>'IF'AND"DL"."CBILLTYPECODE"<>'IE'AND"DL"."PK_GROUP"='0001O1100000000004R2'AND("DL"."FINTRANSITFLAG"=(-1)OR"DL"."FINTRANSITFLAG"=0)AND"DL"."FDATAGETFLAG"<>8AND"DL"."FDATAGETFLAG"<>12AND"DL"."FDATAGETFLAG"<>11AND"DL"."FDATAGETFLAG"<>13AND"DL"."CCALCRANGEID"="TEMP"."ID2")11-access("GL"."PK_ORG"="DL"."PK_ORG"AND"GL"."CINVENTORYID"="DL"."CINVENTORYID"AND"GL"."CCALCRANGEID"="DL"."CCALCRANGEID")12-filter("GL"."PK_BOOK"='1002O110000000000HM6'AND"GL"."DR"=0AND13/13"GL"."PK_GROUP"='0001O1100000000004R2')15-filter(:B1='1002O110000000000HM6'AND:B2='0001O1100000000004R2'AND'1002O110000000000HMT'=:B3AND:B4=0)19-access("DL"."CINVENTORYID"=:B1)filter("DL"."CINVENTORYID"="TEMP"."ID1")20-filter("DL"."IAUDITSEQUENCE">=0AND"DL"."DR"=0AND"DL"."CACCOUNTPERIOD"='2013-06'AND"DL"."PK_BOOK"='1002O110000000000HM6'AND"DL"."PK_ORG"='1002O110000000000HMT'AND"DL"."CBILLTYPECODE"<>'IG'AND"DL"."CBILLTYPECODE"<>'IF'AND"DL"."CBILLTYPECODE"<>'IE'AND"DL"."PK_GROUP"='0001O1100000000004R2'AND("DL"."FINTRANSITFLAG"=(-1)OR"DL"."FINTRANSITFLAG"=0)AND"DL"."FDATAGETFLAG"<>8AND"DL"."FDATAGETFLAG"<>12AND"DL"."FDATAGETFLAG"<>11AND"DL"."FDATAGETFLAG"<>13AND"DL"."CCALCRANGEID"=:B1AND"DL"."CCALCRANGEID"="TEMP"."ID2")Note------dynamicsamplingusedforthisstatement(level=2)四、解决方法建索引后问题解决。createindexidx_IA_DETAILLEDGER_0001onIA_DETAILLEDGER(CINVENTORYID);
