Hi
Version 11.2.0.4 VPD enabled DB
I've below query which uses the pagination and VPD concept. Current time taken to bring 100 rows is ~14 sec. Expected is under 3 secs. Without pagination query brings 55K records. Looks like issue is with INLINE query "VW" which is bringing 155K and at the end result we are throwing away 100K rows. Any help would appreciated.
SELECT * FROM
(SELECT tmp3.*
, DENSE_RANK() OVER ( ORDER BY LAST_NAME_SRT_FLD, FIRST_NAME_SRT_FLD, ASSOCIATE_ID ) row_no
FROM
(SELECT tmp1.*
, COUNT(DISTINCT AOID) OVER() qry_rows
, DENSE_RANK() OVER ( PARTITION BY associate_id ORDER BY prim_position DESC, position_id ) position#
, DECODE(COUNT( 1 ) OVER( PARTITION BY employee_oid ), 0, 'N', 1, 'N', 'Y') has_m_pos
FROM
(SELECT tmp.*
, SUBSTR(business_unit_code_desc, 1, (INSTR(business_unit_code_desc, '`') -1)) business_unit_code
, SUBSTR(business_unit_code_desc, (INSTR(business_unit_code_desc, '`') +1)) business_unit_desc
, SUBSTR(job_title_code_desc, 1, (INSTR(job_title_code_desc, '`') -1)) job_title_code
, SUBSTR(job_title_code_desc, (INSTR(job_title_code_desc, '`') +1)) job_title_desc
FROM
(
SELECT
vw.paid payrollagreementoid
, ca.oid employee_oid
, cc.oid payrollgroupoid
, ca.legal_last_name last_name
, ca.legal_first_name first_name
, NVL(ca.national_identifier, (SELECT wm.xvalue FROM WFM wm WHERE wm.link_oid = ca.oid AND wm.xname = 'TaxId' AND wm.prod_locale_code = ps.prod_locale_code AND ROWNUM = 1)) personuniqueid
, ca.vpd_key vpd_key
, vw.company_code co_code
, vw.file_no filenumber
, LPAD(vw.file_no, 6, '0') filenumber_n
, ca.aoid
, ca.hire_date
, ps.rehire_date
, (ca.legal_last_name || NVL2(TRIM(ca.legal_first_name), (', ' || ca.legal_first_name), NULL)) emp_name
, ps.business_unit_oid business_unit_oid
, ps.chr_job_oid job_title_oid
, ps.start_date start_date
, ca.birth_date birth_date
, ps.eff_date pos_eff_date
, ps.eff_date_end pos_eff_date_end
, ps.status_oid pos_cur_status_oid
, ps.home_cost_no_oid pos_home_cost_no_oid
, ps.home_dept_oid pos_home_dept_oid
, ps.termination_date pos_termination_date
, ps.chr_location_oid pos_location_oid
, ps.union_code_oid pos_unioncodeoid
, ps.chr_pay_grade_oid pos_pay_grade_oid
, ps.f_manager pos_is_supervisor
, ps.pfid
, NVL(vw.active, 1) f_active
, NVL(vw.f_active_chk, 1) f_active_chk
, ps.f_paid
, NULL qualification_suffix
, NULL generation_suffix_code
, -1 f_direct_rpt
, -1 f_rpt_role
, -1 f_payroll_access
, ps.inactivedate
, ca.middle_name
, (SELECT tc.code||' - '||tc.description FROM CJP tc WHERE tc.oid = ps.chr_job_oid) pos_job_title_code_desc
, ca.associate_id
, ps.position_id
, (SELECT tc.code||' - '||tc.description FROM CSV tc WHERE tc.oid = ps.home_dept_oid) pos_home_dept_code_desc
, ca.emp_type_oid
, upper(ca.family_name) last_name_srt_fld
, upper(ca.given_name) first_name_srt_fld
, ca.family_name
, ca.given_name
, ps.f_retired
, ps.prod_locale_code
, ps.position_id display_position_id
, '~`~' business_unit_code_desc
, DECODE(ps.job_oid, NULL, NULL
, (SELECT (TRIM(tv_jobtitle.code) || '`' || tv_jobtitle.description)
FROM CJP tv_jobtitle
WHERE tv_jobtitle.oid = ps.job_oid)
) job_title_code_desc
, DECODE(pp.pfid, ps.pfid, 1, 0) prim_position
FROM
(SELECT pos.pfid, pos.paid, pos.eff_date, pos.eff_date_end, psa.f_etime, pos.istime,
pa.file_no, pos.f_paid, DECODE(pos.f_paid, 1, pa.company_code, psa.company_code) company_code,
DECODE(pos.f_paid, 1, NVL(pa.active, -1), pa.active) f_active_chk,
pa.pctrl_pay_group2_oid, pa.record_transmission_status_oid, pa.active, pa.position_lock
FROM chr_emp_position_rls pos,
POSADD psa,
PRAG pa
WHERE pos.paid = pa.paid(+)
AND pos.pfid = psa.pfid(+)) vw
, POS ps
, chr_associate ca
, company_code cc
, payroll_schedule pys
,PS pp
WHERE
ps.employee_oid = ca.oid
AND ca.f_archived != 1
AND ps.pfid = vw.pfid AND ps.eff_date = vw.eff_date and ps.eff_date_end = vw.eff_date_end
AND vw.company_code = cc.co_code(+)
AND (vw.f_paid = 0 OR (vw.f_paid = 1 AND NVL(vw.active,-1) = 1))
AND cc.oid = pys.oid(+)
AND ps.eff_date = (SELECT MIN(tmp_ps.eff_date) FROM POS tmp_ps
WHERE tmp_ps.pfid = ps.pfid
AND (TRUNC(sysdate) BETWEEN tmp_ps.eff_date AND tmp_ps.eff_date_end
OR tmp_ps.eff_date >= TRUNC(sysdate))
AND tmp_ps.eff_date_end >= tmp_ps.eff_date)
AND ps.employee_oid = pp.employee_oid
AND pp.eff_date = (SELECT MIN(tmp_pp.eff_date) FROM PS tmp_pp
WHERE tmp_pp.employee_oid = ps.employee_oid
AND (TRUNC(sysdate) BETWEEN tmp_pp.eff_date AND tmp_pp.eff_date_end
OR tmp_pp.eff_date >= TRUNC(sysdate))
AND tmp_pp.eff_date_end >= tmp_pp.eff_date)
) tmp
) tmp1
)tmp3
WHERE position# = 1 -- AND row_no BETWEEN :v_st_row AND :v_end_row
) WHERE row_no BETWEEN :v_st_row AND :v_end_row
ORDER BY row_no
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3034 (100)| 100 |00:00:04.21 | 618K| | | |
|* 1 | COUNT STOPKEY | | 101 | | | 0 |00:00:00.01 | 451 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | WFM | 101 | 1 | 5 (0)| 0 |00:00:00.01 | 451 | | | |
|* 3 | INDEX SKIP SCAN | IDX_WFM | 101 | 1 | 4 (0)| 0 |00:00:00.01 | 451 | | | |
|* 4 | INDEX UNIQUE SCAN | UQ_EMPLOYEE_TRAN_LOG_TGT_PAGMT | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | | | |
| 5 | INLIST ITERATOR | | 1 | | | 1 |00:00:00.01 | 3 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | SCHEME | 3 | 1 | 2 (0)| 1 |00:00:00.01 | 3 | | | |
|* 7 | INDEX UNIQUE SCAN | PK_SCHEME | 3 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | | | |
|* 8 | COUNT STOPKEY | | 0 | | | 0 |00:00:00.01 | 0 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | WFM | 0 | 1 | 5 (0)| 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX SKIP SCAN | IDX_WFM | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | | | |
| 11 | INLIST ITERATOR | | 12 | | | 12 |00:00:00.01 | 82 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | CJP | 36 | 1 | 6 (0)| 12 |00:00:00.01 | 82 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_CJP | 36 | 1 | 5 (0)| 12 |00:00:00.01 | 74 | | | |
| 14 | INLIST ITERATOR | | 60 | | | 60 |00:00:00.01 | 405 | | | |
| 15 | PARTITION HASH ITERATOR | | 180 | 1 | 6 (0)| 60 |00:00:00.01 | 405 | | | |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | CSV | 180 | 1 | 6 (0)| 60 |00:00:00.01 | 405 | | | |
|* 17 | INDEX UNIQUE SCAN | PK_CSV | 180 | 1 | 5 (0)| 60 |00:00:00.01 | 360 | | | |
| 18 | INLIST ITERATOR | | 12 | | | 12 |00:00:00.01 | 82 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | CJP | 36 | 1 | 6 (0)| 12 |00:00:00.01 | 82 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_CJP | 36 | 1 | 5 (0)| 12 |00:00:00.01 | 74 | | | |
|* 21 | VIEW | | 1 | 1 | 3034 (1)| 100 |00:00:04.21 | 618K| | | |
|* 22 | WINDOW SORT PUSHED RANK | | 1 | 1 | 3034 (1)| 101 |00:00:04.17 | 617K| 372K| 372K| 330K (0)|
| 23 | NESTED LOOPS OUTER | | 1 | 1 | 2658 (1)| 54874 |00:00:04.03 | 617K| | | |
| 24 | NESTED LOOPS OUTER | | 1 | 1 | 2656 (1)| 54874 |00:00:03.85 | 562K| | | |
|* 25 | HASH JOIN | | 1 | 1 | 2654 (1)| 54874 |00:00:03.57 | 562K| 31M| 3791K| 32M (0)|
| 26 | NESTED LOOPS | | 1 | 1 | 39 (0)| 54874 |00:00:02.62 | 488K| | | |
| 27 | NESTED LOOPS | | 1 | 1 | 39 (0)| 54874 |00:00:02.41 | 436K| | | |
| 28 | NESTED LOOPS | | 1 | 1 | 36 (0)| 54874 |00:00:01.43 | 230K| | | |
| 29 | PARTITION HASH SINGLE | | 1 | 1 | 34 (0)| 55362 |00:00:00.90 | 72715 | | | |
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID | POS | 1 | 1 | 34 (0)| 55362 |00:00:00.89 | 72715 | | | |
|* 31 | INDEX RANGE SCAN | PK_POS | 1 | 1 | 33 (0)| 55362 |00:00:00.57 | 17794 | | | |
| 32 | SORT AGGREGATE | | 55362 | 1 | | 55362 |00:00:00.43 | 16596 | | | |
| 33 | PARTITION HASH SINGLE | | 55362 | 1 | 3 (0)| 55362 |00:00:00.37 | 16596 | | | |
| 34 | FIRST ROW | | 55362 | 1 | 3 (0)| 55362 |00:00:00.26 | 16596 | | | |
|* 35 | INDEX RANGE SCAN (MIN/MAX) | UQ_CHR_EMP_POS_DTS | 55362 | 1 | 3 (0)| 55362 |00:00:00.24 | 16596 | | | |
| 36 | PARTITION HASH SINGLE | | 55362 | 1 | 2 (0)| 54874 |00:00:00.54 | 158K| | | |
|* 37 | TABLE ACCESS BY LOCAL INDEX ROWID | CHS | 55362 | 1 | 2 (0)| 54874 |00:00:00.45 | 158K| | | |
|* 38 | INDEX UNIQUE SCAN | PK_CHS | 55362 | 1 | 1 (0)| 55362 |00:00:00.22 | 102K| | | |
| 39 | PARTITION HASH SINGLE | | 54874 | 1 | 2 (0)| 54874 |00:00:00.94 | 206K| | | |
|* 40 | INDEX RANGE SCAN | PK_POS | 54874 | 1 | 2 (0)| 54874 |00:00:00.86 | 206K| | | |
| 41 | SORT AGGREGATE | | 54874 | 1 | | 54874 |00:00:00.50 | 102K| | | |
| 42 | PARTITION HASH SINGLE | | 54874 | 1 | 3 (0)| 54874 |00:00:00.43 | 102K| | | |
| 43 | FIRST ROW | | 54874 | 1 | 3 (0)| 54874 |00:00:00.37 | 102K| | | |
|* 44 | INDEX RANGE SCAN (MIN/MAX) | UQ_POS_EMP_DTS | 54874 | 1 | 3 (0)| 54874 |00:00:00.34 | 102K| | | |
| 45 | TABLE ACCESS BY LOCAL INDEX ROWID | POS | 54874 | 1 | 3 (0)| 54874 |00:00:00.18 | 51331 | | | |
| 46 | VIEW | | 1 | 2146 | 2615 (1)| 155K|00:00:00.86 | 73993 | | | |
|* 47 | FILTER | | 1 | | | 155K|00:00:00.80 | 73993 | | | |
|* 48 | HASH JOIN RIGHT OUTER | | 1 | 2146 | 2615 (1)| 155K|00:00:00.79 | 73993 | 4046K| 2024K| 4492K (0)|
| 49 | PARTITION HASH SINGLE | | 1 | 4186 | 2082 (1)| 55362 |00:00:00.08 | 51650 | | | |
| 50 | TABLE ACCESS BY LOCAL INDEX ROWID | POSADD | 1 | 4186 | 2082 (1)| 55362 |00:00:00.08 | 51650 | | | |
|* 51 | INDEX RANGE SCAN | PK_POSADD | 1 | 182 | 32 (0)| 55362 |00:00:00.01 | 339 | | | |
|* 52 | FILTER | | 1 | | | 155K|00:00:00.62 | 22343 | | | |
|* 53 | HASH JOIN RIGHT OUTER | | 1 | 2146 | 534 (1)| 155K|00:00:00.56 | 22343 | 5219K| 1416K| 6616K (0)|
| 54 | PARTITION HASH SINGLE | | 1 | 1593 | 109 (0)| 55317 |00:00:00.05 | 3204 | | | |
|* 55 | TABLE ACCESS BY LOCAL INDEX ROWID| PRAG | 1 | 1593 | 109 (0)| 55317 |00:00:00.04 | 3204 | | | |
|* 56 | INDEX RANGE SCAN | IX_PRAG_RECTRANID | 1 | 74 | 14 (0)| 55317 |00:00:00.01 | 426 | | | |
| 57 | PARTITION HASH SINGLE | | 1 | 3679 | 424 (0)| 155K|00:00:00.41 | 19139 | | | |
|* 58 | TABLE ACCESS BY LOCAL INDEX ROWID| POS | 1 | 3679 | 424 (0)| 155K|00:00:00.39 | 19139 | | | |
|* 59 | INDEX RANGE SCAN | IX2_POS | 1 | 161 | 28 (0)| 155K|00:00:00.05 | 1151 | | | |
|* 60 | TABLE ACCESS BY INDEX ROWID | COMPANY_CODE_TBL | 54874 | 1 | 2 (0)| 54856 |00:00:00.23 | 9 | | | |
|* 61 | INDEX RANGE SCAN | UQ_COMPANY_CODE_CO_CODE | 54874 | 1 | 1 (0)| 54856 |00:00:00.11 | 8 | | | |
|* 62 | TABLE ACCESS BY INDEX ROWID | PS | 54874 | 1 | 2 (0)| 54856 |00:00:00.15 | 54870 | | | |
|* 63 | INDEX UNIQUE SCAN | PK_PS | 54874 | 1 | 1 (0)| 54856 |00:00:00.06 | 14 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:SYS_B_001)
2 - filter(("PROD_LOCALE_CODE"=:B1 AND "XNAME"=:SYS_B_000))
3 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "LINK_OID"=:B1)
filter("LINK_OID"=:B1)
4 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "DEST_PAID"=:B1)
6 - filter(("PROD_LOCALE_CODE"=SYS_CONTEXT('ctx_vpd','ctx_locale') OR "PROD_LOCALE_CODE"='UN'))
7 - access((("VPD_KEY"='NG_COMMON' OR "VPD_KEY"='NG_COMMON_'||SYS_CONTEXT('ctx_vpd','ctx_locale') OR "VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil')))
AND "OID"=:B1)
8 - filter(ROWNUM=:SYS_B_042)
9 - filter(("PROD_LOCALE_CODE"=:B1 AND "XNAME"=:SYS_B_041))
10 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "LINK_OID"=:B1)
filter("LINK_OID"=:B1)
12 - filter(("PROD_LOCALE_CODE"=SYS_CONTEXT('ctx_vpd','ctx_locale') OR "PROD_LOCALE_CODE"='UN'))
13 - access((("VPD_KEY"='NG_COMMON' OR "VPD_KEY"='NG_COMMON_'||SYS_CONTEXT('ctx_vpd','ctx_locale') OR "VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil')))
AND "OID"=:B1)
16 - filter((INTERNAL_FUNCTION("PROD_LOCALE_CODE") AND "F_PARTNER_DATA"='N'))
17 - access((("VPD_KEY"='NG_COMMON' OR "VPD_KEY"='NG_COMMON_'||SYS_CONTEXT('ctx_vpd','ctx_locale') OR "VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil')))
AND "OID"=:B1)
19 - filter(("PROD_LOCALE_CODE"=SYS_CONTEXT('ctx_vpd','ctx_locale') OR "PROD_LOCALE_CODE"='UN'))
20 - access((("VPD_KEY"='NG_COMMON' OR "VPD_KEY"='NG_COMMON_'||SYS_CONTEXT('ctx_vpd','ctx_locale') OR "VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil')))
AND "OID"=:B1)
21 - filter(("ROW_NO">=TO_NUMBER(:V_ST_ROW) AND "ROW_NO"<=TO_NUMBER(:V_END_ROW)))
22 - filter(DENSE_RANK() OVER ( ORDER BY UPPER("FAMILY_NAME"),UPPER("GIVEN_NAME"),"ASSOCIATE_ID")<=TO_NUMBER(:V_END_ROW))
25 - access("PFID"="VW"."PFID" AND "EFF_DATE"="VW"."EFF_DATE" AND "EFF_DATE_END"="VW"."EFF_DATE_END")
30 - filter("F_DEL"=0)
31 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil'))
filter("EFF_DATE"=)
35 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "PFID"=:B1)
filter(("EFF_DATE_END">="EFF_DATE" AND (("EFF_DATE_END">=TRUNC(SYSDATE@!) AND "EFF_DATE"<=TRUNC(SYSDATE@!)) OR "EFF_DATE">=TRUNC(SYSDATE@!)) AND "F_DEL"=0))
37 - filter(("F_ARCHIVED"<>:SYS_B_097 AND "F_DEL"=0))
38 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "EMPLOYEE_OID"="OID")
40 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "EMPLOYEE_OID"="EMPLOYEE_OID" AND "EFF_DATE"= AND "F_DEL"=0)
44 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "EMPLOYEE_OID"=:B1)
filter(((("EFF_DATE_END">=TRUNC(SYSDATE@!) AND "EFF_DATE"<=TRUNC(SYSDATE@!)) OR "EFF_DATE">=TRUNC(SYSDATE@!)) AND "F_DEL"=0 AND "EFF_DATE_END">="EFF_DATE"))
47 - filter(TO_NUMBER(:V_END_ROW)>=TO_NUMBER(:V_ST_ROW))
48 - access("PFID"="PFID")
51 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil'))
52 - filter(("F_PAID"=:SYS_B_098 OR ("F_PAID"=:SYS_B_099 AND NVL("ACTIVE",(-:SYS_B_100))=:SYS_B_101)))
53 - access("PAID"="PAID")
55 - filter(("ACTIVE"=1 AND "F_DEL"=0))
56 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil'))
58 - filter("F_DEL"=0)
59 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil'))
60 - filter((INTERNAL_FUNCTION("PROD_LOCALE_CODE") AND (NVL("PS_OID",'*')<>SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid') OR
NVL("PS_OID",'***')=DECODE(NVL(SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid'),'~'),'~',NVL("PS_OID",'***'),'*',NVL("PS_OID"
,'***'),SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid')))))
61 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "VW"."COMPANY_CODE"="CO_CODE")
filter("VPD_KEY"=NVL(SYS_CONTEXT('ctx_vpd','ctx_vkey_fil'),"VPD_KEY"))
62 - filter("F_COMPSERVICE"=0)
63 - access("VPD_KEY"=SYS_CONTEXT('ctx_vpd','ctx_vkey_fil') AND "OID"=CASE WHEN (ROWID IS NOT NULL) THEN CASE WHEN
((SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid')<>'~') AND (SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid')<>'*')) THEN
SYS_CONTEXT('ctx_vpd','ctx_concurrent_pay_oid') ELSE "PS_OID" END ELSE NULL END )