Re: distributed transaction hangs

On Jan 5, 11:10 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <andrei.ku..._at_gmail.com> wrote in messagenews: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 KubarIt 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -- Show quoted text -

The queries still work on 9.2.0.6 and I am pretty sure I tested them on our 10.1.0.4 system before we had to remove it.

>From the description of the problem it sounds as if the problem might
be related to the distributed locks taken in RBS segments. Do these sessions that issue remote queries commit. If not, change them to do so. At the bottom of the referenced article is a link to another article that demostrates that remote queries are transactions and hold RBS entries until the transaction is terminated (commit/rollback).

HTH -- Mark D Powell -- Received on Fri Jan 05 2007 - 16:20:35 CST