Severe Performance Issue with Aging - 7 Buckets - By Amount Report (Doc ID 1616953.1)

Last updated on AUGUST 24, 2016

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Reports Issues

The Aging - 7 Buckets - By Amount report has taken close to 15 hrs in R12 upgraded production environment.
In the 11i Production environment the program used to take around 15-20 mins to complete.

The following is the statement that seems to be affecting the performance:

select ps . org_id cust_org_id , substrb ( party . party_name , 1 , 50 )
short_cust_name , cust_acct . cust_account_id cust_id , cust_acct .
account_number cust_no , sum ( decode ( ps . class , 'PMT' , decode ( :
convert_flag , 'Y' , - app . acctd_amount_applied_from , - app .
amount_applied ) , decode ( : convert_flag , 'Y' , ps .
...
...
EXISTS ( SELECT /*+ push_subq */ 1 FROM hr_organization_information
org_info WHERE app.org_id = org_info.organization_id AND
org_info.org_information_context = 'Operating Unit Information' AND
org_info.org_information3 = TO_CHAR ( 85 ) ) ) ) AND ( :sel_opt_org_id =
ps.org_id) group by ps.org_id , party.party_name ,
cust_acct.cust_account_id , cust_acct.account_number , nvl ( c.SEGMENT1 ,
appcc.SEGMENT1 ) ORDER BY 18 ASC,1 ASC , total_cust_amt desc ,
short_cust_name , bal_segment_value , cust_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.23 0.39 0 0 0 0
Fetch 4 1078.66 46289.58 11007654 11149230 0 59
------- ------ -------- ---------- ---------- ---------- ----------
total 6 1078.89 46289.98 11007654 11149230 0 59

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