BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter