Performance of Customer Search When Searching on the First Letter of the Name and a wildcard (Doc ID 559733.1)

Last updated on AUGUST 18, 2016

Applies to:

Oracle iReceivables - Version: 11.5.10.2 to 11.5.10.3 - Release: 11.5.10 to 11.5.10
Information in this document applies to any platform.
***Checked for relevance on 17-MAR-2011***

Symptoms

Searching for a Customer Name using the first letter and a percent will cause performance issues and timeouts.

The top two SQLS in a TKPROF will look like this:

 INSERT INTO AR_CUST_SEARCH_GT ( CUSTOMER_ID, ADDRESS_ID, BILL_TO_SITE_USE_ID,
DETAILS_LEVEL, CUSTOMER_NUMBER, CUSTOMER_NAME, CONTACT_NAME, CONTACT_PHONE,
SITE_USES, CONCATENATED_ADDRESS, LOCATION )
VALUES
( :B1 , :B2 , DECODE(:B3 ,-1,NULL, :B3 ), :B4 , :B5 , :B6 , :B7 , :B8 , :B9 ,
DECODE(:B2 , -1, :B12 , SUBSTRB(:B10 ,1,255)), :B11 )



call    count     cpu     elapsed     disk      query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       1      0.00       0.00       0          0          0           0
Execute 1125451  790.38     853.31       0          1      2250908         1
Fetch       0      0.00       0.00       0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total  1125452   790.38     853.31       0          1      2250908         1

********************************************************************************

BEGIN arw_search_customers.ari_search(i_keyword => :1 , i_name_num
=> :2 , x_status => :3 , x_msg_count => :4 ,
x_msg_data => :5 ); END;


call     count    cpu     elapsed     disk      query      current     rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       1      0.00       0.00        0         0           0          0
Execute     1    106.15     115.85        0         0      2250900         0
Fetch       0      0.00       0.00        0         0           0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       2    106.15     115.86        0         0      2250900         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