Oracle SQL to find out Blocker and Reasons

Execute this script to find out Blocker/Waiter Session.


SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess_sid,inst_id
FROM GV$LOCK 
WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY sess_sid, request;

This one is to determine why.


SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
 AND event NOT IN ('smon timer','pmon timer','rdbms ipc message', 
    'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: