Differing results from gv$session

oracleoracle-11g-r2oracle-racoracle-se

The following query returns no results, yet it seems like it should.

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

In my 11.2.0.2 Standard Edition RAC environment the above query returns no results, yet the following variations all return results.

SELECT sid FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

SELECT * FROM gv$session where SID = 256; --Your SID will likely be different.

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');

Can anyone confirm this behavior and/or explain it?

Best Answer

I got following results

SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
-- 5276 

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
-- No result

SELECT * FROM gv$session where SID =5276;
-- 4 results for 4 node RAC
-- 1 result matches my USERNAME,OSUSER, MACHINE and PROGRAM
-- 3 results are different from my USERNAME,OSUSER, MACHINE and PROGRAM

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');
-- 1 result

I cannot explain but following query in 4 node RAC gives interesting output.

SELECT '1' AS QUERY_NUMBER,S.* FROM gv$session S where SID = SYS_CONTEXT('USERENV','SID')
UNION ALL
SELECT '2',S.* FROM gv$session S where SID = ( SELECT  SYS_CONTEXT('USERENV','SID') FROM DUAL)
UNION ALL
SELECT '3',-1,S.* FROM v$session S where SID = SYS_CONTEXT('USERENV','SID');

QUERY_NUMBER = 1, no rows

QUERY_NUMBER = 2, 4 rows (4 node RAC)

QUERY_NUMBER = 3, 1 row

Related Question