Re: Troubles with using minus over a database link ??

In article <78o04p$d2b$1_at_news.worldonline.nl>,   "kerkhofm" <michel.kerkhoff_at_prive.ordina.nl> wrote:
> This is one of those questions about procedures where sometimes something
> goes wrong
> for apperently no reason and everybody starts guessing what the problem
> might be eventually ending in me rewriting all code to bypass some obscure
> bug......
>
> This is the problem :
> We have two different databases (two instances) connected via a database
> link. Every night
> a procedure is started on db2 (local) to synchronise some data with db1
> (remote,db1 is leading). For db2 only new or changed rows are required so we
> use the 'minus' option thus : select x,y,z from db1.table1 minus select
> x,y,z from db2.table. The minus option is used in several queries; some
> demanding some easy.
>
> When the procedure is started during the nightbatch it sometimes happens (1
> out of 5 times) that we get a 'end of communication channel' - error. The
> procedure is aborted.
>
> When the trace file is checked the procedure is always aborted at a query
> using a minus. But since 75% of the procedure consists of cursors based (all
> neatly openend and closed) on queries with a minus, this can be accidental.
> Also it doesn't seem to matter if the query is demanding or easy.
>
> So far we haven't come up with a proper explanation, but there are some
> theories :
>
> - Oracle's helpdesk suggests it is a sqlnet bug. When a remote query is to
> long gone sqlnet can abort because it thinks the process is killed. There
> is no proper solution except upgrading. At this moment we do not want to
> upgrade because of one misbehaving procedure.
>
> - Some of our DBA's like the blame the use of minus, stating that a minus is
> slow on remote databases.
> I think slow performance is very different from complete abortion.
>
> - It is also suggested that it is a memory conflict between oracle and unix
> processes. Oracle claims memory already allocated by unix. This is suggested
> by someone who had the same abortion problems (but it had nothing to do with
> minus) and the Oracle helpdesk told him it was a sqlnet bug, only to find
> out it was memory conflict. I doen't have a opinion on this one because this
> beyond my knowledge about Oracle.
>
> Now i want to rewrite the code without minus to make shure that isn't the
> problem. But i don't know if it will help.
>
> Is there anybody who can tell me what the problem is ???????
> We use Oracle 7.3 and SQL*net 2.3
>
> Rgds,
> Michel
>

Have you looked into the init.ora settings for remote transactions? Specifically, distributed_lock_timeout. It may be worth a try and can always be set back if it has no positive effect.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Jan 29 1999 - 10:34:57 CST