Query Oracle LOCK and number of LOCKED sessions

Published by Alessandro Caredda on 7 March 20237 March 2023

In the article “Query Oracle LOCK and number of sessions” will be of great help.

Especially when the application group is stuck due to database errors and the manager is bombarding you with questions just to make you waste more time.

In highly accessed databases, it may happen that, among the numerous requests coming from application servers, multiple sessions insist on the same table or partition.

Numerous DML operations on objects already used by other sessions are the main cause of contention and, in simple terms, waiting times that can create dangerous bottlenecks.

Therefore, when LOCK situations are reported to us, we must be ready to identify the session that is causing the blockage.

Analyzing the V$LOCK view, called by the following query,

we have the possibility to identify useful information to achieve our goal,

which is to precisely identify the session that is causing the contention.

With the following query, you will find which session is causing strong slowdowns or database blocking in a short time.

Query Oracle LOCK and number of sessions

SELECT bs.sid BSID, count(ws.sid) as NumWait 
FROM v$lock hk, v$session bs, v$lock wk, v$session ws 
WHERE hk.BLOCK = 1 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 
AND wk.id2(+) = hk.id2 AND hk.sid = bs.sid(+) 
AND wk.sid = ws.sid(+) AND bs.sid != ws.sid group by bs.sid 
order by 2 desc;

Query for RAC enviroments

SELECT bs.inst_id, bs.sid BSID, count(ws.sid) as NumWait 
FROM gv$lock hk, gv$session bs, gv$lock wk, gv$session ws 
WHERE hk.BLOCK = 1 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 
AND wk.id2(+) = hk.id2 AND hk.sid = bs.sid(+) 
AND wk.sid = ws.sid(+) AND bs.sid != ws.sid group by bs.inst_id, bs.sid
order by 2 desc;

You will be indicated the SID that is causing the blockage and the number of sessions that are blocked.

Once we have identified the session causing the lock, the DBA must take immediate action to resolve the issue. Depending on the type of lock, different actions can be taken.

For example, if the lock is a shared lock, we can try to wait for it to be released or contact the session owner to ask them to release the lock.

Moreover, if the lock is an exclusive lock, we may need to kill the session to release the lock and allow other sessions to access the resource.

So, it is important to carefully consider the potential impact of these actions and communicate with application owners and users to minimize disruption.

In addition, the DBA should investigate the root cause of the lock and take measures to prevent it from happening again in the future.

This could involve optimizing queries, restructuring the database schema, or implementing locking strategies to minimize contention.

In conclusion, identifying and resolving locks in Oracle databases is a critical task for DBAs to ensure optimal performance and minimize disruptions to application users.

To view my guides on CRS commands click here!

At this LINK, however, you will find SQL queries

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin

For any doubt, do not hesitate to comment on the article.