用友股份-LE服务支持部技术方案--《中间件一直readresult问题解决案例》建立日期：2013-04-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-00512/8文档控制创建记录审阅人姓名所属部门职位审阅签字发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-04-01V1.03目录中间件一直readresult问题解决案例.................................................................................4一.问题描述................................................................................................................4三.检查分析................................................................................................................4四.解决方法................................................................................................................8五.其他.......................................................................................................................84中间件一直readresult问题解决案例一.问题描述通过nmc查看，如下图：发现中间件一直readresult,sql语句都已经跑完了，但是中间件一直在等待读取数据集。三.检查分析查看语句的执行计划如下：5当前的等待事件是：SQL*Netmessagetoclient查看的语句如下：selecttb.bbye,fb_oidfromarap_djfbtb,arap_djzbth,bd_cumandocwheretb.vouchid=th.vouchidandth.djdl='sk'andtb.djdl='sk'andth.djztin(1,2,3)andth.dr=0andtb.dr=0andtb.wldx=0andexists(selectclimittypebidfromso_limittype_bareawherearea.climittypecid='100218100000000009GD'and(area.cproductid='#123456789*123456789'orarea.cproductid=tb.productline)and(area.cbiztypeid='#123456789*123456789'orarea.cbiztypeid=th.xslxbm)andarea.dr=0andth.dwbm=area.pk_corp)andordercusmandocin(selectidfromtemp_ic_forminsql001)andbd_cumandoc.pk_corp=tb.dwbmandpk_cubasdoc='0001181000000000DYHA'andbd_cumandoc.dr=0and(custflag='0'orcustflag='2')Planhashvalue:849827234--------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|286|106(0)|00:00:02||*1|FILTER|||||||2|NESTEDLOOPS||1|286|98(0)|00:00:02||3|NESTEDLOOPS||1|230|96(0)|00:00:02|6|4|MERGEJOINCARTESIAN||1|132|7(0)|00:00:01||*5|TABLEACCESSBYINDEXROWID|BD_CUMANDOC|2|60|5(0)|00:00:01||*6|INDEXRANGESCAN|I_BD_CUMANDOC_14|4||1(0)|00:00:01||7|BUFFERSORT||1|102|18E(100)|||8|SORTUNIQUE||1|102|1(0)|00:00:01||9|TABLEACCESSFULL|TEMP_IC_FORMINSQL001|1|102|1(0)|00:00:01||*10|TABLEACCESSBYINDEXROWID|ARAP_DJFB|416|40768|89(0)|00:00:02||*11|INDEXRANGESCAN|I_ARAP_DJFB020|304||16(0)|00:00:01||*12|TABLEACCESSBYINDEXROWID|ARAP_DJZB|1|56|2(0)|00:00:01||*13|INDEXUNIQUESCAN|PK_ARAP_DJZB|1||1(0)|00:00:01||*14|TABLEACCESSFULL|SO_LIMITTYPE_B|1|70|8(0)|00:00:01|--------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------71-filter(EXISTS(SELECT/*+*/0FROM"SO_LIMITTYPE_B""AREA"WHERE"AREA"."CLIMITTYPECID"='100218100000000009GD'AND"AREA"."PK_CORP"=:B1AND"AREA"."DR"=0AND("AREA"."CBIZTYPEID"='#123456789*123456789'OR"AREA"."CBIZTYPEID"=:B2)AND("AREA"."CPRODUCTID"='#123456789*123456789'OR"AREA"."CPRODUCTID"=:B3)))5-filter(("CUSTFLAG"='0'OR"CUSTFLAG"='2')AND"BD_CUMANDOC"."DR"=0)6-access("PK_CUBASDOC"='0001181000000000DYHA')10-filter("TB"."WLDX"=0AND"TB"."DR"=0)11-access("ORDERCUSMANDOC"="ID"AND"BD_CUMANDOC"."PK_CORP"="TB"."DWBM"AND"TB"."DJDL"='sk')filter("ORDERCUSMANDOC"ISNOTNULL)12-filter("TH"."DJDL"='sk'AND"TH"."DR"=0AND("TH"."DJZT"=1OR"TH"."DJZT"=2OR"TH"."DJZT"=3))13-access("TB"."VOUCHID"="TH"."VOUCHID")14-filter("AREA"."CLIMITTYPECID"='100218100000000009GD'AND"AREA"."PK_CORP"=:B1AND"AREA"."DR"=0AND("AREA"."CBIZTYPEID"='#123456789*123456789'OR"AREA"."CBIZTYPEID"=:B2)AND("AREA"."CPRODUCTID"='#123456789*123456789'OR"AREA"."CPRODUCTID"=:B3))8Note------dynamicsamplingusedforthisstatement四.解决方法altersystemset"_optimizer_cartesian_enabled"=true;//修改后正常五.其他这是一个无法解释的问题。
