Balance Group Table Contention Resulting in Restart Of The System
(Doc ID 2219401.1)
Last updated on FEBRUARY 13, 2020
Applies to:
Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
On : 7.5.0.10.0 version,
- Issue is that Balance Group is getting locked for prolonged period resulting in restart of the system
- In more detail, below is how the issue occurred :
- Responses to orders from upstream systems became slow, starting with adjustment and payment related activities and API calls.
- This had a knock on effect to GetBalance queries.
- GetBalance query created contention on bal_grp_t table in BRM database.
- The impact of contention was so high, that all BRM related services were interrupted for more than an hour.
- Database sessions were killed and also BRM services were restarted to restore system
- Below were additional details gathered by DBA (database administrator) from various reports :
- It is observed that there were 75 sessions in the database that became inactive mostly around 9:40 of the 17th October.
- These sessions had :
- last running query with sql_id czm8mtrhb4xfw
Detail #1 - last query run in 75 inactive sessions that where causing other sessions to be blocked (SQL_ID = czm8mtrhb4xfw)
select poid_DB, poid_ID0, poid_TYPE, poid_REV, schedule_obj_DB, schedule_obj_ID0, schedule_obj_TYPE, schedule_obj_REV, status, due_t, billinfo_obj_DB, billinfo_obj_ID0, billinfo_obj_TYPE, billinfo_obj_REV, config_action_obj_DB, config_action_obj_ID0, config_action_obj_TYPE, config_action_obj_REV, agent_obj_DB, agent_obj_ID0, agent_obj_TYPE, agent_obj_REV from collections_action_t where collections_action_t.poid_id0 = :1 - uncommitted transactions locking records in the table bal_grp_t, causing 130 sessions to be blocked on selects for update on the bal_grp_t, of which 106 sessions where stuck in the same query with sql_id 6d5tfynxyu3gr
Detail #2 - top blocked sql (in 106 sessions) (sql_id = 6d5tfynxyu3gr)
select poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t where bal_grp_t.account_obj_ID0 = :1 order by bal_grp_t.poid_id0 for update of bal_grp_t.poid_id0
- last running query with sql_id czm8mtrhb4xfw
Cause
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
Symptoms |
Cause |
Solution |
References |