This content has been marked as final. Show 7 replies
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
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.
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.
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 ..?
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.
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.