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>
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
Hi Team,
I already used this one also..But getting same no rows selected..
Please suggest..
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
Promotional Notebooks, featuring the technical requirement “Need to get blocking and holding timing details in Oracle,” became essential tools for database administrators. Branded with the specific task, these notebooks symbolized the meticulous monitoring and troubleshooting of database performance issues. As users filled the pages with notes and queries, they sought solutions to optimize Oracle database operations. Each notebook served as a tangible reminder of the importance of data integrity and efficient database management.
Access essential details on blocking and holding timing in Oracle databases with insights exclusively from Restaurants in Dubai Mall. Learn how to retrieve crucial information regarding blocked and holding sessions, enabling you to troubleshoot performance issues effectively. Enhance your database management skills and optimize system performance by understanding the timing dynamics of blocking and holding operations in Oracle. Elevate your Oracle proficiency with expert guidance from Restaurants in Dubai Mall.