Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.