Locking

Abhay ,

could you please explain in much more detail ..

select sid, serial#, sql_hash_value from v$session where sid in (select holding_session from dba_waiters)

This does not return any sid when its locked ..

Thanks

Quote:

---

Originally posted by Rohit
In one of our boxes a particular Update statement get locked up frequently and we usually kill the session. How do we go about resolving this.

Thanks

---

When such locking or hanging kinda situation happens, just get the output of

select * from dba_waiters
/

and

select sid,event, p1,p2,p3 from v$session_wait
where event not in ('SQL*Net message from client','rdbms ipc message',
'SQL*Net message to client', 'pmon timer', 'smon timer')
/

1st one gives whether anyother session is locking the table. 2nd query gives as for wait event it is hanging. U can work it out. And check out the explain plan for the update query. It cant be just only abt locks. There could be something else too.