1. What is the release level? -------11.2.0.2
2. Is the query consistently slow, or occasionally? -----Yes this Query is consistently performing slow
3. Is the query slow only with certain bind variables? -----No
4. Has the query performed better in the past (i.e., execution plan regression)? ---- No
5. Are object stats up-to-date? ---- Yes. I collected stats today only
Sql Statement-----
SELECT emplid, empl_rcd, dur, TO_CHAR (dur, 'YYYY-MM-DD'), punch_type,
TO_CHAR (CAST ((punch_dttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
TO_CHAR (CAST ((punch_end) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF'),
rule_element_1, rule_element_2, rule_element_3, rule_element_4,
rule_element_5, taskgroup, oprid, reported_status, oprid_last_updt,
TO_CHAR (CAST ((dttm_modified) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'
),
TO_CHAR (CAST ((dttm_created) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'
),
trc, tl_quantity, country, business_unit, LOCATION, deptid, jobcode,
business_unit_pc, project_id, activity_id, resource_type,
resource_category, resource_sub_cat, user_field_1, user_field_2,
user_field_3, user_field_4, user_field_5,
TO_CHAR (CAST ((lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'
),
supervisor_id, NAME, descr, oi_bus_title, empl_status, empl_type,
oi_sup_name, per_org, descr254, descr1, oi_proj_resp, tl_group_id,
oi_comments
FROM ps_oi_tl_rptim_vw5
WHERE dur BETWEEN TO_DATE (:1, 'YYYY-MM-DD') AND TO_DATE (:2, 'YYYY-MM-DD')
AND (tl_group_id) IN (SELECT tl_group_id
FROM ps_oi_tl_group_sel
WHERE oprid = 'FCAMERON');
EXPLAIN PLAN------------------
Plan hash value: 875496762
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2461 | 24367 (1)| 00:04:53 |
| 1 | SORT ORDER BY | | 1 | 2461 | 24367 (1)| 00:04:53 |
| 2 | NESTED LOOPS | | 1 | 2461 | 24367 (1)| 00:04:53 |
| 3 | VIEW | PS_OI_TL_RPTIM_VW5 | 1 | 2447 | 24367 (1)| 00:04:53 |
| 4 | HASH UNIQUE | | 1 | 641 | 24367 (1)| 00:04:53 |
|* 5 | FILTER | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | HASH JOIN | | 2 | 1282 | 583 (1)| 00:00:07 |
| 8 | NESTED LOOPS | | 1 | 623 | 532 (1)| 00:00:07 |
|* 9 | HASH JOIN | | 59 | 34456 | 414 (1)| 00:00:05 |
| 10 | NESTED LOOPS | | 62 | 34534 | 369 (1)| 00:00:05 |
|* 11 | HASH JOIN | | 62 | 33976 | 369 (1)| 00:00:05 |
|* 12 | HASH JOIN | | 64 | 33792 | 365 (1)| 00:00:05 |
| 13 | NESTED LOOPS | | 45 | 23130 | 361 (0)| 00:00:05 |
| 14 | NESTED LOOPS OUTER | | 45 | 21915 | 271 (0)| 00:00:04 |
| 15 | NESTED LOOPS OUTER | | 45 | 19845 | 226 (0)| 00:00:03 |
| 16 | NESTED LOOPS | | 45 | 12015 | 225 (0)| 00:00:03 |
| 17 | NESTED LOOPS | | 7 | 1463 | 204 (0)| 00:00:03 |
| 18 | TABLE ACCESS BY INDEX ROWID| PS_NAMES | 6 | 222 | 167 (0)| 00:00:03 |
|* 19 | INDEX SKIP SCAN | PSANAMES | 6 | | 160 (0)| 00:00:02 |
| 20 | SORT AGGREGATE | | 1 | 21 | | |
|* 21 | INDEX RANGE SCAN | PS_NAMES | 1 | 21 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME | 1 | 172 | 7 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | PS_TL_RPTD_TIME | 4 | | 5 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 6 | 348 | 3 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX$$_3F450003 | 1 | | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | PS_OI_TL_PRJ_COMNT | 1 | 174 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PS_OI_TL_PRJ_COMNT | 1 | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | PS_PROJECT | 1 | 46 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PS_PROJECT | 1 | | 0 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | PSASET_CNTRL_REC | 1 | 27 | 2 (0)| 00:00:01 |
| 31 | INDEX FAST FULL SCAN | PS_TL_TRC_TBL | 1066 | 14924 | 3 (0)| 00:00:01 |
| 32 | INDEX FAST FULL SCAN | PS4DEPT_TBL | 1815 | 36300 | 4 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PS_PERSONAL_DATA | 1 | 9 | 0 (0)| 00:00:01 |
| 34 | INDEX FAST FULL SCAN | PS0PERSONAL_DATA | 26178 | 690K| 44 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | PS_OI_BUS_TITLE | 1 | 39 | 2 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PS_OI_BUS_TITLE | 1 | | 1 (0)| 00:00:01 |
| 37 | INDEX FAST FULL SCAN | PS_TL_GROUP_DTL | 51579 | 906K| 50 (0)| 00:00:01 |
| 38 | SORT AGGREGATE | | 1 | 20 | | |
|* 39 | INDEX RANGE SCAN | PSAJOB | 1 | 20 | 3 (0)| 00:00:01 |
| 40 | SORT AGGREGATE | | 1 | 14 | | |
|* 41 | INDEX RANGE SCAN | PS_TL_TRC_TBL | 1 | 14 | 2 (0)| 00:00:01 |
| 42 | SORT AGGREGATE | | 1 | 20 | | |
|* 43 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 20 | 2 (0)| 00:00:01 |
| 44 | SORT AGGREGATE | | 1 | 23 | | |
|* 45 | INDEX RANGE SCAN | PSAJOB | 1 | 23 | 3 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | PS_OI_TL_GROUP_SEL | 1 | 14 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("SYS_ALIAS_6"."EFFDT"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
SYSADM."PS_JOB" "B_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B1) AND "B_ED"."EMPL_RCD"=:B2 AND "B_ED"."EMPLID"=:B3 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B4) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT
MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_TL_TRC_TBL" "G_ED" WHERE
SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B5) AND "G_ED"."TRC"=:B6
AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B7) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT
MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "F_ED" WHERE
SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B8) AND
"F_ED"."DEPTID"=:B9 AND "F_ED"."SETID"=:B10 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B11) AND
"SYS_ALIAS_6"."EFFSEQ"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))) FROM SYSADM."PS_JOB"
"B_ES" WHERE SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B12) AND "B_ES"."EMPL_RCD"=:B13 AND
"B_ES"."EMPLID"=:B14 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B15))
6 - filter(TO_DATE(:1,'YYYY-MM-DD')<=TO_DATE(:2,'YYYY-MM-DD'))
7 - access("A"."EMPLID"="L"."EMPLID" AND "A"."EMPL_RCD"="L"."EMPL_RCD")
9 - access("C1"."EMPLID"="SYS_ALIAS_6"."SUPERVISOR_ID")
11 - access("SYS_ALIAS_8"."SETID"="F1"."SETID" AND "SYS_ALIAS_8"."DEPTID"="SYS_ALIAS_6"."DEPTID")
12 - access("SYS_ALIAS_10"."TRC"="A"."TRC")
19 - access("SYS_ALIAS_13"."NAME_TYPE"='PRI')
filter("SYS_ALIAS_13"."NAME_TYPE"='PRI' AND "SYS_ALIAS_13"."EFFDT"= (SELECT
MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_NAMES" "B" WHERE SYS_OP_DESCEND("EFFDT") IS
NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND "B"."NAME_TYPE"=:B1 AND "B"."EMPLID"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHA
R(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')))
21 - access("B"."EMPLID"=:B1 AND "B"."NAME_TYPE"=:B2 AND
SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND
SYS_OP_DESCEND("EFFDT") IS NOT NULL)
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-D
D'))
23 - access("A"."EMPLID"="SYS_ALIAS_13"."EMPLID" AND "A"."DUR">=TO_DATE(:1,'YYYY-MM-DD') AND
"A"."DUR"<=TO_DATE(:2,'YYYY-MM-DD'))
filter("A"."DUR">=TO_DATE(:1,'YYYY-MM-DD') AND "A"."DUR"<=TO_DATE(:2,'YYYY-MM-DD'))
25 - access("A"."EMPLID"="SYS_ALIAS_6"."EMPLID" AND "A"."EMPL_RCD"="SYS_ALIAS_6"."EMPL_RCD")
27 - access("A"."EMPLID"="M"."EMPLID"(+) AND "A"."EMPL_RCD"="M"."EMPL_RCD"(+) AND "A"."DUR"="M"."DUR"(+)
AND "A"."SEQ_NBR"="M"."SEQ_NBR"(+))
filter("M"."DUR"(+)>=TO_DATE(:1,'YYYY-MM-DD') AND "M"."DUR"(+)<=TO_DATE(:2,'YYYY-MM-DD'))
29 - access("D"."BUSINESS_UNIT"(+)="A"."BUSINESS_UNIT_PC" AND "D"."PROJECT_ID"(+)="A"."PROJECT_ID")
30 - access("F1"."SETCNTRLVALUE"="SYS_ALIAS_6"."BUSINESS_UNIT" AND "F1"."RECNAME"='DEPT_TBL')
33 - access("C"."EMPLID"="SYS_ALIAS_6"."EMPLID")
36 - access("E"."EMPLID"="A"."EMPLID" AND "E"."EMPL_RCD"="A"."EMPL_RCD" AND
SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("SYS_ALIAS_6"."EFFDT") AND
SYS_OP_DESCEND("EFFSEQ")=SYS_OP_DESCEND("SYS_ALIAS_6"."EFFSEQ"))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))="SYS_ALIAS_6"."EFFDT" AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))="SYS_ALIAS_6"."EFFSEQ")
39 - access("B_ED"."EMPLID"=:B1 AND "B_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)
41 - access("G_ED"."TRC"=:B1 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B2) AND
SYS_OP_DESCEND("EFFDT") IS NOT NULL)
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
43 - access("F_ED"."SETID"=:B1 AND "F_ED"."DEPTID"=: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)
45 - access("B_ES"."EMPLID"=:B1 AND "B_ES"."EMPL_RCD"=:B2 AND
SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B3))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1)
46 - access("OPRID"='FCAMERON' AND "TL_GROUP_ID"="TL_GROUP_ID")
114 rows selected.
-------View "ps_oi_tl_rptim_vw5" text-----------
CREATE OR REPLACE FORCE VIEW sysadm.ps_oi_tl_rptim_vw5 (emplid,
empl_rcd,
dur,
punch_type,
punch_dttm,
punch_end,
rule_element_1,
rule_element_2,
rule_element_3,
rule_element_4,
rule_element_5,
taskgroup,
oprid,
reported_status,
oprid_last_updt,
dttm_modified,
dttm_created,
trc,
tl_quantity,
country,
business_unit,
LOCATION,
deptid,
jobcode,
business_unit_pc,
project_id,
activity_id,
resource_type,
resource_category,
resource_sub_cat,
user_field_1,
user_field_2,
user_field_3,
user_field_4,
user_field_5,
lastupddttm,
supervisor_id,
NAME,
descr,
oi_bus_title,
empl_status,
empl_type,
oi_sup_name,
per_org,
descr254,
descr1,
oi_proj_resp,
tl_group_id,
oi_comments
)
AS
SELECT DISTINCT a.emplid, a.empl_rcd, a.dur, a.punch_type, a.punch_dttm,
a.punch_end, a.rule_element_1, a.rule_element_2,
a.rule_element_3, a.rule_element_4, a.rule_element_5,
a.taskgroup, a.oprid, a.reported_status, a.oprid_last_updt,
a.dttm_modified, a.dttm_created, a.trc, a.tl_quantity,
a.country, a.business_unit, a.LOCATION, b.deptid,
b.jobcode, a.business_unit_pc, a.project_id, a.activity_id,
a.resource_type, a.resource_category, a.resource_sub_cat,
a.user_field_1, a.user_field_2, a.user_field_3,
a.user_field_4, a.user_field_5, a.lastupddttm,
b.supervisor_id, n.NAME,
CASE
WHEN d.descr IS NULL
THEN 'No Project Information'
ELSE d.descr
END,
e.oi_bus_title, b.empl_status, b.empl_type, c1.NAME,
b.per_org, d.descr, ' ', d.project_user1, l.tl_group_id,
NVL (DBMS_LOB.SUBSTR (m.oi_comments, 2000, 1),
'No Comment')
FROM ps_tl_rptd_time a LEFT OUTER JOIN ps_project d
ON d.business_unit = a.business_unit_pc
AND d.project_id = a.project_id
LEFT OUTER JOIN ps_oi_tl_prj_comnt m
ON a.emplid = m.emplid
AND a.empl_rcd = m.empl_rcd
AND a.dur = m.dur
AND a.seq_nbr = m.seq_nbr
,
ps_job b,
ps_personal_data c,
ps_personal_data c1,
ps_oi_bus_title e,
ps_dept_tbl f,
ps_set_cntrl_rec f1,
ps_tl_trc_tbl g,
ps_person_name n,
ps_tl_group_dtl l
WHERE a.emplid = b.emplid
AND a.empl_rcd = b.empl_rcd
AND a.emplid = n.emplid
AND b.effdt =
(SELECT MAX (b_ed.effdt)
FROM ps_job b_ed
WHERE b_ed.emplid = b.emplid
AND b_ed.empl_rcd = b.empl_rcd
AND b_ed.effdt <= a.dur)
AND b.effseq =
(SELECT MAX (b_es.effseq)
FROM ps_job b_es
WHERE b_es.emplid = b.emplid
AND b_es.empl_rcd = b.empl_rcd
AND b_es.effdt = b.effdt)
AND c.emplid = b.emplid
AND c1.emplid = b.supervisor_id
AND e.emplid = a.emplid
AND e.empl_rcd = a.empl_rcd
AND e.effdt = b.effdt
AND e.effseq = b.effseq
AND f.setid = f1.setid
AND f.deptid = b.deptid
AND f.effdt =
(SELECT MAX (f_ed.effdt)
FROM ps_dept_tbl f_ed
WHERE f_ed.setid = f.setid
AND f_ed.deptid = f.deptid
AND f_ed.effdt <= a.dur)
AND f1.setcntrlvalue = b.business_unit
AND f1.recname = 'DEPT_TBL'
AND g.trc = a.trc
AND g.effdt = (SELECT MAX (g_ed.effdt)
FROM ps_tl_trc_tbl g_ed
WHERE g_ed.trc = g.trc AND g_ed.effdt <= a.dur)
AND a.emplid = l.emplid
AND a.empl_rcd = l.empl_rcd;
Thanks
Jitendra