Performance Issue- Bad Delete Statement on AR_IREC_USER_ACCT_SITES_ALL (Doc ID 2268904.1)

Last updated on MAY 22, 2017

Applies to:

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

Symptoms

In Release 11i, the following file was fixed and the delete statement was modified for performance reasons -

ARWCUSRB.pls 115.44.115102.2

DELETE FROM AR_IREC_USER_ACCT_SITES_ALL A
WHERE A.SESSION_ID = p_session_Id AND
A.USER_ID = p_user_id AND
A.ROWID > ( SELECT MIN(ROWID)
FROM AR_IREC_USER_ACCT_SITES_ALL B
WHERE A.ORG_ID = B.ORG_ID AND
A.SESSION_ID=B.SESSION_ID AND
A.USER_ID=B.USER_ID AND
A.CUSTOMER_ID=B.CUSTOMER_ID AND
A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID AND
A.CREATION_DATE=B.CREATION_DATE );

This was bug - <Bug 8592034> PERF: HUGE BUFFER GETS/CONTENTION BAD DELETE STATEMENT ON AR_IREC_USER_ACCT_SITE

In Release 12.1.3, the performance issue has returned and the delete statement is not the same

ARWCUSRB.pls - 120.14.12010000.18

DELETE FROM ar_irec_user_acct_sites_all A
WHERE ROWID > (SELECT min(rowid)
FROM ar_irec_user_acct_sites_all B
WHERE A.org_id = B.org_id
AND A.SESSION_ID=B.SESSION_ID
AND A.USER_ID=B.USER_ID
AND A.CUSTOMER_ID=B.CUSTOMER_ID
AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
AND A.CREATION_DATE=B.CREATION_DATE
);

 

It appears the change in Release 11i was not carried forward to Release 12.

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