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

"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
>
>

Michel,

I have seen this exact behaviour and tech support could be correct... What I did is move the code to the server and ran it from there rather than from a workstation.. has been working OK ever since..

Robert Prendin

Robert Prendin
RP Data Solutions Inc.
Specializing in ORACLE DBA Support Services Received on Wed Jan 27 1999 - 18:15:35 CST