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
-------------------------------------------------------------------------------