Performance Issue: AR Close Period Form (ARXSUMPS) has Poor Performance

(Doc ID 1089171.1)

Last updated on NOVEMBER 09, 2017

Applies to:

Oracle Receivables - Version 11.5.10.2 and later
Information in this document applies to any platform.
***Checked for relevance on 09-Apr-2013***


Symptoms

In Production on 11i.AR.O, after applying <Patch 5444723> which provides ARXSUMPS.fmb 115.16.15104.6 customer is still seeing a performance issue with the following sql statement:

SELECT COUNT(1)
FROM
DUAL WHERE EXISTS (SELECT /*+ ORDERED USE_NL(sys_org gld ct) */'x' FROM
(SELECT ORG_ID FROM AR_SYSTEM_PARAMETERS_ALL WHERE SET_OF_BOOKS_ID = :b1
AND ACCOUNTING_METHOD != 'CASH' ) SYS_ORG,RA_CUST_TRX_LINE_GL_DIST_ALL GLD,
RA_CUSTOMER_TRX_ALL CT WHERE GLD.ACCOUNT_CLASS = 'REC' AND
GLD.LATEST_REC_FLAG = 'Y' AND GLD.ACCOUNT_SET_FLAG = 'Y' AND GLD.GL_DATE
BETWEEN :b2 AND :b3 AND CT.CUSTOMER_TRX_ID = GLD.CUSTOMER_TRX_ID AND
CT.COMPLETE_FLAG = 'Y' AND :b4 = 'ACCRUAL' AND GLD.ORG_ID =
SYS_ORG.ORG_ID AND CT.ORG_ID = GLD.ORG_ID AND ROWNUM = 1 )



Customer was offered solution in <Note 783867.1>, ARXSUMPS - Performance Problem - Can Not Close AR Period.
The Solution is to create a custom index and gather stats as follows:

SQL> CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_TST
ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL (GL_DATE, ACCOUNT_CLASS, ACCOUNT_SET_FLAG,
LATEST_REC_FLAG, ORG_ID)
PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1073741824 NEXT 16777216 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE &YourTablespace;

SQL> exec fnd_stats.gather_index_stats('AR','RA_CUST_TRX_LINE_GL_DIST_TST', &
Percent);



Customer does not want to add another index if there is another way around this issue.

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