Oracle 如何修复远程sql的执行计划

我们通过数据库链接使用了很多远程sql。

有没有办法修复执行计划？

sql基线，sqlprofile，大纲是否通过数据库链接为远程sql语句工作？谢谢!

使用DB链接的问题之一是，当连接本地表和远程表时，Oracle数据库将在本地站点进行连接。这可能导致通过网络传输大量不必要的数据。

例如，让我们在远程站点上创建两个1,000行表。但是这些只有10行的共同点:

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);
复制

我们会把这些结合在一起。以及本地站点上的另一个1,000行表。此表与两个遥控器有500行重叠:

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);
复制

所以将所有表连接在一起只会返回10行。执行此操作时，Oracle数据库通过链接发送来自远程数据库的所有行。然后在本地站点加入:

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")
复制

根据您的网络质量，这可能会降低您的查询速度。特别是如果你的表是 “大的”。最好在远程站点加入T1和T2。然后将10行结果发送到本地数据库。

幸运的是，有一些技术可以帮助你。

Driving Site Hint

这指示Oracle数据库在远程站点执行查询。在提示中放置要运行查询的站点上的表的名称或别名:

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;
复制

这一切都很好。但是现在你找不到执行计划了!

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)
复制

嗯。没有执行计划!那是因为它的所有详细信息都在far数据库中!

所以要找到这个，你需要去远程站点。不幸的是，在通过链接发送它的过程中，你会发现你的SQL转换为这样的东西:

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_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 
复制

好吧，你有计划了。但是rowstats不见了!如注释所述，您需要将statistics_level设置为all。

但是我们一开始不是就这么做了吗？

是的。但仅在本地数据库上。你也需要在远程数据库上这样做。一种方法是在远程数据库上创建以下过程:

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

然后在执行查询之前通过DB链接调用它:

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;
复制

现在，您可以使用行统计信息等在far DB上获得计划:

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) 
复制

所以我们越来越好。我们只通过网络发送了1,010行。1,000从T3和10的结果再次回来。但这仍然是相当多的。在某些情况下，不明显哪个DB将发送更多行和/或它将根据绑定值进行更改。

幸运的是我们可以做得更好。

No Merge Hint

您可以采取的另一种方法是确保Oracle数据库联接每个站点的所有表。然后将结果发送到驾驶站点以根据需要加入。您可以使用不可合并的子查询来执行此操作。

你是怎么做到的？

带有no_merge提示!

当这样做时，我喜欢使用与子句。在自己的no_merged查询中加入每个站点的表。然后结合结果:

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")  
复制

最后，我们只能通过链接发送10行!

以上方法可以提供帮助。但是有时候，无论您如何处理，都会有大量数据要通过有线发送。在这种情况下，最好在本地站点上获得结果。

同样，有一个简单的方法来做到这一点:

Materialized Views

您可以在MV中计算联接的结果并将它们存储在本地，如下所示:

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;
复制

现在，您可以将其加入本地表。这将完全删除网络。并使您能够在MV上创建索引，而远程站点上可能不允许使用该索引。

不利的一面是，你现在需要考虑如何保持这些最新。如果你能让它们在提交你的黄金时快速刷新。否则你可能不得不做出一些妥协。

所以有一些建议可以让你开始。如果您需要更具体的帮助，请为您的查询分享执行计划。