环境描述


10.135.175.133  LLTEST
10.135.175.136  TEST




1.TEST 数据库执行


SQL> create table system.t1 as select * from dba_objects;


Table created.


insert into system.t1 select * from system.t1;  构造500万数据




SQL> create table system.t2 as select * from system.t1;


Table created.


SQL> create table system.t3 as select * from system.t1;


Table created.

2.LLTEST 数据库执行




SQL> create table system.mm as select * from dba_objects where object_id=1;


Table created.


SQL> create public database link l1 connect to system  identified by oracle using 'test';


Database link created.


SQL> create public database link l2 connect to system  identified by oracle using 'test';


Database link created.


SQL> create public database link l3 connect to system  identified by oracle using 'test';


Database link created.



由于机器少,我把 l1,l2,l3 分别当作3台机器


3.进行一次查询


两边数据库执行


select 
   /*+ gather_plan_statistics */ count(*) 
  from 
     system.mm 
  left join system.t1@l1 t1 on mm.object_id=t1.object_id 
  left join system.t2@l2 t2 on mm.object_id=t2.object_id
  left join system.t3@l3 t3 on mm.object_id=t3.object_id;






PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34c91fa8a4m3b, child number 0
-------------------------------------
select    /*+ gather_plan_statistics */ count(*)   from      system.mm
 left join system.t1@l1 t1 on mm.object_id=t1.object_id   left join
system.t2@l2 t2 on mm.object_id=t2.object_id   left join system.t3@l3
t3 on mm.object_id=t3.object_id


Plan hash value: 3074645921


------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |      1 |00:00:00.01 |       1 |      1 |       |       |          |
|   1 |  SORT AGGREGATE         |      |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |       |       |          |
|*  2 |   HASH JOIN OUTER       |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  211K (0)|
|   3 |    VIEW                 |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|*  4 |     HASH JOIN OUTER     |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  177K (0)|
|   5 |      VIEW               |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|*  6 |       HASH JOIN OUTER   |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  153K (0)|
|   7 |        TABLE ACCESS FULL| MM   |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|   8 |        REMOTE           | T1   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   9 |      REMOTE             | T2   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  10 |    REMOTE               | T3   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("MM"."OBJECT_ID"="T3"."OBJECT_ID")
   4 - access("MM"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("MM"."OBJECT_ID"="T1"."OBJECT_ID")




32 rows selected.




4.使用 driving_site 进行一次查询


两边数据库执行
alter system flush buffer_cache;
alter system flush shared_pool;


select 
   /*+ gather_plan_statistics +driving_site(t1) +driving_site(t2) +driving_site(t3) */ count(*) 
  from 
     system.mm 
  left join system.t1@l1 t1 on mm.object_id=t1.object_id 
  left join system.t2@l2 t2 on mm.object_id=t2.object_id
  left join system.t3@l3 t3 on mm.object_id=t3.object_id;




                



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  23q1t97v6w0fh, child number 0
-------------------------------------
select    /*+ gather_plan_statistics +driving_site(t1)
+driving_site(t2) +driving_site(t3) */ count(*)   from      system.mm
left join system.t1@l1 t1 on mm.object_id=t1.object_id   left join
system.t2@l2 t2 on mm.object_id=t2.object_id   left join system.t3@l3
t3 on mm.object_id=t3.object_id


Plan hash value: 3074645921


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |      1 |00:00:00.01 |       1 |      1 |       |       |          |
|   1 |  SORT AGGREGATE         |      |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |       |       |          |
|*  2 |   HASH JOIN OUTER       |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  145K (0)|
|   3 |    VIEW                 |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|*  4 |     HASH JOIN OUTER     |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  201K (0)|
|   5 |      VIEW               |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|*  6 |       HASH JOIN OUTER   |      |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |  1035K|  1035K|  203K (0)|
|   7 |        TABLE ACCESS FULL| MM   |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |       |       |          |
|   8 |        REMOTE           | T1   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   9 |      REMOTE             | T2   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  10 |    REMOTE               | T3   |      0 |   9736K|      0 |00:00:00.01 |       0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("MM"."OBJECT_ID"="T3"."OBJECT_ID")
   4 - access("MM"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("MM"."OBJECT_ID"="T1"."OBJECT_ID")




33 rows selected.




单从执行计划中,的确看不出 driving_site 的优势.大家有兴趣可以自己研究一下.