Dear Gurus,
i am using oracle 12.2 on linux .
I am having issue while tuning the below query and would appreciate your help .
with tmp as
(
SELECT
wi.work_item_name,
wi.casenum,
wi.item_status,
wi.process_name,
wi.step_name,
p.processid,
q.queue_name,
p.processinstanceid,
wi.work_item_id,
wi.locked_user
FROM
wfmt_core.queue q,
WFMT_CORE.WORK_ITEMS WI,
WFMT_CORE.CASE C,
WFMT_CORE.process p
WHERE
wi.casenum = c.casenum
AND c.process_key = p.processinstanceid
AND wi.ITEM_STATUS in
(
'ASSIGNED',
'DELAYED',
'ESCALATED',
'NEW'
)
AND q.queue_name = '609631704'
AND EXISTS
(
SELECT
1
FROM
WFMT_CORE.WORK_ITEMS_QUEUE_MAP WIQM
WHERE
wiqm.work_item_id = wi.work_item_id
AND q.queue_name = wiqm.queue_name
)
)
,
TMP2 AS
(
SELECT
TMP.PROCESSID,
(
SELECT
PME.value
FROM
wfmt_core.PROCESS_MODEL_ENTRY PME
WHERE
TMP.PROCESSID = PME.PROCESS
AND PME.name = 'PROJ_ID'
)
PROJ_ID,
(
SELECT
PME.value
FROM
wfmt_core.PROCESS_MODEL_ENTRY PME
WHERE
TMP.PROCESSID = PME.PROCESS
AND PME.name = 'TASK_ID'
)
TASK_ID,
(
SELECT
PME.value
FROM
wfmt_core.PROCESS_MODEL_ENTRY PME
WHERE
TMP.PROCESSID = PME.PROCESS
AND PME.name = 'PROJ_TYPE'
)
PROJ_TYPE
FROM
TMP
)
,
TMP3 AS
(
select
TMP2.PROJ_ID PROJECTID,
tmp2.processid process,
(
SELECT
pt.application
FROM
WFMT_DATA.PROJECT_TEMPLATE pt,
WFMT_DATA.PROJECT_TEMPLATE_LINK ptl
WHERE
pt.template_name = ptl.template_name
AND pt.template_version = ptl.template_version
AND ptl.project_id = TMP2.PROJ_ID
)
AS projectPerspective
FROM
TMP2
)
,
tmp4 AS
(
SELECT
TMP3.PROJECTID,
TMP3.process procid,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'ORDER_NAME'
)
OrderName,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'BuildingCode'
)
BuildingCode,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'City'
)
City,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'Country'
)
Country,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'Region'
)
Region,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'JOB_TYPE'
)
JOB_TYPE,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'ORDER_BUILD_TYPE'
)
Build_Type,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'jobPriority'
)
jobPriority,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'agreedRFSDate'
)
agreedRFSDate,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
WHERE
pd.project_id = TMP3.PROJECTID
AND PD.FIELD_NAME = 'noOfHours'
)
noOfHours,
(
SELECT
PD.FIELD_VALUE
FROM
wfmt_data.project_data pd
where
pd.project_id = tmp3.projectid
and pd.field_name = 'wfmtOrderIds'
)
wfmtOrderIds
FROM
TMP3
)
SELECT
tmp.work_item_name,
tmp.casenum,
tmp.item_status,
tmp.process_name,
tmp.step_name,
tmp.processid,
tmp.queue_name,
tmp.processinstanceid,
tmp.work_item_id,
tmp.LOCKED_USER,
TMP2.PROJ_TYPE,
TMP2.TASK_ID,
TMP3.projectPerspective,
tmp4.*,
(
SELECT
T.START_DATE
FROM
WFMT_DATA.TASKS T
WHERE
TMP2.PROJ_ID = T.PROJECT_ID
AND TMP2.TASK_ID = T.TASK_ID
)
Task_Delivery_date,
(
SELECT
T.FINISH_DATE
FROM
WFMT_DATA.TASKS T
WHERE
TMP2.PROJ_ID = T.PROJECT_ID
and t.task_id = 'MPLSBLD23'
and t.status = 2
)
order_completion_date,
(
SELECT
T.FINISH_DATE
FROM
WFMT_DATA.TASKS T
WHERE
TMP2.PROJ_ID = T.PROJECT_ID
and t.task_id = 'MPLSBLD24'
and t.status = 2
)
planning_review_closure_date
FROM
TMP,
TMP2,
TMP3,
TMP4
WHERE
TMP.PROCESSID = TMP2.PROCESSID
AND TMP2.processid = TMP3.process
AND TMP3.process = tmp4.procid;
Plan hash value: 143938951
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4460 | 35M| | 46543 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | PROJECT_DATA | 1 | 31 | | 3 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PROJECT_DATA_PK | 1 | | | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | TASKS | 1 | 27 | | 3 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | TASKS_PK | 1 | | | 2 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | TASKS | 1 | 30 | | 3 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | TASKS_PK | 1 | | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | TASKS | 1 | 30 | | 3 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | TASKS_PK | 1 | | | 2 (0)| 00:00:01 |
| 29 | TEMP TABLE TRANSFORMATION | | | | | | |
| 30 | LOAD AS SELECT | SYS_TEMP_0FD9FC969_56C6A6F1 | | | | | |
|* 31 | HASH JOIN | | 4789 | 818K| | 2507 (1)| 00:00:01 |
|* 32 | HASH JOIN | | 4789 | 640K| | 1014 (1)| 00:00:01 |
| 33 | NESTED LOOPS SEMI | | 4789 | 448K| | 595 (1)| 00:00:01 |
| 34 | NESTED LOOPS | | 47227 | 3643K| | 593 (1)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | QUEUE_PK | 1 | 12 | | 1 (0)| 00:00:01 |
|* 36 | TABLE ACCESS FULL | WORK_ITEMS | 47227 | 3090K| | 592 (1)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | WIS_Q_MAP_Q_PK | 4365 | 74205 | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | CASE | 233K| 9356K| | 418 (1)| 00:00:01 |
| 39 | TABLE ACCESS FULL | PROCESS | 381K| 13M| | 1492 (1)| 00:00:01 |
| 40 | LOAD AS SELECT | SYS_TEMP_0FD9FC96A_56C6A6F1 | | | | | |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED| PROCESS_MODEL_ENTRY | 1 | 22 | | 6 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | IDX_PROCMODELENTRY_PROC | 104 | | | 3 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID BATCHED| PROCESS_MODEL_ENTRY | 1 | 22 | | 6 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | IDX_PROCMODELENTRY_PROC | 104 | | | 3 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID BATCHED| PROCESS_MODEL_ENTRY | 1 | 22 | | 6 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | IDX_PROCMODELENTRY_PROC | 104 | | | 3 (0)| 00:00:01 |
| 47 | VIEW | | 4460 | 57980 | | 30 (0)| 00:00:01 |
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC969_56C6A6F1 | 4460 | 509K| | 30 (0)| 00:00:01 |
| 49 | LOAD AS SELECT | SYS_TEMP_0FD9FC96B_56C6A6F1 | | | | | |
| 50 | NESTED LOOPS | | 1 | 48 | | 3 (0)| 00:00:01 |
| 51 | TABLE ACCESS BY INDEX ROWID | PROJECT_TEMPLATE_LINK | 1 | 22 | | 2 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | PROJECT_TEMPLATE_LINK_PK | 1 | | | 1 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | PROJECT_TEMPLATE | 1 | 26 | | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | PROJECT_TEMPLATE_PK | 1 | | | 0 (0)| 00:00:01 |
| 55 | VIEW | | 4460 | 8776K| | 4 (0)| 00:00:01 |
| 56 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC96A_56C6A6F1 | 4460 | 57980 | | 4 (0)| 00:00:01 |
|* 57 | HASH JOIN | | 4460 | 35M| 8832K| 3802 (1)| 00:00:01 |
| 58 | VIEW | | 4460 | 8776K| | 332 (0)| 00:00:01 |
| 59 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC96B_56C6A6F1 | 4460 | 8767K| | 332 (0)| 00:00:01 |
|* 60 | HASH JOIN | | 4460 | 26M| | 1704 (1)| 00:00:01 |
| 61 | VIEW | | 4460 | 84740 | | 332 (0)| 00:00:01 |
| 62 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC96B_56C6A6F1 | 4460 | 8767K| | 332 (0)| 00:00:01 |
|* 63 | HASH JOIN | | 4460 | 26M| 1344K| 1371 (0)| 00:00:01 |
| 64 | VIEW | | 4460 | 1284K| | 30 (0)| 00:00:01 |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC969_56C6A6F1 | 4460 | 509K| | 30 (0)| 00:00:01 |
| 66 | VIEW | | 4460 | 25M| | 4 (0)| 00:00:01 |
| 67 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC96A_56C6A6F1 | 4460 | 57980 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='ORDER_NAME')
4 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='BuildingCode')
6 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='City')
8 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='Country')
10 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='Region')
12 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='JOB_TYPE')
14 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='ORDER_BUILD_TYPE')
16 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='jobPriority')
18 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='agreedRFSDate')
20 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='noOfHours')
22 - access("PD"."PROJECT_ID"=:B1 AND "PD"."FIELD_NAME"='wfmtOrderIds')
24 - access("T"."PROJECT_ID"=:B1 AND "T"."TASK_ID"=:B2)
25 - filter("T"."STATUS"=2)
26 - access("T"."PROJECT_ID"=:B1 AND "T"."TASK_ID"='MPLSBLD23')
27 - filter("T"."STATUS"=2)
28 - access("T"."PROJECT_ID"=:B1 AND "T"."TASK_ID"='MPLSBLD24')
31 - access("C"."PROCESS_KEY"="P"."PROCESSINSTANCEID")
32 - access("WI"."CASENUM"="C"."CASENUM")
35 - access("Q"."QUEUE_NAME"='609631704')
36 - filter("WI"."ITEM_STATUS"='ASSIGNED' OR "WI"."ITEM_STATUS"='DELAYED' OR "WI"."ITEM_STATUS"='ESCALATED' OR
"WI"."ITEM_STATUS"='NEW')
37 - access("WIQM"."WORK_ITEM_ID"="WI"."WORK_ITEM_ID" AND "WIQM"."QUEUE_NAME"='609631704')
filter("Q"."QUEUE_NAME"="WIQM"."QUEUE_NAME")
41 - filter("PME"."NAME"='PROJ_ID')
42 - access("PME"."PROCESS"=:B1)
43 - filter("PME"."NAME"='TASK_ID')
44 - access("PME"."PROCESS"=:B1)
45 - filter("PME"."NAME"='PROJ_TYPE')
46 - access("PME"."PROCESS"=:B1)
52 - access("PTL"."PROJECT_ID"=:B1)
54 - access("PT"."TEMPLATE_NAME"="PTL"."TEMPLATE_NAME" AND "PT"."TEMPLATE_VERSION"="PTL"."TEMPLATE_VERSION")
57 - access("TMP3"."PROCESS"="TMP3"."PROCESS")
60 - access("TMP2"."PROCESSID"="TMP3"."PROCESS")
63 - access("TMP"."PROCESSID"="TMP2"."PROCESSID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 2 Sql Plan Directives used for this statement
Thanks for looking
Regards
DBApps