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: http://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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2007 - 09:36:23 CDT
