Accessing Remote Tables

Do you have a step in your Explain Plan that reads simply:

REMOTE

You are accessing a table that is on a different Oracle Database, connected by a Database Link. There are a number of ways this could happen:

• Selecting from a table with a database link specified.

SELECT ...
FROM   my_table@wherever;

• Selecting from a synonym which is defined as a remote table

CREATE [PUBLIC] SYNONYM my_table FOR my_table@wherever;

SELECT ...
FROM   my_table;

• Selecting from a view that selects from one of the above.

Remote tables have a number of performance implications:

• If all of the tables from which you are selecting are on the same remote database, then Oracle will get the remote database to run the query and send back the results. The only problems are the cost of shipping the results back, and the fact that you cannot see how the remote database is executing the query in Explain Plan (from Oracle 10g onwards, Explain Plan will get the remote database to plan its bit of the query and pass back the results to your Explain Plan). Make sure that the whole query is being performed remotely - the Explain Plan output should just have the one step - "REMOTE" - or in 10g+, every line of the plan contains the word "REMOTE".

• If some tables are local - or you are joining tables over two or more remote databases - then Oracle will need to ship all of the data to one database before it can perform a join.

  If you want to use an Indexed Nested Loop (low volume) join, then the outer (2nd) table cannot be remote - if it is you will get a full table scan and a hash join. You could use the DRIVING SITE hint to make the remote database the driving site of the query. Then the inner (1st) table will be sent over there, and an index can be used on the outer table. The remote database will then send back the results.

  Even better, speak to your DBA about having the remote table(s) replicated on your database so that you can avoid the remote join altogether.