Re: DB_LINK QUESTION

You're right - the SQL*NET connection between the two instances does stay active until a predefined timeout period expires, or your session on A is ended.

To speed up the initial connection:-

1. If the protocol you are using is TCP/IP, then specify the hosts in the LISTENER.ORA and TNSNAMES.ORA using physical IP addresses, rather than aliases. This will speed up the initial connection radically.
2. Investigate the use of prespawned pools of SQL*NET connections. I can't remember if this is the correct terminology for it, but I believe (under SQL*NET v2) you can specify in the LISTENER.ORA:-
   • the minimum number of spawned processes to create
   • the minimum number of FREE spawned processes to have (this handles the case where all the initial prespawned processes are used....makes sure there are still other spawned processes created, but cleans these ones up when finished)

Hope this helps you.

Regards,

Michael McAllister
The Smith's Snackfood Company Ltd
Sydney, Australia

P.S. You may want to be careful using these prespawned processes. I know that under an early version of v7.1 of the database on WinNT this was causing an OS level crash of our database. pngo_at_acs6.acs.ucalgary.ca (Phuong Ngo) wrote:

>Hi there. I was hoping that I could get some feedback on the
>following problem:
 

> 1) Two seperate Oracle Instances (Inst A; Inst B)
> 2) User logs onto A and does an update on a table.
> 3) Update trigger on table calls a remote procedure that
> resides on B.
> 4) The procedure on B performs an update on some local
> tables.
 

>Here is what I would like to know. The initial update takes
>about 1 min to complete, while subsequent updates afterwards
>complete almost instantly. If however, I log off of Instance A,
>and update again, the first update again takes a long time, while
>the following updates are much faster.
 

>I have noticed that prior to the initial update, there is no
>db_link with B. Once the initial update is complete, I have
>noticed that the db_link between A and B remains. I believe that
>the reason why the first update takes so long is because the
>db_link must be established. Is this true??? If so, are there
>any ways in which I can speed up the db_link connection?

>Regards,
>Andy
>ango_at_talisman-energy.com
Received on Thu Oct 10 1996 - 00:00:00 CEST