Search Customer by Account Number very slow at R12.1.3
                                                 Last update (2012-10-24 09:30:56)
                                                                                                                   Date added (2012-10-24 09:18:26)

Summary
After upgrade to R12.1.3 when go to Receivables Manager -> Customers -> Customers
and search one customer with Account Number it is very slow.

1. First create the index

create index ar.HZ_CUST_ACCT_SITES_N99 on ar.HZ_CUST_ACCT_SITES_ALL(cust_account_id) 
tablespace APPS_TS_TX_IDX;

2. Second run a tuning task as SYSTEM user:

my_task_name VARCHAR2(30);
my_sqltext CLOB; 

BEGIN 
  my_sqltext := 'SELECT * ' || 
  'FROM (SELECT p.party_id, p.party_name, p.person_first_name,' || 
               'p.person_last_name, p.party_type,' || 
               'partytype.meaning type_lookup, p.party_number, p.tax_reference,' || 
               'p.jgzz_fiscal_code taxpayer_id, p.duns_number_c duns_number,' || 
               'p.known_as, p.known_as2, p.known_as3, p.known_as4, p.known_as5,' ||
               'p.organization_name_phonetic, p.person_first_name_phonetic,' ||
               'p.person_last_name_phonetic, ps.location_id,' ||
               'terr.territory_short_name country,' ||
               'p.primary_phone_contact_pt_id, p.primary_phone_country_code,' ||
               'p.primary_phone_area_code, p.primary_phone_number,' ||
               'p.primary_phone_line_type, p.primary_phone_extension,' ||
               'p.email_address email, p.url primary_url,' ||
               'certification.meaning certification_level_meaning,' ||
               'registrystatus.meaning registry_meaning, p.status party_status' ||
          ' FROM hz_parties p,' ||
               'hz_party_sites ps,' ||
               'fnd_territories_vl terr,' ||
               'fnd_lookup_values partytype,' ||
               'fnd_lookup_values certification,' ||
               'fnd_lookup_values registrystatus' ||
         ' WHERE p.party_type IN (' || '''' || 'PERSON' || '''' || ', ' || '''' || 'ORGANIZATION' || '''' || ')' || 
           ' AND registrystatus.lookup_code = p.status' ||
           ' AND ps.party_id(+) = p.party_id' ||
           ' AND ps.identifying_address_flag(+) = ' || '''' || 'Y' || '''' || 
           ' AND terr.territory_code(+) = p.country' || 
           ' AND partytype.view_application_id = 222' ||
           ' AND partytype.lookup_type = ' || '''' || 'PARTY_TYPE' || '''' || 
           ' AND partytype.LANGUAGE = USERENV (' || '''' || 'LANG' || '''' ||')' || 
           ' AND partytype.lookup_code = p.party_type' || 
           ' AND certification.view_application_id(+) = 222' || 
           ' AND certification.lookup_type(+) = ' || '''' || 'HZ_PARTY_CERT_LEVEL' || '''' || 
           ' AND certification.LANGUAGE(+) = USERENV (' || '''' || 'LANG' || '''' || ')' || 
           ' AND certification.lookup_code(+) = p.certification_level' || 
           ' AND registrystatus.lookup_type = ' || '''' || 'HZ_CPUI_REGISTRY_STATUS' || '''' || 
           ' AND registrystatus.LANGUAGE = USERENV (' || '''' || 'LANG' || '''' || ')) qrslt' || 
 ' WHERE (    party_type = :1 '||
        ' AND party_status = ' || '''' || 'A' || '''' || 
        ' AND EXISTS (' ||
               'SELECT NULL' ||
                 ' FROM hz_customer_party_find_v hpfv' || 
                ' WHERE hpfv.org_per_number = qrslt.party_number' ||
                  ' AND hpfv.customer_number = :2)' ||
        ' AND EXISTS (' ||
               'SELECT NULL' ||
                 ' FROM hz_party_usg_assignments' ||
                ' WHERE party_id = qrslt.party_id' ||
                  ' AND party_usage_code = '|| '''' || 'CUSTOMER' || '''' ||  
                  ' AND status_flag = ' || ''''|| 'A' || '''' || 
                  ' AND TRUNC (SYSDATE) BETWEEN effective_start_date ' || 
                                          ' AND effective_end_date)' || 
       ')'; 
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, 
        user_name => 'APPS', 
        scope => 'COMPREHENSIVE', 
        time_limit => 60, 
        task_name => 'search_customer', 
        description => 'search_customer'); 
  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'search_customer'); 

END; 
/

You can check the results of the tuning taks with:

select DBMS_SQLTUNE.report_TUNING_TASK(task_name => 'search_customer') from dual;

In case you find the new explain plan efficient, then accept it with:

my_sqlprofile_name VARCHAR2(30); 
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'search_customer', name => 'search_customer'); 
END; 
/