Collections OOTB Search Taking Very Long Time Due To Absence Of Indexes. (Doc ID 1283632.1)

Last updated on SEPTEMBER 24, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.1.0.0 to 7.3.1.0.1 [Release 7.3.1]
Information in this document applies to any platform.
Checked for relevance on 21-Nov-2013

Symptoms


While pin_collections_process is run, an internal OOTB search is executed to select the overdue bills on the billinfo.

    fm_collections_select_overdue_bills search input flist
# number of field entries allocated 20, used 7
0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_FLAGS INT [0] 256
0 PIN_FLD_TEMPLATE STR [0] "select X from /bill where F1 = V1 and F2 != V2 and F3 < V3 "
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1     PIN_FLD_BILLINFO_OBJ POID [0] 0.0.0.1 /billinfo 160487331 8
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1     PIN_FLD_DUE DECIMAL [0] 0
0 PIN_FLD_ARGS ARRAY [3] allocated 20, used 1
1    PIN_FLD_DUE_T TSTAMP [0] (1263975722) Wed Jan 20 10:22:02 2010
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 4
1    PIN_FLD_POID POID [0] NULL poid pointer
1    PIN_FLD_DUE DECIMAL [0] NULL pin_decimal_t ptr
1    PIN_FLD_DUE_T TSTAMP [0] (0) <null>
1    PIN_FLD_CURRENCY INT [0] 0


This query is hit by every accounts, but since the query is not using any indexed columns, it is taking a very long time to fetch the results and hence drastically affecting the performance of the process.

The corresponding SQL statement from dm_oracle.pinlog is:-

select distinct poid_DB, poid_ID0, poid_TYPE, poid_REV, due, due_t, currency from bill_t where bill_t.billinfo_obj_ID0 = :1 and bill_t.due != :2 and bill_t.due_t < :3



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