RACUST - Customer Interface Running Slow Updating HZ_LOCATIONS and HZ_PARTY_SITES (Doc ID 1089964.1)

Last updated on JULY 28, 2016

Applies to:

Oracle Trading Community - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

RACUST - Customer interface is having performance issues updating tables HZ_LOCATIONS and HZ_PARTY_SITES.

You are on the following patch levels:

11i.HZ.M
11i.AR.N

Per log file, problem is happening inside racuda module. Patch 6912100, that delivers racuda.lpc is 115.23.115103.3, did not solve the performance problem.

These are the top queries in tkprof:

update HZ_PARTY_SITES ps  set (last_updated_by,last_update_date,
 last_update_login,request_id,program_id,program_application_id,
 program_update_date,attribute_category,attribute1,attribute2,attribute3,
 attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,
 attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
 attribute16,attribute17,attribute18,attribute19,attribute20,language)=
 (select :b0 ,sysdate  ,:b0 ,:b2 ,:b3 ,:b4 ,sysdate  ,
 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 ,
 HZ_CUSTOMER_INT.get_language_code(i1.language)  from ra_customers_interface
 i1 ,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=:b2) and i2.interface_status is null ) and
 i2.insert_update_flag='U')) and cas.orig_system_reference=
 i1.orig_system_address_ref) and ps.party_site_id=cas.party_site_id) and
 i1.request_id=:b2) and i1.interface_status is null ) and
 i1.insert_update_flag='U') and rownum =1)) where ( not exists (select 'X'  
 from ra_customers_interface i1 ,hz_cust_acct_sites cas1 where
 ((((i1.orig_system_address_ref=cas1.orig_system_reference and
 cas1.party_site_id=ps.party_site_id) and i1.request_id=:b2) and
 i1.interface_status is  not null ) and i1.insert_update_flag='U')) and
 ps.party_site_id in (select distinct ra1.party_site_id  from hz_party_sites
 ra1 ,ra_customers_interface i1 ,hz_cust_acct_sites cas2 where
 ((((cas2.orig_system_reference=i1.orig_system_address_ref and
 cas2.party_site_id=ra1.party_site_id) and i1.request_id=:b2) and
 i1.interface_status is null ) and i1.insert_update_flag='U')))



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 AND ROWNUM =
1 ) 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' )

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