1. 問題描述

生成資料庫AWR報告：8月22日 10：00到 18：00，TOP1 SQL: 3g5996z6awjux,執行次數：217677次，每次執行時間7.38秒，佔用資料庫總資源(elapsed time)：43.22%。

2. 問題分析

SQL語句：

繫結變數從v$sql_bind_capture獲取

點選(此處)摺疊或開啟

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 EPSA_SN.SA_WORKFORM_INST F,
(SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W
WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR
W.CURRENT_STATE = 3)
AND W.USER_ID = '153812'
AND W.PARTICIPANT_TYPE = 0
UNION ALL
SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W, EPSA_SN.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 = '153812'
AND W.PARTICIPANT_TYPE = 1) WF,
EPSA_SN.SA_USER U,
(SELECT * FROM EPSA_SN.RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O,
EPSA_SN.SA_WF_BUSINESS_EXT B,
EPSA_SN.SA_DEPT D,
EPSA_SN.SA_ORG ORG
WHERE F.PROCINST_ID = WF.PROC_INSTANCE_ID --F.PROCINST_ID
AND WF.WORKITEM_INS_ID = O.INSTANCE_ID(+)
AND F.BUILDER = U.USER_ID(+) --F.BUILDER
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
((WF.DEPART_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (WF.DEPART_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = WF.DEPART_ID))) OR
((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND
((F.DEPT_ID LIKE '*61102|201181|6140143000|6140143009%') OR --F.DEPT_ID
('61102|201181|6140143000|6140143009' LIKE (F.DEPT_ID || '%')) OR ('$61102|201181|6140143000|6140143009' = F.DEPT_ID))))))
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 EPSA_SN.SA_WORKFORM_INST F,
(SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W
WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR
W.CURRENT_STATE = 3)
AND W.USER_ID = '153812'
AND W.PARTICIPANT_TYPE = 0
UNION ALL
SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W, EPSA_SN.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 = '153812'
AND W.PARTICIPANT_TYPE = 1) WF,
EPSA_SN.SA_USER U,
EPSA_SN.RT_PROCESSINSTANCE P,
(SELECT * FROM EPSA_SN.RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O,
EPSA_SN.SA_WF_BUSINESS_EXT B,
EPSA_SN.SA_DEPT D,
EPSA_SN.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
((WF.DEPART_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (WF.DEPART_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = WF.DEPART_ID))) OR
((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND
((F.DEPT_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (F.DEPT_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = F.DEPT_ID))))))
AND NOT EXISTS
(SELECT 1
FROM EPSA_SN.SA_WORKFORM_INST RF
WHERE WF.PROC_INSTANCE_ID = RF.PROCINST_ID)
) LI,
EPSA_SN.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 EPSA_SN.SA_WF_EXAM_ACT_INST A, EPSA_SN.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 EPSA_SN.SA_WF_WORKITEM_EXCLUDED_USER US
WHERE T.WORKITEM_INS_ID = US.WORKITEM_ID
AND US.USER_ID = '153812');

本次最佳化採用在查詢庫，避免快取對最佳化效果的影響，查詢庫硬體裝置，效能較差，由於查詢庫和生產庫，資料庫結構及資料量一致，執行時間請比對分析，查詢庫該語句執行時間：10分鐘14秒，合計：614秒。

點選(此處)摺疊或開啟

Plan hash value: 2391298305
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
| 1 | NESTED LOOPS ANTI | | 1 | 17 | 307 |00:10:14.08 | 338K| 102K|
|* 2 | HASH JOIN OUTER | | 1 | 17 | 438 |00:10:00.63 | 333K| 99548 |
|* 3 | HASH JOIN RIGHT OUTER | | 1 | 17 | 438 |00:10:00.07 | 333K| 99040 |
| 4 | INDEX FULL SCAN | PK_SA_WF_WORKFORM_TYPE_MAP | 1 | 155 | 155 |00:00:00.01 | 1 | 1 |
| 5 | VIEW | | 1 | 17 | 438 |00:10:00.06 | 333K| 99039 |
| 6 | UNION-ALL | | 1 | | 438 |00:10:00.06 | 333K| 99039 |
| 7 | NESTED LOOPS OUTER | | 1 | 14 | 437 |00:08:25.77 | 146K| 84745 |
| 8 | NESTED LOOPS OUTER | | 1 | 14 | 437 |00:08:25.67 | 145K| 84730 |
| 9 | NESTED LOOPS OUTER | | 1 | 14 | 437 |00:08:25.52 | 144K| 84711 |
|* 10 | HASH JOIN RIGHT OUTER | | 1 | 14 | 437 |00:08:24.91 | 143K| 84637 |
|* 11 | TABLE ACCESS FULL | RT_OVERTIME_DEAL | 1 | 282 | 282 |00:00:00.01 | 6 | 5 |
| 12 | NESTED LOOPS OUTER | | 1 | 14 | 437 |00:08:24.89 | 143K| 84632 |
| 13 | NESTED LOOPS | | 1 | 14 | 437 |00:08:24.88 | 143K| 84631 |
| 14 | VIEW | | 1 | 1348 | 26101 |00:07:32.20 | 83627 | 74518 |
| 15 | UNION-ALL | | 1 | | 26101 |00:07:32.17 | 83627 | 74518 |
|* 16 | TABLE ACCESS BY INDEX ROWID | RT_WORKITEMINST | 1 | 10 | 2 |00:00:55.51 | 8328 | 8119 |
|* 17 | INDEX RANGE SCAN | IDX_RT_WORKITEM_USER_DEPART | 1 | 1673 | 9402 |00:00:00.59 | 94 | 92 |
|* 18 | TABLE ACCESS BY INDEX ROWID | RT_WORKITEMINST | 1 | 10 | 26099 |00:06:36.60 | 75299 | 66399 |
| 19 | NESTED LOOPS | | 1 | 1338 | 89575 |00:00:04.55 | 4301 | 785 |
|* 20 | INDEX RANGE SCAN | PK_SA_BROLE_USER | 1 | 131 | 286 |00:00:00.03 | 5 | 5 |
|* 21 | INDEX RANGE SCAN | IDX_RT_WORKITEM_USER_DEPART | 286 | 1673 | 89288 |00:00:04.39 | 4296 | 780 |
|* 22 | TABLE ACCESS BY INDEX ROWID | SA_WORKFORM_INST | 26101 | 1 | 437 |00:00:52.66 | 60358 | 10113 |
|* 23 | INDEX UNIQUE SCAN | IDX_WORKFORM_PROCINST_ID | 26101 | 1 | 6965 |00:00:30.76 | 53393 | 5009 |
| 24 | TABLE ACCESS BY INDEX ROWID | SA_WF_BUSINESS_EXT | 437 | 1 | 0 |00:00:00.01 | 2 | 1 |
|* 25 | INDEX UNIQUE SCAN | PK_SA_WF_BUSINESS_EXT | 437 | 1 | 0 |00:00:00.01 | 2 | 1 |
| 26 | TABLE ACCESS BY INDEX ROWID | SA_USER | 437 | 1 | 437 |00:00:00.61 | 919 | 74 |
|* 27 | INDEX UNIQUE SCAN | PK_SA_USER | 437 | 1 | 437 |00:00:00.22 | 439 | 23 |
| 28 | TABLE ACCESS BY INDEX ROWID | SA_DEPT | 437 | 1 | 437 |00:00:00.15 | 876 | 19 |
|* 29 | INDEX UNIQUE SCAN | PK_SA_DEPT | 437 | 1 | 437 |00:00:00.03 | 439 | 7 |
| 30 | TABLE ACCESS BY INDEX ROWID | SA_ORG | 437 | 1 | 437 |00:00:00.10 | 876 | 15 |
|* 31 | INDEX UNIQUE SCAN | PK_SA_ORG | 437 | 1 | 437 |00:00:00.04 | 439 | 6 |
| 32 | NESTED LOOPS OUTER | | 1 | 3 | 1 |00:01:34.28 | 186K| 14294 |
| 33 | NESTED LOOPS OUTER | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 34 | NESTED LOOPS OUTER | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 35 | NESTED LOOPS OUTER | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 36 | NESTED LOOPS OUTER | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 37 | NESTED LOOPS ANTI | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 38 | NESTED LOOPS | | 1 | 3 | 1 |00:01:34.27 | 186K| 14292 |
| 39 | NESTED LOOPS | | 1 | 321 | 484 |00:01:33.66 | 185K| 14180 |
| 40 | VIEW | | 1 | 1348 | 26101 |00:00:00.97 | 80643 | 0 |
| 41 | UNION-ALL | | 1 | | 26101 |00:00:00.95 | 80643 | 0 |
|* 42 | TABLE ACCESS BY INDEX ROWID| RT_WORKITEMINST | 1 | 10 | 2 |00:00:00.08 | 8328 | 0 |
|* 43 | INDEX RANGE SCAN | IDX_RT_WORKITEM_USER_DEPART | 1 | 1673 | 9402 |00:00:00.01 | 94 | 0 |
|* 44 | TABLE ACCESS BY INDEX ROWID| RT_WORKITEMINST | 1 | 10 | 26099 |00:00:00.81 | 72315 | 0 |
| 45 | NESTED LOOPS | | 1 | 1338 | 89575 |00:00:00.15 | 1665 | 0 |
|* 46 | INDEX RANGE SCAN | PK_SA_BROLE_USER | 1 | 131 | 286 |00:00:00.01 | 5 | 0 |
|* 47 | INDEX RANGE SCAN | IDX_RT_WORKITEM_USER_DEPART | 286 | 1673 | 89288 |00:00:00.03 | 1660 | 0 |
|* 48 | TABLE ACCESS BY INDEX ROWID | RT_PROCESSINSTANCE | 26101 | 1 | 484 |00:01:32.66 | 104K| 14180 |
|* 49 | INDEX RANGE SCAN | IDX20130719025 | 26101 | 1 | 26086 |00:00:41.13 | 78524 | 5630 |
|* 50 | TABLE ACCESS BY INDEX ROWID | SA_WORKFORM_INST | 484 | 1 | 1 |00:00:00.61 | 1124 | 112 |
|* 51 | INDEX UNIQUE SCAN | IDX_WORKFORM_PROCINST_ID | 484 | 1 | 154 |00:00:00.05 | 970 | 8 |
|* 52 | INDEX UNIQUE SCAN | IDX_WORKFORM_PROCINST_ID | 1 | 1 | 0 |00:00:00.01 | 3 | 0 |
| 53 | TABLE ACCESS BY INDEX ROWID | SA_USER | 1 | 1 | 1 |00:00:00.01 | 4 | 0 |
|* 54 | INDEX UNIQUE SCAN | PK_SA_USER | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
| 55 | TABLE ACCESS BY INDEX ROWID | SA_DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 56 | INDEX UNIQUE SCAN | PK_SA_DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
| 57 | TABLE ACCESS BY INDEX ROWID | SA_WF_BUSINESS_EXT | 1 | 1 | 0 |00:00:00.01 | 1 | 0 |
|* 58 | INDEX UNIQUE SCAN | PK_SA_WF_BUSINESS_EXT | 1 | 1 | 0 |00:00:00.01 | 1 | 0 |
| 59 | TABLE ACCESS BY INDEX ROWID | SA_ORG | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 60 | INDEX UNIQUE SCAN | PK_SA_ORG | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
| 61 | TABLE ACCESS BY INDEX ROWID | RT_OVERTIME_DEAL | 1 | 1 | 0 |00:00:00.01 | 2 | 2 |
|* 62 | INDEX UNIQUE SCAN | PK_RT_OVERTIME_DEAL | 1 | 1 | 0 |00:00:00.01 | 2 | 2 |
| 63 | VIEW | | 1 | 16536 | 597 |00:00:00.55 | 519 | 508 |
| 64 | HASH GROUP BY | | 1 | 16536 | 597 |00:00:00.55 | 519 | 508 |
|* 65 | HASH JOIN | | 1 | 16536 | 597 |00:00:00.55 | 519 | 508 |
|* 66 | TABLE ACCESS FULL | SA_WF_EXAM_INST | 1 | 5024 | 238 |00:00:00.19 | 170 | 167 |
| 67 | INDEX FAST FULL SCAN | PK_EXAM_ACT_INST | 1 | 32739 | 32739 |00:00:00.33 | 349 | 341 |
|* 68 | TABLE ACCESS BY INDEX ROWID | SA_WF_WORKITEM_EXCLUDED_USER | 438 | 1 | 131 |00:00:13.44 | 4737 | 3440 |
|* 69 | INDEX RANGE SCAN | IDX20130720140 | 438 | 106 | 136K|00:00:05.75 | 2509 | 1389 |

3 訪問路徑最佳化

EPSA_SN.SA_WORKFORM_INST，索引訪問次數：26101，回表次數：26101，合計：52202次IO，全表掃描訪問：4064次IO，建議修改為全表掃描。

RT_PROCESSINSTANCE：同上分析，該也建議全表掃描

圖1 表EPSA_SN.SA_WORKFORM_INST索引掃描

圖2表EPSA_SN.SA_WORKFORM_INST，全表掃描訪問：4064次IO，表RT_PROCESSINSTANCE全表掃描訪問次數：18784

最佳化測試：

alter system flush shared_pool;

alter system flush buffer_cache;--消除快取影響

--修改hint如下，

點選(此處)摺疊或開啟

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 /*+full(F)*/
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 EPSA_SN.SA_WORKFORM_INST F,
(SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W
WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR
W.CURRENT_STATE = 3)
AND W.USER_ID = '153812'
AND W.PARTICIPANT_TYPE = 0
UNION ALL
SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W, EPSA_SN.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 = '153812'
AND W.PARTICIPANT_TYPE = 1) WF,
EPSA_SN.SA_USER U,
(SELECT * FROM EPSA_SN.RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O,
EPSA_SN.SA_WF_BUSINESS_EXT B,
EPSA_SN.SA_DEPT D,
EPSA_SN.SA_ORG ORG
WHERE F.PROCINST_ID = WF.PROC_INSTANCE_ID --F.PROCINST_ID
AND WF.WORKITEM_INS_ID = O.INSTANCE_ID(+)
AND F.BUILDER = U.USER_ID(+) --F.BUILDER
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
((WF.DEPART_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (WF.DEPART_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = WF.DEPART_ID))) OR
((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND
((F.DEPT_ID LIKE '*61102|201181|6140143000|6140143009%') OR --F.DEPT_ID
('61102|201181|6140143000|6140143009' LIKE (F.DEPT_ID || '%')) OR ('$61102|201181|6140143000|6140143009' = F.DEPT_ID))))))
UNION ALL
SELECT /*+full(P)*/
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 EPSA_SN.SA_WORKFORM_INST F,
(SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W
WHERE (W.CURRENT_STATE = 1 OR W.CURRENT_STATE = 2 OR
W.CURRENT_STATE = 3)
AND W.USER_ID = '153812'
AND W.PARTICIPANT_TYPE = 0
UNION ALL
SELECT W.*
FROM EPSA_SN.RT_WORKITEMINST W, EPSA_SN.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 = '153812'
AND W.PARTICIPANT_TYPE = 1) WF,
EPSA_SN.SA_USER U,
EPSA_SN.RT_PROCESSINSTANCE P,
(SELECT * FROM EPSA_SN.RT_OVERTIME_DEAL WHERE TIMER_TYPE = 1) O,
EPSA_SN.SA_WF_BUSINESS_EXT B,
EPSA_SN.SA_DEPT D,
EPSA_SN.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
((WF.DEPART_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (WF.DEPART_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = WF.DEPART_ID))) OR
((WF.DEPART_ID IS NULL OR Wf.DEPART_ID = '') AND
((F.DEPT_ID LIKE '*61102|201181|6140143000|6140143009%') OR
('61102|201181|6140143000|6140143009' LIKE (F.DEPT_ID || '%')) OR
('$61102|201181|6140143000|6140143009' = F.DEPT_ID))))))
AND NOT EXISTS
(SELECT 1
FROM EPSA_SN.SA_WORKFORM_INST RF
WHERE WF.PROC_INSTANCE_ID = RF.PROCINST_ID)
) LI,
EPSA_SN.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 EPSA_SN.SA_WF_EXAM_ACT_INST A, EPSA_SN.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 EPSA_SN.SA_WF_WORKITEM_EXCLUDED_USER US
WHERE T.WORKITEM_INS_ID = US.WORKITEM_ID
AND US.USER_ID = '153812');

4. 小結

透過訪問路徑最佳化：最佳化前執行時間：614秒，最佳化後：83秒。

真實世界SQL最佳化案例2_訪問路徑最佳化

相關文章