Last updated on SEPTEMBER 02, 2013
Applies to:Oracle Communications Billing and Revenue Management - Version 126.96.36.199.0 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Sep-2013***
Function fm_subscription_get_actg_dates (called during deal_purchase) causes performance problems due to full scan on au_billinfo_t.
In a customer setup, it has been observed that not all times but some times, during the deal purchase opcode, this function fm_subscription_get_actg_dates is called, which results in a full table scan on au_billinfo_t.
From the log (cm pinlog), which involves op_subscription_deal_purchase, we saw that there is a call to "op_subscription_get_actg_dates", and this internal opcode is in turn firing a search for audit billinfo. Now this search on au_billinfo_t took around 10 seconds, because of a full table scan.
The SQL query and the explain plan is shown below:
created_t, poid_DB, poid_ID0, poid_TYPE, poid_REV from au_billinfo_t where
au_billinfo_t.au_parent_obj_ID0 = :1 order by au_billinfo_t.created_t desc
explain plan (full table scan):
SELECT STATEMENT ALL_ROWS
Cost: 93,446 Bytes: 120 Cardinality: 2
Cost: 93,445 Bytes: 120 Cardinality: 2
TABLE ACCESS FULL TABLE PIN.AU_BILLINFO_T
Cost: 93,444 Bytes: 120 Cardinality: 2
How is it possible to avoid the call to the function (get_actg_dates) itself? Or please explain what are the conditions that lead to invoking this function, so that one can take appropriate measure to avoid the full table scan.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms