Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

How to read distributed SQL execution plans

Oracle News by Donald BurlesonFebruary 11, 2015

Tuning distributed Oracle SQL is fundamentally different than tuning ordinary SQL.  Because a single SQL query may touch many distributed instances, the optimizer resolution becomes more complex, and this reflects in the execution plan for the distributed query.  Tuning any SQL statement involves extracting the execution plan for the SQL, and getting a real execution plan is much more difficult task in a distributed environment.

Let's start with a discussion of the performance issues that happen when an SQL query spans several Oracle databases.

Understanding distributed SQL execution plans

The phenomenal challenge of optimizing SQL gets even harder when we see the explain plan for a distributed query.  The Oracle database is forced to "break-up" a distributed SQL statement into several components and ship each query to the remote Oracle database for execution.  The Database must then gather the result set from each remote database and prepare a single result from the query.

The physical challenge for optimizing cross-database SQL is compounded by these issues:   

  • Unknown remote database characteristics - The database involved in a distributed query may be vastly different from each other.  For example, you can join an EMP table on a giant IBM Mainframe with a SALARY table that resides on a tiny Windows server.  The optimizer cannot always compute the relative expense of an operation on a remote server.

  • Hard to see the entire distributed execution plan - As we shall see, a remote execution plan display will only show the SQL that was sent to the remote instance for execution.  To see the execution plan for the remote instance, we must go to that instance and execute the sub-component to get the execution plan.

  • Oracle*Net has no knowledge of network latency - Oracle cannot factor-in the costs of network transmission time between the instances.  Hence, Oracle may choose a sub-optimal execution plan, resulting in excessive network traffic and slow results. However, Oracle has some safeguards against this problem.  For example, if all the objects in a query are remote (and use the same database link), Oracle will attempt to send the complete query to the remote database for processing.

  • No cohesive SQL execution - If some tables in an SQL query are remote and some are local, then Oracle will break up the query into individual SQL statements and pass the SQL to the remote database for independent execution.  If one database is very slow, the whole query will wait.

Creating cross-databases execution plans

As we know, Oracle develops an execution plan for every SQL statement prior to starting the execution.  In a single Oracle database, determining the execution plan is straightforward because a single Oracle data dictionary contains the object statistics. 

 

Execution plan for distributed queries are different.  The "set autotrace on" command does not work for distributed queries, and you must run a special script to display the remote execution plan. 

 

<        remote_sql_execution_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

connect by prior id=parent_id

;

 

Note that this differs from the standard SQL execution plan display because of the inclusion of the other column in the query. 

 

For distributed queries, Oracle uses plan_table.other column to show the remote queries that were sent to the remote instance for execution.

 


connect pubs/pubs@pubs

 

truncate table plan_table;

 

explain plan for

select

   initcap(book.book_title) c1,

   book_retail_price        c2,

   publishing_qty           c3

from

   book

natural join

   book_details@newpubs

order by

   book_title

;

 

@remote_plan

 

Here we see an interesting execution plan.  In this case we are doing a remote hash join to the book_details table in the newpubs database.  It is very important to note that Oracle has "split" this query. 

 

Note that Oracle sent the select statement for the book_details table over Oracle*Net to be executed on the remote instance (newpubs), and we do not see the execution plan for this piece of the distributed query.

 

OPERATION              OPTIONS  OBJECT_NAM OPTIMIZER  OBJEC OTHER  

---------------------- -------- ---------- ---------- ----- -----------

SELECT STATEMENT                           CHOOSE                              

  SORT                 ORDER BY                                                 

    HASH JOIN                                                                  

      TABLE ACCESS     FULL     BOOK       ANALYZED                            

      REMOTE                                          NEWPU SELECT         
                                                     "BOOK_KEY","P

                                                      BS.US UBLISHING_QTY"
                                                      FROM

                                                      .ORAC "BOOK_DETAILS"

                                                      "BOOK

                                                      LE.CO _DETAILS" M

 

Now that we see how to get remote execution plans, let's look at how to control the site for the remote execution.

 

Determining the driving site and driving table for cross-database queries

 

Just like an SQL query has a "driving table", a distributed query has a "driving site".   The choice of the driving site can be manipulated with the driving_site SQL hint.  Here is an example of a remote query with a driving_site hint:

 


select /*+ driving_site(R1) */

   initcap(book.book_title) c1,

   book_retail_price        c2,

   publishing_qty           c3

from

   book@pubs                r1

natural join

   book_details@newpubs     r2

order by

   book_title

;

 

In this example, we see that we have directed Oracle to make the r1 site (PUBS) the driving site for this query.

Let's get the distributed execution plan for the following SQL.  Note that 3 of the four database tables are remote, and only the book_details table is local to this instance.

 


connect pubs_remote/pubs_remote@newpubs

 

truncate table plan_table;

 

explain plan for

select

   initcap(book.book_title) c1,

   author_last_name,

   book_retail_price        c2,

   publishing_qty           c3

from

   book@pubs

natural join

   book_author@pubs

natural join

   author@pubs

natural join

   book_details

order by

   book_title

;

 

@remote_plan

 

Here is the execution plan.  In the output we see the remote notation. 

 

OPERATION              OPTIONS  OBJECT_NAM OPTIMIZER  OBJEC OTHER              
---------------------- -------- ---------- ---------- ----- -------------------
SELECT STATEMENT                                                        

  SORT                 ORDER BY                                                

    HASH JOIN                                                                  

      HASH JOIN                                                                

        HASH JOIN           

          REMOTE                                      DIOGE SELECT"AUTHOR_KEY",

                                                      NES.N "AUTHOR_LAST_NAME"

                                                      F EWPUB ROM "AUTHOR"

                                                      "AUTHORS"

REMOTE                                                DIOGE SELECT"AUTHOR_KEY",    

                                                      NES.N "BOOK_KEY" FROM                                                            

                                                     "BOO EWPUB K_AUTHOR"                                                      

                                                       S     OR"    

 

REMOTE                                                DIOGE SELECT                                                       

                                                      "BOOK_KEY","B NES.N                                                             

                                                      BOOK_TITLE","BOOK_RET

                                                      EWPUB AIL_PRICE" FROM                                                       

                                                      "BOO S     K" "BOOK"

TABLE ACCESS     FULL     BOOK_DETAIL

                                   LS            

 

Let's take a closer look at what is happening inside a distributed SQL statement.  Note that Oracle has broken-up the query into many separate pieces:

 

1 - Independent SQL is sent to each remote Oracle database

 

2 - Result sets are sent back from each remote Oracle database.  The query cannot continue until all remote Oracle databases have sent their results back over Oracle*Net.

 

3- The independent result from each remote request are collected inside the SGA RAM memory.  Only after all results are collected can the SQL progress to join the tables.

 

4 - Oracle performs hash joins on the remote result sets, creating a single result set for the query.

 

5 - Oracle then sorts the result set (for the ORDER BY clause) in on the originating Oracle database.

 

6 - Oracle passes the completed result back to the originating query.

Tips for distributed SQL execution plans

Given the challenges of distributed SQL execution plans, there are several things that you can do to improve the performance of distributed SQL query execution:

 

·         Choose the right driving table - Always execute the join remotely when the remote table is much larger than the local one!

·         Pull vs. Push - In general, performance can be faster if you "pull" the data (calling the remote table from the master instance), as opposed to a "push" where you perform the join on the remote table.  This is especially true of you have a large sort, because the rows may be transferred to the remote host for sorting, and then back again afterwards.

 

·         Pre-join remote tables with views - If you want to join the remote tables, the best way to achieve this is by building a view of these tables on the remote site.  Remote views allow join operations to happen on the remote Oracle database, thereby reducing the amount of Oracle*Net traffic. Note that the predicates do not have to be created within the view as they should be passed within the SQL statement sent from the local to remote node.

·         Use subqueries - While correlated subqueries should be avoided in favor of standard joins, there are cases when a distributed SQL that only returns a small rowset from a remote database will run faster if you replace a standard join with a correlated subquery.  Using this technique, only the subquery is passed to the remote database and the remote database is not aware of the distributed join, making optimization easier to manage.

·         Make the largest table local to your SQL - If possible, always try to make the largest table the local table, to minimize network overhead.  You can use the driving_site hint for this purpose.

·         Use the ordered hint - Joining tables across database links is always challenging, and a savvy developer will use the ordered hint to tip-off the optimizer about the optimal table join order.  This ensures fast SQL response time, and also reduces the amount of work parsing the SQL statement.

·         Consider using views - The ensure optimal performance, consider encapsulating a distributed join into a remote view.  You define the view on the remote database, and then reference the view from the local database.

 

·         Replicate the tables locally - If the tables are small and non-volatile, using Oracle replication to keep a local copy of the tables is the fastest alternative to distributed SQL.  If you can reduce network overhead by replicating a read-only table, you should do so.

 

·         Remember sorting - By default all sorting imposed by order by and group by operations should be done on the local database to minimize network traffic.  

 

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational