Re: DBLINKs in critical production system
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: Ajay_Thotangare@xxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 May 2007 07:36:23 -0700 (PDT)
Alfonso
>I did not get this part.” Another issue is the performance, because now you
>have at least two
> optimizers working and only one of them can handle the query”.
> Two optimizers working!!? Is DBLINK queries behavior/access path different
> from non-dblink queries?
The optimizer at the driving site decides how to execute the distributed query;
then during the execution it passes (possibly several) query components to the
(possibly multiple) remote sites. Each remote site then separately optimizes
each query component it receives from the master. There is (AFAIK) no direct
coordination between these optimization phases, and there is only limited
statistical information available to the driving site/"master" optimizer (I
think Jonathan Lewis's CBO book has more details, but I don't have it handy).
The optimizer code may also be different at each site (eg Oracle 8, 9, 10) and
the parameters and system stats may be configured differently.
So yes: DBLINK queries (at least, complex ones) ARE different.
> Why dblink queries are slow compared to non-dblink queries considering both
> databases are in same location ?
DB link queries can be slow (compared to the same query using local objects)
for a number of reasons including
a) because of the way the query is deconstructed and optimized (as above)
b) because of the context switch between 'local' and 'remote' data access, and
the extra code length and latency involved in accessing SQL*Net, the underlying
comms interface (eg TCP/IP), and the comms hardware (whether an internal bus,
interconnect, LAN, WAN or the internet itself)
c) because of specific limits on the database-to-database communication channel
defined by the db link - see init.ora parameters OPEN_LINKS and
OPEN_LINKS_PER_INSTANCE.
I'm sure there are other obvious factors I've forgotten to include here. Note
that even a loopback dblink to another schema on the same database introduces
many of these overheads.
Also be aware that (as mentioned recently on this list, see thread here:
//www.freelists.org/archives/oracle-l/04-2007/msg00048.html) any remote
query (yes, SQL query - not just DML) starts a transaction on the remote
database; some resources are not freed until the transaction is committed or
rolled back.
Finally: in spite of all this, DB links can be a very useful part of the Oracle
developer's toolkit. Just don't use a hammer when you really need a wrench.
HTH
Regards Nigel
Other related posts:
