Re: Query via Database Link

Mark Wilson <mark_at_foulscreche.demon.co.uk> wrote:
>Can anyone advise me on the performance impact on the remote database
>caused by query across a database link. Most of the resource seems to be
>utilised on the local database, but are any processes started on the
>remote database and how much resource is consumed there?

A dblink opens a client session in the target instance, including (depending on the platform and on particulars like MTS) starting a process on the target instance node. In that sense the first use of a given dblink (in your local Oracle session) carries the cost of another Oracle logon. After that the dblink tends to stay open (as long as your local session lasts) so this cost is amortized if you use the dblink multiple times in a session.

SQL-wise, dblinks can be expensive if you do things that split a statement across instances. Joining a local and remote table, for example. If a SQL statement references *only* tables at the remote instance, it generally will execute entirely at the remote instance as-is.

/b

--
Bill Manry - IBM Products Division - Oracle Corp. USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.
Remove the "." from "B.Manry" to email me.