Brm Upgrade: Billing Performance Issue On Parent A/C If Child Has 0 As Last_bill_obj_id0
Last updated on SEPTEMBER 26, 2016
Applies to:Oracle Communications Billing and Revenue Management - Version 18.104.22.168.0 to 22.214.171.124.0 [Release 7.4.0]
Information in this document applies to any platform.
On : 126.96.36.199.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)
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...
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.
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