Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to identify blocked and blocking sessions in Oracle ?
Problem:
You’d like to identify the blocking and the blocked sessions in your database.
Solution
When we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that is held by a different session. We can issue the following command to view information about the blocked and the blocking sessions in ORacle.
Example
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type FROM v$lock WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM v$lock WHERE request > 0 ) ORDER BY id1, request;
The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s).
A blocking session can block multiple sessions simultaneously, if all of them are wanting to use the same object that is being blocked.
You can use below SQL to fetch the information.
Example
select sid,type,lmode,request,ctime,block from v$lock;
Output
SID TY LMODE REQUEST CTIME BLOCK -------------- -------- ----------- ----------- -------- ------- 140 TX 4 6 11655 0 38 TM 3 0 826 0 38 TX 6 0 826 1
The key column to watch is the BLOCK column which will have the value 1 for the blocking session. In our example, session 38 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 38, also shows a lock mode 6 under the LMODE column which mean it is holding this lock in the exclusive mode. Therefore session 140 is hanging for the same reson and unable to perform its update operation. The blocked session shows a value of 0 in the BLOCK column.
If you want to find out the wait class and for how long a blocking session has been blocking others, we can do so by querying the V$SESSION view.
Example
SELECT blocking_session, sid, wait_class, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL ORDER BY blocking_session;
Output
BLOCKING_SESSION SID WAIT_CLASS SECONDS_IN_WAIT ----------------- -------- ------------- ------------------- 38 140 Application 1237
20K+ Views
