Loss Of Rating Performance Due To "DB:PURCHASEDPRODUCTUPDATE" (Doc ID 1054353.1)

Last updated on FEBRUARY 22, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 to 7.3.1.0.0 [Release 7.3.0 to 7.3.1]
Information in this document applies to any platform.
***Checked for relevance on 04-Sept-2013***

Symptoms

In the Production system, the rating performance is decreasing significantly.
Using instrumentation to collect module performance statistics, the slowest module was found to be FCT_Account.
To be more precise it is due to: DB:PurchasedProductUpdate.

IFW is calling this function too many times even if it is not necessary (see the scenario described below).
The called sql statement is:

SELECT tag_au_purchased_product_t.au_parent_obj_db, tag_au_purchased_product_t.au_parent_obj_id0, tag_au_purchased_product_t.au_parent_obj_type, tag_au_purchased_product_t.effective_t, tag_au_purchased_product_t.usage_start_t, tag_au_purchased_product_t.usage_end_t, tag_au_purchased_product_t.purchase_start_t, tag_au_purchased_product_t.quantity, tag_au_purchased_product_t.usage_start_details, tag_au_purchased_product_t.usage_end_details, tag_au_purchased_product_t.overridden_obj_db, tag_au_purchased_product_t.overridden_obj_id0, tag_au_purchased_product_t.status
FROM au_purchased_product_t tag_au_purchased_product_t
WHERE tag_au_purchased_product_t.au_parent_obj_id0 = :ph10 ORDER BY tag_au_purchased_product_t.effective_t DESC

The scenario is
1st Month: Activate a contract with Deal_A (on service SMS)
10th Month: Cancel Deal_A and purchase Deal_B (on service SMS).
12th Month: Rate some usage. Every SMS (with timestamp greater that 10th Month) is triggering the DB:PurchasedProductUpdate.

Perhaps IFW is looping for every purchased_product and checking the validity of each one. For Deal_A, the current revision (loaded into IFW memory) is recognized as invalid, then IFW is trying to retrieve another revision from database; it is wrong because the current revision has usage_end_t less than the charging start timestamp of CDR.

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