Perf: PYUGEN: Performance Issues With Payroll (Doc ID 341686.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

Oracle Payroll - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

Problem Statement:

The PAYROLL PROCESS has slowed down -

Most expensive query appears to be the following:

SELECT /*+ ORDERED */
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0))
FROM /* Person within Government Reporting Entity */
 pay_assignment_actions BAL_ASSACT
 , pay_payroll_actions BACT
 , per_all_assignments_f ASS
 , pay_assignment_actions ASSACT
 , pay_payroll_actions PACT
 , pay_run_results RR
 , pay_run_result_values TARGET
 , pay_balance_feeds_f FEED
where  BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
  and  BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
  and  FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0)
  and  FEED.input_value_id = TARGET.input_value_id
  and  nvl(TARGET.result_value,'0') != '0'
  and  TARGET.run_result_id = RR.run_result_id
  and  RR.assignment_action_id = ASSACT.assignment_action_id
  and  ASSACT.payroll_action_id = PACT.payroll_action_id
  and  PACT.effective_date between FEED.effective_start_date
  and  FEED.effective_end_date
  and  RR.status in ('P','PA')
  and  ASSACT.action_sequence <= BAL_ASSACT.action_sequence
  and  ASSACT.assignment_id = ASS.assignment_id
/* This may break under the cost-based optimizer, since the result depends on
the correct plan! */
  and  ASS.person_id =
        (select person_id
           from per_all_assignments_f START_ASS
          where START_ASS.assignment_id = BAL_ASSACT.assignment_id
            and rownum = 1)
  and  PACT.effective_date between ASS.effective_start_date
  and  ASS.effective_end_date
  and  ASSACT.tax_unit_id = :TAX_UNIT_ID
/* Year To Date */
  and   PACT.effective_date >=trunc(BACT.effective_date,'Y')

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms