patternMinor
Does Oracle cache queries when using a database link
Viewed 0 times
databasecacheusingdoeswhenqueriesoraclelink
Problem
Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized.
I can't find any information confirming/infirming this.
Naively I would not see why Oracle would not be able to do query caching when using a db link.
I can't find any information confirming/infirming this.
Naively I would not see why Oracle would not be able to do query caching when using a db link.
Solution
There are some restrictions with database links. You can't execute DDL remotely for example.
Queries run against remote tables are not processed in exactly the same way as regular tables:
The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
In most cases, the breaking process is adequate and you will just get the expected overhead of the database link (network delay between the databases, two query analyses/processing instead of one, etc..). The remote subqueries will be treated as normal queries by the remote database and the data will get into the remote cache as with regular queries.
You might get into performance problems if you attempt to join a local table to a remote table (or tables from two distinct db links) since the data has to be carried to a single database to perform the join. In that case, you might want to change the breaking up process (for example performing a complex join in a remote database instead of the local database).
The documentation details a number of ways you can tune distributed queries.
Queries run against remote tables are not processed in exactly the same way as regular tables:
The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
In most cases, the breaking process is adequate and you will just get the expected overhead of the database link (network delay between the databases, two query analyses/processing instead of one, etc..). The remote subqueries will be treated as normal queries by the remote database and the data will get into the remote cache as with regular queries.
You might get into performance problems if you attempt to join a local table to a remote table (or tables from two distinct db links) since the data has to be carried to a single database to perform the join. In that case, you might want to change the breaking up process (for example performing a complex join in a remote database instead of the local database).
The documentation details a number of ways you can tune distributed queries.
Context
StackExchange Database Administrators Q#9358, answer score: 3
Revisions (0)
No revisions yet.