Re: distributed transaction hangs

<andrei.kubar_at_gmail.com> wrote in message news:1168013004.233783.325680_at_51g2000cwl.googlegroups.com...
> Hello,
>
> My question is: how to determine which session on the remote db
> corresponds to the local session while a distributed transaction is in
> progress? In Note:118219.1 on metalink it is written that the
> V$SESSION.PROCESS and V$SESSION.MACHINE will have the same values on
> all nodes, but it seems to be not true to me, I guess that article is
> old and things have changed since then, because I don't see any
> sessions with same process and machine values on the remote db.
>
> Basically the problem which I have is that in some cases (very rarely)
> it happens so that after executing some DML over dblink, the local
> session hangs waiting for "SQL*Net message from dblink" event. And all
> other sessions who execute the same procedure hang waiting for the same
> event. Which causes eventually the maximum number of session limit
> being reached and I have to restart the database, because killing them
> one by one is too inconvinient and takes too much time.
>
> So the fact that they are not dying on the distributed_lock_timeout=60,
> must be telling that the remote session is not waiting for a lock? But
> it must be waiting for something. So I need to find out which remote
> session corresponds to the local one and see what is that remote
> session up to. Are my reasonings correct, or must another kind of
> approach be used for solving such problems?
>
> wbr,
> Andrei Kubar
>

It may be out of date by now (written in 2002 for 8.1.7) but Mark Powell has a note that might help here:

    http://www.jlcomp.demon.co.uk/faq/find_dist.html

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html