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!
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 HintThis 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 HintAnother 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 ViewsYou 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.