7 Replies Latest reply on Jan 11, 2013 9:06 PM by Dom Brooks

    Not able to find blocking session

      I am working on oracle 11g and AIX.We were facing some blocking issues.For more investigation on the blocking sesssion we checked active session history of the database.Like session 'A' was blocked by session 'B' ...Now if we check infromation about sessions 'A' it is showing that this session was blocked by session 'B'.

      But when we checked for session 'B' informaiton ,it is not showing any thing...from where we can get infromation about session B and in what all scenarios
      active session history does not show information about blocking session....

      any idea...
        • 1. Re: Not able to find blocking session
          Niket Kumar
          select l1.sid, ' IS BLOCKING ', l2.sid
          from v$lock l1, v$lock l2
          where l1.block =1 and l2.request > 0
          and l1.id1=l2.id1
          and l1.id2=l2.id2;

          select * from dba_waiters;
          select * from dba_blockers;

          you can check bloking session from above queries.

          and if you want to trace sql_text you can combine v$session and v$sql.
          • 2. Re: Not able to find blocking session
            I could be wrong on this but I believe that you might not have all the information in the DBA_HIST views because it might not have been flushed there. The v$ views will contain everything at the time it is happening or since instance startup, but if the buffer in which the active session history information is kept becomes full before it has been written out to be visible in DBA_HIST_ACTIVE_SESS_HISTORY then it will be lost. I think there is a finite amount of storage for the active session history v$ view which is just cycled around.

            I think I read this in the docs somewhere. I tried to find it but I couldn't from a quick glance.
            • 3. Re: Not able to find blocking session
              Found it:

              • 4. Re: Not able to find blocking session
                actually ..the lock was happened 2-3 day ago , now we need to investigate that ...so i can not use this sqls ...


                so , active session history must hold all the sessions infromation ?

                one scenario that can happen ...is that ....one session loged in use sqlplus and locked some rows ...and not doing any thing now ..so it become inactive...

                now is that ...this session infromation will also recodered in the active session history view of the database ..?
                • 5. Re: Not able to find blocking session
                  From reading the docs my understanding is that any session which is nor waiting on the idle wait event could be included in the sample which is written to v$ASH and which is in turn written to dba ash. Whether or not it gets included in the sample is a different matter. It sounds like you shouldn't rely on it because there is no guarantee it will get there.

                  In the past I have set up a job to write into another database table from v$session (in periods of low activity, such as overnight) when we've had locking issues so I could see what was going on properly. That's the only way you are going to get historically accurate information. Other than that, you need to catch the lock as it's happening.
                  • 6. Re: Not able to find blocking session
                    Did you look in MOS :
                    Detecting Blocking Sessions in RAC and non-RAC (Enqueue Locks) Environments [ID 398519.1]
                    Find Blocking Sessions In SQLPLUS [ID 728539.1]
                    • 7. Re: Not able to find blocking session
                      Dom Brooks
                      Finding what statement took the lock that blocked the other session is a combination of the unreliable and the lucky.

                      Doug Burns covers the issue here:

                      The problem is that the in-memory ASH buffer captures active sessions every second. Of this, the active sessions for the interval once every 10 seconds are flushed down to the repository.

                      Both sources should be sufficient to capture the blocked/waiting session for a "normal" locking scenario. ASH (and V$SESSION) will show you who is the blocker but that's it.

                      In general you've got to be lucky to get the specific blocker activity captured in ASH and then very lucky for it to be captured in the 1-in-10 repository sample. Even if the statement was captured, there's nothing standing up waving a big flag saying "I'm the statement that locked the row that causes the problem in 3 days time."

                      There's no guaranteed solution here.