11.2.0.4 in RHEL 6.5
I have the following query to list the blocking sessions' SID, Serial and Instance_ID for a RAC DB.
But, it is returning duplicates like below. Is there a way to remove the duplicates without using DISTINCT ? ie by adding another join condition or something?
I know its difficult to find a DB with lot of blocking sessions , so you volunteers might not be able to test this query. So, a rough guess will do
SELECT gvh.SID sessid, gvs.serial# serial,
gvh.inst_id instance_id
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM gv$lock
WHERE lmode = 0)
AND gvh.id1 = gvw.id1
AND gvw.inst_id = gvh.inst_id --- added to remove duplicate rows. But, didn't help
AND gvh.id2 = gvw.id2
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id;
SESSID SERIAL INSTANCE_ID
---------- ---------- -----------
5708 13979 3
5708 13979 3
3343 51141 3
3343 51141 3
7321 8978 3
6284 4387 3
1341 23095 3
6941 25391 3
6941 25391 3
6083 14875 3
.
.
.
<output snipped>