Customer Form Performance Issue: Customer Standard Form has Poor Performance on RDBMS 9.2.0.8 (Doc ID 580399.1)

Last updated on FEBRUARY 18, 2014

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.
*** Checked for Relevance 18-Feb-2014 ***
Form:ARXCUDCI.FMB - Customers


Symptoms

Performance is very slow in the Customer Standard Form when searching with any criteria (customer name, customer number, etc).

Top queries causing this performance issue are:

SELECT LOCATION,BILL_TO_LOCATION,PRIMARY_FLAG,STATUS,TAX_CODE,TAX_REFERENCE,
SIC_CODE,PAYMENT_TERM_NAME,CONTACT_NAME,PRIMARY_SALESREP_NAME,
FINCHRG_RECEIVABLES_TRX_NAME,INVENTORY_LOCATION_CODE,
INVENTORY_ORGANIZATION_NAME,TAX_CLASSIFICATION,TAX_HEADER_LEVEL_FLAG,
TAX_ROUNDING_RULE,ORDER_TYPE_NAME,SHIP_PARTIAL,PRICE_LIST_NAME,
GSA_INDICATOR,WAREHOUSE_NAME,ITEM_CROSS_REF_PREF,SHIP_VIA,
DATE_TYPE_PREFERENCE,SHIP_SETS_INCLUDE_LINES_FLAG,
ARRIVALSETS_INCLUDE_LINES_FLAG,SCHED_DATE_PUSH_FLAG,
DATES_NEGATIVE_TOLERANCE,OVER_SHIPMENT_TOLERANCE,DATES_POSITIVE_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,INVOICE_QUANTITY_RULE,OVER_RETURN_TOLERANCE,
UNDER_RETURN_TOLERANCE,ROW_ID,SITE_USE_ID,INVENTORY_ORGANIZATION_ID,
INVENTORY_LOCATION_ID,ADDRESS_ID,CONTACT_ID,BILL_TO_SITE_USE_ID,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,SITE_USE_CODE,
LAST_UPDATE_LOGIN,PAYMENT_TERM_ID,FOB_POINT,ORDER_TYPE_ID,PRICE_LIST_ID,
FREIGHT_TERM,WAREHOUSE_ID,TERRITORY_ID,DEMAND_CLASS_CODE,
PRIMARY_SALESREP_ID,FINCHRG_RECEIVABLES_TRX_ID,GL_ID_REC,GL_ID_REV,
GL_ID_TAX,GL_ID_FREIGHT,GL_ID_CLEARING,GL_ID_UNBILLED,GL_ID_UNEARNED,
GL_ID_UNPAID_REC,GL_ID_REMITTANCE,GL_ID_FACTOR,ATTRIBUTE_CATEGORY,
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,
ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,
ATTRIBUTE25,GLOBAL_ATTRIBUTE_CATEGORY,GLOBAL_ATTRIBUTE20,GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE16,GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE12,GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE8,GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE4,GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE1,OBJECT_VERSION
FROM
HZ_SITE_USES_V WHERE (ADDRESS_ID=:1)

 

SELECT ACCOUNT_NAME,NUM_OF_EMPLOYEES,YEAR_ESTABLISHED,FISCAL_YEAREND_MONTH,
ANALYSIS_FY,POTENTIAL_REVENUE_CURR_FY,POTENTIAL_REVENUE_NEXT_FY,
THIRD_PARTY_FLAG,REFERENCE_USE_FLAG,COMPETITOR_FLAG,MISSION_STATEMENT,
PARTY_TYPE,CUSTOMER_NAME,PERSON_PRE_NAME_ADJUNCT,PERSON_FIRST_NAME,
PERSON_MIDDLE_NAME,PERSON_LAST_NAME,CUSTOMER_NUMBER,PARTY_NUMBER,
PERSON_SUFFIX,CUSTOMER_NAME_PHONETIC,TAXPAYER_ID,TAX_REFERENCE,STATUS,
SHIP_SETS_INCLUDE_LINES_FLAG,ARRIVALSETS_INCLUDE_LINES_FLAG,
SCHED_DATE_PUSH_FLAG,CUSTOMER_PROSPECT_CODE,ORIG_SYSTEM_REFERENCE,
PROFILE_CLASS_NAME,CUSTOMER_TYPE_MEANING,OVER_SHIPMENT_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,OVER_RETURN_TOLERANCE,UNDER_RETURN_TOLERANCE,
CUSTOMER_CLASS_MEANING,CUSTOMER_CATEGORY_MEANING,TAX_CODE,GSA_INDICATOR,
SIC_CODE_TYPE,SIC_CODE,PRIMARY_SALESREP_NAME,SHIP_PARTIAL,SHIP_VIA,
FOB_POINT_MEANING,ORDER_TYPE_NAME,ITEM_CROSS_REF_PREF,DATE_TYPE_PREFERENCE,
DATES_NEGATIVE_TOLERANCE,DATES_POSITIVE_TOLERANCE,INVOICE_QUANTITY_RULE,
PRICE_LIST_NAME,FREIGHT_TERM_MEANING,WAREHOUSE_NAME,SALES_CHANNEL_MEANING,
duns_number_c,TAX_HEADER_LEVEL_FLAG,TAX_ROUNDING_RULE,ROW_ID,CUSTOMER_ID,
PARTY_ID,ORGANIZATION_PROFILE_ID,PERSON_PROFILE_ID,CUSTOMER_KEY,
CUSTOMER_TYPE,CUSTOMER_CLASS_CODE,CUSTOMER_CATEGORY_CODE,
PERSON_FIRST_NAME_PHONETIC,PERSON_NAME_PHONETIC,PERSON_LAST_NAME_PHONETIC,
PRIMARY_SALESREP_ID,ORDER_TYPE_ID,SALES_CHANNEL_CODE,FOB_POINT,WAREHOUSE_ID,
FREIGHT_TERM,PRICE_LIST_ID,CUSTOMER_PROFILE_CLASS_ID,ATTRIBUTE_CATEGORY,
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,
ATTRIBUTE19,ATTRIBUTE20,CREATION_DATE,LAST_UPDATED_BY,CREATED_BY,
LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,CONT_RES_LAST_UPDATE_DATE,
CONT_POINT_LAST_UPDATE_DATE,ORG_PROF_LAST_UPDATE_DATE,
PER_PROF_LAST_UPDATE_DATE,PARTY_LAST_UPDATE_DATE,GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,
GLOBAL_ATTRIBUTE_CATEGORY,OBJECT_VERSION,ORG_PROF_OBJECT_VERSION,
PER_PROF_OBJECT_VERSION,PARTY_OBJECT_VERSION
FROM
AR_CUSTOMERS_V WHERE customer_prospect_code = 'CUSTOMER' AND -1=-1 and
(CUSTOMER_ID=&custid) order by PARTY_TYPE, customer_name;

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