Brm Upgrade: Billing Performance Issue On Parent A/C If Child Has 0 As Last_bill_obj_id0 (Doc ID 1389404.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 to 7.4.0.0.0 [Release 7.4.0]
Information in this document applies to any platform.

Symptoms


On : 7.4.0.0.0 version, Upgrade

If there is a billing account existing and a child account is added to this account, after adding this child, the first bill that gets created takes long time as the querry in item_t goes for a full table scan because it searches for bill_obj_id0 =0.

The following SQL seems to be the cause of the full table scan.
SELECT decode(poid_id0 - ar_billinfo_obj_id0, 0, bill_obj_id0, last_bill_obj_id0)
FROM pinpap.BILLINFO_T
WHERE AR_BILLINFO_OBJ_ID0 = <value>

In this case, there are a parent and child account. The child account's last_bill_obj_id0 =0, as it's newly created account. Because of this query, the entire bill run gets to a grinding halt..If there are many accounts like this and all of them get hit at the same time, the entire DB gets to a hang state as there are lot of buffers that is getting moved across...


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a parent and child account hierarchy
2. Run the billing for child
3. Run the billing for parent and you will see this update querry on item_t which has the above mentioned decode statement.

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