oracle blocking sessions

Question

I am trying to investigate some concurrency issues on Oracle 11 RAC. So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking everyone else and what it is waiting for. However, to take advantage of that view, I need to catch the problem as it is happening. So I'm looking for a way to query oracle for historic data which would give me data similar to what "blocking sessions" screen has to offer in Enterprise Manager.

Answer 1

Assuming you are licensed to use the AWR, you could query either the gv$active_session_history view for more recent data or dba_hist_active_sess_history for older data. gv$active_session_history will have a snapshot of what every active session was doing at the top of each second and will hold that data for a few hours. dba_hist_active_sess_history will have the data from, essentially, every 10th snapshot in gv$active_session_history but it will be retained for much longer (whatever your AWR retention period is).

Both of these views will have a BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, and BLOCKING_INST_ID that will tell you which session was holding the lock that the blocked session was waiting on. You can then look too see what queries the blocking session was running at around the same time-- the blocking session may well not be active in the particular snapshot you're looking at but it probably was in the snapshot a second or two earlier.