My Oracle Support Banner

Customer Interface Performance Issue: Performance Issue With Customer Interface (RACUST) Updating HZ_LOCATIONS (Doc ID 567433.1)

Last updated on FEBRUARY 14, 2019

Applies to:

Oracle Receivables - Version 11.5.10.2 to 11.5.10.3 [Release 11.5.10]
Information in this document applies to any platform.
Executable:RACUST - Customer Interface


Symptoms

Customer interface is running very slow and not completing even after 12 hours.

Running a trace/tkprof on the program shows the following as the top sql:

UPDATE HZ_LOCATIONS RA SET ( RA . LAST_UPDATED_BY , RA . LAST_UPDATE_DATE ,
RA . LAST_UPDATE_LOGIN , RA . ADDRESS1 , RA . ADDRESS2 , RA . ADDRESS3 , RA
. ADDRESS4 , RA . CITY , RA . POSTAL_CODE , RA . STATE , RA . PROVINCE , RA
. COUNTY , RA . COUNTRY , RA . ATTRIBUTE_CATEGORY , RA . ATTRIBUTE1 , RA .
ATTRIBUTE2 , RA . ATTRIBUTE3 , RA . ATTRIBUTE4 , RA . ATTRIBUTE5 , RA .
ATTRIBUTE6 , RA . ATTRIBUTE7 , RA . ATTRIBUTE8 , RA . ATTRIBUTE9 , RA .
ATTRIBUTE10 , RA . ATTRIBUTE11 , RA . ATTRIBUTE12 , RA . ATTRIBUTE13 , RA .
ATTRIBUTE14 , RA . ATTRIBUTE15 , RA . ATTRIBUTE16 , RA . ATTRIBUTE17 , RA .
ATTRIBUTE18 , RA . ATTRIBUTE19 , RA . ATTRIBUTE20 , RA .
ADDRESS_LINES_PHONETIC , REQUEST_ID , PROGRAM_ID , PROGRAM_APPLICATION_ID ,
PROGRAM_UPDATE_DATE , LANGUAGE , DATE_VALIDATED , VALIDATION_STATUS_CODE ) =
( SELECT :B5 , SYSDATE , :B4 , I1 . ADDRESS1 , I1 . ADDRESS2 , I1 .
ADDRESS3 , I1 . ADDRESS4 , I1 . CITY , I1 . POSTAL_CODE , I1 . STATE , I1 .
PROVINCE , I1 . COUNTY , I1 . COUNTRY , I1 . ADDRESS_ATTRIBUTE_CATEGORY ,
I1 . ADDRESS_ATTRIBUTE1 , I1 . ADDRESS_ATTRIBUTE2 , I1 . ADDRESS_ATTRIBUTE3
, I1 . ADDRESS_ATTRIBUTE4 , I1 . ADDRESS_ATTRIBUTE5 , I1 .
ADDRESS_ATTRIBUTE6 , I1 . ADDRESS_ATTRIBUTE7 , I1 . ADDRESS_ATTRIBUTE8 , I1
. ADDRESS_ATTRIBUTE9 , I1 . ADDRESS_ATTRIBUTE10 , I1 . ADDRESS_ATTRIBUTE11 ,
I1 . ADDRESS_ATTRIBUTE12 , I1 . ADDRESS_ATTRIBUTE13 , I1 .
ADDRESS_ATTRIBUTE14 , I1 . ADDRESS_ATTRIBUTE15 , I1 . ADDRESS_ATTRIBUTE16 ,
I1 . ADDRESS_ATTRIBUTE17 , I1 . ADDRESS_ATTRIBUTE18 , I1 .
ADDRESS_ATTRIBUTE19 , I1 . ADDRESS_ATTRIBUTE20 , I1 .
ADDRESS_LINES_PHONETIC , :B3 , :B2 , :B1 , SYSDATE , HZ_CUSTOMER_INT .
GET_LANGUAGE_CODE ( I1 . LANGUAGE ) , NULL , NULL FROM
RA_CUSTOMERS_INTERFACE I1 , HZ_PARTY_SITES PS , HZ_CUST_ACCT_SITES CAS
WHERE
I1 . ROWID IN ( SELECT MIN ( I2 . ROWID ) FROM RA_CUSTOMERS_INTERFACE I2
WHERE I2 . ORIG_SYSTEM_ADDRESS_REF = I1 . ORIG_SYSTEM_ADDRESS_REF AND I2 .
REQUEST_ID = :B3 AND I2 . INTERFACE_STATUS IS NULL AND I2 .
INSERT_UPDATE_FLAG = 'U' ) AND CAS . ORIG_SYSTEM_REFERENCE = I1 .
ORIG_SYSTEM_ADDRESS_REF AND CAS . PARTY_SITE_ID = PS . PARTY_SITE_ID AND PS
. LOCATION_ID = RA . LOCATION_ID AND I1 . REQUEST_ID = :B3 AND I1 .
INTERFACE_STATUS IS NULL AND I1 . INSERT_UPDATE_FLAG = 'U' AND RA .
DATE_VALIDATED IS NULL AND RA . VALIDATION_STATUS_CODE IS NULL ) WHERE NOT
EXISTS ( SELECT 'X' FROM RA_CUSTOMERS_INTERFACE I1 , HZ_PARTY_SITES PS1 ,
HZ_CUST_ACCT_SITES CAS1 WHERE I1 . ORIG_SYSTEM_ADDRESS_REF = CAS1 .
ORIG_SYSTEM_REFERENCE AND CAS1 . PARTY_SITE_ID = PS1 . PARTY_SITE_ID AND
PS1 . LOCATION_ID = RA . LOCATION_ID AND I1 . REQUEST_ID = :B3 AND I1 .
INTERFACE_STATUS IS NOT NULL AND I1 . INSERT_UPDATE_FLAG = 'U' ) AND RA .
LOCATION_ID IN ( SELECT DISTINCT RA1 . LOCATION_ID FROM HZ_LOCATIONS RA1 ,
RA_CUSTOMERS_INTERFACE I1 , HZ_PARTY_SITES PS2 , HZ_CUST_ACCT_SITES CAS2
WHERE CAS2 . ORIG_SYSTEM_REFERENCE = I1 . ORIG_SYSTEM_ADDRESS_REF AND CAS2
. PARTY_SITE_ID = PS2 . PARTY_SITE_ID AND PS2 . LOCATION_ID = RA1 .
LOCATION_ID AND I1 . REQUEST_ID = :B3 AND I1 . INTERFACE_STATUS IS NULL AND
I1 . INSERT_UPDATE_FLAG = 'U' )


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1    0.00        0.00          0          0          0          0
Execute      1 4567.40     4731.79       6837  561191760       7483          0
Fetch        0    0.00        0.00          0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2 4567.40     4731.79 6837        561191760       7483          0

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.