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
