I’ve been using the view DBA_HIST_ACTIVE_SESS_HISTORY to investigate problems lately. I’ve found this useful for both performance problems and hangs. I’ve done some more complicated things, but I find that just dumping out every column of the view for a very narrow window of time can help explain what was happening in a way that other tools do not.
DBA_HIST_ACTIVE_SESS_HISTORY records the list of active sessions every 10 seconds. It is like looking at your active sessions in Toad except going back in time. We have been keeping six weeks of history on the AWR so I can go back to any ten second interval over the past six weeks and see what was running.
In the case of a hang or locking you can see the blocking sessions and what they are spending time on. Here is a recent example I used to debug a hang caused by sessions on two different instances – 1 and 12. I picked a 10 second interval that I knew was during the hang:
select
*
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time
between
to_date('18-APR-2012 10:40:00','DD-MON-YYYY HH24:MI:SS')
and
to_date('18-APR-2012 10:40:10','DD-MON-YYYY HH24:MI:SS')
and instance_number in (1,12)
order by sample_time,instance_number,SESSION_ID;
One key point if you have a hang or other issue is to run DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT before bouncing the database if you have to bounce. Otherwise you will lose all of the active session history since the last snapshot when the database bounces.
– Bobby
Hi Bobby,
I have a question. In my production, We have been encountering Inactive DB sessions from the application which are causing DB locks and blocking other DB sessions.
It is showing status : INACTIVE but it is blocker status. Because of that other sid are in wait condition and we stuck and frustated to kill the session. When we check the sql_id then it is a simple straight forward sql where no joins, only 1 table and order by id and fetch first 2 rows only.
How do we get the root cause of block session because of INACTIVE.
Please advise
It sounds like an update occurred earlier in a session and it never got committed. Either that or a select for update. I’m so used to looking this up in Toad I don’t know the correct v$ tables to see this. v$locked_object might help you identify the table that the inactive session is holding the lock on. Toad shows you all the open cursors so I sometimes dig through those. You could also trace the offending session once you know what table it is locking. But I see what you describe often. The last SQL statement is just some normal query but the inactive session is holding a lock and blocking an active session. It just means that an earlier SQL statement grabbed the lock and never released it through a commit or rollback.
Thanks for your comment.
Bobby