OraFAQ Forum: Server Administration » Blocking

Home » RDBMS Server » Server Administration » Blocking_session in v$session

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Blocking_session in v$session [message #244823]

Thu, 14 June 2007 03:45

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

Report message to a moderator

Re: Blocking_session in v$session [message #244832 is a reply to message #244823]

Thu, 14 June 2007 04:24

Michel Cadot
Messages: 68683
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

Report message to a moderator

Re: Blocking_session in v$session [message #294697 is a reply to message #244823]

Fri, 18 January 2008 08:42

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

Report message to a moderator

Re: Blocking_session in v$session [message #294700 is a reply to message #294697]

Fri, 18 January 2008 08:58

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.

Report message to a moderator

Previous Topic:	problem in opening the databae
Next Topic:	what is the difference between cloning and datagaurd

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 05:47:47 CDT 2024