My Oracle Support Banner

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

Last updated on FEBRUARY 14, 2019

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.

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.