Database link performance problems

We have two instances on the same NT4 server, both Oracle8i version 8.1.5..

We are experiencing performance problems across a db link between the two..

We are accessing instanceA using OAS4.0.7.1 PL/SQL cartridge. A stored procedure in instanceA retreives data from instanceB across the link...this is painfully slow....when the same procedure is ran directly on instanceB accessed by OAS it is fast..I don't know where the problem lies but the above scenario suggests that the link is the problem...

I've ran a test procedure on instanceA which logs the start time of the procedure, calls a procedure on instanceB ( which does nothing ) and then records the time after this procedure has completed...

When ran from a browser the time taken is always in the region of 100 ms....when ran from a SQL*Plus session the initial run takes approx 100 ms, then after that it takes 2-3 ms....it seems to me that the OAS method needs to establish a new session on instanceB every time...??

SQL*Net8 is installed on the server and it has been configured to use TCP/IP to access instanceB...

Does anyone know how I can improve the performance of the link...what other aspects of the database or application server configuration do I need to be looking at ?? What other information will I need to supply to the NG to have more chance of sorting this one out ??

Cheers in advance

Steve Bright

steve.bright_at_capgemini.co.uk Received on Tue Jul 06 1999 - 04:53:25 CDT