Balance Group Table Contention Resulting in Restart Of The System (Doc ID 2219401.1)

Last updated on AUGUST 04, 2021

Applies to:

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
        
        
        

Cause

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