Not very technical end-user come and complain that the record she requires is lock for a fair amount of time. Could you give me a systematic steps that you as a DBA expert will follow to find the root of the problem ( for example do you start to look at V$lock, V$session, OEM, I don't know what else ... )
or if you have a all-inclusive script for similar case. I am looking for fastest and systematic way.
Normally when you set the trace to the error and the trace level to 10 the max trace, it would spit out the entire stack
One other query that you can use is
v$sqlarea b ,
WHERE process in ( select distinct process from v$locked_object )
and sql_address = b.address AND USER_OBJECTS.OBJECT_ID IN ( SELECT OBJECT_ID FROM v$locked_object);
to find the object causing the dead lock.
This link under metalink would get your the scripts to monitor the locking