Forum Stats

  • 3,815,638 Users
  • 2,259,064 Discussions
  • 7,893,194 Comments

Discussions

Blocking sessions occurred in FOR UPDATE statements (BRM system)

User_OPD8R
User_OPD8R Member Posts: 15 Green Ribbon

Hello

We have an Oracle 12 c environment used from Oracle BRM tool as a database.

We are having continuously repeated blocking sessions with this statement:

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

I know here that that FOR UPDATE clause , causes the table to be locked, and this cause ROW WAIT event in DB and the performance to be very slow because of the occupied resources from that statement.

I have tried to tune it but also with SQL Tunning advisor i got this message:


Schema Name: PINPRO01

SQL ID   : a01fzrrsn0mm8

SQL Text  : select  poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t  

       where bal_grp_t.poid_ID0 = :1  for update of bal_grp_t.poid_id0 

Bind Variables :

 1 - (VARCHAR2(32)):68514444958

There are no recommendations to improve the statement.

Is there any action i can perform on this SQL or in DB in overall so we can avoid such blocking sessions in the future?


Thanks


Dario



-------------------------------------------------------------------------------

Answers