Poor Performance When Extracting From PAY_AU_ASG_ELEMENT_PAYMENTS_V On Custom Report And Also With Seeded Pay Advice (Australia) (Doc ID 2044562.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle HRMS (Australia) - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

On : 12.1 HRMS RUP7 version, Australian Payroll

ACTUAL BEHAVIOR
---------------
After applying <Patch 21267565> - STATUTORY UPDATES EFFECTIVE 1 JULY 2015, getting performance issue when generating custom report which extract data from pay_au_asg_element_payments_v. For the same set of parameters, the report now takes 3-4 hours while it used to take only 2-3 mins.

The issue is also reproducible for the seeded Pay Advice (Australia).


BEFORE PATCH:

select decode(classification_name, 'Taxable Earnings', 1,
'Non Taxable Earnings', 2,
'Pre Tax Deductions', 3,
'Tax Deductions',4,
'Post Tax Deductions',5,
'Direct Payments',6
) classification_sort,
assignment_action_id assignment_action_id_e,
classification_name classification_name,
element_reporting_name element_name,
processing_priority processing_priority,
payment payment,
hours hours,
rate hourly_rate
from pay_au_asg_element_payments_v p
WHERE ( classification_name <> 'Employer Superannuation Contributions' ) AND
( :assignment_action_id = assignment_action_id) union all select 7
classification_sort,
assignment_action_id assignment_action_id_e,
classification_name classification_name,
element_reporting_name element_name,
processing_priority processing_priority,
SUM(payment) payment,
hours hours,
rate hourly_rate
from pay_au_asg_element_payments_v p
WHERE ( classification_name = 'Employer Superannuation Contributions' ) AND
( :assignment_action_id = assignment_action_id) group by
assignment_action_id , classification_name , processing_priority ,
element_reporting_name , hours , rate ORDER BY 1 ASC,3 ASC ,
classification_sort , processing_priority

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- -------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.37 0.40 2 22191 0 9
------- ------ -------- ---------- ------- ---------- ---------- -------
total 6 0.38 0.40 2 22191 0 9

 

POST PATCH:

select decode(classification_name, 'Taxable Earnings', 1,
'Non Taxable Earnings', 2,
'Pre Tax Deductions', 3,
'Tax Deductions',4,
'Post Tax Deductions',5,
'Direct Payments',6
) classification_sort,
assignment_action_id assignment_action_id_e,
classification_name classification_name,
element_reporting_name element_name,
processing_priority processing_priority,
payment payment,
hours hours,
rate hourly_rate
from pay_au_asg_element_payments_v p
WHERE ( classification_name <> 'Employer Superannuation Contributions' ) AND
( :assignment_action_id = assignment_action_id) union all select 7
classification_sort,
assignment_action_id assignment_action_id_e,
classification_name classification_name,
element_reporting_name element_name,
processing_priority processing_priority,
SUM(payment) payment,
hours hours,
rate hourly_rate
from pay_au_asg_element_payments_v p
WHERE ( classification_name = 'Employer Superannuation Contributions' ) AND
( :assignment_action_id = assignment_action_id) group by
assignment_action_id , classification_name , processing_priority ,
element_reporting_name , hours , rate ORDER BY 1 ASC,3 ASC ,
classification_sort , processing_priority

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- -------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 157.88 158.20 0 4999 0 9
------- ------ -------- ---------- ------- ---------- ---------- -------
total 6 157.88 158.20 0 4999 0 9

 

The issue seems to be due to changes in the view definition.

EXPECTED BEHAVIOR
Expect no performance degradation post patch.

BUSINESS IMPACT
Due to this issue, both custom report and seeded Pay Advice is performing poorly.

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