Home » RDBMS Server » Server Administration » Blocking_session in v$session
Blocking_session in v$session [message #244823] Thu, 14 June 2007 03:45 Go to next message
tayalarun
Messages: 20
Registered: December 2005
Junior Member
Hi,

My Oracle version :
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

When I am quering the v$seesion, it shows some Blocking_sessions in Blocking_sessions column.
But when I try to locate that Blocking_session through SID column,
Some time I am not able to see record of that SID whereas some times it shows that SID.

If I see that from Oracle Enterprise Manager database control, I am able to see the list
of blocking sessions with some other SID.

For Example :
If I fire the following query, it shows only one row where as it should also show me a
seesion of SID 520 also.

select SID, SERIAL#, machine, USERNAME, status, BLOCKING_SESSION
from v$session where BLOCKING_SESSION IS NOT NULL
UNION ALL
select SID, SERIAL#, machine, USERNAME, status, BLOCKING_SESSION
from v$session A where SID IN
(SELECT BLOCKING_SESSION FROM V$SESSION b WHERE A.SID=B.BLOCKING_SESSION)
order by machine, status;


SID SERIAL# MACHINE USERNAME STATUS BLOCKING_SESSION
---------- ---------- ----------------- ---------- ------- ----------------
490 2738 nitappsun04-zone1 MYSUN_NI ACTIVE 520

1 row selected.


Can I have some pointers for this.

Thanks & Regards
Arun Tayal
Re: Blocking_session in v$session [message #244832 is a reply to message #244823] Thu, 14 June 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think your query (its second part) is wrong.
Format it: How to format your posts.

Regards
Michel
Re: Blocking_session in v$session [message #294697 is a reply to message #244823] Fri, 18 January 2008 08:42 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Let's revive a sleeping thread if that's ok.

I have actually the same question.

In our shop we're using following query to get a look at sessions that are blocked by other sessions:

select    s.inst_id||':'||s.sid    blocker    ,
    substr(s.program,1,40)    s_program    ,
    s.username  s_username  ,
    w.inst_id||':'||w.sid    blocked    ,
    substr(w.program,1,40)    w_program    ,
    w.username  w_username  ,
    s.event    h_event    ,
    w.event    w_event
from    gv$session    s ,
    gv$session    w
where    w.blocking_session = s.sid
and w.blocking_instance = s.inst_id ;


But apparently the value in blocking_session isn't the one that's actually holding the lock!
How can we be sure of that?

Well:
- we started two sql*plus sessions and looked them up in gv$session.
- in each session we did the same update statement; the first session got the lock, the second one is waiting for the first to finish

But when looking in the gv$session we got a totally different blocking session!

So: something wrong with that gv$session?

We're running 10.2.0.3.0



Re: Blocking_session in v$session [message #294700 is a reply to message #294697] Fri, 18 January 2008 08:58 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Never mind: we're hitting Bug 5481650 GV$SESSION.blocking_session has incorrect value

Versions confirmed as being affected

* 10.2.0.3

With description:

If "Blocker" and "Waiter" are on the same instance then "blocking_session"
in "GV$SESSION" is shown exactly 1 greater than the actual SID.
Previous Topic: problem in opening the databae
Next Topic: what is the difference between cloning and datagaurd
Goto Forum:
  


Current Time: Fri Dec 02 14:14:21 CST 2016

Total time taken to generate the page: 0.09267 seconds