1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help needed interpreting explain plan and suggestions tuning the query

Discussion in 'SQL PL/SQL' started by james shallow, Apr 15, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Please, I need your help to tune this query as it is very slow, taking about 30 minutes to run.
    Code (SQL):
    SELECT brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code)               AS FAM
          ,DECODE('2', '1', org.team_area
                      ,NULL)                                                 AS AREA
          ,org.body_position                                                 AS body_pos
          ,org.full_name
          ,peo.surname || ', ' || peo.initials                               AS NAME
          ,peo.payroll_number
          ,DECODE(BRSF_SES_IND(peo.person_code),
                  NULL, BRSF_SES_IND(peo.person_code,TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))
                 ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')), BRSF_SES_IND(peo.person_code)
                 ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) ||
                                                ' at revision date, ' ||
                                                BRSF_SES_IND(peo.person_code) || ' now') AS SES
          ,app.pos_post_number                                               AS post_number
          ,app.appointment_code
          ,sev.start_date                                                    AS salev_date
          ,org.team_area
          ,BRSF_APPT_GRADE(app.appointment_code,      sev.start_date)        AS grade_name
          ,BRSF_APPT_HOURS(app.appointment_code,      sev.start_date)        AS hours_per_week
          ,BRSF_APPT_SALARY(app.appointment_code,     sev.start_date)        AS salary
          ,BRSF_APPT_FTE_SALARY(app.appointment_code, sev.start_date)        AS fte_salary
          ,DECODE(BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date)
                 ,'N', 'NON-SPINAL'
                 ,'Y', 'Spinal')                                             AS spinal_ind
          ,gra.psp_category || gra.psp_group                                 AS staffcat
      FROM hes_people              peo
          ,hes_periods_of_service  POS
          ,hes_appointments        app
          ,brs_post_org_histories  poh
          ,hes_organisation_units  org
          ,brsv_salary_events      sev
          ,hes_app_grade_histories agh
          ,hes_grades              gra
    WHERE peo.person_code        = pos.per_person_code
       AND pos.period_of_ser_code = app.pes_period_of_ser_code
       AND app.pos_post_number    = poh.post_number
       AND app.appointment_code   = agh.app_appointment_code
       AND sev.appointment_code   = app.appointment_code
       AND poh.organisation_code  = org.organisation_code
       AND poh.start_date         = (SELECT MAX(poh2.start_date)
                                       FROM brs_post_org_histories poh2
                                      WHERE poh2.post_number = poh.post_number
                                        AND poh2.start_date <= sev.start_date)
       AND agh.gra_grade_code     = gra.grade_code
       AND TO_DATE('01-AUG-2016', 'DD-MON-YYYY') BETWEEN pos.start_date
                                                     AND pos.end_date
       AND sev.start_date BETWEEN gra.start_date
                              AND gra.end_date
       AND sev.start_date BETWEEN agh.effective_start_date
                              AND agh.effective_end_date
       AND sev.start_date BETWEEN app.start_date
                              AND app.end_date
       AND app.end_date          >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')
       AND app.pos_post_number   != 'Z1'
       AND (
            sev.start_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')
            OR
            sev.start_date = (SELECT MAX(sev2.start_date)
                                FROM brsv_salary_events sev2
                               WHERE sev2.appointment_code = sev.appointment_code
                                 AND sev2.start_date < TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))
           )
       AND (
            (
             'Y' = 'Y'
             AND
             BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'Y'
            )
            OR
            (
             'N' = 'Y'
             AND
             BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'N'
            )
           )
       AND (
            org.team_area IN ('A')
            OR 'null' IN ('A')
           )
    ORDER BY 1 ASC
             ,2 ASC
             ,3 ASC
             ,4 ASC
             ,5 ASC
             ,6 ASC
             ,7 ASC
             ,8 ASC
             ,9 ASC
             ,10 DESC;
    explain plan
    -------------------



    ---------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 185 | 14207 |
    | 1 | SORT ORDER BY | | 1 | 185 | 14207 |
    | 2 | FILTER | | | | |
    | 3 | NESTED LOOPS | | 1 | 185 | 14203 |
    | 4 | NESTED LOOPS | | 1 | 185 | 14203 |
    | 5 | HASH JOIN | | 1 | 166 | 14202 |
    | 6 | NESTED LOOPS | | 1 | 166 | 14202 |
    | 7 | STATISTICS COLLECTOR | | | | |
    | 8 | HASH JOIN | | 1 | 145 | 14199 |
    | 9 | NESTED LOOPS | | 1 | 145 | 14199 |
    | 10 | STATISTICS COLLECTOR | | | | |
    | 11 | NESTED LOOPS | | 1 | 128 | 14197 |
    | 12 | NESTED LOOPS | | 1 | 87 | 14196 |
    | 13 | NESTED LOOPS | | 1 | 70 | 14193 |
    | 14 | HASH JOIN | | 1 | 48 | 14190 |
    | 15 | TABLE ACCESS FULL | HES_APPOINTMENTS | 8056 | 204K| 603 |
    | 16 | VIEW | BRSV_SALARY_EVENTS | 424K| 9119K| 13581 |
    | 17 | SORT UNIQUE | | 424K| 10M| 13581 |
    | 18 | UNION-ALL | | | | |
    | 19 | HASH JOIN | | 5502 | 231K| 896 |
    | 20 | NESTED LOOPS | | 5502 | 231K| 896 |
    | 21 | NESTED LOOPS | | | | |
    | 22 | STATISTICS COLLECTOR | | | | |
    | 23 | HASH JOIN | | 5502 | 188K| 335 |
    | 24 | NESTED LOOPS | | 5502 | 188K| 335 |
    | 25 | STATISTICS COLLECTOR | | | | |
    | 26 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
    | 27 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_INCR_HISTORIES | 4 | 92 | 326 |
    | 28 | BITMAP CONVERSION TO ROWIDS | | | | |
    | 29 | BITMAP AND | | | | |
    | 30 | BITMAP CONVERSION FROM ROWIDS | | | | |
    | 31 | SORT ORDER BY | | | | |
    | 32 | INDEX RANGE SCAN | BRSI_AGIH_PRIME | 361 | | 9 |
    | 33 | BITMAP CONVERSION FROM ROWIDS | | | | |
    | 34 | INDEX RANGE SCAN | BRSI_AGIH_GRADECODE | 361 | | 71 |
    | 35 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
    | 36 | INDEX UNIQUE SCAN | HES_AGH_PK | | | |
    | 37 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 1 | 8 | 560 |
    | 38 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 39 | HASH JOIN | | 28942 | 2148K| 920 |
    | 40 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 41 | HASH JOIN | | 28942 | 1921K| 358 |
    | 42 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 7009 | 157K| 18 |
    | 43 | HASH JOIN | | 87637 | 3851K| 335 |
    | 44 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
    | 45 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
    | 46 | HASH JOIN | | 121K| 2248K| 747 |
    | 47 | NESTED LOOPS | | 121K| 2248K| 747 |
    | 48 | STATISTICS COLLECTOR | | | | |
    | 49 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 50 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 184 |
    | 51 | INDEX FAST FULL SCAN | BRSI_AGHW_PRIME | 121K| 1301K| 184 |
    | 52 | HASH JOIN | | 91342 | 1694K| 691 |
    | 53 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 54 | INDEX FAST FULL SCAN | BRSI_AGIH_GHC_START | 91342 | 981K| 128 |
    | 55 | HASH JOIN | | 127K| 2372K| 731 |
    | 56 | NESTED LOOPS | | 127K| 2372K| 731 |
    | 57 | STATISTICS COLLECTOR | | | | |
    | 58 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 59 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 168 |
    | 60 | INDEX FAST FULL SCAN | BRSI_SALHISTS_PRIME | 127K| 1373K| 168 |
    | 61 | HASH JOIN | | 49691 | 1698K| 566 |
    | 62 | NESTED LOOPS | | 49691 | 1698K| 566 |
    | 63 | NESTED LOOPS | | | | |
    | 64 | STATISTICS COLLECTOR | | | | |
    | 65 | HASH JOIN | | 587 | 8218 | 4 |
    | 66 | INDEX FULL SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
    | 67 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
    | 68 | INDEX RANGE SCAN | BRSI_AGH_GRADECODE | | | |
    | 69 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 85 | 1785 | 560 |
    | 70 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2133K| 560 |
    | 71 | TABLE ACCESS BY INDEX ROWID | HES_PERIODS_OF_SERVICE | 1 | 22 | 2 |
    | 72 | INDEX UNIQUE SCAN | HES_PES_PRIME | 1 | | 1 |
    | 73 | TABLE ACCESS BY INDEX ROWID BATCHED | BRS_POST_ORG_HISTORIES | 1 | 17 | 3 |
    | 74 | INDEX RANGE SCAN | BRSI_POH_POST_START | 1 | | 2 |
    | 75 | SORT AGGREGATE | | 1 | 13 | |
    | 76 | FIRST ROW | | 1 | 13 | 3 |
    | 77 | INDEX RANGE SCAN (MIN/MAX) | BRSI_POH_POST_START | 1 | 13 | 3 |
    | 78 | TABLE ACCESS BY INDEX ROWID | HES_ORGANISATION_UNITS | 1 | 41 | 1 |
    | 79 | INDEX UNIQUE SCAN | HES_ORG_PRIME | 1 | | 0 |
    | 80 | TABLE ACCESS BY INDEX ROWID | HES_PEOPLE | 1 | 17 | 2 |
    | 81 | INDEX UNIQUE SCAN | HES_PER_PRIME | 1 | | 1 |
    | 82 | TABLE ACCESS FULL | HES_PEOPLE | 1 | 17 | 2 |
    | 83 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
    | 84 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | 2 |
    | 85 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
    | 86 | INDEX UNIQUE SCAN | HES_GRA_PRIME | 1 | | 0 |
    | 87 | TABLE ACCESS BY INDEX ROWID | HES_GRADES | 1 | 19 | 1 |
    | 88 | SORT AGGREGATE | | 1 | 22 | |
    | 89 | VIEW | BRSV_SALARY_EVENTS | 8557 | 183K| 736 |
    | 90 | SORT UNIQUE | | 8557 | 292K| 736 |
    | 91 | UNION-ALL | | | | |
    | 92 | NESTED LOOPS | | 3 | 57 | 8 |
    | 93 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 94 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 95 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 2 |
    | 96 | NESTED LOOPS | | 5 | 95 | 8 |
    | 97 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 98 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 99 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 2 | 22 | 2 |
    | 100 | NESTED LOOPS | | 3 | 57 | 8 |
    | 101 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 102 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 103 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 2 |
    | 104 | HASH JOIN | | 8545 | 292K| 10 |
    | 105 | HASH JOIN | | 99 | 1386 | 4 |
    | 106 | INDEX SKIP SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
    | 107 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
    | 108 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 104K| 2148K| 4 |
    | 109 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 110 | HASH JOIN | | 1 | 30 | 696 |
    | 111 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 112 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 113 | VIEW | VW_JF_SET$8DB8F874 | 33940 | 729K| 692 |
    | 114 | SORT UNIQUE | | 33940 | 2076K| 692 |
    | 115 | UNION-ALL | | | | |
    | 116 | HASH JOIN | | 5501 | 188K| 335 |
    | 117 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
    | 118 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
    | 119 | HASH JOIN | | 28439 | 1888K| 357 |
    | 120 | HASH JOIN | | 4913 | 215K| 26 |
    | 121 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
    | 122 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 6888 | 154K| 18 |
    | 123 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
    ---------------------------------------------------------------------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version

    ---------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    ---------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 185 | 14207 |
    | 1 | SORT ORDER BY | | 1 | 185 | 14207 |
    | 2 | FILTER | | | | |
    | 3 | NESTED LOOPS | | 1 | 185 | 14203 |
    | 4 | NESTED LOOPS | | 1 | 185 | 14203 |
    | 5 | HASH JOIN | | 1 | 166 | 14202 |
    | 6 | NESTED LOOPS | | 1 | 166 | 14202 |
    | 7 | STATISTICS COLLECTOR | | | | |
    | 8 | HASH JOIN | | 1 | 145 | 14199 |
    | 9 | NESTED LOOPS | | 1 | 145 | 14199 |
    | 10 | STATISTICS COLLECTOR | | | | |
    | 11 | NESTED LOOPS | | 1 | 128 | 14197 |
    | 12 | NESTED LOOPS | | 1 | 87 | 14196 |
    | 13 | NESTED LOOPS | | 1 | 70 | 14193 |
    | 14 | HASH JOIN | | 1 | 48 | 14190 |
    | 15 | TABLE ACCESS FULL | HES_APPOINTMENTS | 8056 | 204K| 603 |
    | 16 | VIEW | BRSV_SALARY_EVENTS | 424K| 9119K| 13581 |
    | 17 | SORT UNIQUE | | 424K| 10M| 13581 |
    | 18 | UNION-ALL | | | | |
    | 19 | HASH JOIN | | 5502 | 231K| 896 |
    | 20 | NESTED LOOPS | | 5502 | 231K| 896 |
    | 21 | NESTED LOOPS | | | | |
    | 22 | STATISTICS COLLECTOR | | | | |
    | 23 | HASH JOIN | | 5502 | 188K| 335 |
    | 24 | NESTED LOOPS | | 5502 | 188K| 335 |
    | 25 | STATISTICS COLLECTOR | | | | |
    | 26 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
    | 27 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_INCR_HISTORIES | 4 | 92 | 326 |
    | 28 | BITMAP CONVERSION TO ROWIDS | | | | |
    | 29 | BITMAP AND | | | | |
    | 30 | BITMAP CONVERSION FROM ROWIDS | | | | |
    | 31 | SORT ORDER BY | | | | |
    | 32 | INDEX RANGE SCAN | BRSI_AGIH_PRIME | 361 | | 9 |
    | 33 | BITMAP CONVERSION FROM ROWIDS | | | | |
    | 34 | INDEX RANGE SCAN | BRSI_AGIH_GRADECODE | 361 | | 71 |
    | 35 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
    | 36 | INDEX UNIQUE SCAN | HES_AGH_PK | | | |
    | 37 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 1 | 8 | 560 |
    | 38 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 39 | HASH JOIN | | 28942 | 2148K| 920 |
    | 40 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 41 | HASH JOIN | | 28942 | 1921K| 358 |
    | 42 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 7009 | 157K| 18 |
    | 43 | HASH JOIN | | 87637 | 3851K| 335 |
    | 44 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
    | 45 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
    | 46 | HASH JOIN | | 121K| 2248K| 747 |
    | 47 | NESTED LOOPS | | 121K| 2248K| 747 |
    | 48 | STATISTICS COLLECTOR | | | | |
    | 49 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 50 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 184 |
    | 51 | INDEX FAST FULL SCAN | BRSI_AGHW_PRIME | 121K| 1301K| 184 |
    | 52 | HASH JOIN | | 91342 | 1694K| 691 |
    | 53 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 54 | INDEX FAST FULL SCAN | BRSI_AGIH_GHC_START | 91342 | 981K| 128 |
    | 55 | HASH JOIN | | 127K| 2372K| 731 |
    | 56 | NESTED LOOPS | | 127K| 2372K| 731 |
    | 57 | STATISTICS COLLECTOR | | | | |
    | 58 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
    | 59 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 168 |
    | 60 | INDEX FAST FULL SCAN | BRSI_SALHISTS_PRIME | 127K| 1373K| 168 |
    | 61 | HASH JOIN | | 49691 | 1698K| 566 |
    | 62 | NESTED LOOPS | | 49691 | 1698K| 566 |
    | 63 | NESTED LOOPS | | | | |
    | 64 | STATISTICS COLLECTOR | | | | |
    | 65 | HASH JOIN | | 587 | 8218 | 4 |
    | 66 | INDEX FULL SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
    | 67 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
    | 68 | INDEX RANGE SCAN | BRSI_AGH_GRADECODE | | | |
    | 69 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 85 | 1785 | 560 |
    | 70 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2133K| 560 |
    | 71 | TABLE ACCESS BY INDEX ROWID | HES_PERIODS_OF_SERVICE | 1 | 22 | 2 |
    | 72 | INDEX UNIQUE SCAN | HES_PES_PRIME | 1 | | 1 |
    | 73 | TABLE ACCESS BY INDEX ROWID BATCHED | BRS_POST_ORG_HISTORIES | 1 | 17 | 3 |
    | 74 | INDEX RANGE SCAN | BRSI_POH_POST_START | 1 | | 2 |
    | 75 | SORT AGGREGATE | | 1 | 13 | |
    | 76 | FIRST ROW | | 1 | 13 | 3 |
    | 77 | INDEX RANGE SCAN (MIN/MAX) | BRSI_POH_POST_START | 1 | 13 | 3 |
    | 78 | TABLE ACCESS BY INDEX ROWID | HES_ORGANISATION_UNITS | 1 | 41 | 1 |
    | 79 | INDEX UNIQUE SCAN | HES_ORG_PRIME | 1 | | 0 |
    | 80 | TABLE ACCESS BY INDEX ROWID | HES_PEOPLE | 1 | 17 | 2 |
    | 81 | INDEX UNIQUE SCAN | HES_PER_PRIME | 1 | | 1 |
    | 82 | TABLE ACCESS FULL | HES_PEOPLE | 1 | 17 | 2 |
    | 83 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
    | 84 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | 2 |
    | 85 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
    | 86 | INDEX UNIQUE SCAN | HES_GRA_PRIME | 1 | | 0 |
    | 87 | TABLE ACCESS BY INDEX ROWID | HES_GRADES | 1 | 19 | 1 |
    | 88 | SORT AGGREGATE | | 1 | 22 | |
    | 89 | VIEW | BRSV_SALARY_EVENTS | 8557 | 183K| 736 |
    | 90 | SORT UNIQUE | | 8557 | 292K| 736 |
    | 91 | UNION-ALL | | | | |
    | 92 | NESTED LOOPS | | 3 | 57 | 8 |
    | 93 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 94 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 95 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 2 |
    | 96 | NESTED LOOPS | | 5 | 95 | 8 |
    | 97 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 98 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 99 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 2 | 22 | 2 |
    | 100 | NESTED LOOPS | | 3 | 57 | 8 |
    | 101 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 102 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 103 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 2 |
    | 104 | HASH JOIN | | 8545 | 292K| 10 |
    | 105 | HASH JOIN | | 99 | 1386 | 4 |
    | 106 | INDEX SKIP SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
    | 107 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
    | 108 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 104K| 2148K| 4 |
    | 109 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 110 | HASH JOIN | | 1 | 30 | 696 |
    | 111 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
    | 112 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
    | 113 | VIEW | VW_JF_SET$8DB8F874 | 33940 | 729K| 692 |
    | 114 | SORT UNIQUE | | 33940 | 2076K| 692 |
    | 115 | UNION-ALL | | | | |
    | 116 | HASH JOIN | | 5501 | 188K| 335 |
    | 117 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
    | 118 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
    | 119 | HASH JOIN | | 28439 | 1888K| 357 |
    | 120 | HASH JOIN | | 4913 | 215K| 26 |
    | 121 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
    | 122 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 6888 | 154K| 18 |
    | 123 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
    ---------------------------------------------------------------------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You absolutely need to upgrade the PLAN_TABLE definition as you are missing a lot of important information about the plan; run $ORACLE_HOME/rdbms/admin/utlxplan.sql to do this.

    After you upgrade PLAN_TABLE run the query again and generate the plan, then post that new plan.

    We really can't help without having the complete picture although I can make a couple of observations.

    The STATISTICS COLLECTOR steps indicate dynamic sampling is occurring, where the optimizer is generating statistics while generating the eventual execution plan. Without the current PLAN_TABLE definition it's impossible to know which steps were replaced based on the gathered statistics so the plan you posted is more confusing than it should be.

    I can help more once PLAN_TABLE is the current definition.
     
    james shallow likes this.
  3. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Thanks
    zargon,

    I am not sure if I have the privilege to run this script but if I can't then I will have to wait till I talk to the DBA on Tuesday otherwise I will do as you suggested. Many thanks for your help
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The DBA SHOULD have caught this before now. You probably don't have sufficient privilege to run that script; when this gets resolved generate the plan again and post it here.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is part of the information you are currently missing (an example from the documentation follows):

    Code (SQL):
    -------------------------------------------------------------------------------
    |Id | Operation                     | Name     |ROWS|Bytes|Cost(%CPU)|TIME    |
    -------------------------------------------------------------------------------
    |  0| SELECT STATEMENT              |                     |4|128|7(0)|00:00:01|
    | *1|  HASH JOIN                    |                     |4|128|7(0)|00:00:01|
    |- 2|   NESTED LOOPS                |                     | |   |    |        |
    |- 3|    NESTED LOOPS               |                     | |128|7(0)|00:00:01|
    |- 4|     STATISTICS COLLECTOR      |                     | |   |    |        |
    | *5|      TABLE ACCESS FULL        | ORDER_ITEMS         |4| 48|3(0)|00:00:01|
    |-*6|     INDEX UNIQUE SCAN         | PRODUCT_INFORMATI_PK|1|   |0(0)|00:00:01|
    |- 7|    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION |1| 20|1(0)|00:00:01|
    |  8|   TABLE ACCESS FULL           | PRODUCT_INFORMATION |1| 20|1(0)|00:00:01|
    -------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
       5 - FILTER("O"."UNIT_PRICE"=15 AND "QUANTITY">1)
       6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

    Note
    -----
       - this IS an adaptive plan (ROWS marked '-' are inactive)
    Such information is necessary to 'weed out' the steps the optimizer did NOT choose.
     
  6. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Hi zargon, yes I ran this script and got this error. Pls how can I correct this? thanks

    Error starting at line : 37 File @ C:\app\product\11.2.0\client_2\RDBMS\ADMIN\utlxplan.sql
    In command -
    Code (SQL):
    Error starting at line : 37 File @ C:\app\product\11.2.0\client_2\RDBMS\ADMIN\utlxplan.SQL
    IN command -
    CREATE TABLE PLAN_TABLE (
            statement_id       varchar2(30),
            plan_id            NUMBER,
            TIMESTAMP          DATE,
            remarks            varchar2(4000),
            operation          varchar2(30),
            options            varchar2(255),
            object_node        varchar2(128),
            object_owner       varchar2(30),
            object_name        varchar2(30),
            object_alias       varchar2(65),
            object_instance    NUMERIC,
            object_type        varchar2(30),
            optimizer          varchar2(255),
            search_columns     NUMBER,
            id                 NUMERIC,
            parent_id          NUMERIC,
            depth              NUMERIC,
            POSITION           NUMERIC,
            cost               NUMERIC,
            cardinality        NUMERIC,
            bytes              NUMERIC,
            other_tag          varchar2(255),
            partition_start    varchar2(255),
            partition_stop     varchar2(255),
            partition_id       NUMERIC,
            other              long,
            distribution       varchar2(30),
            cpu_cost           NUMERIC,
            io_cost            NUMERIC,
            temp_space         NUMERIC,
            access_predicates  varchar2(4000),
            filter_predicates  varchar2(4000),
            projection         varchar2(4000),
            TIME               NUMERIC,
            qblock_name        varchar2(30),
            other_xml          CLOB
    )
    Error report -
    SQL Error: ORA-00955: name IS already used BY an existing object
    00955. 00000 -  "name is already used by an existing object"
    *Cause:  
    *Action:
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can try this from the SQL> prompt:

    rename plan_table to plan_table_old;

    Then run the script again.
     
  8. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Thanks, zargon. I have been able to recreate the plan table following your instructions.
    I am in the process of creating the explain plan. Please if I will definitely post it as soon as I can. Many thanks for your help.
     
  9. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Hi zargon, I have posted the new plan, sorry I couldn't format it to fit this space but I have uploaded the file.
    Many thanks

    Plan hash value: 4038509449

    -----------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 211 | | 22510 (13)| 00:00:01 |
    | 1 | SORT ORDER BY | | 1 | 211 | | 22510 (13)| 00:00:01 |
    |* 2 | FILTER | | | | | | |
    | 3 | NESTED LOOPS | | 1 | 211 | | 22506 (13)| 00:00:01 |
    | 4 | NESTED LOOPS | | 1 | 211 | | 22506 (13)| 00:00:01 |
    | 5 | NESTED LOOPS | | 1 | 191 | | 22504 (13)| 00:00:01 |
    | 6 | NESTED LOOPS | | 1 | 165 | | 22502 (13)| 00:00:01 |
    |* 7 | HASH JOIN | | 451 | 64493 | | 1289 (3)| 00:00:01 |
    | 8 | TABLE ACCESS FULL | HES_GRADES | 330 | 7920 | | 3 (0)| 00:00:01 |
    |* 9 | HASH JOIN | | 451 | 53669 | | 1286 (3)| 00:00:01 |
    |* 10 | HASH JOIN | | 362 | 34028 | | 724 (3)| 00:00:01 |
    |* 11 | HASH JOIN | | 1582 | 99666 | | 121 (4)| 00:00:01 |
    |* 12 | TABLE ACCESS FULL | HES_ORGANISATION_UNITS | 36 | 1548 | | 18 (6)| 00:00:01 |
    |* 13 | TABLE ACCESS FULL | BRS_POST_ORG_HISTORIES | 41559 | 811K| | 103 (3)| 00:00:01 |
    |* 14 | TABLE ACCESS FULL | HES_APPOINTMENTS | 8056 | 243K| | 603 (2)| 00:00:01 |
    | 15 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2550K| | 560 (3)| 00:00:01 |
    |* 16 | VIEW | BRSV_SALARY_EVENTS | 1 | 22 | | 47 (13)| 00:00:01 |
    | 17 | SORT UNIQUE | | 6 | 257 | | 47 (13)| 00:00:01 |
    | 18 | UNION ALL PUSHED PREDICATE | | | | | | |
    |* 19 | FILTER | | | | | | |
    | 20 | NESTED LOOPS | | 1 | 53 | | 8 (0)| 00:00:01 |
    | 21 | NESTED LOOPS | | 1 | 38 | | 7 (0)| 00:00:01 |
    | 22 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 23 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_INCR_HISTORIES | 1 | 28 | | 3 (0)| 00:00:01 |
    |* 25 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 1 | | | 2 (0)| 00:00:01 |
    |* 26 | INDEX RANGE SCAN | BRSI_SIM_GRC_IP | 1 | 15 | | 1 (0)| 00:00:01 |
    |* 27 | FILTER | | | | | | |
    | 28 | NESTED LOOPS | | 1 | 90 | | 10 (0)| 00:00:01 |
    | 29 | NESTED LOOPS | | 1 | 64 | | 8 (0)| 00:00:01 |
    | 30 | NESTED LOOPS | | 1 | 38 | | 7 (0)| 00:00:01 |
    | 31 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 32 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 33 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_INCR_HISTORIES | 1 | 28 | | 3 (0)| 00:00:01 |
    |* 34 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 1 | | | 2 (0)| 00:00:01 |
    |* 35 | INDEX RANGE SCAN | BRSI_SIM_GRC_IP | 1 | 26 | | 1 (0)| 00:00:01 |
    |* 36 | INDEX RANGE SCAN | BRSI_PRH_SPINE_POINT_START | 1 | 26 | | 2 (0)| 00:00:01 |
    |* 37 | FILTER | | | | | | |
    | 38 | NESTED LOOPS | | 1 | 23 | | 6 (0)| 00:00:01 |
    | 39 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 40 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 41 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 13 | | 2 (0)| 00:00:01 |
    |* 42 | FILTER | | | | | | |
    | 43 | NESTED LOOPS | | 1 | 23 | | 6 (0)| 00:00:01 |
    | 44 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 45 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 46 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 1 | 13 | | 2 (0)| 00:00:01 |
    |* 47 | FILTER | | | | | | |
    | 48 | NESTED LOOPS | | 1 | 23 | | 6 (0)| 00:00:01 |
    | 49 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 50 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 51 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 13 | | 2 (0)| 00:00:01 |
    |* 52 | FILTER | | | | | | |
    | 53 | NESTED LOOPS | | 1 | 45 | | 5 (0)| 00:00:01 |
    | 54 | NESTED LOOPS | | 1 | 45 | | 5 (0)| 00:00:01 |
    | 55 | NESTED LOOPS | | 1 | 37 | | 4 (0)| 00:00:01 |
    |* 56 | INDEX FULL SCAN | BRSI_PSHH_UI | 1 | 12 | | 1 (0)| 00:00:01 |
    |* 57 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_HISTORIES | 1 | 25 | | 3 (0)| 00:00:01 |
    |* 58 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 2 (0)| 00:00:01 |
    |* 59 | INDEX UNIQUE SCAN | HES_GRA_PRIME | 1 | | | 0 (0)| 00:00:01 |
    |* 60 | TABLE ACCESS BY INDEX ROWID | HES_GRADES | 1 | 8 | | 1 (0)| 00:00:01 |
    |* 61 | TABLE ACCESS BY INDEX ROWID | HES_PERIODS_OF_SERVICE | 1 | 26 | | 2 (0)| 00:00:01 |
    |* 62 | INDEX UNIQUE SCAN | HES_PES_PRIME | 1 | | | 1 (0)| 00:00:01 |
    |* 63 | INDEX UNIQUE SCAN | HES_PER_PRIME | 1 | | | 1 (0)| 00:00:01 |
    | 64 | TABLE ACCESS BY INDEX ROWID | HES_PEOPLE | 1 | 20 | | 2 (0)| 00:00:01 |
    | 65 | SORT AGGREGATE | | 1 | 15 | | | |
    | 66 | FIRST ROW | | 1 | 15 | | 3 (0)| 00:00:01 |
    |* 67 | INDEX RANGE SCAN (MIN/MAX) | BRSI_POH_POST_START | 1 | 15 | | 3 (0)| 00:00:01 |
    | 68 | SORT AGGREGATE | | 1 | 22 | | | |
    | 69 | VIEW | BRSV_SALARY_EVENTS | 25225 | 541K| | 1611 (4)| 00:00:01 |
    | 70 | SORT UNIQUE | | 25225 | 1108K| 1532K| 1611 (4)| 00:00:01 |
    | 71 | UNION-ALL | | | | | | |
    | 72 | NESTED LOOPS | | 1 | 23 | | 6 (0)| 00:00:01 |
    | 73 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 74 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 75 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 13 | | 2 (0)| 00:00:01 |
    | 76 | NESTED LOOPS | | 3 | 69 | | 6 (0)| 00:00:01 |
    | 77 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 78 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 79 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 2 | 26 | | 2 (0)| 00:00:01 |
    | 80 | NESTED LOOPS | | 3 | 69 | | 6 (0)| 00:00:01 |
    | 81 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 82 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 83 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 2 | 26 | | 2 (0)| 00:00:01 |
    |* 84 | HASH JOIN | | 25216 | 1108K| | 10 (20)| 00:00:01 |
    |* 85 | HASH JOIN | | 189 | 3780 | | 4 (0)| 00:00:01 |
    |* 86 | INDEX SKIP SCAN | BRSI_PSHH_UI | 83 | 996 | | 1 (0)| 00:00:01 |
    | 87 | TABLE ACCESS FULL | HES_GRADES | 330 | 2640 | | 3 (0)| 00:00:01 |
    | 88 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 104K| 2557K| | 4 (0)| 00:00:01 |
    |* 89 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    |* 90 | HASH JOIN | | 2 | 64 | | 711 (6)| 00:00:01 |
    | 91 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 10 | | 4 (0)| 00:00:01 |
    |* 92 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | | 3 (0)| 00:00:01 |
    | 93 | VIEW | VW_JF_SET$8DB8F874 | 153K| 3298K| | 705 (6)| 00:00:01 |
    | 94 | SORT UNIQUE | | 153K| 11M| | 705 (6)| 00:00:01 |
    | 95 | UNION-ALL | | | | | | |
    |* 96 | HASH JOIN | | 10990 | 461K| | 336 (4)| 00:00:01 |
    |* 97 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 21645 | | 7 (0)| 00:00:01 |
    |* 98 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 87714 | 2398K| | 327 (3)| 00:00:01 |
    |* 99 | HASH JOIN | | 142K| 10M| | 369 (8)| 00:00:01 |
    |*100 | HASH JOIN | | 13897 | 705K| | 26 (4)| 00:00:01 |
    | 101 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 37518 | | 7 (0)| 00:00:01 |
    |*102 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 6936 | 176K| | 18 (0)| 00:00:01 |
    |*103 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 87714 | 2398K| | 327 (3)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter("POH"."START_DATE"= (SELECT MAX("POH2"."START_DATE") FROM "BRS_POST_ORG_HISTORIES" "POH2" WHERE
    "POH2"."START_DATE"<=:B1 AND "POH2"."POST_NUMBER"=:B2) AND ("SEV"."START_DATE">=TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd
    hh24:mi:ss') OR "SEV"."START_DATE"= (SELECT MAX("SEV2"."START_DATE") FROM ( (SELECT "AGH"."APP_APPOINTMENT_CODE"
    "APPOINTMENT_CODE","HWH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_AGH_HRS_WKS_HISTORIES" "HWH" WHERE "HWH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd
    hh24:mi:ss') AND "HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGH"."APP_APPOINTMENT_CODE"=:B3)UNION (SELECT
    "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","AGIH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH" WHERE "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00',
    'syyyy-mm-dd hh24:mi:ss') AND "AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND
    "AGH"."APP_APPOINTMENT_CODE"=:B4)UNION (SELECT "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","SH"."EFFECTIVE_START_DATE"
    "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES" "AGH","OES"."HES_SALARY_HISTORIES" "SH" WHERE
    "SH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGH"."APP_APPOINTMENT_CODE"=:B5)UNION (SELECT
    "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","SHH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_GRADES" "G","OES"."HES_PSP_STD_HRS_HISTORIES" "SHH","OES"."HES_GRADES" "G" WHERE
    "SHH"."PSP_GROUP"="G"."PSP_GROUP" AND "SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "SHH"."EFFECTIVE_START_DATE"<TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SHH"."PSP_GROUP"="G"."PSP_GROUP" AND
    "SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "AGH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd
    hh24:mi:ss') AND "AGH"."APP_APPOINTMENT_CODE"=:B6 AND "SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE")UNION (SELECT
    "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","ITEM_2" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES" "AGH", ( (SELECT
    "AGIH"."AGH_APP_GRADE_HIST_CODE" "ITEM_1","SIM"."EFFECTIVE_START_DATE" "ITEM_2" FROM "OES"."HES_SPINAL_INCREMENT_MAPS"
    "SIM","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH" WHERE
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND "SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE" AND
    "SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "SIM"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))UNION (SELECT
    "AGIH"."AGH_APP_GRADE_HIST_CODE" "ITEM_1","PRH"."EFFECTIVE_START_DATE" "ITEM_2" FROM "OES"."HES_SPINAL_INCREMENT_MAPS"
    "SIM","OES"."HES_POINT_RATE_HISTORIES" "PRH","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH","OES"."HES_APP_GRADE_INCR_HISTORIES"
    "AGIH" WHERE "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    7 - access("AGH"."GRA_GRADE_CODE"="GRA"."GRADE_CODE")
    9 - access("APP"."APPOINTMENT_CODE"="AGH"."APP_APPOINTMENT_CODE")
    10 - access("APP"."POS_POST_NUMBER"="POH"."POST_NUMBER")
    11 - access("POH"."ORGANISATION_CODE"="ORG"."ORGANISATION_CODE")
    12 - filter("ORG"."TEAM_AREA"='A')
    13 - filter("POH"."POST_NUMBER"<>'Z1')
    14 - filter("APP"."END_DATE">=TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "APP"."POS_POST_NUMBER"<>'Z1')
    16 - filter("BRSF_APPT_SPINAL_IND"("APP"."APPOINTMENT_CODE","SEV"."START_DATE")='Y')
    19 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    23 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE")
    24 - filter("AGIH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "AGIH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "AGIH"."EFFECTIVE_END_DATE">="APP"."START_DATE")
    25 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGIH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("AGIH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "AGIH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    26 - access("AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "SIM"."EFFECTIVE_START_DATE">="GRA"."START_DATE" AND "SIM"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("SIM"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "SIM"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="APP"."END_DATE" AND "SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    27 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    32 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE")
    33 - filter("AGIH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "AGIH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "AGIH"."EFFECTIVE_END_DATE">="APP"."START_DATE")
    34 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGIH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("AGIH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "AGIH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    35 - access("AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT")
    36 - access("SIM"."SPP_SPC_SPINE_NAME"="PRH"."SPP_SPC_SPINE_NAME" AND "SIM"."SPP_POINT"="PRH"."SPP_POINT" AND
    "PRH"."EFFECTIVE_START_DATE">="GRA"."START_DATE" AND "PRH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("PRH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "PRH"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="APP"."END_DATE" AND "PRH"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    37 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    40 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE")
    41 - access("HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "HWH"."EFFECTIVE_START_DATE">="GRA"."START_DATE"
    AND "HWH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("HWH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "HWH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "HWH"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND
    "HWH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    42 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    45 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE")
    46 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGIH"."EFFECTIVE_START_DATE">="GRA"."START_DATE"
    AND "AGIH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("AGIH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "AGIH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "AGIH"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND
    "AGIH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    47 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    50 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE")
    51 - access("SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "SH"."EFFECTIVE_START_DATE">="GRA"."START_DATE" AND
    "SH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("SH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "SH"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND
    "SH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    52 - filter("APP"."END_DATE">="APP"."START_DATE" AND "APP"."START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "APP"."END_DATE">="AGH"."EFFECTIVE_START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "APP"."START_DATE"<="GRA"."END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND "APP"."END_DATE">="GRA"."START_DATE"
    AND "AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "GRA"."END_DATE">="GRA"."START_DATE")
    56 - access("SHH"."EFFECTIVE_START_DATE">="GRA"."START_DATE" AND "SHH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("SHH"."EFFECTIVE_START_DATE">="GRA"."START_DATE" AND "SHH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE" AND
    "SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND
    "SHH"."EFFECTIVE_START_DATE">="APP"."START_DATE" AND "SHH"."EFFECTIVE_START_DATE"<="APP"."END_DATE")
    57 - filter("AGH"."EFFECTIVE_END_DATE">="GRA"."START_DATE" AND "AGH"."EFFECTIVE_END_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "AGH"."EFFECTIVE_END_DATE">="APP"."START_DATE" AND "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE")
    58 - access("AGH"."APP_APPOINTMENT_CODE"="APP"."APPOINTMENT_CODE" AND "AGH"."EFFECTIVE_START_DATE"<="GRA"."END_DATE")
    filter("AGH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<="APP"."END_DATE" AND
    "SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE")
    59 - access("G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE")
    60 - filter("SHH"."PSP_GROUP"="G"."PSP_GROUP" AND "SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY")
    61 - filter("POS"."END_DATE">=TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "POS"."START_DATE"<=TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    62 - access("POS"."PERIOD_OF_SER_CODE"="APP"."PES_PERIOD_OF_SER_CODE")
    63 - access("PEO"."PERSON_CODE"="POS"."PER_PERSON_CODE")
    67 - access("POH2"."POST_NUMBER"=:B1 AND "POH2"."START_DATE"<=:B2)
    74 - access("AGH"."APP_APPOINTMENT_CODE"=:B1)
    75 - access("HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "HWH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01
    00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    78 - access("AGH"."APP_APPOINTMENT_CODE"=:B1)
    79 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGIH"."EFFECTIVE_START_DATE"<TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    82 - access("AGH"."APP_APPOINTMENT_CODE"=:B1)
    83 - access("SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "SH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01
    00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    84 - access("G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE")
    filter("SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE")
    85 - access("SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "SHH"."PSP_GROUP"="G"."PSP_GROUP")
    86 - access("SHH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    filter("SHH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    89 - access("AGH"."APP_APPOINTMENT_CODE"=:B1 AND "AGH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd
    hh24:mi:ss'))
    90 - access("ITEM_1"="AGH"."APP_GRADE_HIST_CODE")
    92 - access("AGH"."APP_APPOINTMENT_CODE"=:B1)
    96 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    filter("SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    97 - filter("SIM"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    98 - filter("AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    99 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    filter("PRH"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    100 - access("SIM"."SPP_SPC_SPINE_NAME"="PRH"."SPP_SPC_SPINE_NAME" AND "SIM"."SPP_POINT"="PRH"."SPP_POINT")
    102 - filter("PRH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    103 - filter("AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    Note
    -----
     

    Attached Files:

  10. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Sorry I missed this bit of the note:

    Note
    -----
    - dynamic statistics used: dynamic sampling (level=2)
    - this is an adaptive plan
    - 13 Sql Plan Directives used for this statement
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The lines with a * have predicate information listed at the end of the plan and indicate if the steps are access or filter steps. Access steps determine HOW the optimizer gets the data, filter steps show how the result set was 'massaged' to return the data you asked for.

    You might want to read here:

    https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL94618

    This is for 12.1 but it will help you read through and understand the plan, and I won't write that much in a single post. :)

    If you have further questions come back; I may not see it for a while but I do check occasionally and will respond.
     
    james shallow likes this.
  12. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Thanks so much for your help.
    Just one question, please. If a query uses a UNION ALL on two queries and it is very slow but individual query are fast about 2 minutes to return results but the UNION ALL takes 20 minutes, do you know what could be going wrong here?
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    UNION ALL joins two or more result sets; it usually doesn't order the entire result but it could take time and resources to generate the final results. Your UNION ALL statements feed SORT UNIQUE statements, which are the steps that are taking the time; I see no temp space estimates so these sorts are being performed in memory but they DO take time. Imagine sorting playing cards by suit and rank and imagine you have 1000 decks of cards all mixed together. Sorting that pile will take a bit of time. One of the sort operations is operating on around 157,000 rows so sorting and de-duplicating that volume of data won't be fast.
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation

    Hi.
    Notice that in the query plan there are many operations of multi-block reads(FTS,IFFS,..).Is individual querys had these operations ?
    Optimize the conditions - you need to get rid of unnecessary conditions OR.
    You also need to pay attention to the queries that are executed in your packages.

    p.s. Can you , find in v$sql your query and see what value has the field "is_reoptimizable" ?.
    As an experiment, try using the hint :
    select /*+ optimizer_features_enable('11.2.0.4') no_expand */

    Code (Text):

    AND (
            sev.start_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')
            OR
            sev.start_date = (SELECT MAX(sev2.start_date)
                                FROM brsv_salary_events sev2
                               WHERE sev2.appointment_code = sev.appointment_code
                                 AND sev2.start_date < TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))
           )
       AND (
            (
             'Y' = 'Y'
             AND
             BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'Y'
            )
            OR
            (
             'N' = 'Y'
             AND
             BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'N'
            )
           )
       AND (
            org.team_area IN ('A')
            OR 'null' IN ('A')
     

    If you might , then write on your result...
     
    Last edited: Apr 17, 2017
    james shallow likes this.
  15. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    Thanks for your reply Sergey,

    You are correct, Sorry I haven't got the privilege to the v$sql views but I can ask the DBA when I get back to work. Meanwhile, I have been looking into tuning this query and as you spotted. this view "brsv_salary_events" is having a significant impact on the query. I have looked at the DDL and it has so many UNIONS and I can either rewrite this or replace it with the "WITH CLAUSE" in the query. When I replaced it with the "WITH CLAUSE", the query runs a bit faster. I will post both the amended query and the DDL for the view just in case I could get some help from you or anyone. Any help will be appreciated.

    The new query with "WITH CLAUSE"

    Code (SQL):
    SELECT * FROM (
    WITH qry1 AS (
    SELECT appointment_code, sev.start_date start_date FROM BRSV_SALARY_EVENTS sev
    WHERE sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') ))
    SELECT
    brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
    decode('2','1',org.team_area,NULL) AREA,
    org.body_position body_pos,
    org.full_name,
    peo.surname||', '||peo.initials name,
    peo.payroll_number,
    decode(BRSF_SES_IND(peo.person_code),
    NULL,  BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')),
    BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')),  BRSF_SES_IND(peo.person_code),
    BRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')) ||
    ' at revision date, ' || BRSF_SES_IND(peo.person_code) || ' now') SES,
    app.pos_post_number post_number,
    app.appointment_code,
    qry1.start_date salev_date,
    org.team_area,
    BRSF_APPT_GRADE(app.appointment_code,qry1.start_date) grade_name,
    BRSF_APPT_HOURS(app.appointment_code, qry1.start_date) hours_per_week,
    BRSF_APPT_SALARY(app.appointment_code, qry1.start_date) salary,
    BRSF_APPT_FTE_SALARY(app.appointment_code, qry1.start_date) fte_salary,
    decode(BRSF_APPT_SPINAL_IND(app.appointment_code, qry1.start_date),
    'N','NON-SPINAL',
    'Y','Spinal') spinal_ind,
    gra.psp_category||gra.psp_group staffcat
    FROM
    hes_people peo,
    hes_periods_of_service pos,
    hes_appointments app,
    brs_post_org_histories poh,
    hes_organisation_units org,
    --brsv_salary_events sev,
    hes_app_grade_histories agh,
    hes_grades gra,
    qry1
    WHERE peo.person_code = pos.per_person_code
    AND to_date('01-AUG-2016','DD-MON-YYYY') BETWEEN pos.start_date AND pos.end_date
    AND pos.period_of_ser_code = app.pes_period_of_ser_code
    AND app.pos_post_number = poh.post_number
    AND poh.start_date= (SELECT MAX(poh2.start_date)
                         FROM brs_post_org_histories poh2
                         WHERE poh2.post_number=poh.post_number
                         AND poh2.start_date<=qry1.start_date)
    AND poh.organisation_code = org.organisation_code
    AND agh.gra_grade_code = gra.grade_code
    AND qry1.start_date BETWEEN gra.start_date AND gra.end_date
    AND app.appointment_code = agh.app_appointment_code
    AND qry1.start_date BETWEEN agh.effective_start_date AND agh.effective_end_date
    AND qry1.start_date BETWEEN app.start_date AND app.end_date
    AND app.end_date >= to_date('01-AUG-2016','DD-MON-YYYY')
    AND app.pos_post_number != 'Z1'
    AND qry1.appointment_code = app.appointment_code
    -------
    /*  and app.appointment_code=sev_prev.appointment_code
      and sev_prev.start_date=
          (select max(subsev.start_date)
           from brsv_salary_events subsev
           where subsev.appointment_code=sev_prev.appointment_code
             and subsev.start_date<to_date('01-AUG-2016','DD-MON-YYYY'))*/

    UNION ALL
    (
    SELECT
    BRSF_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
    decode('2','1',org.team_area,NULL) AREA,
    org.body_position org_body_pos,
    org.full_name,
    peo.surname||', '||peo.initials name,
    peo.payroll_number,
    decode(BRSF_SES_IND(peo.person_code),
    NULL, BRSF_SES_IND(peo.person_code,pos.start_date),
    BRSF_SES_IND(peo.person_code,pos.start_date),  BRSF_SES_IND(peo.person_code),
    BRSF_SES_IND(peo.person_code,pos.start_date) ||
    ' at start date, ' || BRSF_SES_IND(peo.person_code) || ' now') SES,
    app.pos_post_number post_number,
    app.appointment_code,
    pos.start_date service_start_date,
    org.team_area,
    BRSF_APPT_GRADE(app.appointment_code, pos.start_date) grade_name,
    BRSF_APPT_HOURS(app.appointment_code, pos.start_date) hours_per_week,
    BRSF_APPT_SALARY(app.appointment_code, pos.start_date) salary,
    BRSF_APPT_FTE_SALARY(app.appointment_code, pos.start_date) fte_salary,
    decode(BRSF_APPT_SPINAL_IND(app.appointment_code, pos.start_date),
    'N','NON-SPINAL',
    'Y','Spinal') spinal_ind,
    gra.psp_category||gra.psp_group staffcat
    FROM
    hes_people peo,
    hes_periods_of_service pos,
    hes_appointments app,
    brs_post_org_histories poh,
    hes_organisation_units org,
    hes_app_grade_histories agh,
    hes_grades gra
    WHERE  peo.person_code = pos.per_person_code
    AND pos.start_date > to_date('01-AUG-2016', 'DD-MON-YYYY')
    AND (BRSF_JOB_FAMILY(app.appointment_code) IN ('R')  OR 'null' IN (''))
    AND pos.period_of_ser_code = app.pes_period_of_ser_code
    AND pos.start_date BETWEEN app.start_date AND app.end_date
    AND app.pos_post_number != 'Z1'
    AND app.pos_post_number = poh.post_number
    AND pos.start_date BETWEEN poh.start_date AND poh.end_date
    AND poh.organisation_code = org.organisation_code
    AND app.appointment_code = agh.app_appointment_code
    AND pos.start_date BETWEEN agh.effective_start_date AND agh.effective_end_date
    AND agh.gra_grade_code = gra.grade_code
    AND pos.start_date BETWEEN gra.start_date AND gra.end_date)
    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 DESC)
    Code (SQL):
      CREATE OR REPLACE FORCE VIEW "TRS"."BRSV_SALARY_EVENTS" ("APPOINTMENT_CODE", "START_DATE") AS
      SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           sim.effective_start_date         START_DATE
    FROM hes_spinal_increment_maps sim,
         hes_app_grade_incr_histories agih,
         hes_app_grade_histories agh
    WHERE agih.agh_app_grade_hist_code=agh.app_grade_hist_code
      AND agih.sim_gra_grade_code=sim.gra_grade_code
      AND agih.sim_increment_point=sim.increment_point
      AND sim.effective_start_date BETWEEN agih.effective_start_date
                                       AND agih.effective_end_date
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           prh.effective_start_date         START_DATE
    FROM hes_point_rate_histories prh,
         hes_spinal_increment_maps sim,
         hes_app_grade_incr_histories agih,
         hes_app_grade_histories agh
    WHERE agih.agh_app_grade_hist_code=agh.app_grade_hist_code
      AND agih.sim_gra_grade_code=sim.gra_grade_code
      AND agih.sim_increment_point=sim.increment_point
      AND sim.spp_point=prh.spp_point
      AND sim.spp_spc_spine_name=prh.spp_spc_spine_name
      AND prh.effective_start_date BETWEEN agih.effective_start_date
                                       AND agih.effective_end_date
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           hwh.effective_start_date         START_DATE
    FROM hes_agh_hrs_wks_histories hwh,
         hes_app_grade_histories agh
    WHERE hwh.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           agih.effective_start_date        START_DATE
    FROM hes_app_grade_incr_histories agih,
         hes_app_grade_histories agh
    WHERE agih.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           sh.effective_start_date          START_DATE
    FROM hes_salary_histories sh,
         hes_app_grade_histories agh
    WHERE sh.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           shh.effective_start_date         START_DATE
    FROM HES_PSP_STD_HRS_HISTORIES shh,
         HES_GRADES g,
         HES_APP_GRADE_HISTORIES agh
    WHERE shh.psp_category=g.psp_category
      AND shh.psp_group=g.psp_group
      AND g.grade_code=agh.gra_grade_code
      AND shh.effective_start_date BETWEEN agh.effective_start_date
                                       AND agh.effective_end_date
    --
    --union
    --
    --select app.appointment_code             APPOINTMENT_CODE,
    --       phwh.start_date                  START_DATE
    --from hes_pgh_hrs_wks_histories phwh,
    --     hes_appointments app
    --where app.pos_post_number=phwh.pgh_pos_post_number
    --  and phwh.start_date between app.start_date
    --                          and app.end_date
    ;
     
     
  16. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    Tell me, why these blocks are not used filter on date (Are these tables is history of data)?
    Can you provide an example query from
    the view "TRS"."BRSV_SALARY_EVENTS" and plan his execution ?

    Possible problems : do not push the predicates, do not use the optimal index for the historical tables, and etc.

    Code (Text):

    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           hwh.effective_start_date         START_DATE
    FROM hes_agh_hrs_wks_histories hwh,
         hes_app_grade_histories agh
    WHERE hwh.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           agih.effective_start_date        START_DATE
    FROM hes_app_grade_incr_histories agih,
         hes_app_grade_histories agh
    WHERE agih.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
    UNION
    --
    SELECT agh.app_appointment_code         APPOINTMENT_CODE,
           sh.effective_start_date          START_DATE
    FROM hes_salary_histories sh,
         hes_app_grade_histories agh
    WHERE sh.agh_app_grade_hist_code=agh.app_grade_hist_code
    --
     
     
    Last edited: Apr 17, 2017
  17. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    This is the plan for the view Sorry Sergey, I don't know how to format this explain plan to fit this space. Yes, this view is based on the history of appointments dates within the establishment.

    Plan hash value: 2198637991

    ------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 424K| 9119K| | 12309 (2)| 00:00:01 |
    |* 1 | VIEW | BRSV_SALARY_EVENTS | 424K| 9119K| | 12309 (2)| 00:00:01 |
    | 2 | SORT UNIQUE | | 424K| 9022K| 12M| 12309 (2)| 00:00:01 |
    | 3 | UNION-ALL | | | | | | |
    |* 4 | HASH JOIN | | 121K| 2248K| | 747 (3)| 00:00:01 |
    | 5 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    | 6 | INDEX FAST FULL SCAN | BRSI_AGHW_PRIME | 121K| 1301K| | 184 (3)| 00:00:01 |
    |* 7 | HASH JOIN | | 91342 | 1694K| | 691 (3)| 00:00:01 |
    | 8 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    | 9 | INDEX FAST FULL SCAN | BRSI_AGIH_GHC_START | 91342 | 981K| | 128 (3)| 00:00:01 |
    |* 10 | HASH JOIN | | 127K| 2372K| | 731 (3)| 00:00:01 |
    | 11 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    | 12 | INDEX FAST FULL SCAN | BRSI_SALHISTS_PRIME | 127K| 1373K| | 168 (3)| 00:00:01 |
    |* 13 | HASH JOIN | | 49691 | 1698K| | 566 (3)| 00:00:01 |
    |* 14 | HASH JOIN | | 587 | 8218 | | 4 (0)| 00:00:01 |
    | 15 | INDEX FULL SCAN | BRSI_PSHH_UI | 83 | 747 | | 1 (0)| 00:00:01 |
    | 16 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | | 3 (0)| 00:00:01 |
    | 17 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2133K| | 560 (3)| 00:00:01 |
    |* 18 | HASH JOIN | | 34444 | 1009K| | 1254 (4)| 00:00:01 |
    | 19 | VIEW | VW_JF_SET$534BCA89 | 34444 | 740K| | 692 (4)| 00:00:01 |
    | 20 | SORT UNIQUE | | 34444 | 2109K| | 692 (4)| 00:00:01 |
    | 21 | UNION-ALL | | | | | | |
    |* 22 | HASH JOIN | | 5502 | 188K| | 335 (3)| 00:00:01 |
    | 23 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | | 7 (0)| 00:00:01 |
    | 24 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| | 326 (3)| 00:00:01 |
    |* 25 | HASH JOIN | | 28942 | 1921K| | 358 (5)| 00:00:01 |
    | 26 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 7009 | 157K| | 18 (0)| 00:00:01 |
    |* 27 | HASH JOIN | | 87637 | 3851K| | 335 (3)| 00:00:01 |
    | 28 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | | 7 (0)| 00:00:01 |
    | 29 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| | 326 (3)| 00:00:01 |
    | 30 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    | 31 | SORT AGGREGATE | | 1 | 9 | | | |
    | 32 | VIEW | BRSV_SALARY_EVENTS | 371K| 3265K| | 10698 (2)| 00:00:01 |
    | 33 | SORT UNIQUE | | 371K| 7391K| 10M| 10698 (2)| 00:00:01 |
    | 34 | UNION-ALL | | | | | | |
    |* 35 | HASH JOIN | | 118K| 2189K| | 748 (3)| 00:00:01 |
    | 36 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    |* 37 | INDEX FAST FULL SCAN | BRSI_AGHW_PRIME | 118K| 1267K| | 184 (3)| 00:00:01 |
    |* 38 | HASH JOIN | | 88382 | 1639K| | 691 (3)| 00:00:01 |
    |* 39 | INDEX FAST FULL SCAN | BRSI_AGIH_GHC_START | 88382 | 949K| | 128 (3)| 00:00:01 |
    | 40 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    |* 41 | HASH JOIN | | 122K| 2275K| | 732 (3)| 00:00:01 |
    | 42 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    |* 43 | INDEX FAST FULL SCAN | BRSI_SALHISTS_PRIME | 122K| 1317K| | 169 (3)| 00:00:01 |
    |* 44 | HASH JOIN | | 8545 | 292K| | 566 (3)| 00:00:01 |
    |* 45 | HASH JOIN | | 99 | 1386 | | 4 (0)| 00:00:01 |
    |* 46 | INDEX SKIP SCAN | BRSI_PSHH_UI | 83 | 747 | | 1 (0)| 00:00:01 |
    | 47 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | | 3 (0)| 00:00:01 |
    |* 48 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2148K| | 560 (3)| 00:00:01 |
    |* 49 | HASH JOIN | | 33940 | 994K| | 1254 (4)| 00:00:01 |
    | 50 | VIEW | VW_JF_SET$256ABE4D | 33940 | 729K| | 692 (4)| 00:00:01 |
    | 51 | SORT UNIQUE | | 33940 | 2076K| | 692 (4)| 00:00:01 |
    | 52 | UNION-ALL | | | | | | |
    |* 53 | HASH JOIN | | 5501 | 188K| | 335 (3)| 00:00:01 |
    |* 54 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | | 7 (0)| 00:00:01 |
    |* 55 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| | 327 (3)| 00:00:01 |
    |* 56 | HASH JOIN | | 28439 | 1888K| | 357 (4)| 00:00:01 |
    |* 57 | HASH JOIN | | 4913 | 215K| | 26 (4)| 00:00:01 |
    | 58 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | | 7 (0)| 00:00:01 |
    |* 59 | INDEX FAST FULL SCAN| BRSI_PRH_SPINE_POINT_START | 6888 | 154K| | 18 (0)| 00:00:01 |
    |* 60 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| | 327 (3)| 00:00:01 |
    | 61 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| | 560 (3)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter("SEV"."START_DATE">=TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
    "SEV"."START_DATE"= (SELECT MAX("SEV2"."START_DATE") FROM ( (SELECT "AGH"."APP_APPOINTMENT_CODE"
    "APPOINTMENT_CODE","HWH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_AGH_HRS_WKS_HISTORIES" "HWH","OES"."HES_AGH_HRS_WKS_HISTORIES" "HWH" WHERE
    "HWH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND
    "HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "HWH"."EFFECTIVE_START_DATE"<TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))UNION (SELECT "AGH"."APP_APPOINTMENT_CODE"
    "APPOINTMENT_CODE","AGIH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH","OES"."HES_APP_GRADE_HISTORIES" "AGH" WHERE
    "AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "AGIH"."EFFECTIVE_START_DATE"<TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE"
    )UNION (SELECT "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","SH"."EFFECTIVE_START_DATE" "START_DATE" FROM
    "OES"."HES_APP_GRADE_HISTORIES" "AGH","OES"."HES_SALARY_HISTORIES" "SH","OES"."HES_SALARY_HISTORIES" "SH" WHERE
    "SH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND
    "SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE" AND "SH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01
    00:00:00', 'syyyy-mm-dd hh24:mi:ss'))UNION (SELECT "AGH"."APP_APPOINTMENT_CODE"
    "APPOINTMENT_CODE","SHH"."EFFECTIVE_START_DATE" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH","OES"."HES_GRADES" "G","OES"."HES_PSP_STD_HRS_HISTORIES" "SHH","OES"."HES_APP_GRADE_HISTORIES" "AGH"
    WHERE "G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE" AND "SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE"
    AND "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "AGH"."EFFECTIVE_START_DATE"<TO_DATE('
    2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SHH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SHH"."PSP_GROUP"="G"."PSP_GROUP" AND
    "SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE" AND "G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE" AND
    "AGH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))UNION (SELECT
    "AGH"."APP_APPOINTMENT_CODE" "APPOINTMENT_CODE","ITEM_2" "START_DATE" FROM "OES"."HES_APP_GRADE_HISTORIES"
    "AGH", ( (SELECT "AGIH"."AGH_APP_GRADE_HIST_CODE" "ITEM_1","SIM"."EFFECTIVE_START_DATE" "ITEM_2" FROM
    "OES"."HES_SPINAL_INCREMENT_MAPS" "SIM","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH" WHERE
    "SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "SIM"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))UNION (SELECT
    "AGIH"."AGH_APP_GRADE_HIST_CODE" "ITEM_1","PRH"."EFFECTIVE_START_DATE" "ITEM_2" FROM
    "OES"."HES_SPINAL_INCREMENT_MAPS" "SIM","OES"."HES_POINT_RATE_HISTORIES"
    "PRH","OES"."HES_APP_GRADE_INCR_HISTORIES" "AGIH" WHERE
    "PRH"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE" AND "AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    "SIM"."SPP_POINT"="PRH"."SPP_POINT" AND "SIM"."SPP_SPC_SPINE_NAME"="PRH"."SPP_SPC_SPINE_NAME" AND
    "PRH"."EFFECTIVE_STAR)
    4 - access("HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    7 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    10 - access("SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    13 - access("G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE")
    filter("SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE")
    14 - access("SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "SHH"."PSP_GROUP"="G"."PSP_GROUP")
    18 - access("ITEM_1"="AGH"."APP_GRADE_HIST_CODE")
    22 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    filter("SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    25 - access("SIM"."SPP_SPC_SPINE_NAME"="PRH"."SPP_SPC_SPINE_NAME" AND "SIM"."SPP_POINT"="PRH"."SPP_POINT")
    filter("PRH"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    27 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    35 - access("HWH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    37 - filter("HWH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    38 - access("AGIH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    39 - filter("AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    41 - access("SH"."AGH_APP_GRADE_HIST_CODE"="AGH"."APP_GRADE_HIST_CODE")
    43 - filter("SH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    44 - access("G"."GRADE_CODE"="AGH"."GRA_GRADE_CODE")
    filter("SHH"."EFFECTIVE_START_DATE">="AGH"."EFFECTIVE_START_DATE" AND
    "SHH"."EFFECTIVE_START_DATE"<="AGH"."EFFECTIVE_END_DATE")
    45 - access("SHH"."PSP_CATEGORY"="G"."PSP_CATEGORY" AND "SHH"."PSP_GROUP"="G"."PSP_GROUP")
    46 - access("SHH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    filter("SHH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    48 - filter("AGH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    49 - access("ITEM_1"="AGH"."APP_GRADE_HIST_CODE")
    53 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    filter("SIM"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "SIM"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    54 - filter("SIM"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    55 - filter("AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    56 - access("AGIH"."SIM_INCREMENT_POINT"="SIM"."INCREMENT_POINT" AND
    "AGIH"."SIM_GRA_GRADE_CODE"="SIM"."GRA_GRADE_CODE")
    filter("PRH"."EFFECTIVE_START_DATE">="AGIH"."EFFECTIVE_START_DATE" AND
    "PRH"."EFFECTIVE_START_DATE"<="AGIH"."EFFECTIVE_END_DATE")
    57 - access("SIM"."SPP_SPC_SPINE_NAME"="PRH"."SPP_SPC_SPINE_NAME" AND "SIM"."SPP_POINT"="PRH"."SPP_POINT")
    59 - filter("PRH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    60 - filter("AGIH"."EFFECTIVE_START_DATE"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    Note
    -----
    - dynamic statistics used: dynamic sampling (level=2)
    - this is an adaptive plan

    Sample Data

    Code (SQL):
    SELECT appointment_code, sev.start_date start_date FROM BRSV_SALARY_EVENTS sev
    WHERE sev.start_date >= to_date('01-AUG-2016','DD-MON-YYYY')
      OR sev.start_date =
          (SELECT MAX(sev2.start_date)
           FROM brsv_salary_events sev2
           WHERE sev2.start_date < to_date('01-AUG-2016','DD-MON-YYYY') )

    App_code Start_date
    622755    01-AUG-16
    622796    01-AUG-16
    622889    01-AUG-16
    622893    01-AUG-16
    623034    01-AUG-16
    623110    01-AUG-16
    623134    01-AUG-16
    623262    01-AUG-16
    623326    01-AUG-16
    623429    01-AUG-16
    623466    01-AUG-16
    623816    01-AUG-16
    623822    01-AUG-16
    623829    01-AUG-16
    623864    01-AUG-16
    623947    01-AUG-16
    624133    01-AUG-16
    624169    01-AUG-16
    624413    01-AUG-16
    624529    01-AUG-16
    624555    01-AUG-16
    624678    01-AUG-16
    624678    01-FEB-17
    624699    01-AUG-16
    624715    01-AUG-16
    624770    01-AUG-16
    624799    01-AUG-16
    624843    01-AUG-16
    624921    01-AUG-16
    624996    01-AUG-16

    SELECT * FROM

    622699    12-NOV-84
    622699    01-APR-85
    622699    01-FEB-86
    622699    01-APR-86
    622699    01-FEB-87
    622699    01-APR-87
    622699    01-FEB-88
    622699    01-APR-88
    622699    01-FEB-89
    622699    01-APR-89
    622700    01-APR-82
    622700    01-APR-83
    622700    01-APR-84
    622700    01-APR-85
    622700    01-APR-86
    622700    01-APR-87
    622700    01-APR-88
    622701    09-JUN-87
    622702    01-OCT-87
    622702    01-JUL-88
    622702    01-AUG-88
    622703    14-SEP-87
    622703    01-APR-88
    622703    01-DEC-88
    622703    02-AUG-89
    622703    01-APR-90
    622703    01-APR-91
    622704    20-JAN-86
    622704    01-APR-86
    622704    01-APR-87
     
    Last edited: Apr 17, 2017
  18. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation


    This condition is not optimal.
    But, the best solution will be to optimize the logic of a report(query), than a technical optimization ,if possible....

    Your historical tables partitioned ?
    What indexes are defined on your history tables?

    How long does this query :
    Code (Text):

    select max(sev2.start_date)  from brsv_salary_events sev2
    where sev2.start_date < to_date('01-JAN-2016','DD-MON-YYYY');
     

    Possible solutions are few :
    1) change the condition - for example to set the amount of viewing historical data ,for example:
    start_date >= add_months(your_date),-3)
    2) to test
    Code (Text):

     select /*+ optimizer_features_enable('11.2.0.4')  push_subq(tst) no_merge(tst) */
    sev.appointment_code, sev.start_date
    from BRSV_SALARY_EVENTS SEV,(select max(sev2.start_date)  start_date
    from brsv_salary_events sev2
    where sev2.then start_date < date('01-Aug-2016','DD-mon-YYYY')
    ) tst
    where sev.start_date >= tst.start_date;
     
     
    Last edited: Apr 17, 2017
  19. james shallow

    james shallow Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    uk
    I got this error when I ran this, please pardon my inexperience if I have done something wrong. Just to let you know the background, the date parameter will be on the run date usually "1st of August of the year". The idea is to work out staff in employment and their wages up to this date. It is possible they have changed positions(appointments), hence wages could have changed. Another job will run the following month to track and work out old and new salary. But, please don't worry about this.

    Code (SQL):
     SELECT /*+ push_subq(tst) no_merge(tst) */
    appointment_code, North.THEN start_date THEN start_date
    FROM BRSV_SALARY_EVENTS SEV,(SELECT MAX(sev2.THEN start_date) THEN start_date
    FROM brsv_salary_events sev2
    WHERE sev2.THEN start_date < DATE('01-Aug-2016','DD-mon-YYYY')
    ) tst
    WHERE sev.start_date >= tst.start_date;

    ORA-01747: invalid USER.TABLE.COLUMN, TABLE.COLUMN, OR COLUMN specification
    01747. 00000 -  "invalid user.table.column, table.column, or column specification"
    *Cause:  
    *Action:
    Error at Line: 14 COLUMN: 25
     
  20. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    When I edited the scripts , you might have copied them...
    Try this script:
    Code (Text):

     select /*+ optimizer_features_enable('11.2.0.4')  push_subq(tst) no_merge(tst) */
     sev.appointment_code, sev.start_date
    from BRSV_SALARY_EVENTS SEV,
      (select max(sev2.start_date)  start_date
      from brsv_salary_events sev2
      where sev2.then start_date < date('01-Aug-2016','DD-mon-YYYY')
      ) tst
    where sev.start_date >= tst.start_date;

     
    Tell me, what is the period relevant to the date of the change ?
    You can look, for example, earlier than in a month, a quarter from date of start: add_months(run_date,1) ?
    The definition of the period is very important not to view extra data
     
    Last edited: Apr 17, 2017