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  |
 |
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 #564739 is a reply to message #564735] |
Tue, 28 August 2012 05:27  |
John Watson
Messages: 8960
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 #564742 is a reply to message #564741] |
Tue, 28 August 2012 05:36 |
John Watson
Messages: 8960
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 #564749 is a reply to message #564745] |
Tue, 28 August 2012 06:35 |
cookiemonster
Messages: 13958
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.
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 16:17:42 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
