Re: Database links...again

I don't think setting SDU will make any difference because you are implicitly using single row fetches in the loops, so the volume of data being passed is much less than the default SDU anyway.

Are both instances configured identically ? Have you traced the SQL that hits the remote instance to see what it actually does, and if it is in some way different from the local SQL, or if its performance characteristics are different (does one database have 10 times as many objects at the other ?)

The other thing to bear in mind is that a single instance test may operate fully in memory, but when the two instances have to be running concurrently they may be forcing each other to swap to disc all the time.

Try the test with:

1. Using a loopback connection as the 'remote'
2. doing a test with local looping inside the remote
3. remote looping inside a local to see if the results give you any further clues.

BTW - SDU has to be set in both the tnsnames.ora and the listener.ora to be effective.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Steve B wrote in message <7m2fv3$9d7$1_at_taliesin.netcom.net.uk>...
>At present remote queries across a database link are taking at least ten
>times as long to execute compared to being directly executed on the remote
>instance...I've included a test procedure at the end of this post...
>
>Is there any reason why the procedure below shold be so slow ????
>
>What parameters can I set to improve this performance ??
>
>I've read about the Session Data Unit ( SDU ) parameter for tnsnames
>configuration....
Received on Thu Jul 08 1999 - 10:33:49 CDT