1 Reply Latest reply: Apr 30, 2013 6:27 PM by davidp 2 RSS

    find sessions blocked by the blocking sessions in history

    Moazzam
      We are using Oracle 10g R2 on Linux. I am using following query to get all the blocking sessions in the last 7 days:
      SELECT  min(A.SAMPLE_TIME) start_time,max(A.SAMPLE_TIME) end_time,a.inst_id,a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME,max(A.SAMPLE_TIME) - min(A.SAMPLE_TIME)  
      FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s, dba_objects o
      where a.sql_id=s.sql_id
      and A.CURRENT_OBJ# = O.OBJECT_ID
      and blocking_session is not null
      and a.user_id <> 0 -- exclude SYS user 
      and a.sample_time > sysdate - 7 
      and a.event = 'enq: TX - row lock contention'
      group by a.inst_id,a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME
      I also want to get the sessions that were blocked by these blocking sessions. Can you please guide me?
        • 1. Re: find sessions blocked by the blocking sessions in history
          davidp 2
          Most of the content of GV$ACTIVE_SESSION_HISTORY is about the blocked session. The user_id you are seeing is the blocked session's user ID. To get the blocked session ID, just add a.SESSION_ID, a.SESSION_SERIAL# to the query:
          SELECT  a.SESSION_ID, a.SESSION_SERIAL#, min(A.SAMPLE_TIME) start_time,max(A.SAMPLE_TIME) end_time,a.inst_id,  a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME,max(A.SAMPLE_TIME) - min(A.SAMPLE_TIME)  
          FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s, dba_objects o
          where a.sql_id=s.sql_id
          and A.CURRENT_OBJ# = O.OBJECT_ID
          and blocking_session is not null
          and a.user_id  != 0 -- exclude SYS user 
          and a.sample_time > sysdate - 7 
          and a.event = 'enq: TX - row lock contention'
          group by a.SESSION_ID, a.SESSION_SERIAL#, a.inst_id,a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME
          regards,
          David