Locks on Balance_Group and Reservation_List Objects in a Production Environment
Last updated on SEPTEMBER 19, 2016
Applies to:Oracle Communications Billing and Revenue Management - Version 126.96.36.199.0 to 188.8.131.52.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***
On the production environment, in the billing system, there are many database locks between two or more transactions running the following queries:
What are those locks caused from?
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?
Should the opcode change be applied to stop_accounting as well, apart from authorize and re-authorize?
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?
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?
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:
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
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?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms