Re: DB_LINK QUESTION

In article <53el0l$mbi_at_ds2.acs.ucalgary.ca>, Phuong Ngo wrote:
>
> 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.

Is this really a remote procedure that is being executed over the DB_LINK or is it a local procedure that is doing table manipulation over the DB_LINK?

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

It sounds as if you have your post update trigger dropping the link and recreating it on the fly. This can be causing some lag. To verify, try disabling the portions of your code that drop and recreate the DB_LINK. Make sure the DB_LINK exists before you call your code, and test again. You should see a pick-up in performance.

-- 
--roby_at_h2net.net------------------------------------------------Aurora, CO--

  A well-used door needs no oil on its hinges.
  A swift-flowing stream does not grow stagnant.
  Neither sound nor thoughts can travel through a vacuum.
  Data rots if not used.

  These are great mysteries.
----------------------------------------------------------------------------