Function 'fm_subscription_get_actg_dates' Hits Audit Tables During Deal Purchase Causing Performance Issue (Doc ID 1324288.1)

Last updated on SEPTEMBER 02, 2013

Applies to:

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

Goal

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:

select distinct actg_last_t, actg_next_t, actg_future_t, actg_cycle_dom,
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
SORT UNIQUE
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.

Solution

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