Forum Stats

  • 3,874,681 Users
  • 2,266,766 Discussions
  • 7,911,940 Comments

Discussions

Need Help Tuning a query

DBApps
DBApps Member Posts: 75 Blue Ribbon
edited Jun 9, 2022 8:14AM in SQL & PL/SQL

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

Answers

  • ora_1978
    ora_1978 Member Posts: 523 Bronze Badge

    Run this alone and see the time taken.

     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'

  • ora_1978
    ora_1978 Member Posts: 523 Bronze Badge
    edited Jun 9, 2022 6:53PM

    Run the below and see the time taken.


      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


    Why you are using /*+ FIRST_ROWS(10) */ in many places. Please check.

  • ora_1978
    ora_1978 Member Posts: 523 Bronze Badge

    check the performance of join condition between table that is undergoing full table scan with it's joining table.

  • ora_1978
    ora_1978 Member Posts: 523 Bronze Badge
    edited Jun 10, 2022 4:06AM

    First_rows will only give first 10 rows in the shortest possible time. this hint to be used for results. so no need to use in with clause. try removing the hint in the with clause and provide the order of filters like first join condition should remove more records and remaining join condition should use the remaining less number of records for performing joins.


    for example

    b = c (filter 1000 records) and c = d (filter 5000 records) and a =b (filter 100000 records)


    This should change to

    a =b (filter 100000 records) and c = d (filter 5000 records) and b = c (filter 1000 records)