Performance issue seen in Prepayment, Costing, and Transfer to GL (Doc ID 1481721.1)

select /*+ ORDERED USE_NL(pay_asg) */ distinct pay_pos.person_id, null, null
from pay_payroll_actions pay_pa1,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_act,
per_all_assignments_f pay_asg,
per_periods_of_service pay_pos
where pay_pa1.payroll_action_id = :payroll_action_id
and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
and pay_pa2.payroll_id = pay_pa1.payroll_id
and pay_pa2.effective_date between
pay_pa1.start_date and pay_pa1.effective_date
and pay_act.payroll_action_id = pay_pa2.payroll_action_id
and pay_asg.assignment_id = pay_act.assignment_id
and pay_pa1.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date
and pay_pos.period_of_service_id = pay_asg.period_of_service_id
order by pay_pos.person_id

SELECT /*+ ORDERED
index(pa2 PAY_PAYROLL_ACTIONS_N5)
index(as1 PER_ASSIGNMENTS_F_N4)
index(as2 PER_ASSIGNMENTS_F_PK)
USE_NL(pos pop as1) */ ACT.ASSIGNMENT_ACTION_ID,
ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID FROM PAY_PAYROLL_ACTIONS PA1,
PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL,
PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F
AS1, PAY_ASSIGNMENT_ACTIONS ACT, PER_ALL_ASSIGNMENTS_F AS2 WHERE
PA1.PAYROLL_ACTION_ID = :B2 AND PA2.CONSOLIDATION_SET_ID =
PA1.CONSOLIDATION_SET_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND
PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND
ACT.ACTION_STATUS IN ('C','S') AND PCL.CLASSIFICATION_NAME = :B3 AND
PA2.ACTION_TYPE = PCL.ACTION_TYPE AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID
AND PA2.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_DATE AND
AS1.EFFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND
PA1.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_START_DATE AND
AS2.EFFECTIVE_END_DATE AND POP.PAYROLL_ACTION_ID = :B2 AND POP.CHUNK_NUMBER
= :B1 AND POS.PERSON_ID = POP.PERSON_ID AND POS.PERIOD_OF_SERVICE_ID =
AS1.PERIOD_OF_SERVICE_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR
PA1.PAYROLL_ID IS NULL) AND NOT EXISTS ( SELECT NULL FROM
PAY_ASSIGNMENT_ACTIONS AC2, PAY_PAYROLL_ACTIONS PA3, PAY_ACTION_INTERLOCKS
INT WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND
AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND PA3.PAYROLL_ACTION_ID =
AC2.PAYROLL_ACTION_ID AND PA3.ACTION_TYPE IN ('C', 'S')) AND NOT EXISTS (
SELECT /*+ ORDERED*/ NULL FROM PER_ALL_ASSIGNMENTS_F AS3,
PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B4 = 'N' AND AC3.PAYROLL_ACTION_ID =
PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ('C','S') AND
AS3.ASSIGNMENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN
AS3.EFFECTIVE_START_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID =
AS2.PERSON_ID) ORDER BY ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID,
POS.PERIOD_OF_SERVICE_ID

call count cpu elapsed disk query current  rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 73 806.03 1227.62 8181201 9355768 73 0
Fetch 73 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 146 806.03 1227.62 8181201 9355768 73 0