Skip to Main Content
  • Questions

  • How to fix the execution plan for remote sql

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, prayer.

Asked: July 26, 2017 - 5:11 pm UTC

Last updated: November 02, 2023 - 5:03 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

We used a lot of remote sql through database links.

Is there a way to fix the execution plan?

Is sql baseline, sqlprofile, outline working for remote sql statements through database links? Thanks!

and Chris said...

One of the issues with using DB links is that when joining local and remote tables, Oracle Database will do the joins at the local site. This can lead to transferring a lot of unnecessary data over the network.

For example, let's create two 1,000 row tables on the remote site. But these only have 10 rows in common:

create table t1 as
  select rownum x from dual connect by level <= 1000;

create table t2 as
  select rownum+990 x from dual connect by level <= 1000;
  
create index i1 on t1 (x);
create index i2 on t2 (x);


We'll join these together. Along with another 1,000 row table at the local site. This table has a 500 row overlap with the two remotes:

create table t3 as
  select rownum+500 x, lpad('x', 20, 'x') stuff 
  from   dual connect by level <= 1000;
  
create index i3 on t3 (x);


So joining all the tables together only returns 10 rows. When you do this, Oracle Database sends all the rows from the remote DB over the link. Then joins at the local site:

alter session set statistics_level = all;
select /* STD_SQL */* 
from   t3
join   t1@db11204 t1
on     t1.x = t3.x
join   t2@db11204 t2
on     t2.x = t3.x;

select p.*
from v$sql s, table (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ROWSTATS LAST'
  )
) p
where s.sql_text like '%STD_SQL%'
and   s.sql_text not like '%not this%';

PLAN_TABLE_OUTPUT                                                     
SQL_ID  58khu9adzd83y, child number 0                                 
-------------------------------------                                 
select /* STD_SQL */*  from   t3 join   t1@db11204 t1 on     t1.x =   
t3.x join   t2@db11204 t2 on     t2.x = t3.x                          
                                                                      
Plan hash value: 4292527742                                           
                                                                      
---------------------------------------------------------------       
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |       
---------------------------------------------------------------       
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |       
|*  1 |  HASH JOIN          |      |      1 |    167 |     10 |       
|   2 |   REMOTE            | T2   |      1 |    409 |   1000 |       
|*  3 |   HASH JOIN         |      |      1 |    409 |   1000 |       
|   4 |    REMOTE           | T1   |      1 |    409 |   1000 |       
|   5 |    TABLE ACCESS FULL| T3   |      1 |   1000 |   1000 |       
---------------------------------------------------------------       
                                                                      
Predicate Information (identified by operation id):                   
---------------------------------------------------                   
                                                                      
   1 - access("T2"."X"="T3"."X")                                      
   3 - access("T1"."X"="T3"."X")


Depending on the quality of your network, this could slow your queries down a lot. Particularly if your tables are "large". It'd be better to join T1 and T2 at the remote site. Then send the 10 row result to the local database.

Fortunately there's some techniques that can help you.

Driving Site Hint

This instructs Oracle Database to execute the query at the remote site. Place the name or alias of the table at the site you want the query to run on in the hint:

select /*+ driving_site (t1) DRSITE */* from t3
join   t1@db11204 t1
on     t1.x = t3.x
join   t2@db11204 t2
on     t2.x = t3.x;


This is all very well and good. But now you can't find the execution plan!

select p.*
from   v$sql s, table (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ROWSTATS LAST'
  )
) p
where s.sql_text like '%DRSITE%'
and   s.sql_text not like '%not this%';

PLAN_TABLE_OUTPUT                                                                     
SQL_ID  av2tjpvcmygg7, child number 0                                                 
                                                                                      
select /*+ driving_site (t1) DRSITE */* from t3 join   t1@db11204 t1 on               
    t1.x = t3.x join   t2@db11204 t2 on     t2.x = t3.x                               
                                                                                      
NOTE: cannot fetch plan for SQL_ID: av2tjpvcmygg7, CHILD_NUMBER: 0                    
      Please verify value of SQL_ID and CHILD_NUMBER;                                 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


Hmmm. No execution plan! That's because all its details are on the far database!

So to find this you need to go to the remote site. Unfortunately in the process of sending it over the link, you'll find your SQL transformed to something like this:

select
  "A3"."X",  "A3"."STUFF",  "A2"."X",
  "A2"."Y",  "A1"."X",  "A1"."Y"
from  "T3"@! "A3",  "T1" "A2",  "T2" "A1"
where "A1"."X" = "A3"."X"
and   "A2"."X" = "A3"."X"


The comments/hints are gone and all the tables are fully qualified with quoted identifiers! This can make it tricky to find your query. But once you have you can get the plan on the remote DB:

PLAN_TABLE_OUTPUT                                                                  
SQL_ID  708by4kup2t8h, child number 0                                              
-------------------------------------                                              
SELECT "A3"."X","A3"."STUFF","A2"."X","A2"."Y","A1"."X","A1"."Y" FROM              
"T3"@! "A3","T1" "A2","T2" "A1" WHERE "A1"."X"="A3"."X" AND                        
"A2"."X"="A3"."X"                                                                  
                                                                                   
Plan hash value: 3189052467                                                        
                                                                                   
---------------------------------------------                                      
| Id  | Operation           | Name | E-Rows |                                      
---------------------------------------------                                      
|   0 | SELECT STATEMENT    |      |        |                                      
|*  1 |  HASH JOIN          |      |    654 |                                      
|*  2 |   HASH JOIN         |      |    654 |                                      
|   3 |    REMOTE           | T3   |    654 |                                      
|   4 |    TABLE ACCESS FULL| T1   |   1000 |                                      
|   5 |   TABLE ACCESS FULL | T2   |   1000 |                                      
---------------------------------------------                                      
                                                                                   
Predicate Information (identified by operation id):                                
---------------------------------------------------                                
                                                                                   
   1 - access("A1"."X"="A3"."X")                                                   
   2 - access("A2"."X"="A3"."X")                                                   
                                                                                   
Note                                                                               
-----                                                                              
   - dynamic sampling used for this statement (level=2)                            
   - Warning: basic plan statistics not available. These are only collected when:  
       * hint 'gather_plan_statistics' is used for the statement or                
       * parameter 'statistics_level' is set to 'ALL', at session or system level


OK, so you got the plan. But the rowstats are missing! As the note says, you need to set the statistics_level to all.

But didn't we do that right at the start?

Well yes. But only on the local DB. You need to do this on the remote DB too. One way to do this is to create the following procedure on the remote DB:

create or replace procedure set_stats as
begin
  execute immediate 'alter session set statistics_level = all';
end;
/


Then call it over the DB link before you execute your query:

exec set_stats@db11204;
select /*+ driving_site (t1) DRSITE */* from t3
join   t1@db11204 t1
on     t1.x = t3.x
join   t2@db11204 t2
on     t2.x = t3.x;


Now you can get the plan on the far DB with the row stats etc.:

SQL_ID  708by4kup2t8h, child number 1                                              
-------------------------------------                                              
SELECT "A3"."X","A3"."STUFF","A2"."X","A2"."Y","A1"."X","A1"."Y" FROM              
"T3"@! "A3","T1" "A2","T2" "A1" WHERE "A1"."X"="A3"."X" AND                        
"A2"."X"="A3"."X"                                                                  
                                                                                   
Plan hash value: 3189052467                                                        
                                                                                   
---------------------------------------------------------------                    
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |                    
---------------------------------------------------------------                    
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |                    
|*  1 |  HASH JOIN          |      |      1 |    654 |     10 |                    
|*  2 |   HASH JOIN         |      |      1 |    654 |   1000 |                    
|   3 |    REMOTE           | T3   |      1 |    654 |   1000 |                    
|   4 |    TABLE ACCESS FULL| T1   |      1 |   1000 |   1000 |                    
|   5 |   TABLE ACCESS FULL | T2   |      1 |   1000 |   1000 |                    
---------------------------------------------------------------                    
                                                                                   
Predicate Information (identified by operation id):                                
---------------------------------------------------                                
                                                                                   
   1 - access("A1"."X"="A3"."X")                                                   
   2 - access("A2"."X"="A3"."X")                                                   
                                                                                   
Note                                                                               
-----                                                                              
   - dynamic sampling used for this statement (level=2)


So we're getting better. We've only sent 1,010 rows over the network. 1,000 from T3 and the 10 results back again. But that's still quite a few. And there may be cases where it's not obvious which DB will send more rows and/or it will change depending on bind values.

Fortunately we can do better.

No Merge Hint

Another approach you can take is to ensure that Oracle Database joins all the tables at each site. Then sends the results to the driving site to join as needed. You can do this with non-mergeable subqueries.

How do you do that?

With the no_merge hint!

When doing this I love to use the with clause. Join the tables for each site in its own no_merged query. Then combine the results:

with remote as (
  select /*+ no_merge */t1.x x, t2.x t2x from t1@db11204 t1
  join   t2@db11204 t2
  on     t2.x = t1.x
)
  select /* NOT_MERGING */* from t3
  join   remote r
  on     t3.x = r.x;

select p.*
from v$sql s, table (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
where s.sql_text like '%NOT_MERGING%'
and   s.sql_text not like '%not this%';

PLAN_TABLE_OUTPUT                                                                      
SQL_ID  8s0f3v28cj0mh, child number 0                                                  
-------------------------------------                                                  
with remote as (   select /*+ no_merge */t1.x x, t2.x t2x from                         
t1@db11204 t1   join   t2@db11204 t2   on     t2.x = t1.x )   select /*                
NOT_MERGING */* from t3   join   remote r   on     t3.x = r.x                          
                                                                                       
Plan hash value: 1822593425                                                            
                                                                                       
-------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.02 |       8 |  
|*  1 |  HASH JOIN         |      |      1 |    409 |     10 |00:00:00.02 |       8 |  
|   2 |   VIEW             |      |      1 |    409 |     10 |00:00:00.01 |       0 |  
|   3 |    REMOTE          |      |      1 |        |     10 |00:00:00.01 |       0 |  
|   4 |   TABLE ACCESS FULL| T3   |      1 |   1000 |   1000 |00:00:00.01 |       8 |  
-------------------------------------------------------------------------------------  
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - access("T3"."X"="R"."X")


At last we're down to sending just 10 rows over the link!

The above methods can help. But there will be times when you've got stacks of data to send over the wire however you tackle it. In this case it can be better to have the results at the local site.

Again, there's an easy way to do this:

Materialized Views

You can compute the result of the join in an MV and store them locally like so:

create materialized view rem_mv as 
  select t1.x x, t2.x t2x from t1@db11204 t1
  join   t2@db11204 t2
  on     t2.x = t1.x;


Now you can join this to your local tables. This removes the network completely. And enables you to create indexes on the MV which you may not be allowed to on the remote site.

The downside is you now need to think about how you're going to keep these up-to-date. If you can make them fast refreshable on commit your golden. Otherwise you may have to make some compromises.

So there's some pointers to get you started. If you need more specific help, then please share the execution plans for your queries.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

prayer Huang, July 27, 2017 - 4:28 pm UTC

Thanks for the answer!!

We are using driving_site a lot depending on our understanding of the sql.

I asked this question but didn't see answer to it.

Is sql baseline, sqlprofile, outline working for remote sql statements through database links?

For local sqls, when we have performance issue, we get the good plan and implement baseline so future run will automatically take the good plan. But does baseline works on the remote sqls?

Also, from your description, it seems like hints is the only way we can tune remote sqls, right? ( we are not in the condition to create the materialized view for this moment).

I read about sql patch, if I want to change the remote sql without driving_site hint to pick up the plan using driving_site, what is the hint text I need to use in sql patch sys.dbms_sqldiag_internal.i_create_patch??
Chris Saxon
August 01, 2017 - 3:12 pm UTC

"Is sql baseline, sqlprofile, outline working for remote sql statements through database links? "

Yes and no ;)

As the docs say:

SQL management objects, such as SQL profiles, SQL plan baselines, and SQL patches, and stored outlines might not always work as expected if your query references remote tables with database links. For example, for SQL plan management, when Oracle uses a SQL plan baseline for the query, the parts of the query that are remotely executed might use a different plan than when the SQL plan baseline was created.

https://docs.oracle.com/database/121/ADMIN/ds_appdev.htm#ADMIN12197

So what does happen?

Let's see...

Repeat the tables above, but without the indexes on T1 & T2. Then run this query:

select /* Q995 */* from t3
join   t1@db11204 t1
on     t1.x = t3.x
join   t2@db11204 t2
on     t2.x = t3.x
where  t1.x = 995;


This only returns one row. So you'd like it to use indexes to access all the tables. To start with you get this plan:

SQL_ID  3y5kvz17dvwt4, child number 0
-------------------------------------
select /* Q995 */* from t3 join   t1@db11204 t1 on     t1.x = t3.x join 
  t2@db11204 t2 on     t2.x = t3.x where  t1.x = 995
 
Plan hash value: 3833295017
 
---------------------------------------------------------------
| Id  | Operation                             | Name | E-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |        |
|*  1 |  HASH JOIN                            |      |      3 |
|*  2 |   HASH JOIN                           |      |      2 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T3   |      1 |
|*  4 |     INDEX RANGE SCAN                  | I3   |      1 |
|   5 |    REMOTE                             | T1   |      2 |
|   6 |   REMOTE                              | T2   |      2 |
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T2"."X"="T3"."X")
   2 - access("T1"."X"="T3"."X")
   4 - access("T3"."X"=995)


So let's create a baseline for it on the local database:

set serveroutput on 
declare
  l_plans_loaded  pls_integer;
begin
  l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '3y5kvz17dvwt4');
    
  dbms_output.put_line('plans loaded: ' || l_plans_loaded);
end;
/

select sql_handle, substr(sql_text, 1, 50) from dba_sql_plan_baselines;

SQL_HANDLE            SUBSTR(SQL_TEXT,1,50)                               
SQL_2bd10d99dfd91eb5  select /* Q995 */* from t3
join   t1@db11204 t1
on


Then create an index on t1 on the remote database

create index i1 on t1(x);


So at this point we want it to have a new plan. But the baseline should stop it, right?

Not quite:

SQL_ID  3y5kvz17dvwt4, child number 1
-------------------------------------
select /* Q995 */* from t3 join   t1@db11204 t1 on     t1.x = t3.x join 
  t2@db11204 t2 on     t2.x = t3.x where  t1.x = 995
 
Plan hash value: 1051900850
 
----------------------------------------------------------------------------------
| Id  | Operation                     | Name | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |        |       |       |          |
|*  1 |  HASH JOIN                    |      |      3 |  1021K|  1021K|          |
|   2 |   MERGE JOIN CARTESIAN        |      |      2 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3   |      1 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I3   |      1 |       |       |          |
|   5 |    BUFFER SORT                |      |      2 |  2048 |  2048 | 2048  (0)|
|   6 |     REMOTE                    | T1   |      2 |       |       |          |
|   7 |   REMOTE                      | T2   |      2 |       |       |          |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T2"."X"="T3"."X")
   4 - access("T3"."X"=995)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


Note the lack of a note saying a baseline was used!

But you can use a baseline on the remote DB to lock its plan. Let's create a baseline for this query. Then index T2.

Again, we want the query to use the index. But expect the baseline to prevent it

And we see (on the remote database):

declare
  l_plans_loaded  pls_integer;
begin
  l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '8q7xwv3mv04tz');
    
  dbms_output.put_line('plans loaded: ' || l_plans_loaded);
end;
/

select sql_handle, substr(sql_text, 1, 50) from dba_sql_plan_baselines;

SQL_HANDLE            SUBSTR(SQL_TEXT,1,50)                    
SQL_d7fa641ef4acf23a  SELECT "X" FROM "T2" "T2" WHERE "X"=995 

create index i2 on t2(x);

-- run the query on the other DB, then ...

select p.*
from v$sql s, table (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
where s.sql_text like 'SELECT%"T2"%995%'
and   s.sql_text not like '%not this%';

SQL_ID  8q7xwv3mv04tz, child number 2                                              
-------------------------------------                                              
SELECT "X" FROM "T2" "T2" WHERE "X"=995                                            
                                                                                   
Plan hash value: 3093347570                                                        
                                                                                   
-------------------------------------------                                        
| Id  | Operation         | Name | E-Rows |                                        
-------------------------------------------                                        
|   0 | SELECT STATEMENT  |      |        |                                        
|*  1 |  TABLE ACCESS FULL| T2   |      2 |                                        
-------------------------------------------                                        
                                                                                   
Predicate Information (identified by operation id):                                
---------------------------------------------------                                
                                                                                   
   1 - filter("X"=995)                                                             
                                                                                   
Note                                                                               
-----                                                                              
   - SQL plan baseline SQL_PLAN_dgym43vuatwjub860bcf2 used for this statement      
   - Warning: basic plan statistics not available. These are only collected when:  
       * hint 'gather_plan_statistics' is used for the statement or                
       * parameter 'statistics_level' is set to 'ALL', at session or system level


The baseline is used!

So the TL;DR is:

You can use baselines on the remote DB to lock the plans. Just make sure you're baselining the correct query!

(using no_merge to join all the remote tables together will help with this)

HT to Tim Hall for the plan capture scripts

https://oracle-base.com/articles/11g/sql-plan-management-11gr1#manual_plan_loading

ELAPSED_TIME for queries over DB Links

Frank Olsen, October 26, 2023 - 2:58 pm UTC

Dear "Tom",

Maybe my question should be a new one, but it is closely related to this one which already provides a great discussion of performance of queries over DB Links for a part of it and I only wanted a hint on a related issue.

I had a bad query on one database (T) that I see coming over TNS from another database server (S), so probably over a database link.

On S I find one query that have several parts querying T.
In S in the execution plan I see:
VIEW ...
REMOTE
The columns queried over the DB Link seems to correspond.
However, on S the execution time (gathered from DBA_HIST_SQLSTAT.ELAPSED_TIME) is 25 seconds, but on T it is 125 seconds.

So, did I find the correct query on S? Or should the ELAPSED_TIME be that of S+T?

Thanks for your help.

Best regards,
Frank Olsen

Connor McDonald
November 02, 2023 - 5:03 am UTC

The elapsed time on the database that *originated* the query is the one you want to use.

It doesn't really matter where *parts* of the query were executed, because from the originating database's perpsective, that's no different to just a slow part of a query running locally, ie, it was just something that added to the response time.

So your elapsed time should be 125 seconds.

Of course, this does not mean that you can't spend attention on the query processing that ocurred on *either* node in terms of trying tune things to make it faster,
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.