My Oracle Support Banner

FAQ About Balance Group Table Contention Issues (Doc ID 2219410.1)

Last updated on JULY 08, 2022

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 and later
Information in this document applies to any platform.

Purpose

During investigation of a problem scenario on a customer production system, wherein the balance group table (bal_grp_t) experienced row lock contentions leading to Billing and Revenue Management (BRM) application to become unresponsive, the below questions were raised and answers are captured in this article.

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Purpose
Questions and Answers
 From the locking sessions, a database administrator (DBA) can extract the details like the client server name, sqlid and dm_oracle name of the session which was causing the lock.
Is there a method to trace back the complete transaction of the session coming from BRM? How to possibly determine what BRM operation caused this lock base on below information?
 As per the user, when the locks started appearing, client side operating system (OS) process Connection Manager (CM) was killed on BRM application server and still there was a lock on the database. That is, when the user killed the CM, they noticed that not all database sessions were closed, and they traced these to a Data Manager (DM) which was still running. The user expected that when they kill the CM, the DMs should close the sessions to the database as well. Is that correct? Would there be open sessions to database expected when the CM is killed? After killing the suspect DM, the sessions on the database were all closed.
 During one such occurrence of the bal_grp_t contention it was noticed that one of the top blocker session was running current SQL, for example, id 2y12szx11gx61 which was causing delay in select query because of Full Table Scan on BILLINFO_T table because the column is not indexed (where billinfo_t.payinfo_obj_ID0 = :1 --- This column required indexing). In this context, what is the relation between BAL_GRP_T and BILLINFO_T table. How this is leading to contention on bal_grp_t?
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.