Last updated on JUNE 08, 2016
Applies to:Oracle Receivables - Version 126.96.36.199 and later
Information in this document applies to any platform.
***Checked for relevance on 09-Apr-2013***
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:
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:
ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL (GL_DATE, ACCOUNT_CLASS, ACCOUNT_SET_FLAG,
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)
SQL> exec fnd_stats.gather_index_stats('AR','RA_CUST_TRX_LINE_GL_DIST_TST', &
Customer does not want to add another index if there is another way around this issue.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms