Home » RDBMS Server » Performance Tuning » SQL Tuning Assistance Required (Oracle 10.2.0.3, PeopleSoft 8.8, PeopleTools 8.49)
SQL Tuning Assistance Required [message #564735] Tue, 28 August 2012 04:55 Go to next message
SBhaumik_DBA
Messages: 3
Registered: August 2012
Junior Member
Hello,

I am running an Oracle 10.2.0.3 on Solaris 5.9 OS. Front end appplication is PeopleSoft v8.8.

From my AWR report I have found below SQL which needs to be tuned:

SELECT TO_CHAR (TO_DATE (TO_CHAR (B.ASOFDATE, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
                  'dd/mm/yyyy'),
         B.EMPLID,
         B.PWCUK_LEGACY_ID,
         B.NAME,
         B.LAST_NAME,
         B.PREFERRED_NAME,
         B.NAME_PREFIX,
         B.PER_ORG,
         B.OFFICER_CD,
         B.EMPL_CLASS,
         B.EMPL_STATUS,
         E.JOBCODE,
         E.DESCR,
         B.JOB_FUNCTION,
         B.PWCE_JOB_FUNC_DESC,
         E.DESCRSHORT,
         B.PWC_FEE_EARNER,
         B.SEX,
         B.AGE,
         TO_CHAR (TO_DATE (TO_CHAR (B.BIRTHDATE, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
                  'dd/mm/yyyy'),
         TO_CHAR (TO_DATE (TO_CHAR (B.HIRE_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
                  'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (
               DECODE (TO_CHAR (B.REHIRE_DT, 'YYYY-MM-DD'),
                       '', TO_CHAR (B.HIRE_DT, 'YYYY-MM-DD'),
                       TO_CHAR (B.REHIRE_DT, 'YYYY-MM-DD')),
               'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (TO_CHAR (B.TERMINATION_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (TO_CHAR (B.CONTRACT_BEGIN_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (TO_CHAR (B.CONTRACT_END_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         FLOOR (
            (SYSDATE
             - TO_DATE (TO_CHAR (B.SERVICE_DT, 'YYYY-MM-DD'), 'YYYY-MM-DD'))
            / 365.25),
         TO_CHAR (TO_DATE (TO_CHAR (B.SERVICE_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
                  'dd/mm/yyyy'),
         B.GRADE,
         C.STEP,
         B.PWCUK_GRADE_DESCR,
         CASE
            WHEN B.GRADE <= '02' THEN '1, Partner'
            WHEN B.GRADE <= '05' THEN '2, Director'
            WHEN B.GRADE = '06' THEN '3, Senior Manager'
            WHEN B.GRADE = '07' THEN '2, Director'
            WHEN B.GRADE <= '13' THEN '3, Senior Manager'
            WHEN B.GRADE <= '17' THEN '4, Manager'
            WHEN B.GRADE <= '22' THEN '5, Senior Associate'
            WHEN B.GRADE = '23' THEN '8, Client Account Support'
            WHEN B.GRADE = '24' THEN '5, Senior Associate'
            WHEN B.GRADE <= '26' THEN '6, Associate'
            WHEN B.GRADE = '27' THEN '5, Senior Associate'
            WHEN B.GRADE = '28' THEN '6, Associate'
            WHEN B.GRADE <= '33' THEN '5, Senior Associate'
            WHEN B.GRADE <= '41' THEN '6, Associate'
            WHEN B.GRADE = '42' THEN '8, Client Account Support'
            WHEN B.GRADE <= '46' THEN '6, Associate'
            WHEN B.GRADE = '47' THEN '7, Other'
            WHEN B.GRADE <= '49' THEN '6, Associate'
            WHEN B.GRADE <= '59' THEN '7, Other'
            WHEN B.GRADE = '60' THEN '5, Senior Associate'
            WHEN B.GRADE <= '62' THEN '6, Associate'
            WHEN B.GRADE = '63' THEN '7, Other'
            WHEN B.GRADE = '64' THEN '8, Client Account Support'
            WHEN B.GRADE <= '79' THEN '7, Other'
            WHEN B.GRADE <= '82' THEN '8, Client Account Support'
            WHEN B.GRADE <= '88' THEN '7, Other'
            WHEN B.GRADE = '89' THEN '9, Support'
            WHEN B.GRADE <= '99' THEN '7, Other'
            ELSE 'Error'
         END,
         FLOOR (
            MONTHS_BETWEEN (
               SYSDATE,
               TO_DATE (TO_CHAR (B.GRADE_ENTRY_DT, 'YYYY-MM-DD'), 'YYYY-MM-DD'))),
         B.PWCUK_OVERTIME_PAY,
         CASE
            WHEN NVL (TO_DATE (TO_CHAR (N.EFFDT, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
                      SYSDATE + 1) > SYSDATE
            THEN
               ' '
            ELSE
               N.CONTRACT_CLAUSE
         END,
         INITCAP (
            DECODE (N.CONTRACT_CLAUSE,
                    'GOA', 'ALL HOURS (PAID AT TIME + 1/2)',
                    'GOB', 'Toil Only; capped @ 2 wks',
                    'GOC', 'TIME + 1/2 OR DOUBLE TIME',
                    'GOD', '?11 PER HOUR',
                    'GOE', 'THRESHOLD 30 HRS/QTR @ TIME',
                    'GOF', 'TIME,TIME + 1/2 OR DOUBLE TIME',
                    'GOG', 'THRESHOLD, OVER 45 HOURS/QTR',
                    'GOH', 'ABAS VAC STUDENT @ ?7/HR',
                    'GON', 'NOT ELIGIBLE',
                    'GOP', 'TOIL/OVERTIME AT TIME',
                    'GOS', 'TIME +1/2 OR TOIL (MANUAL SUB)',
                    'GOT', 'TOIL ONLY',
                    'GOU', 'UNKNOWN (DATA CONVERSION ONLY)',
                    'GOW', 'THRESHOLD 50 HRS @ TIME (SMGR)',
                    'GOX', 'THRESHOLD SM @ 1.5 TIME',
                    'GOY', 'LINE OF SERVICE BONUS',
                    N.CONTRACT_CLAUSE)),
         B.PWCUK_TIMESH_CNTRL,
         B.COMPRATE,
         C.ANNL_BENEF_BASE_RT,
         B.PWCE_BENEFIT_PACK,
         B.BENEFIT_PROGRAM,
         TO_CHAR (TO_DATE (TO_CHAR (B.BEN_EFFDT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
                  'dd/mm/yyyy'),
         C.FTE,
         B.STD_HOURS,
         B.FULL_PART_TIME,
         B.REG_TEMP,
         C.SAL_ADMIN_PLAN,
         B.PWCE_WRK_PAT,
         B.PWCE_WRK_PAT_D,
         B.PWCE_WRK_MON,
         B.PWCE_WRK_TUE,
         B.PWCE_WRK_WED,
         B.PWCE_WRK_THU,
         B.PWCE_WRK_FRI,
         B.PWCE_WRK_SAT,
         B.PWCE_WRK_SUN,
         B.COMPANY,
         B.COMPANY_DESCR,
         B.PWC_ORIG_FIRM,
         B.DEPTID,
         S.DESCR,
         S.PWCUK_DEPARTMENT,
         S.PWCUK_DEPT_DESCR,
         S.PWCUK_BUSINESSUNIT,
         S.PWCUK_BU_DESCR,
         S.PWCUK_SUBREGION,
         S.PWCUK_SR_DESCR,
         S.PWCUK_REGION,
         S.PWCUK_R_DESCR,
         SUBSTR (B.PWCE_BU_DESCR, 16, 15),
         B.PWCUK_MATRNITY_STS,
         B.PWCUK_ABS_ACTION,
         B.PWCUK_ABS_REASON,
         TO_CHAR (
            TO_DATE (TO_CHAR (B.PWCUK_ABS_START_DT, 'YYYY-MM-DD'),
                     'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (TO_CHAR (B.PWCUK_ABS_END_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         B.PWCUK_INTL_SEC_STS,
         DECODE (B.EMPL_CLASS, 'INT', B.PWCUK_HOME_DEPTID, ''),
         DECODE (B.EMPL_CLASS, 'INT', B.PWCUK_HOME_DESCR, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_DEPARTMENT, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_DEPT_DESCR, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_BUSINESSUNIT, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_BU_DESCR, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_SUBREGION, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_SR_DESCR, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_REGION, ''),
         DECODE (B.EMPL_CLASS, 'INT', A.PWCUK_R_DESCR, ''),
         DECODE (
            B.EMPL_CLASS,
            'INT', DECODE (A.PWCUK_REGION,
                           'OU21', 'Advisory',
                           'OU80', 'Advisory',
                           'OU05', 'Assurance',
                           'OU55', 'Assurance',
                           'OU65', 'Assurance',
                           'OU66', 'Assurance',
                           'OU89', 'Landwell',
                           'OU15', 'Tax',
                           'OU70', 'Tax',
                           'OU71', 'Tax',
                           'OU32', 'Not In Use',
                           'OU81', 'Not In Use',
                           'OU31', 'BPO',
                           'OU47', 'BPO',
                           'OU59', 'BPO',
                           'OU46', 'IFS',
                           'OU48', 'IFS',
                           'OU49', 'IFS',
                           'OU50', 'IFS',
                           'OU51', 'IFS',
                           'OU52', 'IFS',
                           'OU53', 'IFS',
                           'OU54', 'IFS',
                           'OU90', 'IFS',
                           'OU94', 'IFS',
                           'OU95', 'IFS',
                           'OU96', 'IFS',
                           'OU97', 'IFS',
                           'OU98', 'IFS',
                           'OU30', 'MCS',
                           'OU57', 'MCS',
                           'OU75', 'MCS',
                           'OU76', 'MCS',
                           'ERROR - LoS'),
            ''),
         TO_CHAR (
            TO_DATE (
               (CASE
                   WHEN F.ACTION IN ('CSB', 'CSS')
                   THEN
                      TO_CHAR (F.EFFDT, 'YYYY-MM-DD')
                   ELSE
                      TO_CHAR (B.PWCUK_SEC_START_DT, 'YYYY-MM-DD')
                END),
               'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         TO_CHAR (
            TO_DATE (
               (CASE
                   WHEN I.ACTION IN ('CSE', 'CSP')
                   THEN
                      TO_CHAR (I.EFFDT, 'YYYY-MM-DD')
                   ELSE
                      TO_CHAR (B.PWCUK_SEC_END_DT, 'YYYY-MM-DD')
                END),
               'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         CASE WHEN B.RATING_SCALE <> 'GB02' THEN B.RATING_SCALE END,
         CASE
            WHEN B.RATING_SCALE <> 'GB02'
            THEN
               TO_CHAR (B.REVIEW_DT, 'YYYY-MM-DD')
         END,
         CASE WHEN B.RATING_SCALE <> 'GB02' THEN B.REVIEW_RATING END,
         CASE WHEN B.RATING_SCALE <> 'GB02' THEN B.PWCUK_RATING_DESCR END,
         U.RATING_SCALE,
         TO_CHAR (U.EFFDT, 'YYYY-MM-DD'),
         U.REVIEW_RATING,
         U.REVIEW_RATING,
         B.PWCE_GUID,
         B.EMAIL_ADDR,
         B.EMAIL_ADDR2,
         B.WORK_PHONE,
         B.PWCE_ROOM_LOC,
         B.LOCATION,
         B.LOCATION_DESCR,
         B.PWCE_LOC_LVL3_DESC,
         B.PWCE_LOC_LVL2_DESC,
         B.PWCE_LOC_LVL1_DESC,
         B.ADDRESS1,
         B.ADDRESS2,
         B.ADDRESS3,
         B.ADDRESS4,
         B.CITY,
         B.POSTAL,
         B.PWC_ROLE,
         B.COUNTY,
         B.PWCUK_SECURITY_IND,
         TO_CHAR (
            TO_DATE (TO_CHAR (B.PWCUK_LAST_PROM_DT, 'YYYY-MM-DD'),
                     'yyyy-mm-dd'),
            'dd/mm/yyyy'),
         FLOOR (
            (SYSDATE
             - TO_DATE (TO_CHAR (B.PWCUK_LAST_PROM_DT, 'YYYY-MM-DD'),
                        'YYYY-MM-DD'))
            / 30.5),
         B.MAR_STATUS,
         C.SUPERVISOR_ID,
         B.PAYGROUP,
         DECODE (B.REFERRAL_SOURCE,
                 '0', 'Advertisements',
                 '1', 'Employee Referral Program',
                 '10', 'Employee Agency - Other',
                 '11', 'Intern form College',
                 '12', 'Campus Recruiting - entry',
                 '13', 'Intern - Grad School',
                 '14', 'Campus Recruiting - Grad Level',
                 '15', 'Temporary to Permanent',
                 '16', 'Overseas Hire',
                 '2', 'Staff Member or Friend of Firm',
                 '3', 'Internet',
                 '4', 'Direct Source',
                 '5', 'Career Fair',
                 '6', 'Open House',
                 '7', 'Own Initiative',
                 '8', 'Tranfers - Foreign Office',
                 '9', 'Retained Search\Researcher',
                 'AD', 'Advertisement',
                 'AG', 'Agency',
                 'CL', 'Client Referral',
                 'CO', 'College Recruiting',
                 'EE', 'Employee',
                 'FE', 'Former Employee',
                 'JF', 'Job Fair',
                 'JP', 'Job Posting',
                 'OH', 'Open House',
                 'OT', 'Other Source',
                 'PI', 'Phone Inquiry',
                 'TH', 'Tupe Hire',
                 'UK', 'Unknown',
                 'US', 'Unsolicited',
                 'WI', 'Walk-In',
                 'XR', 'Executive Referral',
                 'XS', 'Executive Search'),
         B.SPECIFIC_REFER_SRC,
         M.CONTRACT_TYPE,
         B.PWCE_EP_ROLEEMPLID,
         B.ALTER_EMPLID,
         B.FIRST_NAME_CD,
         B.LAST_NAME_CD,
         B.GRADE_DFLT,
         B.DESCR,
         P.PWCE_PRODUCT,
         Q.DESCRSHORT,
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
         E.SETID,
         TO_CHAR (E.EFFDT, 'YYYY-MM-DD'),
         Q.SETID,
         Q.PWCE_PRODUCT,
         Q.PWCE_SUB_PRODUCT,
         TO_CHAR (Q.EFFDT, 'YYYY-MM-DD')
    FROM (   PS_PWCUK_EMP_C_VW B
          LEFT OUTER JOIN
             PS_PWCUK_TREE_TBL A
          ON B.PWCUK_HOME_DEPTID = A.DEPTID),
         (   PS_JOB C
          LEFT OUTER JOIN
             PS_BENEFIT_PARTIC D
          ON     C.EMPLID = D.EMPLID
             AND C.EMPL_RCD = D.EMPL_RCD
             AND D.PLAN_TYPE = '3Y'),
         PS_JOBCODE_TBL E,
         PS_PWCUK_JB_NSEC_V F,
         PS_PWCUK_JB_NSEC_V I,
         (   (   PS_JOB L
              LEFT OUTER JOIN
                 PS_WKF_CNT_TYPE M
              ON L.EMPLID = M.EMPLID AND M.CONTRACT_NUM = L.CONTRACT_NUM)
          LEFT OUTER JOIN
             PS_WKF_CNT_CLAUSE N
          ON     M.EMPLID = N.EMPLID
             AND M.CONTRACT_NUM = N.CONTRACT_NUM
             AND N.CONTRACT_CLAUSE LIKE 'GO%'),
         PS_PWCUK_TREE_TBL S,
         (   PS_PWCUK_EMP_C_VW T
          LEFT OUTER JOIN
             PS_EMPLOYEE_REVIEW U
          ON     T.EMPLID = U.EMPLID
             AND U.EMPL_RCD = T.EMPL_RCD
             AND U.RATING_SCALE = 'GB02'),
         (   PS_PWCE_JOB P
          LEFT OUTER JOIN
             PS_PWCE_SUBPROD_TB Q
          ON Q.PWCE_PRODUCT = P.PWCE_PRODUCT
             AND Q.PWCE_SUB_PRODUCT = P.PWCE_SUB_PRODUCT),
         PS_PERS_SRCH_QRY B1,
         PS_EMPLMT_SRCH_QRY C1,
         PS_EMPLMT_SRCH_QRY D1,
         PS_EMPLMT_SRCH_QRY L1,
         PS_PERS_SRCH_QRY M1,
         PS_PERS_SRCH_QRY N1,
         PS_PERS_SRCH_QRY T1,
         PS_EMPLMT_SRCH_QRY U1,
         PS_EMPLMT_SRCH_QRY P1
   WHERE     B.EMPLID = B1.EMPLID
         AND B1.OPRID = 'smorris006a'
         AND C.EMPLID = C1.EMPLID
         AND C.EMPL_RCD = C1.EMPL_RCD
         AND C1.OPRID = 'smorris006a'
         AND (D.EMPLID = D1.EMPLID OR D.EMPLID IS NULL)
         AND (D.EMPL_RCD = D1.EMPL_RCD OR D.EMPL_RCD IS NULL)
         AND D1.OPRID = 'smorris006a'
         AND L.EMPLID = L1.EMPLID
         AND L.EMPL_RCD = L1.EMPL_RCD
         AND L1.OPRID = 'smorris006a'
         AND (M.EMPLID = M1.EMPLID OR M.EMPLID IS NULL)
         AND M1.OPRID = 'smorris006a'
         AND (N.EMPLID = N1.EMPLID OR N.EMPLID IS NULL)
         AND N1.OPRID = 'smorris006a'
         AND T.EMPLID = T1.EMPLID
         AND T1.OPRID = 'smorris006a'
         AND (U.EMPLID = U1.EMPLID OR U.EMPLID IS NULL)
         AND (U.EMPL_RCD = U1.EMPL_RCD OR U.EMPL_RCD IS NULL)
         AND U1.OPRID = 'smorris006a'
         AND P.EMPLID = P1.EMPLID
         AND P.EMPL_RCD = P1.EMPL_RCD
         AND P1.OPRID = 'smorris006a'
         AND (    B1.EMPLID = M1.EMPLID
              AND B1.EMPLID = N1.EMPLID
              AND B1.EMPLID = C1.EMPLID
              AND B1.EMPLID = T1.EMPLID
              AND C1.EMPLID = D1.EMPLID
              AND C1.EMPL_RCD = D1.EMPL_RCD
              AND C1.EMPLID = L1.EMPLID
              AND C1.EMPL_RCD = L1.EMPL_RCD
              AND C1.EMPLID = U1.EMPLID
              AND C1.EMPL_RCD = U1.EMPL_RCD
              AND C1.EMPLID = P1.EMPLID
              AND C1.EMPL_RCD = P1.EMPL_RCD
              AND (9 = 9
                   AND (B.EMPL_STATUS IN ('A', 'L', 'P')
                        OR (B.EMPL_STATUS = 'S' AND B.EMPL_CLASS = 'XPT'))
                   AND B.EFFDT <= TO_DATE (SYSDATE, 'dd-mon-yy')
                   AND B.BUSINESS_UNIT <> 'GBR99'
                   AND B.JOBCODE <> '7777'
                   AND ( (   0 < INSTR (:1, B.PWCUK_REGION)
                          OR 0 < INSTR (:2, B.PWCUK_SUBREGION)
                          OR 0 < INSTR (:3, B.PWCUK_BUSINESSUNIT)
                          OR 0 < INSTR (:4, B.DEPTID))
                        OR (' ' = :5 AND ' ' = :6 AND ' ' = :7 AND ' ' = :8))
                   AND 0 < INSTR (:9, DECODE (TRIM (:10), NULL, ' ', B.GRADE))
                   AND 0 <
                          INSTR (:11,
                                 DECODE (TRIM (:12), NULL, ' ', B.LOCATION))
                   AND 0 <
                          INSTR (
                             :13,
                             DECODE (TRIM (:14), NULL, ' ', B.PWC_FEE_EARNER))
                   AND 0 <
                          INSTR (
                             :15,
                             DECODE (TRIM (:16),
                                     NULL, ' ',
                                     B.PWCUK_SECURITY_IND))
                   AND 0 <
                          INSTR (:17,
                                 DECODE (TRIM (:18), NULL, ' ', B.PER_ORG))
                   AND 0 <
                          INSTR (:19,
                                 DECODE (TRIM (:20), NULL, ' ', B.EMPL_CLASS))
                   AND 0 <
                          INSTR (
                             :21,
                             DECODE (TRIM (:22), NULL, ' ', M.CONTRACT_TYPE))
                   AND C.EMPLID = B.EMPLID
                   AND C.EMPL_RCD = B.EMPL_RCD
                   AND C.EFFDT =
                          (SELECT MAX (C_ED.EFFDT)
                             FROM PS_JOB C_ED
                            WHERE C.EMPLID = C_ED.EMPLID
                                  AND C.EMPL_RCD = C_ED.EMPL_RCD
                                  AND C_ED.EFFDT <=
                                         TO_DATE (SYSDATE, 'dd-mon-yy'))
                   AND C.EFFSEQ =
                          (SELECT MAX (C_ES.EFFSEQ)
                             FROM PS_JOB C_ES
                            WHERE     C.EMPLID = C_ES.EMPLID
                                  AND C.EMPL_RCD = C_ES.EMPL_RCD
                                  AND C.EFFDT = C_ES.EFFDT)
                   AND E.JOBCODE = C.JOBCODE
                   AND E.SETID = C.SETID_JOBCODE
                   AND E.EFFDT =
                          (SELECT MAX (E_ED.EFFDT)
                             FROM PS_JOBCODE_TBL E_ED
                            WHERE E.SETID = E_ED.SETID
                                  AND E.JOBCODE = E_ED.JOBCODE
                                  AND E_ED.EFFDT <=
                                         TO_DATE (SYSDATE, 'dd-mon-yy')))
              AND F.EMPLID = C.EMPLID
              AND F.EMPL_RCD = C.EMPL_RCD
              AND F.EFFDT =
                     (SELECT NVL (MAX (G.EFFDT), C.EFFDT)
                        FROM PS_PWCUK_JB_NSEC_V G
                       WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD
                             AND G.EFFDT =
                                    (SELECT MAX (G_ED.EFFDT)
                                       FROM PS_PWCUK_JB_NSEC_V G_ED
                                      WHERE     G.EMPLID = G_ED.EMPLID
                                            AND G.EMPL_RCD = G_ED.EMPL_RCD
                                            AND G_ED.EFFDT <= C.EFFDT
                                            AND action IN ('CSS', 'CSB'))
                             AND G.ACTION IN ('CSS', 'CSB'))
              AND F.EFFSEQ =
                     (SELECT NVL (MAX (H.EFFSEQ), C.EFFSEQ)
                        FROM PS_PWCUK_JB_NSEC_V H
                       WHERE     H.EMPLID = C.EMPLID
                             AND H.EMPL_RCD = C.EMPL_RCD
                             AND H.EFFDT = F.EFFDT
                             AND H.ACTION IN ('CSS', 'CSB'))
              AND I.EMPLID = C.EMPLID
              AND I.EMPL_RCD = C.EMPL_RCD
              AND I.EFFDT =
                     (SELECT NVL (MIN (J.EFFDT), C.EFFDT)
                        FROM PS_PWCUK_JB_NSEC_V J
                       WHERE J.EMPLID = C.EMPLID AND J.EMPL_RCD = C.EMPL_RCD
                             AND J.EFFDT =
                                    (SELECT MIN (J_ED.EFFDT)
                                       FROM PS_PWCUK_JB_NSEC_V J_ED
                                      WHERE     J.EMPLID = J_ED.EMPLID
                                            AND J.EMPL_RCD = J_ED.EMPL_RCD
                                            AND J_ED.EFFDT >= C.EFFDT
                                            AND action IN ('CSE', 'CSP'))
                             AND J.ACTION IN ('CSE', 'CSP'))
              AND I.EFFSEQ =
                     (SELECT NVL (MAX (K.EFFSEQ), C.EFFSEQ)
                        FROM PS_PWCUK_JB_NSEC_V K
                       WHERE     K.EMPLID = C.EMPLID
                             AND K.EMPL_RCD = C.EMPL_RCD
                             AND K.EFFDT = I.EFFDT
                             AND K.ACTION IN ('CSE', 'CSP'))
              AND C.EMPLID = L.EMPLID
              AND C.EMPL_RCD = L.EMPL_RCD
              AND L.EFFDT = C.EFFDT
              AND L.EFFSEQ = C.EFFSEQ
              AND (M.EFFDT =
                      (SELECT MAX (M_ED.EFFDT)
                         FROM PS_WKF_CNT_TYPE M_ED
                        WHERE     M.EMPLID = M_ED.EMPLID
                              AND M.CONTRACT_NUM = M_ED.CONTRACT_NUM
                              AND M_ED.EFFDT <= L.EFFDT)
                   OR M.EFFDT IS NULL
                   OR (M.EFFDT =
                          (SELECT MIN (M_ED.EFFDT)
                             FROM PS_WKF_CNT_TYPE M_ED
                            WHERE M.EMPLID = M_ED.EMPLID
                                  AND M.CONTRACT_NUM = M_ED.CONTRACT_NUM)
                       AND M.EFFDT > L.EFFDT))
              AND NOT EXISTS
                         (SELECT 'X'
                            FROM PS_WKF_CNT_CLAUSE O, PS_PERS_SRCH_QRY O1
                           WHERE O.EMPLID = O1.EMPLID
                                 AND O1.OPRID = 'smorris006a'
                                 AND (    O.EMPLID = N.EMPLID
                                      AND O.CONTRACT_NUM = N.CONTRACT_NUM
                                      AND O.EFFDT = N.EFFDT
                                      AND O.CONTRACT_SEQ > N.CONTRACT_SEQ
                                      AND O.CONTRACT_CLAUSE LIKE 'GO%'))
              AND (N.EFFDT =
                      (SELECT MAX (N_ED.EFFDT)
                         FROM PS_WKF_CNT_CLAUSE N_ED
                        WHERE     N.EMPLID = N_ED.EMPLID
                              AND N.CONTRACT_NUM = N_ED.CONTRACT_NUM
                              AND N_ED.EFFDT <= TO_DATE (SYSDATE, 'dd-mon-yy')
                              AND CONTRACT_CLAUSE LIKE 'GO%')
                   OR N.EFFDT IS NULL
                   OR (N.EFFDT =
                          (SELECT MIN (N_ED.EFFDT)
                             FROM PS_WKF_CNT_CLAUSE N_ED
                            WHERE N.EMPLID = N_ED.EMPLID
                                  AND N.CONTRACT_NUM = N_ED.CONTRACT_NUM)
                       AND N.EFFDT > TO_DATE (SYSDATE, 'dd-mon-yy')))
              AND S.DEPTID = B.DEPTID
              AND B.EMPLID = T.EMPLID
              AND T.EMPL_RCD = B.EMPL_RCD
              AND T.EFFDT = B.EFFDT
              AND T.EFFSEQ = B.EFFSEQ
              AND (U.EFFDT =
                      (SELECT MAX (U_ED.EFFDT)
                         FROM PS_EMPLOYEE_REVIEW U_ED
                        WHERE     U.EMPLID = U_ED.EMPLID
                              AND U.EMPL_RCD = U_ED.EMPL_RCD
                              AND U_ED.EFFDT <= TO_DATE (SYSDATE, 'dd-mon-yy')
                              AND RATING_SCALE = 'GB02')
                   OR U.EFFDT IS NULL)
              AND B.EMPLID = P.EMPLID
              AND P.EMPL_RCD = B.EMPL_RCD
              AND P.EFFDT =
                     (SELECT MAX (P_ED.EFFDT)
                        FROM PS_PWCE_JOB P_ED
                       WHERE     P.EMPLID = P_ED.EMPLID
                             AND P.EMPL_RCD = P_ED.EMPL_RCD
                             AND P_ED.EFFDT <= B.EFFDT)
              AND P.EFFSEQ =
                     (SELECT MAX (P_ES.EFFSEQ)
                        FROM PS_PWCE_JOB P_ES
                       WHERE     P.EMPLID = P_ES.EMPLID
                             AND P.EMPL_RCD = P_ES.EMPL_RCD
                             AND P.EFFDT = P_ES.EFFDT)
              AND (Q.EFFDT =
                      (SELECT MAX (Q_ED.EFFDT)
                         FROM PS_PWCE_SUBPROD_TB Q_ED
                        WHERE     Q.SETID = Q_ED.SETID
                              AND Q.PWCE_PRODUCT = Q_ED.PWCE_PRODUCT
                              AND Q.PWCE_SUB_PRODUCT = Q_ED.PWCE_SUB_PRODUCT
                              AND Q_ED.EFFDT <= P.EFFDT)
                   OR Q.EFFDT IS NULL))
ORDER BY 67,
         65,
         63,
         59,
         8 DESC,
         28,
         4;


I have generated the EXPLAIN PLAN for this query, which is as under:


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3593814755

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                    |     1 |  3117 |  9060   (1)| 00:00:43 |
|   1 |  SORT ORDER BY                                               |                    |     1 |  3117 |  9060   (1)| 00:00:43 |
|*  2 |   FILTER                                                     |                    |       |       |            |          |
|*  3 |    FILTER                                                    |                    |       |       |            |          |
|   4 |     NESTED LOOPS OUTER                                       |                    |     1 |  3117 |  9005   (1)| 00:00:43 |
|   5 |      NESTED LOOPS OUTER                                      |                    |     1 |  3087 |  9004   (1)| 00:00:43 |
|   6 |       NESTED LOOPS                                           |                    |     1 |  2998 |  9003   (1)| 00:00:43 |
|   7 |        NESTED LOOPS                                          |                    |     1 |  2893 |  9002   (1)| 00:00:43 |
|   8 |         NESTED LOOPS                                         |                    |     1 |   639 |  8998   (1)| 00:00:43 |
|*  9 |          HASH JOIN                                           |                    |     1 |   528 |  8993   (1)| 00:00:43 |
|* 10 |           HASH JOIN                                          |                    |     1 |   512 |  8095   (1)| 00:00:38 |
|* 11 |            HASH JOIN                                         |                    |     1 |   498 |  7197   (1)| 00:00:34 |
|* 12 |             HASH JOIN                                        |                    |     1 |   484 |  6299   (1)| 00:00:30 |
|* 13 |              HASH JOIN                                       |                    |     1 |   468 |  5402   (1)| 00:00:26 |
|* 14 |               HASH JOIN                                      |                    |     1 |   451 |  4324   (1)| 00:00:21 |
|* 15 |                HASH JOIN                                     |                    |     1 |   434 |  3247   (1)| 00:00:16 |
|  16 |                 NESTED LOOPS OUTER                           |                    |     1 |   417 |  2169   (1)| 00:00:11 |
|  17 |                  NESTED LOOPS                                |                    |     1 |   399 |  2168   (1)| 00:00:11 |
|  18 |                   NESTED LOOPS                               |                    |     1 |   351 |  2167   (1)| 00:00:11 |
|  19 |                    NESTED LOOPS                              |                    |     1 |   315 |  2166   (1)| 00:00:11 |
|  20 |                     NESTED LOOPS OUTER                       |                    |     1 |   279 |  2165   (1)| 00:00:11 |
|* 21 |                      FILTER                                  |                    |       |       |            |          |
|  22 |                       NESTED LOOPS OUTER                     |                    |     1 |   247 |  2164   (1)| 00:00:11 |
|  23 |                        NESTED LOOPS                          |                    |     1 |   217 |  2163   (1)| 00:00:11 |
|  24 |                         NESTED LOOPS                         |                    |     1 |   187 |  2162   (1)| 00:00:11 |
|* 25 |                          HASH JOIN OUTER                     |                    |     1 |   117 |  2161   (1)| 00:00:11 |
|  26 |                           NESTED LOOPS                       |                    |     1 |    81 |  2155   (1)| 00:00:11 |
|* 27 |                            HASH JOIN                         |                    |     1 |    34 |  2154   (1)| 00:00:11 |
|  28 |                             VIEW                             | PS_EMPLMT_SRCH_QRY |   448 |  7616 |  1077   (1)| 00:00:06 |
|  29 |                              HASH UNIQUE                     |                    |   448 | 59584 |  1077   (1)| 00:00:06 |
|  30 |                               TABLE ACCESS BY INDEX ROWID    | PS_SJT_PERSON      |     1 |    30 |     1   (0)| 00:00:01 |
|  31 |                                NESTED LOOPS                  |                    |   448 | 59584 |  1076   (1)| 00:00:06 |
|  32 |                                 NESTED LOOPS                 |                    |   448 | 46144 |   717   (1)| 00:00:04 |
|  33 |                                  NESTED LOOPS                |                    |     1 |    67 |     2   (0)| 00:00:01 |
|  34 |                                   TABLE ACCESS BY INDEX ROWID| PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|* 35 |                                    INDEX UNIQUE SCAN         | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
|  36 |                                   TABLE ACCESS BY INDEX ROWID| PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|* 37 |                                    INDEX RANGE SCAN          | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|* 38 |                                  TABLE ACCESS BY INDEX ROWID | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|* 39 |                                   INDEX RANGE SCAN           | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|* 40 |                                 INDEX RANGE SCAN             | PS_SJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
|  41 |                             VIEW                             | PS_EMPLMT_SRCH_QRY |   448 |  7616 |  1077   (1)| 00:00:06 |
|  42 |                              HASH UNIQUE                     |                    |   448 | 59584 |  1077   (1)| 00:00:06 |
|  43 |                               TABLE ACCESS BY INDEX ROWID    | PS_SJT_PERSON      |     1 |    30 |     1   (0)| 00:00:01 |
|  44 |                                NESTED LOOPS                  |                    |   448 | 59584 |  1076   (1)| 00:00:06 |
|  45 |                                 NESTED LOOPS                 |                    |   448 | 46144 |   717   (1)| 00:00:04 |
|  46 |                                  NESTED LOOPS                |                    |     1 |    67 |     2   (0)| 00:00:01 |
|  47 |                                   TABLE ACCESS BY INDEX ROWID| PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|* 48 |                                    INDEX UNIQUE SCAN         | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
|  49 |                                   TABLE ACCESS BY INDEX ROWID| PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|* 50 |                                    INDEX RANGE SCAN          | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|* 51 |                                  TABLE ACCESS BY INDEX ROWID | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|* 52 |                                   INDEX RANGE SCAN           | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|* 53 |                                 INDEX RANGE SCAN             | PS_SJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
|  54 |                            TABLE ACCESS BY INDEX ROWID       | PS_PWCE_JOB        |     1 |    47 |     1   (0)| 00:00:01 |
|* 55 |                             INDEX RANGE SCAN                 | PS_PWCE_JOB        |     1 |       |     1   (0)| 00:00:01 |
|  56 |                              SORT AGGREGATE                  |                    |     1 |    26 |            |          |
|* 57 |                               INDEX RANGE SCAN               | PS_PWCE_JOB        |     1 |    26 |    15  (94)| 00:00:01 |
|  58 |                           TABLE ACCESS FULL                  | PS_PWCE_SUBPROD_TB |  1748 | 62928 |     5   (0)| 00:00:01 |
|  59 |                          TABLE ACCESS BY INDEX ROWID         | PS_JOB             |     1 |    70 |     1   (0)| 00:00:01 |
|* 60 |                           INDEX RANGE SCAN                   | PS_JOB             |     1 |       |     1   (0)| 00:00:01 |
|  61 |                            SORT AGGREGATE                    |                    |     1 |    23 |            |          |
|  62 |                             FIRST ROW                        |                    |     1 |    23 |     1   (0)| 00:00:01 |
|* 63 |                              INDEX RANGE SCAN (MIN/MAX)      | PSAJOB             |     1 |    23 |     1   (0)| 00:00:01 |
|  64 |                               SORT AGGREGATE                 |                    |     1 |    26 |            |          |
|  65 |                                FIRST ROW                     |                    |     1 |    26 |     1   (0)| 00:00:01 |
|* 66 |                                 INDEX RANGE SCAN (MIN/MAX)   | PSAJOB             |     1 |    26 |     1   (0)| 00:00:01 |
|  67 |                         TABLE ACCESS BY INDEX ROWID          | PS_JOB             |     1 |    30 |     1   (0)| 00:00:01 |
|* 68 |                          INDEX UNIQUE SCAN                   | PS_JOB             |     1 |       |     1   (0)| 00:00:01 |
|  69 |                        TABLE ACCESS BY INDEX ROWID           | PS_WKF_CNT_TYPE    |     1 |    30 |     1   (0)| 00:00:01 |
|* 70 |                         INDEX RANGE SCAN                     | PS_WKF_CNT_TYPE    |     1 |       |     1   (0)| 00:00:01 |
|* 71 |                      TABLE ACCESS BY INDEX ROWID             | PS_WKF_CNT_CLAUSE  |     1 |    32 |     1   (0)| 00:00:01 |
|* 72 |                       INDEX RANGE SCAN                       | PS_WKF_CNT_CLAUSE  |     1 |       |     1   (0)| 00:00:01 |
|* 73 |                     TABLE ACCESS BY INDEX ROWID              | PS_JOB             |     1 |    36 |     1   (0)| 00:00:01 |
|* 74 |                      INDEX RANGE SCAN                        | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  75 |                       SORT AGGREGATE                         |                    |     1 |    36 |            |          |
|* 76 |                        TABLE ACCESS BY INDEX ROWID           | PS_JOB             |     1 |    36 |     2   (0)| 00:00:01 |
|* 77 |                         INDEX RANGE SCAN                     | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  78 |                       SORT AGGREGATE                         |                    |     1 |    33 |            |          |
|* 79 |                        TABLE ACCESS BY INDEX ROWID           | PS_JOB             |     1 |    33 |     2   (0)| 00:00:01 |
|* 80 |                         INDEX RANGE SCAN                     | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  81 |                          SORT AGGREGATE                      |                    |     1 |    33 |            |          |
|* 82 |                           TABLE ACCESS BY INDEX ROWID        | PS_JOB             |     1 |    33 |     2   (0)| 00:00:01 |
|* 83 |                            INDEX RANGE SCAN                  | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|* 84 |                    TABLE ACCESS BY INDEX ROWID               | PS_JOB             |     1 |    36 |     1   (0)| 00:00:01 |
|* 85 |                     INDEX RANGE SCAN                         | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  86 |                      SORT AGGREGATE                          |                    |     1 |    36 |            |          |
|* 87 |                       TABLE ACCESS BY INDEX ROWID            | PS_JOB             |     1 |    36 |     2   (0)| 00:00:01 |
|* 88 |                        INDEX RANGE SCAN                      | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  89 |                      SORT AGGREGATE                          |                    |     1 |    33 |            |          |
|* 90 |                       TABLE ACCESS BY INDEX ROWID            | PS_JOB             |     1 |    33 |     2   (0)| 00:00:01 |
|* 91 |                        INDEX RANGE SCAN                      | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  92 |                         SORT AGGREGATE                       |                    |     1 |    33 |            |          |
|* 93 |                          TABLE ACCESS BY INDEX ROWID         | PS_JOB             |     1 |    33 |     2   (0)| 00:00:01 |
|* 94 |                           INDEX RANGE SCAN                   | PSBJOB             |     1 |       |     1   (0)| 00:00:01 |
|  95 |                   TABLE ACCESS BY INDEX ROWID                | PS_JOBCODE_TBL     |     1 |    48 |     1   (0)| 00:00:01 |
|* 96 |                    INDEX RANGE SCAN                          | PS_JOBCODE_TBL     |     1 |       |     1   (0)| 00:00:01 |
|  97 |                     SORT AGGREGATE                           |                    |     1 |    21 |            |          |
|* 98 |                      INDEX RANGE SCAN                        | PS_JOBCODE_TBL     |     1 |    21 |     1   (0)| 00:00:01 |
|* 99 |                  INDEX RANGE SCAN                            | PS_BENEFIT_PARTIC  |     1 |    18 |     1   (0)| 00:00:01 |
| 100 |                 VIEW                                         | PS_EMPLMT_SRCH_QRY |   448 |  7616 |  1077   (1)| 00:00:06 |
| 101 |                  HASH UNIQUE                                 |                    |   448 | 59584 |  1077   (1)| 00:00:06 |
| 102 |                   TABLE ACCESS BY INDEX ROWID                | PS_SJT_PERSON      |     1 |    30 |     1   (0)| 00:00:01 |
| 103 |                    NESTED LOOPS                              |                    |   448 | 59584 |  1076   (1)| 00:00:06 |
| 104 |                     NESTED LOOPS                             |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 105 |                      NESTED LOOPS                            |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 106 |                       TABLE ACCESS BY INDEX ROWID            | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*107 |                        INDEX UNIQUE SCAN                     | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 108 |                       TABLE ACCESS BY INDEX ROWID            | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*109 |                        INDEX RANGE SCAN                      | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*110 |                      TABLE ACCESS BY INDEX ROWID             | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*111 |                       INDEX RANGE SCAN                       | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*112 |                     INDEX RANGE SCAN                         | PS_SJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
| 113 |                VIEW                                          | PS_EMPLMT_SRCH_QRY |   448 |  7616 |  1077   (1)| 00:00:06 |
| 114 |                 HASH UNIQUE                                  |                    |   448 | 59584 |  1077   (1)| 00:00:06 |
| 115 |                  TABLE ACCESS BY INDEX ROWID                 | PS_SJT_PERSON      |     1 |    30 |     1   (0)| 00:00:01 |
| 116 |                   NESTED LOOPS                               |                    |   448 | 59584 |  1076   (1)| 00:00:06 |
| 117 |                    NESTED LOOPS                              |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 118 |                     NESTED LOOPS                             |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 119 |                      TABLE ACCESS BY INDEX ROWID             | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*120 |                       INDEX UNIQUE SCAN                      | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 121 |                      TABLE ACCESS BY INDEX ROWID             | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*122 |                       INDEX RANGE SCAN                       | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*123 |                     TABLE ACCESS BY INDEX ROWID              | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*124 |                      INDEX RANGE SCAN                        | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*125 |                    INDEX RANGE SCAN                          | PS_SJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
| 126 |               VIEW                                           | PS_EMPLMT_SRCH_QRY |   448 |  7616 |  1077   (1)| 00:00:06 |
| 127 |                HASH UNIQUE                                   |                    |   448 | 59584 |  1077   (1)| 00:00:06 |
| 128 |                 TABLE ACCESS BY INDEX ROWID                  | PS_SJT_PERSON      |     1 |    30 |     1   (0)| 00:00:01 |
| 129 |                  NESTED LOOPS                                |                    |   448 | 59584 |  1076   (1)| 00:00:06 |
| 130 |                   NESTED LOOPS                               |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 131 |                    NESTED LOOPS                              |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 132 |                     TABLE ACCESS BY INDEX ROWID              | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*133 |                      INDEX UNIQUE SCAN                       | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 134 |                     TABLE ACCESS BY INDEX ROWID              | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*135 |                      INDEX RANGE SCAN                        | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*136 |                    TABLE ACCESS BY INDEX ROWID               | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*137 |                     INDEX RANGE SCAN                         | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*138 |                   INDEX RANGE SCAN                           | PS_SJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
| 139 |              VIEW                                            | PS_PERS_SRCH_QRY   |   448 |  7168 |   897   (1)| 00:00:05 |
| 140 |               HASH UNIQUE                                    |                    |   448 | 58240 |   897   (1)| 00:00:05 |
| 141 |                NESTED LOOPS                                  |                    |   448 | 58240 |   896   (1)| 00:00:05 |
| 142 |                 NESTED LOOPS                                 |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 143 |                  NESTED LOOPS                                |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 144 |                   TABLE ACCESS BY INDEX ROWID                | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*145 |                    INDEX UNIQUE SCAN                         | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 146 |                   TABLE ACCESS BY INDEX ROWID                | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*147 |                    INDEX RANGE SCAN                          | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*148 |                  TABLE ACCESS BY INDEX ROWID                 | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*149 |                   INDEX RANGE SCAN                           | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*150 |                 INDEX RANGE SCAN                             | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
| 151 |             VIEW                                             | PS_PERS_SRCH_QRY   |   448 |  6272 |   897   (1)| 00:00:05 |
| 152 |              HASH UNIQUE                                     |                    |   448 | 58240 |   897   (1)| 00:00:05 |
| 153 |               NESTED LOOPS                                   |                    |   448 | 58240 |   896   (1)| 00:00:05 |
| 154 |                NESTED LOOPS                                  |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 155 |                 NESTED LOOPS                                 |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 156 |                  TABLE ACCESS BY INDEX ROWID                 | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*157 |                   INDEX UNIQUE SCAN                          | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 158 |                  TABLE ACCESS BY INDEX ROWID                 | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*159 |                   INDEX RANGE SCAN                           | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*160 |                 TABLE ACCESS BY INDEX ROWID                  | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*161 |                  INDEX RANGE SCAN                            | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*162 |                INDEX RANGE SCAN                              | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
| 163 |            VIEW                                              | PS_PERS_SRCH_QRY   |   448 |  6272 |   897   (1)| 00:00:05 |
| 164 |             HASH UNIQUE                                      |                    |   448 | 58240 |   897   (1)| 00:00:05 |
| 165 |              NESTED LOOPS                                    |                    |   448 | 58240 |   896   (1)| 00:00:05 |
| 166 |               NESTED LOOPS                                   |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 167 |                NESTED LOOPS                                  |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 168 |                 TABLE ACCESS BY INDEX ROWID                  | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*169 |                  INDEX UNIQUE SCAN                           | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 170 |                 TABLE ACCESS BY INDEX ROWID                  | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*171 |                  INDEX RANGE SCAN                            | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*172 |                TABLE ACCESS BY INDEX ROWID                   | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*173 |                 INDEX RANGE SCAN                             | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*174 |               INDEX RANGE SCAN                               | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
| 175 |           VIEW                                               | PS_PERS_SRCH_QRY   |   448 |  7168 |   897   (1)| 00:00:05 |
| 176 |            HASH UNIQUE                                       |                    |   448 | 58240 |   897   (1)| 00:00:05 |
| 177 |             NESTED LOOPS                                     |                    |   448 | 58240 |   896   (1)| 00:00:05 |
| 178 |              NESTED LOOPS                                    |                    |   448 | 46144 |   717   (1)| 00:00:04 |
| 179 |               NESTED LOOPS                                   |                    |     1 |    67 |     2   (0)| 00:00:01 |
| 180 |                TABLE ACCESS BY INDEX ROWID                   | PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
|*181 |                 INDEX UNIQUE SCAN                            | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
| 182 |                TABLE ACCESS BY INDEX ROWID                   | PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
|*183 |                 INDEX RANGE SCAN                             | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
|*184 |               TABLE ACCESS BY INDEX ROWID                    | PS_SJT_CLASS_ALL   |   447 | 16092 |   715   (1)| 00:00:04 |
|*185 |                INDEX RANGE SCAN                              | PS_SJT_CLASS_ALL   |  1859 |       |     4   (0)| 00:00:01 |
|*186 |              INDEX RANGE SCAN                                | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
| 187 |          VIEW                                                | PS_PWCUK_EMP_C_VW  |     1 |   111 |     4   (0)| 00:00:01 |
| 188 |           UNION ALL PUSHED PREDICATE                         |                    |       |       |            |          |
| 189 |            TABLE ACCESS BY INDEX ROWID                       | PS_PWCUK_EMPLOYEES |     1 |    38 |     1   (0)| 00:00:01 |
|*190 |             INDEX RANGE SCAN                                 | PS_PWCUK_EMPLOYEES |     1 |       |     1   (0)| 00:00:01 |
|*191 |            FILTER                                            |                    |       |       |            |          |
| 192 |             NESTED LOOPS OUTER                               |                    |     1 |    97 |     3   (0)| 00:00:01 |
| 193 |              NESTED LOOPS OUTER                              |                    |     1 |    85 |     2   (0)| 00:00:01 |
| 194 |               TABLE ACCESS BY INDEX ROWID                    | PS_PWCUK_EX_EMPLS  |     1 |    38 |     1   (0)| 00:00:01 |
|*195 |                INDEX RANGE SCAN                              | PS_PWCUK_EX_EMPLS  |     1 |       |     1   (0)| 00:00:01 |
|*196 |               TABLE ACCESS BY INDEX ROWID                    | PS_PWCE_EP_ROLES   |     1 |    47 |     1   (0)| 00:00:01 |
|*197 |                INDEX RANGE SCAN                              | PSAPWCE_EP_ROLES   |     1 |       |     1   (0)| 00:00:01 |
|*198 |              INDEX RANGE SCAN                                | PS_PWCUK_EMPLOYEES |     1 |    12 |     1   (0)| 00:00:01 |
| 199 |             SORT AGGREGATE                                   |                    |     1 |    23 |            |          |
|*200 |              TABLE ACCESS BY INDEX ROWID                     | PS_PWCE_EP_ROLES   |     1 |    23 |     1   (0)| 00:00:01 |
|*201 |               INDEX RANGE SCAN                               | PSAPWCE_EP_ROLES   |     1 |       |     1   (0)| 00:00:01 |
|*202 |         VIEW                                                 | PS_PWCUK_EMP_C_VW  |     1 |  2254 |     4   (0)| 00:00:01 |
| 203 |          UNION ALL PUSHED PREDICATE                          |                    |       |       |            |          |
|*204 |           FILTER                                             |                    |       |       |            |          |
|*205 |            TABLE ACCESS BY INDEX ROWID                       | PS_PWCUK_EMPLOYEES |     1 |   760 |     1   (0)| 00:00:01 |
|*206 |             INDEX UNIQUE SCAN                                | PS_PWCUK_EMPLOYEES |     1 |       |     1   (0)| 00:00:01 |
|*207 |           FILTER                                             |                    |       |       |            |          |
|*208 |            FILTER                                            |                    |       |       |            |          |
| 209 |             NESTED LOOPS OUTER                               |                    |     1 |   742 |     3   (0)| 00:00:01 |
| 210 |              NESTED LOOPS OUTER                              |                    |     1 |   691 |     2   (0)| 00:00:01 |
|*211 |               TABLE ACCESS BY INDEX ROWID                    | PS_PWCUK_EX_EMPLS  |     1 |   637 |     1   (0)| 00:00:01 |
|*212 |                INDEX UNIQUE SCAN                             | PS_PWCUK_EX_EMPLS  |     1 |       |     1   (0)| 00:00:01 |
|*213 |               TABLE ACCESS BY INDEX ROWID                    | PS_PWCE_EP_ROLES   |     1 |    54 |     1   (0)| 00:00:01 |
|*214 |                INDEX RANGE SCAN                              | PSAPWCE_EP_ROLES   |     1 |       |     1   (0)| 00:00:01 |
| 215 |              TABLE ACCESS BY INDEX ROWID                     | PS_PWCUK_EMPLOYEES |     1 |    51 |     1   (0)| 00:00:01 |
|*216 |               INDEX RANGE SCAN                               | PS_PWCUK_EMPLOYEES |     1 |       |     1   (0)| 00:00:01 |
| 217 |            SORT AGGREGATE                                    |                    |     1 |    23 |            |          |
|*218 |             TABLE ACCESS BY INDEX ROWID                      | PS_PWCE_EP_ROLES   |     1 |    23 |     1   (0)| 00:00:01 |
|*219 |              INDEX RANGE SCAN                                | PSAPWCE_EP_ROLES   |     1 |       |     1   (0)| 00:00:01 |
|*220 |        INDEX RANGE SCAN                                      | PS_PWCUK_TREE_TBL  |     1 |   105 |     1   (0)| 00:00:01 |
|*221 |       INDEX RANGE SCAN                                       | PS_PWCUK_TREE_TBL  |     1 |    89 |     1   (0)| 00:00:01 |
|*222 |      TABLE ACCESS BY INDEX ROWID                             | PS_EMPLOYEE_REVIEW |     1 |    30 |     1   (0)| 00:00:01 |
|*223 |       INDEX RANGE SCAN                                       | PS_EMPLOYEE_REVIEW |     1 |       |     1   (0)| 00:00:01 |
| 224 |    SORT AGGREGATE                                            |                    |     1 |    28 |            |          |
|*225 |     TABLE ACCESS BY INDEX ROWID                              | PS_EMPLOYEE_REVIEW |     1 |    28 |     2   (0)| 00:00:01 |
|*226 |      INDEX RANGE SCAN                                        | PS_EMPLOYEE_REVIEW |     1 |       |     1   (0)| 00:00:01 |
| 227 |      SORT AGGREGATE                                          |                    |     1 |    26 |            |          |
|*228 |       INDEX RANGE SCAN                                       | PS_WKF_CNT_TYPE    |     1 |    26 |     1   (0)| 00:00:01 |
| 229 |        SORT AGGREGATE                                        |                    |     1 |    26 |            |          |
|*230 |         INDEX RANGE SCAN                                     | PS_WKF_CNT_TYPE    |     1 |    26 |     1   (0)| 00:00:01 |
| 231 |          NESTED LOOPS                                        |                    |     1 |   133 |     5   (0)| 00:00:01 |
| 232 |           NESTED LOOPS                                       |                    |     1 |   111 |     4   (0)| 00:00:01 |
| 233 |            NESTED LOOPS                                      |                    |     1 |    75 |     3   (0)| 00:00:01 |
| 234 |             NESTED LOOPS                                     |                    |     1 |    48 |     2   (0)| 00:00:01 |
| 235 |              TABLE ACCESS BY INDEX ROWID                     | PSOPRDEFN          |     1 |    16 |     1   (0)| 00:00:01 |
|*236 |               INDEX UNIQUE SCAN                              | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
|*237 |              TABLE ACCESS BY INDEX ROWID                     | PS_WKF_CNT_CLAUSE  |     1 |    32 |     1   (0)| 00:00:01 |
|*238 |               INDEX RANGE SCAN                               | PS_WKF_CNT_CLAUSE  |     1 |       |     1   (0)| 00:00:01 |
| 239 |             TABLE ACCESS BY INDEX ROWID                      | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
|*240 |              INDEX RANGE SCAN                                | PSASJT_PERSON      |     1 |       |     1   (0)| 00:00:01 |
|*241 |            INDEX RANGE SCAN                                  | PSASJT_CLASS_ALL   |     1 |    36 |     1   (0)| 00:00:01 |
|*242 |           INDEX RANGE SCAN                                   | PSASJT_OPR_CLS     |     1 |    22 |     1   (0)| 00:00:01 |
| 243 |            SORT AGGREGATE                                    |                    |     1 |    29 |            |          |
|*244 |             TABLE ACCESS BY INDEX ROWID                      | PS_WKF_CNT_CLAUSE  |     1 |    29 |     2   (0)| 00:00:01 |
|*245 |              INDEX RANGE SCAN                                | PS_WKF_CNT_CLAUSE  |     1 |       |     1   (0)| 00:00:01 |
| 246 |              SORT AGGREGATE                                  |                    |     1 |    25 |            |          |
|*247 |               INDEX RANGE SCAN                               | PS_WKF_CNT_CLAUSE  |     1 |    25 |     1   (0)| 00:00:01 |
| 248 |                SORT AGGREGATE                                |                    |     1 |    23 |            |          |
|*249 |                 INDEX RANGE SCAN                             | PS_PWCE_JOB        |     1 |    23 |     1   (0)| 00:00:01 |
| 250 |                  SORT AGGREGATE                              |                    |     1 |    26 |            |          |
|*251 |                   INDEX RANGE SCAN                           | PS_PWCE_SUBPROD_TB |     1 |    26 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter((SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT /*+ */ MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))
              FROM "SYSADM"."PS_EMPLOYEE_REVIEW" "U_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND "U_ED"."EMPL_RCD"=:B1 AND
              "U_ED"."EMPLID"=:B2 AND "RATING_SCALE"='GB02' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd
              -mon-yy')) AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND( (SELECT /*+ */ MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
              "SYSADM"."PS_EMPLOYEE_REVIEW" "U_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND "U_ED"."EMPL_RCD"=:B3 AND
              "U_ED"."EMPLID"=:B4 AND "RATING_SCALE"='GB02' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd
              -mon-yy'))) OR SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")) IS NULL) AND (SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT
              /*+ */ MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_WKF_CNT_TYPE" "M_ED" WHERE SYS_OP_DESCEND("EFFDT")
              IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B5) AND "M_ED"."CONTRACT_NUM"=:B6 AND "M_ED"."EMPLID"=:B7 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B8) AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND( (SELECT /*+ */
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_WKF_CNT_TYPE" "M_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT
              NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B9) AND "M_ED"."CONTRACT_NUM"=:B10 AND "M_ED"."EMPLID"=:B11 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B12)) OR SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")) IS NULL OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT /*+ */ MIN(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
              "SYSADM"."PS_WKF_CNT_TYPE" "M_ED" WHERE "M_ED"."CONTRACT_NUM"=:B13 AND "M_ED"."EMPLID"=:B14) AND
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND( (SELECT /*+ */ MIN(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
              "SYSADM"."PS_WKF_CNT_TYPE" "M_ED" WHERE "M_ED"."CONTRACT_NUM"=:B15 AND "M_ED"."EMPLID"=:B16)) AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))>SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")) AND
              SYS_OP_DESCEND("EFFDT")<SYS_OP_DESCEND("EFFDT")) AND  NOT EXISTS (SELECT /*+ */ 0 FROM SYSADM."PS_SJT_OPR_CLS"
              "SOC",SYSADM."PS_SJT_PERSON" "SEC",SYSADM."PS_SJT_CLASS_ALL" "CLS",SYSADM."PSOPRDEFN" "OPR","SYSADM"."PS_WKF_CNT_CLAUSE"
              "O" WHERE "O"."CONTRACT_SEQ">:B17 AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B18) AND "O"."CONTRACT_NUM"=:B19 AND
              "O"."EMPLID"=:B20 AND "O"."CONTRACT_CLAUSE" LIKE 'GO%' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B21 AND
              "OPR"."OPRID"='smorris006a' AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3" AND "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND
              "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND "CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_SET_CD"='PPLJOB'
              AND "SEC"."EMPLID"=:B22 AND "O"."EMPLID"="SEC"."EMPLID" AND "SOC"."OPRID"='smorris006a' AND
              "SOC"."CLASSID"="CLS"."CLASSID" AND ("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1'
              OR "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')) AND (SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT /*+ */
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_WKF_CNT_CLAUSE" "N_ED" WHERE SYS_OP_DESCEND("EFFDT") IS
              NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND
              "N_ED"."CONTRACT_NUM"=:B23 AND "N_ED"."EMPLID"=:B24 AND "CONTRACT_CLAUSE" LIKE 'GO%' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND( (SELECT /*+ */ MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
              "SYSADM"."PS_WKF_CNT_CLAUSE" "N_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND "N_ED"."CONTRACT_NUM"=:B25 AND
              "N_ED"."EMPLID"=:B26 AND "CONTRACT_CLAUSE" LIKE 'GO%' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT)
   3 - filter(("U"."EMPLID"="U1"."EMPLID" OR "U"."EMPLID" IS NULL) AND ("U"."EMPL_RCD"="U1"."EMPL_RCD" OR "U"."EMPL_RCD"
              IS NULL))
   9 - access("B1"."EMPLID"="T1"."EMPLID")
  10 - access("B1"."EMPLID"="M1"."EMPLID")
       filter("M"."EMPLID"="M1"."EMPLID" OR "M"."EMPLID" IS NULL)
  11 - access("B1"."EMPLID"="N1"."EMPLID")
       filter("N"."EMPLID"="N1"."EMPLID" OR "N"."EMPLID" IS NULL)
  12 - access("B1"."EMPLID"="C1"."EMPLID")
  13 - access("C1"."EMPLID"="U1"."EMPLID" AND "C1"."EMPL_RCD"="U1"."EMPL_RCD")
  14 - access("C1"."EMPLID"="D1"."EMPLID" AND "C1"."EMPL_RCD"="D1"."EMPL_RCD")
       filter(("D"."EMPLID"="D1"."EMPLID" OR "D"."EMPLID" IS NULL) AND ("D"."EMPL_RCD"="D1"."EMPL_RCD" OR "D"."EMPL_RCD"
              IS NULL))
  15 - access("L"."EMPLID"="L1"."EMPLID" AND "L"."EMPL_RCD"="L1"."EMPL_RCD" AND "C1"."EMPLID"="L1"."EMPLID" AND
              "C1"."EMPL_RCD"="L1"."EMPL_RCD")
  21 - filter(INSTR(:21,DECODE(TRIM(:22),NULL,' ',"M"."CONTRACT_TYPE"))>0)
  25 - access("Q"."PWCE_SUB_PRODUCT"(+)="P"."PWCE_SUB_PRODUCT" AND "Q"."PWCE_PRODUCT"(+)="P"."PWCE_PRODUCT")
  27 - access("C1"."EMPLID"="P1"."EMPLID" AND "C1"."EMPL_RCD"="P1"."EMPL_RCD")
  35 - access("OPR"."OPRID"='smorris006a')
  37 - access("SOC"."OPRID"='smorris006a')
  38 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
  39 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
  40 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
  48 - access("OPR"."OPRID"='smorris006a')
  50 - access("SOC"."OPRID"='smorris006a')
  51 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
  52 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
  53 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
  55 - access("P"."EMPLID"="P1"."EMPLID" AND "P"."EMPL_RCD"="P1"."EMPL_RCD")
       filter("P"."EFFSEQ"= (SELECT /*+ */ MAX("P_ES"."EFFSEQ") FROM "SYSADM"."PS_PWCE_JOB" "P_ES" WHERE
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B1) AND "P_ES"."EMPL_RCD"=:B2 AND "P_ES"."EMPLID"=:B3 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B4))
  57 - access("P_ES"."EMPLID"=:B1 AND "P_ES"."EMPL_RCD"=:B2 AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B3))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1)
  60 - access("C"."EMPLID"="C1"."EMPLID" AND "C"."EMPL_RCD"="C1"."EMPL_RCD")
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT /*+ */ MAX("C_ED"."EFFDT") FROM "SYSADM"."PS_JOB" "C_ED"
              WHERE "C_ED"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy') AND "C_ED"."EMPL_RCD"=:B1 AND "C_ED"."EMPLID"=:B2) AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))= (SELECT /*+ */ MAX("C_ES"."EFFSEQ") FROM "SYSADM"."PS_JOB" "C_ES" WHERE
              "C_ES"."EFFDT"=:B3 AND "C_ES"."EMPL_RCD"=:B4 AND "C_ES"."EMPLID"=:B5))
  63 - access("C_ED"."EMPLID"=:B1 AND "C_ED"."EMPL_RCD"=:B2 AND "C_ED"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy'))
  66 - access("C_ES"."EMPLID"=:B1 AND "C_ES"."EMPL_RCD"=:B2 AND "C_ES"."EFFDT"=:B3)
  68 - access("C"."EMPLID"="L"."EMPLID" AND "C"."EMPL_RCD"="L"."EMPL_RCD" AND
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("EFFDT") AND SYS_OP_DESCEND("EFFSEQ")=SYS_OP_DESCEND("EFFSEQ"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")) AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))=SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ")))
  70 - access("L"."EMPLID"="M"."EMPLID"(+) AND "M"."CONTRACT_NUM"(+)="L"."CONTRACT_NUM")
  71 - filter("N"."CONTRACT_CLAUSE"(+) LIKE 'GO%')
  72 - access("M"."EMPLID"="N"."EMPLID"(+) AND "M"."CONTRACT_NUM"="N"."CONTRACT_NUM"(+))
  73 - filter("A"."BUSINESS_UNIT" LIKE 'GBR%')
  74 - access("A"."EMPLID"="C"."EMPLID" AND "A"."EMPL_RCD"="C"."EMPL_RCD" AND "A"."EFFDT"= (SELECT /*+ */
              NVL(MIN("A"."EFFDT"),:B1) FROM SYSADM."PS_JOB" "SYS_ALIAS_2" WHERE "A"."EMPL_RCD"=:B2 AND "A"."EMPLID"=:B3 AND
              ("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE 'GBR%' AND "A"."EFFDT"= (SELECT /*+ */
              MIN("A"."EFFDT") FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT">=:B4 AND "A"."EMPL_RCD"=:B5 AND "A"."EMPLID"=:B6 AND
              ("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')))
       filter("A"."EFFSEQ"= (SELECT /*+ */ NVL(MAX("A"."EFFSEQ"),:B1) FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT"=:B2 AND
              "A"."EMPL_RCD"=:B3 AND "A"."EMPLID"=:B4 AND ("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE
              'GBR%'))
  76 - filter(("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  77 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2 AND "A"."EFFDT"=:B3)
  79 - filter(("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  80 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2)
       filter("A"."EFFDT"= (SELECT /*+ */ MIN("A"."EFFDT") FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT">=:B1 AND
              "A"."EMPL_RCD"=:B2 AND "A"."EMPLID"=:B3 AND ("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE
              'GBR%'))
  82 - filter(("A"."ACTION"='CSE' OR "A"."ACTION"='CSP') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  83 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2 AND "A"."EFFDT">=:B3)
  84 - filter("A"."BUSINESS_UNIT" LIKE 'GBR%')
  85 - access("A"."EMPLID"="C"."EMPLID" AND "A"."EMPL_RCD"="C"."EMPL_RCD" AND "A"."EFFDT"= (SELECT /*+ */
              NVL(MAX("A"."EFFDT"),:B1) FROM SYSADM."PS_JOB" "SYS_ALIAS_2" WHERE "A"."EMPL_RCD"=:B2 AND "A"."EMPLID"=:B3 AND
              ("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE 'GBR%' AND "A"."EFFDT"= (SELECT /*+ */
              MAX("A"."EFFDT") FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT"<=:B4 AND "A"."EMPL_RCD"=:B5 AND "A"."EMPLID"=:B6 AND
              ("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')))
       filter("A"."EFFSEQ"= (SELECT /*+ */ NVL(MAX("A"."EFFSEQ"),:B1) FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT"=:B2 AND
              "A"."EMPL_RCD"=:B3 AND "A"."EMPLID"=:B4 AND ("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE
              'GBR%'))
  87 - filter(("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  88 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2 AND "A"."EFFDT"=:B3)
  90 - filter(("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  91 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2)
       filter("A"."EFFDT"= (SELECT /*+ */ MAX("A"."EFFDT") FROM SYSADM."PS_JOB" "A" WHERE "A"."EFFDT"<=:B1 AND
              "A"."EMPL_RCD"=:B2 AND "A"."EMPLID"=:B3 AND ("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE
              'GBR%'))
  93 - filter(("A"."ACTION"='CSB' OR "A"."ACTION"='CSS') AND "A"."BUSINESS_UNIT" LIKE 'GBR%')
  94 - access("A"."EMPLID"=:B1 AND "A"."EMPL_RCD"=:B2 AND "A"."EFFDT"<=:B3)
  96 - access("E"."SETID"="C"."SETID_JOBCODE" AND "E"."JOBCODE"="C"."JOBCODE")
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT /*+ */ MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))
              FROM "SYSADM"."PS_JOBCODE_TBL" "E_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND "E_ED"."JOBCODE"=:B1 AND
              "E_ED"."SETID"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')))
  98 - access("E_ED"."SETID"=:B1 AND "E_ED"."JOBCODE"=:B2 AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy'))
  99 - access("C"."EMPLID"="D"."EMPLID"(+) AND "C"."EMPL_RCD"="D"."EMPL_RCD"(+) AND "D"."PLAN_TYPE"(+)='3Y')
       filter("D"."PLAN_TYPE"(+)='3Y')
 107 - access("OPR"."OPRID"='smorris006a')
 109 - access("SOC"."OPRID"='smorris006a')
 110 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 111 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 112 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 120 - access("OPR"."OPRID"='smorris006a')
 122 - access("SOC"."OPRID"='smorris006a')
 123 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 124 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 125 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 133 - access("OPR"."OPRID"='smorris006a')
 135 - access("SOC"."OPRID"='smorris006a')
 136 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 137 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 138 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 145 - access("OPR"."OPRID"='smorris006a')
 147 - access("SOC"."OPRID"='smorris006a')
 148 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 149 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 150 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 157 - access("OPR"."OPRID"='smorris006a')
 159 - access("SOC"."OPRID"='smorris006a')
 160 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 161 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 162 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 169 - access("OPR"."OPRID"='smorris006a')
 171 - access("SOC"."OPRID"='smorris006a')
 172 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 173 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 174 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 181 - access("OPR"."OPRID"='smorris006a')
 183 - access("SOC"."OPRID"='smorris006a')
 184 - filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 185 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "CLS"."SCRTY_SET_CD"='PPLJOB')
 186 - access("CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND
              "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 190 - access("A"."EMPLID"="T1"."EMPLID")
 191 - filter("A"."EFFDT"= (SELECT /*+ */ MAX("A"."EFFDT") FROM SYSADM."PS_PWCE_EP_ROLES" "A" WHERE
              TO_NUMBER("PWCE_EP_ROLE_NUM")=01 AND "A"."EMPLID"=:B1 AND "A"."EFFDT"<=SYSDATE@!) OR "A"."EFFDT" IS NULL)
 195 - access("B"."EMPLID"="T1"."EMPLID")
 196 - filter("A"."EMPLID"(+)="T1"."EMPLID" AND "B"."EMPLID"="A"."EMPLID"(+))
 197 - access(TO_NUMBER("PWCE_EP_ROLE_NUM"(+))=01)
 198 - access("D"."EMPLID"(+)="A"."PWCE_EP_ROLEEMPLID")
 200 - filter("A"."EMPLID"=:B1 AND "A"."EFFDT"<=SYSDATE@!)
 201 - access(TO_NUMBER("PWCE_EP_ROLE_NUM")=01)
 202 - filter("T"."EFFDT"="B"."EFFDT" AND "T"."EFFSEQ"="B"."EFFSEQ")
 204 - filter("C"."EMPL_RCD"="P"."EMPL_RCD" AND "B1"."EMPLID"="P"."EMPLID" AND "C"."EMPL_RCD"="T"."EMPL_RCD" AND
              "B1"."EMPLID"="T"."EMPLID" AND "B1"."EMPLID"="C"."EMPLID")
 205 - filter(INSTR(:9,DECODE(TRIM(:10),NULL,' ',"A"."GRADE"))>0 AND INSTR(:11,DECODE(TRIM(:12),NULL,'
              ',"A"."LOCATION"))>0 AND INSTR(:13,DECODE(TRIM(:14),NULL,' ',"A"."PWC_FEE_EARNER"))>0 AND
              INSTR(:15,DECODE(TRIM(:16),NULL,' ',"A"."PWCUK_SECURITY_IND"))>0 AND INSTR(:17,DECODE(TRIM(:18),NULL,' ',"A"."PER_ORG"))>0
              AND INSTR(:19,DECODE(TRIM(:20),NULL,' ',"A"."EMPL_CLASS"))>0 AND (INSTR(:1,"A"."PWCUK_REGION")>0 OR
              INSTR(:2,"A"."PWCUK_SUBREGION")>0 OR INSTR(:3,"A"."PWCUK_BUSINESSUNIT")>0 OR INSTR(:4,"A"."DEPTID")>0 OR ' '=:5 AND ' '=:6
              AND ' '=:7 AND ' '=:8) AND "A"."JOB_EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy') AND (("A"."EMPL_STATUS"='A' OR
              "A"."EMPL_STATUS"='L' OR "A"."EMPL_STATUS"='P') OR "A"."EMPL_STATUS"='S' AND "A"."EMPL_CLASS"='XPT') AND
              "A"."JOBCODE"<>'7777' AND "A"."BUSINESS_UNIT"<>'GBR99')
 206 - access("A"."EMPLID"="B1"."EMPLID" AND "A"."EMPL_RCD"="C"."EMPL_RCD")
 207 - filter("A"."EFFDT"= (SELECT /*+ */ MAX("A"."EFFDT") FROM SYSADM."PS_PWCE_EP_ROLES" "A" WHERE
              TO_NUMBER("PWCE_EP_ROLE_NUM")=01 AND "A"."EMPLID"=:B1 AND "A"."EFFDT"<=SYSDATE@!) OR "A"."EFFDT" IS NULL)
 208 - filter("C"."EMPL_RCD"="P"."EMPL_RCD" AND "B1"."EMPLID"="P"."EMPLID" AND "C"."EMPL_RCD"="T"."EMPL_RCD" AND
              "B1"."EMPLID"="T"."EMPLID" AND "B1"."EMPLID"="C"."EMPLID")
 211 - filter(("B"."EMPL_CLASS"='XPT' AND "B"."EMPL_STATUS"='S' OR ("B"."EMPL_STATUS"='A' OR "B"."EMPL_STATUS"='L' OR
              "B"."EMPL_STATUS"='P')) AND INSTR(:9,DECODE(TRIM(:10),NULL,' ',"B"."GRADE"))>0 AND INSTR(:11,DECODE(TRIM(:12),NULL,'
              ',"B"."LOCATION"))>0 AND INSTR(:13,DECODE(TRIM(:14),NULL,' ',"B"."PWC_FEE_EARNER"))>0 AND
              INSTR(:15,DECODE(TRIM(:16),NULL,' ',"B"."PWCUK_SECURITY_IND"))>0 AND INSTR(:17,DECODE(TRIM(:18),NULL,' ',"B"."PER_ORG"))>0
              AND INSTR(:19,DECODE(TRIM(:20),NULL,' ',"B"."EMPL_CLASS"))>0 AND (INSTR(:1,"B"."PWCUK_REGION")>0 OR
              INSTR(:2,"B"."PWCUK_SUBREGION")>0 OR INSTR(:3,"B"."PWCUK_BUSINESSUNIT")>0 OR INSTR(:4,"B"."DEPTID")>0 OR ' '=:5 AND ' '=:6
              AND ' '=:7 AND ' '=:8) AND "B"."JOBCODE"<>'7777' AND "B"."BUSINESS_UNIT"<>'GBR99' AND
              "B"."JOB_EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy'))
 212 - access("B"."EMPLID"="B1"."EMPLID" AND "B"."EMPL_RCD"="C"."EMPL_RCD")
 213 - filter("A"."EMPLID"(+)="B1"."EMPLID" AND "B"."EMPLID"="A"."EMPLID"(+))
 214 - access(TO_NUMBER("PWCE_EP_ROLE_NUM"(+))=01)
 216 - access("D"."EMPLID"(+)="A"."PWCE_EP_ROLEEMPLID")
 218 - filter("A"."EMPLID"=:B1 AND "A"."EFFDT"<=SYSDATE@!)
 219 - access(TO_NUMBER("PWCE_EP_ROLE_NUM")=01)
 220 - access("S"."DEPTID"="B"."DEPTID")
 221 - access("B"."PWCUK_HOME_DEPTID"="A"."DEPTID"(+))
 222 - filter("U"."RATING_SCALE"(+)='GB02')
 223 - access("T"."EMPLID"="U"."EMPLID"(+) AND "U"."EMPL_RCD"(+)="T"."EMPL_RCD")
 225 - filter("RATING_SCALE"='GB02')
 226 - access("U_ED"."EMPLID"=:B1 AND "U_ED"."EMPL_RCD"=:B2 AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy'))
 228 - access("M_ED"."EMPLID"=:B1 AND "M_ED"."CONTRACT_NUM"=:B2 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B3) AND
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
 230 - access("M_ED"."EMPLID"=:B1 AND "M_ED"."CONTRACT_NUM"=:B2)
 236 - access("OPR"."OPRID"='smorris006a')
 237 - filter("O"."CONTRACT_CLAUSE" LIKE 'GO%')
 238 - access("O"."EMPLID"=:B1 AND "O"."CONTRACT_NUM"=:B2 AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B3) AND
              "O"."CONTRACT_SEQ">:B4)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1)
 240 - access("SEC"."EMPLID"=:B1)
       filter("O"."EMPLID"="SEC"."EMPLID")
 241 - access("CLS"."SCRTY_SET_CD"='PPLJOB' AND "CLS"."SCRTY_TYPE_CD"="SEC"."SCRTY_TYPE_CD" AND
              "CLS"."SCRTY_KEY1"="SEC"."SCRTY_KEY1" AND "CLS"."SCRTY_KEY2"="SEC"."SCRTY_KEY2" AND "CLS"."SCRTY_KEY3"="SEC"."SCRTY_KEY3")
 242 - access("SOC"."CLASSID"="CLS"."CLASSID" AND "SOC"."OPRID"='smorris006a')
       filter("CLS"."TREE"='Y' AND "SOC"."CLASSID"="OPR"."ROWSECCLASS" AND ("SOC"."SEC_RSC_FLG"='1' OR
              "SOC"."SEC_RSC_FLG"='3') OR "CLS"."TREE"='N')
 244 - filter("CONTRACT_CLAUSE" LIKE 'GO%')
 245 - access("N_ED"."EMPLID"=:B1 AND "N_ED"."CONTRACT_NUM"=:B2 AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy')) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!),'dd-mon-yy'))
 247 - access("N_ED"."EMPLID"=:B1 AND "N_ED"."CONTRACT_NUM"=:B2)
 249 - access("P_ED"."EMPLID"=:B1 AND "P_ED"."EMPL_RCD"=:B2 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B3) AND
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
 251 - access("Q_ED"."SETID"=:B1 AND "Q_ED"."PWCE_PRODUCT"=:B2 AND "Q_ED"."PWCE_SUB_PRODUCT"=:B3 AND
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B4) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)

519 rows selected.



Please advice for possible tuning options.

Additional info:
Above job is being run from PeopleSoft v8.8.

For more information, please let me know.


Regards,
Suddhasatwa
Re: SQL Tuning Assistance Required [message #564738 is a reply to message #564735] Tue, 28 August 2012 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: SQL Tuning Assistance Required [message #564739 is a reply to message #564735] Tue, 28 August 2012 05:27 Go to previous messageGo to next message
John Watson
Messages: 4409
Registered: January 2010
Location: Global Village
Senior Member
Just an opening comment. You say Quote:
From my AWR report I have found below SQL which needs to be tuned:

but that is not correct. You should tune SQL only if your users say that it is too slow. The AWR report really
doesn't care: it doesn't have any feelings, it isn't waiting for a report to finish before it can go home, it
doesn't have a customer on the telephone who needs an answer.
So, do your users actually have a problem?
Re: SQL Tuning Assistance Required [message #564741 is a reply to message #564739] Tue, 28 August 2012 05:31 Go to previous messageGo to next message
SBhaumik_DBA
Messages: 3
Registered: August 2012
Junior Member
Thanks for the inputs above. The users have also compained about this query being slow, which led me to investigate via AWR and I found this query in the Top list of queries taking high CPU on the database.
Apologies, I should have included this information earlier.

Please advice for possible tuning options.
For more info, kindly let me know.

Thanks.
Re: SQL Tuning Assistance Required [message #564742 is a reply to message #564741] Tue, 28 August 2012 05:36 Go to previous messageGo to next message
John Watson
Messages: 4409
Registered: January 2010
Location: Global Village
Senior Member
Then begin by firing the programmer who wrote it.
What is all that to_date(to_char(to_date business about?
Why the bizarre mix of ISO join syntax and the old non-ISO syntax?
Functions applied to columns in predicates?
Sub-queries that can't be merged?
Your only solution is to start again. Determine what the users actually want, and write it properly.

Good luck.
Re: SQL Tuning Assistance Required [message #564745 is a reply to message #564742] Tue, 28 August 2012 06:06 Go to previous messageGo to next message
SBhaumik_DBA
Messages: 3
Registered: August 2012
Junior Member
Thanks for the inputs. Smile
I would pass on this information to the Developers for further progress.
I can show the AWR report to you, but it looks clean. So, I am supremely confident that DB tuning would not help in this case.

Note: This query is written (I Believe!) using the PSQUERY tools in PeopleSoft.
Re: SQL Tuning Assistance Required [message #564749 is a reply to message #564745] Tue, 28 August 2012 06:35 Go to previous message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
SBhaumik_DBA wrote on Tue, 28 August 2012 12:06

Note: This query is written (I Believe!) using the PSQUERY tools in PeopleSoft.


Then the tools are rubbish, or the developers don't know what they are doing.

This is a bug waiting to happen:
                                  AND E_ED.EFFDT <=
                                         TO_DATE (SYSDATE, 'dd-mon-yy')



Observe:
SQL> create table date_test as select sysdate + rownum dat from dual connect by level < 5;

Table created.

SQL> select * from date_test;

DAT
---------
29-AUG-12
30-AUG-12
31-AUG-12
01-SEP-12

SQL> select * from date_test where dat > to_date(sysdate, 'dd-mon-yy');

DAT
---------
29-AUG-12
30-AUG-12
31-AUG-12
01-SEP-12

SQL> alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> select * from date_test where dat > to_date(sysdate, 'dd-mon-yy');
select * from date_test where dat > to_date(sysdate, 'dd-mon-yy')
                                            *
ERROR at line 1:
ORA-01843: not a valid month


SQL> 


You should never, ever to_date a date.
Previous Topic: index behaviour
Next Topic: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP
Goto Forum:
  


Current Time: Thu Jul 31 07:37:52 CDT 2014

Total time taken to generate the page: 0.11933 seconds