Need to get bllocking and holding timing details in oracle

set lines 120
col sess format a15
SELECT DECODE(request,0,’Holder: ‘,’ Waiter: ‘)||sid sess,id1,id2, lmode,inst_id, request, type
FROM GV$LOCK WHERE (id1, id2, type)
IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
/

 

Hi team,

I have used this command to find out blocking session it is showing session is there but suggested commands is shoing no rows.
Can you please send the command and also add detail how much time holding/blocking session is blocked waiter session?

SESS ID1 ID2 LMODE INST_ID REQUEST TY
————— ———- ———- ———- ———- ———- —
Holder: 2397 524319 548489 6 1 0 TX
Waiter: 474 524319 548489 0 6 6 TX
Holder: 2981 6815776 391294 6 1 0 TX
Waiter: 542 6815776 391294 0 5 6 TX
Holder: 1918 10223642 429877 6 2 0 TX
Waiter: 1754 10223642 429877 0 2 6 TX
Waiter: 2565 10223642 429877 0 1 6 TX
Holder: 1924 11927575 573769 6 2 0 TX
Waiter: 1918 11927575 573769 0 2 6 TX
Holder: 2895 28180490 1078657 6 5 0 TX
Waiter: 1905 28180490 1078657 0 2 6 TX

SESS ID1 ID2 LMODE INST_ID REQUEST TY

 

SQL> SELECT
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait,
s.event
FROM
gv$session s
WHERE
blocking_session IS NOT NULL and s.seconds_in_wait > 10; 2 3 4 5 6 7 8 9 10 11

no rows selected

SQL>

Default Asked on December 4, 2018 in ORACLE.
Add Comment
3 Answer(s)

Dear,

 

In the query you are using s.seconds_in_wait >10 , means it will reporting the blocking session where waiting time is more than 10 seconds.

 

 

Use the below query to find the blocking sessions.

 

SELECT
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait,
s.event
FROM
gv$session s
WHERE
blocking_session IS NOT NULL;

 

 

Regards

FORUM ADMIN

Newbie Answered on December 5, 2018.
Add Comment

Hi Team,

 

I already used this one also..But getting same no rows selected..

 

Please suggest..

Default Answered on December 5, 2018.
Add Comment

Dear ,

 

You cant use v$lock view to find blocking sessions. For finding blocking sessions you need to check gv$session only.

 

The query with the v$lock will always return rows because transactions happen regularly in the database. It doesn’t mean that you have blocking sessions.

 

Below is the best query to find blocking sessions. If it shows no rows means you dont have blocking sessions present.

 

SELECT
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait,
s.event
FROM
gv$session s
WHERE
blocking_session IS NOT NULL;

 

 

Regards

Forum Admin

Newbie Answered on December 6, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.