Locks on Balance_Group and Reservation_List Objects in a Production Environment (Doc ID 782706.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 to 7.3.0.0.1 [Release 7.3.0]
Information in this document applies to any platform.
***Checked for relevance on 18-May-2012***
***Checked for relevance on 13-May-2014***

Goal

Question #1

On the production environment, in the billing system, there are many database locks between two or more transactions running the following queries:

1 - select poid_DB, poid_ID0, poid_TYPE, poid_REV from reservation_list_t where reservation_list_t.poid_id0 = :1 for update of reservation_list_t.poid_id0  
2 - select poid_DB, poid_ID0, poid_TYPE, poid_REV, created_t, mod_t, read_access, write_access, account_obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, bal_grp_obj_DB, bal_grp_obj_ID0, bal_grp_obj_TYPE, bal_grp_obj_REV from reservation_list_t where poid_id0 = :1 for update of reservation_list_t.poid_id0

What are those locks caused from?

Question #2

Documentation makes a difference between Telco and non-Telco services, about the use of reservation_list vs balance_groups, and it's not the use of TIMOS that makes the difference there (See: "Reserving resources for concurrent network sessions"). When NOT using TIMOS, but using Telco services, is that suggestion applicable as well?

Question #3

Should the opcode change be applied to stop_accounting as well, apart from authorize and re-authorize?

Question #4


How should the pending reservations going to be handled, in the transition from the existing to the new, suggested configuration? Is some sort of DB cleanup needed?

Question #5


In order to cleanup the reservation_t table, which is too large, the customer was thinking to remove all records with a status different from 0 (Reserved). Do you see any side effect on the system on doing that? Would you use a direct 'DELETE' on the database table, or would you suggest an opcode? Which one?

Question #6

It was noticed that the lock-induced delays are amplified by a high number of BAL_GRP_SUB_BALS_T rows, for a given account's /bg object, all having VALID_TO = 0 and CURRENT_BAL = 0. These looks like "empty" rows that may be removed from the system.

In this case, it's possible that we're hitting BUGID 6820625 (FOR EVERY FREE CALL, THERE IS A NEW SUB BALANCE ELEMENT CREATED).

The above bug was fixed with <patch 7330312>, but that need the pre-requisite rollup <patch 6787882> installed. As a workaround, before <patch 7330312> is deployed to production, an SQL script to remove the unexpected rows from the DB is:

DELETE FROM bal_grp_sub_bals_t
WHERE (obj_id0, rec_id) IN (
SELECT obj_id0, rec_id
FROM (SELECT obj_id0, rec_id, rec_id2,
ROW_NUMBER () OVER
(PARTITION BY obj_id0, rec_id2
ORDER BY rec_id) counter
FROM bal_grp_sub_bals_t
WHERE current_bal = 0 AND valid_to = 0)
WHERE counter > 1)
-- and obj_id0 = 2877794971

Can this script be used as a safe workaround to the bug?

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