My Oracle Support Banner

Aging Reports Performance Issue: Poor Performance Running Aging - 4 Buckets Report (ARXAGE) (Doc ID 1307559.1)

Last updated on FEBRUARY 12, 2018

Applies to:

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

Symptoms

Poor performance of ARXAGE module: Aging - 4 Buckets Report. In this particular instance it has been running for over 24+ hours.

The following costly SQL  is causing the issue.

SELECT SITE_USES.SITE_USE_ID , RTRIM(RPAD(LOC.STATE , 2 ) ) ,
RTRIM(RPAD(LOC.CITY , 25 ) ) , RTRIM(RPAD(LOC.CITY , 25 ) ) ||
DECODE(LOC.CITY , NULL , NULL , DECODE(LOC.STATE , NULL , NULL , ',
' ) ) || RTRIM(RPAD(LOC.STATE , 2 ) )
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE , HZ_PARTY_SITES PARTY_SITE , HZ_LOCATIONS
LOC , HZ_CUST_SITE_USES_ALL SITE_USES , HZ_CUST_ACCOUNTS CUST_ACCT WHERE
SITE_USES.SITE_USE_CODE = 'BILL_TO' AND ACCT_SITE.CUST_ACCOUNT_ID = :b1
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND
LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND SITE_USES.CUST_ACCT_SITE_ID =
ACCT_SITE.CUST_ACCT_SITE_ID AND NVL(SITE_USES.STATUS , 'A' ) = 'A'
AND CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID AND
NVL(ACCT_SITE.ORG_ID , -1 ) = DECODE(:b2 , '1000' , NVL(ACCT_SITE.ORG_ID ,
-1 ) , :b3 )



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.