Slow Performance While Searching With ID In Customer Center Tool (Doc ID 1382615.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Aug-2013***

Symptoms


In Customer Center search form, Giving an id and clicking search returns results after a minute

The cm.log with logleve 3 is showing that the following search is taking time.

0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_RESULTS ARRAY [10] allocated 20, used 4
1 PIN_FLD_ACCOUNT_NO STR [0] NULL str ptr
1 PIN_FLD_POID POID [0] NULL poid pointer
1 PIN_FLD_BRAND_OBJ POID [0] NULL poid pointer
1 PIN_FLD_NAMEINFO ARRAY [*] allocated 20, used 4
2 PIN_FLD_LAST_NAME STR [0] NULL str ptr
2 PIN_FLD_FIRST_NAME STR [0] NULL str ptr
2 PIN_FLD_STATE STR [0] NULL str ptr
2 PIN_FLD_CITY STR [0] NULL str ptr
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1 PIN_FLD_LOGIN STR [0] "mbalusan%"
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [3] allocated 20, used 1
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer
0 PIN_FLD_TEMPLATE STR [0] "select X from /account 1, /service 2 where lower( 2.F1 ) like lower( V1 ) and 1.F2 = 2.F3 "
0 PIN_FLD_FLAGS INT [0] 256

This query in db shown as below

select distinct account_t.account_no, account_t.poid_DB,
account_t.poid_ID0, account_t.poid_TYPE, account_t.poid_REV,
account_t.brand_obj_DB, account_t.brand_obj_ID0,
account_t.brand_obj_TYPE, account_t.brand_obj_REV from account_t
,service_t where lower( service_t.login ) like lower( :1 ) and
account_t.poid_ID0 = service_t.account_obj_ID0

below is the explain plan from db for this query


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1787798670

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 41653 |  3823K|       | 54683   (1)| 00:09:35 |
|   1 |  HASH UNIQUE        |           | 41653 |  3823K|  4904K| 54683   (1)| 00:09:35 |
|*  2 |   HASH JOIN         |           | 41653 |  3823K|       | 51970   (1)| 00:09:06 |
|*  3 |    TABLE ACCESS FULL| SERVICE_T | 41653 |  1342K|       | 36160   (1)| 00:06:20 |
|   4 |    TABLE ACCESS FULL| ACCOUNT_T |  1379K|    80M|       | 15805   (1)| 00:02:46 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("ACCOUNT_T"."POID_ID0"="SERVICE_T"."ACCOUNT_OBJ_ID0")
  3 - filter(LOWER("SERVICE_T"."LOGIN") LIKE 'mahender')

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