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.
Oracle blocking sessions
concurrencyoracleoracle-11g-r2oracle-enterprise-manager
Best Answer
Assuming you are licensed to use the AWR, you could query either the
gv$active_session_history
view for more recent data ordba_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 ingv$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#
, andBLOCKING_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.