Blocking sessions occurred in FOR UPDATE statements (BRM system)

User_OPD8R

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

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

EdStevens

I think you meant to ask this in the SQL forum, as this has nothing to do with Recovery Manager. See https://community.oracle.com/tech/developers/categories/sql_and_pl_sql