1 Reply Latest reply on Feb 26, 2014 8:09 AM by Forenoon

    Tune the query to get the final approver

    984530

      Hi ,

       

       

      Actually my database addm report shows that this query is taking much time.

      Could anybody help me tunning it or give some hints if possible to tune this below query

       

                            SELECT distinct wntf2.recipient_role

                      FROM

                      apps.wf_notifications wntf2,

                      apps.irc_offers wio2,

                      apps.per_all_vacancies wpav2,

                      apps.per_all_assignments_f wpaaf2,

                      apps.per_all_people_f wpapf2

                      WHERE wntf2.message_type = 'HRSSA'

                      AND wntf2.status = 'CLOSED'

                      AND wntf2.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

                      AND wntf2.subject IS NOT NULL

                      AND wntf2.item_key IS NOT NULL

                      AND UPPER(wntf2.subject) LIKE '%OFFER%'

                      AND wntf2.notification_id IN

                      (

                      SELECT MAX(wntf.notification_id) FROM

                      apps.wf_notifications wntf,

                      apps.irc_offers wio,

                      apps.per_all_vacancies wpav,

                      apps.per_all_assignments_f wpaaf,

                      apps.per_all_people_f wpapf

                      WHERE wntf.message_type = 'HRSSA'

                      AND wntf.status = 'CLOSED'

                      AND wntf.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

                      AND wntf.subject IS NOT NULL

                      AND wntf.item_key IS NOT NULL

                      AND wntf2.item_key = wntf.item_key

                      AND UPPER(wntf.subject) LIKE '%OFFER%'

                      AND UPPER(SUBSTR(wntf.subject, INSTR(wntf.subject,'IRC'))) LIKE 'IRC%'

                      AND wntf.notification_id IN

                      (

                      SELECT

                      MAX(wntf1.notification_id)

                      FROM apps.wf_notifications wntf1 WHERE

                      wntf1.item_key = wntf.item_key

                      AND wntf1.message_type = 'HRSSA'

                      AND wntf1.status = 'CLOSED'

                      AND wntf1.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

                      AND wntf1.subject IS NOT NULL

                      AND wntf.item_key IS NOT NULL

                      AND UPPER(wntf1.subject) LIKE '%OFFER%'

                      AND UPPER(SUBSTR(wntf1.subject, INSTR(wntf1.subject,'IRC'))) like 'IRC%'

                      )

                      AND TRIM(wpav.name) = TRIM(SUBSTR(SUBSTR(wntf.subject, INSTR(wntf.subject,'IRC')),1,INSTR(SUBSTR(wntf.subject, INSTR(wntf.subject,'IRC')),' ')))

                      AND wio.vacancy_id = wpav.vacancy_id

                      AND wio.offer_id = offr_rec.offer_id

                      AND wpav.vacancy_id = wpaaf.vacancy_id

                      AND wio.offer_assignment_id = wpaaf.assignment_id

                      AND wpapf.person_id = wpaaf.person_id

                      )

                      AND TRIM(wpav2.name) = TRIM(SUBSTR(SUBSTR(wntf2.subject, INSTR(wntf2.subject,'IRC')),1,INSTR(SUBSTR(wntf2.subject, INSTR(wntf2.subject,'IRC')),' ')))

                      AND wio2.vacancy_id = wpav2.vacancy_id

                      AND wio2.offer_id = offr_rec.offer_id

                      AND to_char(wntf2.end_date,'MM/DD/YYYY') = to_char(offr_rec.last_update_date,'MM/DD/YYYY')

                      AND wpav2.vacancy_id = wpaaf2.vacancy_id

                      AND wio2.offer_assignment_id = wpaaf2.assignment_id

                      AND wpapf2.person_id = wpaaf2.person_id

       

       

       

      ----------------------------------------------------------------------------------------------------------------------------------------------

      Execution Plan for above query

       

      -----------------------------------------------------------------------------------------------------------------------------------------------

       

      call     count       cpu    elapsed       disk      query    current        rows

      ------- ------  -------- ---------- ---------- ---------- ----------  ----------

      Parse        0      0.00       0.00          0          0          0           0

      Execute    514      0.10       0.11          0          0          0           0

      Fetch      514     56.34      57.06        223   10993203          0           4

      ------- ------  -------- ---------- ---------- ---------- ----------  ----------

      total     1028     56.45      57.17        223   10993203          0           4

       

       

      Misses in library cache during parse: 0

      Optimizer mode: ALL_ROWS

      Parsing user id: 193  (APPS)   (recursive depth: 1)

       

       

      Rows     Execution Plan

      -------  ---------------------------------------------------

            0  SELECT STATEMENT   MODE: ALL_ROWS

            0   HASH (UNIQUE)

            0    NESTED LOOPS

            0     NESTED LOOPS

            0      MERGE JOIN (CARTESIAN)

            0       NESTED LOOPS

            0        NESTED LOOPS

            0         NESTED LOOPS

            0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                           'IRC_OFFERS' (TABLE)

            0           INDEX   MODE: ANALYZED (UNIQUE SCAN) OF

                            'IRC_OFFERS_PK' (INDEX (UNIQUE))

            0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                           'PER_ALL_VACANCIES' (TABLE)

            0           INDEX   MODE: ANALYZED (UNIQUE SCAN) OF

                            'PER_VACANCIES_PK' (INDEX (UNIQUE))

            0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                          'PER_ALL_ASSIGNMENTS_F' (TABLE)

            0          INDEX   MODE: ANALYZED (RANGE SCAN) OF

                           'PER_ASSIGNMENTS_F_PK' (INDEX (UNIQUE))

            0        INDEX   MODE: ANALYZED (RANGE SCAN) OF

                         'PER_PEOPLE_F_PK' (INDEX (UNIQUE))

            0       BUFFER (SORT)

            0        VIEW OF 'VW_SQ_1' (VIEW)

            0         HASH (GROUP BY)

            0          FILTER

            0           NESTED LOOPS

            0            NESTED LOOPS

            0             NESTED LOOPS

            0              NESTED LOOPS

            0               NESTED LOOPS

            0                TABLE ACCESS   MODE: ANALYZED (BY

                                 INDEX ROWID) OF 'IRC_OFFERS' (TABLE)

            0                 INDEX   MODE: ANALYZED (UNIQUE SCAN)

                                  OF 'IRC_OFFERS_PK' (INDEX (UNIQUE))

            0                TABLE ACCESS   MODE: ANALYZED (BY

                                 INDEX ROWID) OF 'PER_ALL_VACANCIES' (TABLE)

            0                 INDEX   MODE: ANALYZED (UNIQUE SCAN)

                                  OF 'PER_VACANCIES_PK' (INDEX (UNIQUE))

            0               TABLE ACCESS   MODE: ANALYZED (BY INDEX

                                ROWID) OF 'PER_ALL_ASSIGNMENTS_F' (TABLE)

            0                INDEX   MODE: ANALYZED (RANGE SCAN) OF

                                 'PER_ASSIGNMENTS_F_PK' (INDEX (UNIQUE))

            0              INDEX   MODE: ANALYZED (RANGE SCAN) OF

                               'PER_PEOPLE_F_PK' (INDEX (UNIQUE))

            0             INDEX   MODE: ANALYZED (RANGE SCAN) OF

                              'WF_NOTIFICATIONS_N5' (INDEX)

            0            TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)

                             OF 'WF_NOTIFICATIONS' (TABLE)

            0           SORT (AGGREGATE)

            0            FILTER

            0             TABLE ACCESS   MODE: ANALYZED (BY INDEX

                              ROWID) OF 'WF_NOTIFICATIONS' (TABLE)

            0              INDEX   MODE: ANALYZED (RANGE SCAN) OF

                               'WF_NOTIFICATIONS_N5' (INDEX)

            0      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF

                       'WF_NOTIFICATIONS_PK' (INDEX (UNIQUE))

            0     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                      'WF_NOTIFICATIONS' (TABLE)

       

       

       

       

      Elapsed times include waiting on following events:

        Event waited on                             Times   Max. Wait  Total Waited

        ----------------------------------------   Waited  ----------  ------------

        db file sequential read                       193        0.01          0.06

        db file parallel read                           4        0.16          0.16

        Disk file operations I/O                        1        0.00          0.00

      ********************************************************************************

       

       

       

       

      Regards,

        • 1. Re: Tune the query to get the final approver
          Forenoon

          Hi,

          try this :

              SELECT distinct wntf2.recipient_role

                          FROM

                          apps.wf_notifications wntf2,

                          apps.irc_offers wio2,

                          apps.per_all_vacancies wpav2,

                          apps.per_all_assignments_f wpaaf2,

                          apps.per_all_people_f wpapf2

                          WHERE wntf2.message_type = 'HRSSA'

                          AND wntf2.status = 'CLOSED'

                          AND wntf2.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

                          AND wntf2.subject IS NOT NULL

                          AND wntf2.item_key IS NOT NULL

                          AND UPPER(wntf2.subject) LIKE '%OFFER%'

                          AND TRIM(wpav2.name) = TRIM(SUBSTR(SUBSTR(wntf2.subject, INSTR(wntf2.subject,'IRC')),1,INSTR(SUBSTR(wntf2.subject, INSTR(wntf2.subject,'IRC')),' ')))

                          AND wio2.vacancy_id = wpav2.vacancy_id

                          AND wio2.offer_id = offr_rec.offer_id

                          AND to_char(wntf2.end_date,'MM/DD/YYYY') = to_char(offr_rec.last_update_date,'MM/DD/YYYY')

                          AND wpav2.vacancy_id = wpaaf2.vacancy_id

                          AND wio2.offer_assignment_id = wpaaf2.assignment_id

                          AND wpapf2.person_id = wpaaf2.person_id

                          AND ROWNUM = 1

                          ORDER BY wntf2.notification_id DESC