oracle执行计划变化频繁,执行计划经常发生改变，怎么回事？

xing2999 发表于 2013-10-22 14:22

有可能是绑定变量窥视导致的问题。

能否给出SQL语句？

SELECT * FROM (SELECT LI.*, CASE WHEN LI.OVERDUED_NE = 1 OR LI.OVERDUED_NE = 3 THEN 1 WHEN LI.OVERDUED_NE = 0 OR LI.OVERDUED_NE = 2 THEN CASE WHEN E.SUSPEND_FLAG = 0 THEN CASE WHEN SYSTIMESTAMP >= E.TARGET_OVERDUE_MINTIME THEN 1 ELSE 0 END WHEN E.SUSPEND_FLAG = 1 THEN CASE WHEN E.SUSPEND_TIME >= E.TARGET_OVERDUE_MINTIME THEN 1 ELSE 0 END ELSE 0 END ELSE 0 END OVERDUED, CASE WHEN E.TARGET_OVERDUE_MINTIME IS NULL THEN LI.TARGET_TIME_NE WHEN LI.TARGET_TIME_NE IS NULL THEN E.TARGET_OVERDUE_MINTIME WHEN E.TARGET_OVERDUE_MINTIME <= LI.TARGET_TIME_NE THEN E.TARGET_OVERDUE_MINTIME ELSE LI.TARGET_TIME_NE END TARGET_TIME, FTM.WORKFORM_TYPE_KIND FROM (SELECT /*+INDEX(F IDX_WORKFORM_PROCINST_ID)*/ WF.PROC_INSTANCE_ID, WF.ACTIVITY_INS_ID, WF.WORKITEM_INS_ID, WF.NAME, WF.CURRENT_ STATE, WF.COMPLETE_TIME, WF.CREATE_TIME, WF.LIMIT_TIME, WF.APP_URL, WF.USER_NAME, WF.USER_ID, WF.USER_ENTITY_TYPE, WF.USER_ENTITY_NAME, WF.USER_ENTITY_ID, WF.PARTICIPANT_TYPE, WF.SEND_TYPE, WF.OVERDUED AS OVERDUED_NE, WF.EXT_PROP, WF.FLOW_DIRECTION, WF.BUSINESSKEY, WF.DEPART_ID, F.INSTANCE_ID AS WI_INSTANCE_ID, F.INSTANCE_NAME AS WI_INSTANCE_NAME, F.WORKFORM_TYPE AS WI_WORKFORM_TYPE, F.BUILDER AS WI_BUILDER, F.START_TIME AS WI_CREATE_TIME, F.INSTANCE_STATUS AS WI_INSTANCE_STATUS, F.BUSINESS AS WI_BUSINESS, F.KEYDATA_ID AS WI_KEYDATA_ID, F.IS_FLOW AS WI_IS_FLOW, F.ATTR1 AS WI_ATTR1, F.DEPT_ID AS WI_DEPT_ID, '' AS WT_DEPT_ID, WF.CURRENT_STATE AS WT_WORKTASK_STATUS, NULL AS WT_TARGET_TIME, '' AS WT_LINK_URL, '' AS WT_LINK_PARAM, '' AS WT_IS_BATCH, U.REAL_NAME AS WI_BUILDER_REALNAME, NULL AS HOWLONG_WILL_OVERDUE, NULL AS HOWLONG_OVERDUED, '' AS ACTIVITY_TMP_ID, O.TIME_OUT_TIME AS TARGET_TIME_NE, B.APPROVAL_STATUS AS BE_APPROVAL_STATUS, ORG.ORG_NO, ORG.ORG_NAME, '' AS VIEW_FLAGS FROM SA_WORKFORM_INST F, (SELECT W.* FROM RT_WORKITEMINST W WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR W.CURRENT_STATE = 3) AND W.USER_ID = :1 AND W.PARTICIPANT_TYPE = 0 UNION ALL SELECT W.* FROM RT_WORKITEMINST W, SA_BROLE_USER R WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR W.CURRENT_STATE = 3) AND W.USER_ID = R.ROLE_ID AND R.USER_ID = :2 AND W.PARTICIPANT_TYPE = 1) WF, SA_USER U, (SELECT * FROM RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O, SA_WF_BUSINESS_EXT B, SA_DEPT D, SA_ORG ORG WHERE F.PROCINST_ID = WF.PROC_INSTANCE_ID AND WF.WORKITEM_INS_ID = O.INSTANCE_ID(+) AND F.BUILDER = U.USER_ID(+) AND U.DEPT_ID = D.DEPT_ID(+) AND D. ORG_NO = ORG.ORG_NO(+) AND WF.WORKITEM_INS_ID = B.WORKITEM_ID(+) AND (WF.PARTICIPANT_TYPE = 0 OR (WF.PARTICIPANT_TYPE = 1 AND ((WF.DEPART_ID IS NOT NULL AND ((:3 LIKE (WF.DEPART_ID || '%')) OR (:4 = WF.DEPART_ID) OR (:5 = WF.DEPART_ID) OR (:6 = WF.DEPART_ID) OR (WF.DEPART_ID LIKE :7))) OR ((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND ((:8 LIKE (F.DEPT_ID || '%')) OR (:9 = F.DEPT_ID) OR (:10 = F.DEPT_ID) OR (:11 = F.DEPT_ID) OR (F.DEPT_ID LIKE :12)))))) UNION ALL SELECT /*+INDEX(F IDX_WORKFORM_PROCINST_ID)*/ WF.PROC_INSTANCE_ID, WF.ACTIVITY_INS_ID, WF.WORKITEM_INS_ID, WF.NAME, WF.CURRENT_STATE, WF.COMPLETE_TIME, WF.CREATE_TIME, WF.LIMIT_TIME, WF.APP_URL, WF.USER_NAME, WF.USER_ID, WF.USER_ENTITY_TYPE, WF.USER_ENTITY_NAME, WF.USER_ENTITY_ID, WF.PARTICIPANT_TYPE, WF.SEND_TYPE, WF.OVERDUED AS OVERDUED_NE, WF.EXT_PROP, WF.FLOW_DIRECTION, WF.BUSINESSKEY, WF.DEPART_ID, F.INSTANCE_ID AS WI_INSTANCE_ID, F.INSTANCE_NAME AS WI_INSTANCE_NAME, F.WORKFORM_TYPE AS WI_WORKFORM_TYPE, F.BUILDER AS WI_BUILDER, F.START_TIME AS WI_CREATE_TIME, F.INSTANCE_STATUS AS WI_INSTANCE_STATUS, F.BUSINESS AS WI_BUSINESS, F.KEYDATA_ID AS WI_KEYDATA_ID, F.IS_FLOW AS WI_IS_FLOW, F.ATTR1 AS WI_ATTR1, F.DEPT_ID AS WI_DEPT_ID, '' AS WT_DEPT_ID, WF.CURRENT_STATE AS WT_WORKTASK_STATUS, NULL AS WT_TARGET_TIME, '' AS WT_LINK_URL, '' AS WT_LINK_PARAM, '' AS WT_IS_BATCH, U.REAL_NAME AS WI_BUILDER_REALNAME, NULL AS HOWLONG_WILL_OVERDUE, NULL AS HOWLONG_OVERDUED, '' AS ACTIVITY_TMP_ID, O.TIME_OUT_TIME AS TARGET_TIME_NE, B.APPROVAL_STATUS AS BE_APPROVAL_STATUS, ORG.ORG_NO, ORG.ORG_NAME, '' AS VIEW_FLAGS FROM SA_WORKFORM_INST F, (SELECT W.* FROM RT_WORKITEMINST W WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR W.CURRENT_STATE = 3) AND W.USER_ID = :13 AND W.PARTICIPANT_TYPE = 0 UNION ALL SELECT W.* FROM RT_WORKITEMINST W, SA_BROLE_USER R WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR W.CURRENT_STATE = 3) AND W.USER_ID = R.ROLE_ID AND R.USER_ID = :14 AND W.PARTICIPANT_TYPE = 1) WF, SA_USER U, RT_PROCESSINSTANCE P, (SELECT * FROM RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O, SA_WF_BUSINESS_EXT B, SA_DEPT D, SA_ORG ORG WHERE WF.PROC_INSTANCE_ID = P.PROC_INSTANCE_ID AND P.PARENT_PROCINC_ID IS NOT NULL AND F.PROCINST_ID = P.PARENT_PROCINC_ID AND F.BUILDER = U.USER_ID(+) AND U.DEPT_ID = D.DEPT_ID(+) AND D.ORG_NO = ORG.ORG_NO(+) AND WF.WORKITEM_INS_ID = O.INSTANCE_ID(+) AND WF.WORKITEM_INS_ID = B.WORKITEM_ID(+) AND (WF.PARTICIPANT_TYPE = 0 OR (WF.PARTICIPANT_TYPE = 1 AND ((WF.DEPART_ID IS NOT NULL AND ((:15 LIKE (WF.DEPART_ID || '%')) OR (:16 = WF.DEPART_ID) OR (:17 = WF.DEPART_ID) OR (:18 = WF.DEPART_ID) OR (WF.DEPART_ID LIKE :19))) OR ((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND ((:20 LIKE (F.DEPT_ID || '%')) OR (:21 = F.DEPT_ID) OR (:22 = F.DEPT_ID) OR (:23 = F.DEPT_ID) OR (F.DEPT_ID LIKE :24)))))) AND NOT EXISTS (SELECT 1 FROM SA_WORKFORM_INST RF WHERE WF.PROC_INSTANCE_ID = RF.PROCINST_ID)) LI, SA_WF_WORKFORM_TYPE_MAP FTM, (SELECT ACT_INST_ID, SUSPEND_FLAG, MIN(TARGET_OVERDUE_MINTIME) AS TARGET_OVERDUE_MINTIME, MIN(SUSPEND_TIME) AS SUSPEND_TIME FROM (SELECT A.ACT_INST_ID, B.* FROM SA_WF_EXAM_ACT_INST A, SA_WF_EXAM_INST B WHERE A.SUBJECT_INST_ID = B.SUBJECT_INST_ID AND A.PROC_INST_ID = B.PROC_INST_ID AND B.COMPLETE_FLAG = 0) GROUP BY ACT_INST_ID, SUSPEND_FLAG) E WHERE LI.WI_WORKFORM_TYPE = FTM.WORKFORM_TYPE(+) AND LI.ACTIVITY_INS_ID = E.ACT_INST_ID(+)) T WHERE NOT EXISTS (SELECT 1 FROM SA_WF_WORKITEM_EXCLUDED_USER US WHERE T.WORKITEM_INS_ID = US.WORKITEM_ID AND US.USER_ID = :25)