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.
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
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:
d.deptno = e.deptno
d.loc = l.loc
e.empno = 1234;
Here is the execution plan from this SQL statement
OPTIONS OBJECT_NAM OPTIMIZER OBJEC OTHER
------------------------- ---------- ---------- -----
ROSE. SELECT "EMPNO","ENAM
WORLD E","DEPTNO" FROM
"EMP" E WHERE
ROSE. SELECT "DEPTNO","DNA
WORLD ME","LOC" FROM "DEPT
" D WHERE "DEPTNO"=:
TABLE ACCESS FULL
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.