Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need Help Tuning a query

DBAppsJun 9 2022 — edited Jun 9 2022

Hi Friends ,
Could you please help me reduce the cost of this query or how more effectively can i rewrite this query .
db :- 12.1 , OS - OEL 7
WITH tmp1 AS (
SELECT /*+ FIRST_ROWS(10) */
project_template_link.project_id
FROM
wfmt_data.project_template
inner join wfmt_data.project_template_link using(template_version,template_name)
where project_template.application = 'EES'
),
tmp2 AS (
SELECT /*+ FIRST_ROWS(10) */
pme.process processid
FROM
wfmt_core.process_model_entry pme,
tmp1
WHERE
pme.name = 'PROJ_ID'
AND tmp1.project_id = pme.value
)
SELECT /*+ FIRST_ROWS(10) */
q.queue_name,
q.description,
wi.item_status
FROM
wfmt_core.queue q,
wfmt_core.work_items wi,
wfmt_core.case c,
wfmt_core.process p,
wfmt_core.work_items_queue_map wiqm,
tmp2
WHERE
tmp2.processid = p.processid
AND p.processinstanceid = c.process_key
AND c.casenum = wi.casenum
AND wi.work_item_id = wiqm.work_item_id
AND wiqm.queue_name = q.queue_name
AND wi.item_status IN ( 'ASSIGNED', 'DELAYED', 'ESCALATED', 'NEW' );

Plan hash value: 1728370568
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1660 | 2412 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 1660 | 2412 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PROJECT_TEMPLATE | 76 | 2356 | 36 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 67 | 12864 | 2376 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 67 | 12864 | 2376 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 6 | 996 | 2370 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 67 | 9715 | 2297 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 51 | 6426 | 2195 (1)| 00:00:01 |
| 8 | NESTED LOOPS | | 190 | 19570 | 1158 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 190 | 11970 | 398 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | WORK_ITEMS | 167K| 3598K| 18 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | CASE | 1 | 41 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | CASE_PK | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| PROCESS | 1 | 40 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_PROCESS_PROCINSTID | 1 | | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | PROCESS_MODEL_ENTRY | 1 | 23 | 6 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_PROCMODELENTRY_PROC | 57 | | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | WIS_Q_MAP_Q_PK | 1 | 19 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PROJECT_TEMPLATE_LINK | 1 | 21 | 2 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PROJECT_TEMPLATE_LINK_PK | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | QUEUE_PK | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | QUEUE | 11 | 286 | 1 (0)| 00:00:01 |

Regards,
DBApps

Comments
Post Details
Added on Jun 9 2022
4 comments
254 views