Customer Interface Performance Issue: Performance Issue With Customer Interface (RACUST) Updating HZ_LOCATIONS

(Doc ID 567433.1)

Last updated on AUGUST 25, 2017

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

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