Always Use the CBO for Distributed Joins Oracle Tips by Burleson Consulting

The rule-based optimizer has severe limitations for distributed joins primarily because it does not have information about indexes for remote tables. Therefore, the RBO commonly generates a nested loop join between a local table and a remote table with the local table as the outer table in the join. The RBO uses either a nested loop join with the remote table as the outer table or a sort merge join, depending on the indexes available for the local table.

Remember, the cost-based optimizer can consider more execution plans than the rule-based optimizer. For example, the cost-based optimizer knows whether indexes on remote tables are available and in which cases it makes sense to remote indexes. In addition, the cost-based optimizer considers index access of the remote tables as well as full-table scans, whereas the rule-based optimizer considers only full-table scans.

Viewing the Execution Plan for Distributed Joins

Oracle provides a script to display the execution plan for a remote query. You should note that Oracle uses the other column in plan_table to hold the remote join information. The remote_plan.sql script is used on the initiating database, but you must then extract the remote query and re-explain it on the remote database.

remote_plan.sql

set long 2000
set arraysize 1

col operation   format a22
col options     format a8
col object_name format a10
col object_node format a5
col other       format a20
col position    format 99999
col optimizer   format a10

select lpad(' ',2*(level-1))||operation operation,options,object_name,
optimizer,object_node,other
from plan_table
start with id=0 and statement_id='A'
connect by prior id=parent_id and statement_id='A';
set echo on

To see how remote_plan.sql differs from a standard remote join, consider the following distributed query:

select
   e.empno,
   e.ename,
   d.dname,
   l.loc,
   d.deptno
from
   emp@new_york  e,
   dept@san_fran d,
   location      l
where
   d.deptno = e.deptno
and
   d.loc = l.loc
and
   e.empno = 1234;

Here is the execution plan from this SQL statement using remote_plan.sql.

OPERATION        OPTIONS  OBJECT_NAM OPTIMIZER  OBJEC OTHER
------------------------- ---------- ---------- ----- --------------------
SELECT STATEMENT                     CHOOSE
  NESTED LOOPS
    NESTED LOOPS
      REMOTE                                 ROSE. SELECT "EMPNO","ENAM
                                             WORLD E","DEPTNO" FROM
                                                   "EMP" E WHERE
                                                   "EMPNO"=1234

      REMOTE                                 ROSE. SELECT "DEPTNO","DNA
                                             WORLD ME","LOC" FROM "DEPT
                                                   " D WHERE "DEPTNO"=:
                                                            1

    TABLE ACCESS       FULL     LOCATION   ANALYZED

With remote tables, the row with the REMOTE table access method stores the SQL sent the remote node in the other column of the remote_plan.sql output. It is important to note that we can only see the SQL being transmitted to the remote site and we are not told how the SQL will be executed at the remote site. The solution is to log onto the remote database and evaluate the execution plan on the remote server.

Next, let’s review some very important guidelines for tuning distributed SQL joins.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.