Skip to Main Content

Query tuning

DBAppsAug 6 2020 — edited Aug 6 2020

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

Comments
Post Details
Added on Aug 6 2020
3 comments
54 views