在dblink对象上使用全局并行是无效，应使用普通形式

最新推荐文章于 2024-04-29 14:21:15 发布
launch_225
最新推荐文章于 2024-04-29 14:21:15 发布
阅读量2.7k
点赞数
本文链接：https://blog.csdn.net/launch_225/article/details/20556803
版权
情况1：普通的未加/*+ DRIVING_SITE() */HINT,耗时最长40秒；
select t2.status,t1.object_type
from t2,aiki.t1@dblk_spcom_to_crm t1
where t2.object_id=t1.object_id
and t2.object_id=123456;

Execution Plan
----------------------------------------------------------
Plan hash value: 2307048685

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   507 | 16731 |   278K  (1)| 00:55:44 |        |      |
|*  1 |  HASH JOIN         |      |   507 | 16731 |   278K  (1)| 00:55:44 |        |      |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     9 |     3   (0)| 00:00:01 |        |      |
|   3 |   REMOTE           | T1   |  1013 | 24312 |   278K  (1)| 00:55:44 | DBLK_~ | R->S |
-------------------------------------------------------------------------------------------

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

   1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
   2 - filter("T2"."OBJECT_ID"=123456)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "OBJECT_ID","OBJECT_TYPE" FROM "AIKI"."T1" "T1" WHERE
       "OBJECT_ID"=123456 (accessing 'DBLK_SPCOM_TO_CRM' )



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      18219  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1024  rows processed

1024 rows selected.

Elapsed: 00:00:40.67




情况2：加/*+ DRIVING_SITE() */HINT,耗时36.76秒,稍快些,留意下T2的变化,以及新增的note内容；

select /*+ driving_site(t1) */ t2.status,t1.object_type
from t2,aiki.t1@dblk_spcom_to_crm t1
where t2.object_id=t1.object_id
and t2.object_id=123456;


1024 rows selected.

Elapsed: 00:00:36.76

Execution Plan
----------------------------------------------------------
Plan hash value: 1167035991

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |   507 | 15717 |   434K  (1)| 01:26:57 |        |      |
|*  1 |  HASH JOIN             |      |   507 | 15717 |   434K  (1)| 01:26:57 |        |      |
|   2 |   REMOTE               | T2   |     1 |    16 |     2   (0)| 00:00:01 |      ! | R->S |
|*  3 |   TABLE ACCESS FULL    | T1   |  1013 | 15195 |   434K  (1)| 01:26:57 | CXCSDB |      |
-----------------------------------------------------------------------------------------------

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

   1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   3 - filter("A1"."OBJECT_ID"=123456)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "OBJECT_ID","STATUS" FROM "T2" "A2" WHERE "OBJECT_ID"=123456 (accessing
       '!' )


Note
-----
   - fully remote statement


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      18837  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed
       
       
 第3种情况：采用parallel方式,时间变为32秒，又加快了些；      
       
SQL> select /*+ parallel(t1,8) driving_site(t1) */ t2.status,t1.object_type
  2  from t2,aiki.t1@dblk_spcom_to_crm t1
  3  where t2.object_id=t1.object_id
  4  and t2.object_id=123456;

1024 rows selected.

Elapsed: 00:00:32.90

Execution Plan
----------------------------------------------------------
Plan hash value: 4033577536

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|          |   507 | 15717 | 60304   (1)| 00:12:04 |        |      |            |
|   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)  | :TQ10001 |   507 | 15717 | 60304   (1)| 00:12:04 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN           |          |   507 | 15717 | 60304   (1)| 00:12:04 |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT        |          |       |       |            |          |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE        |          |     1 |    16 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND BROADCAST| :TQ10000 |     1 |    16 |     2   (0)| 00:00:01 | CXCSDB | S->P | BROADCAST  |
|   7 |        REMOTE          | T2       |     1 |    16 |     2   (0)| 00:00:01 |      ! | R->S |            |
|   8 |     PX BLOCK ITERATOR  |          |  1013 | 15195 | 60301   (1)| 00:12:04 |  Q1,01 | PCWC |            |
|*  9 |      TABLE ACCESS FULL | T1       |  1013 | 15195 | 60301   (1)| 00:12:04 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

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

   3 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   9 - filter("A1"."OBJECT_ID"=123456)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   7 - SELECT "OBJECT_ID","STATUS" FROM "T2" "A2" WHERE "OBJECT_ID"=123456 (accessing '!' )


Note
-----
   - fully remote statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      18837  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed


第4种情况，采用全局HINT写法，可以看到些写法对于远程的DBLINK对象是无效的

SQL> select /*+ parallel(8) driving_site(t1) */ t2.status,t1.object_type
  2  from t2,aiki.t1@dblk_spcom_to_crm t1
  3  where t2.object_id=t1.object_id
  4  and t2.object_id=123456;

1024 rows selected.

Elapsed: 00:00:38.70

Execution Plan
----------------------------------------------------------
Plan hash value: 1167035991

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |   507 | 15717 |   434K  (1)| 01:26:57 |        |      |
|*  1 |  HASH JOIN             |      |   507 | 15717 |   434K  (1)| 01:26:57 |        |      |
|   2 |   REMOTE               | T2   |     1 |    16 |     2   (0)| 00:00:01 |      ! | R->S |
|*  3 |   TABLE ACCESS FULL    | T1   |  1013 | 15195 |   434K  (1)| 01:26:57 | CXCSDB |      |
-----------------------------------------------------------------------------------------------

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

   1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   3 - filter("A1"."OBJECT_ID"=123456)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "OBJECT_ID","STATUS" FROM "T2" "A2" WHERE "OBJECT_ID"=123456 (accessing
       '!' )


Note
-----
   - fully remote statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      18837  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed
       
第5种情况，在远端T1表上建立索引，可以看到速度飞快，但同时也看到并行是用不上的；
SQL> create index idx_t1_object_id on t1(object_id);
    
SQL> select /*+ parallel(t1,8) driving_site(t1) */ t2.status,t1.object_type
  2  from t2,aiki.t1@dblk_spcom_to_crm t1
  3  where t2.object_id=t1.object_id
  4  and t2.object_id=123456;

1024 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 338167503

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |                  |   507 | 15717 |  1020   (0)| 00:00:13 |        |      |
|   1 |  MERGE JOIN CARTESIAN         |                  |   507 | 15717 |  1020   (0)| 00:00:13 |        |      |
|   2 |   REMOTE                      | T2               |     1 |    16 |     2   (0)| 00:00:01 |      ! | R->S |
|   3 |   BUFFER SORT                 |                  |  1013 | 15195 |  1018   (0)| 00:00:13 |        |      |
|   4 |    TABLE ACCESS BY INDEX ROWID| T1               |  1013 | 15195 |  1018   (0)| 00:00:13 | CXCSDB |      |
|*  5 |     INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |  1013 |       |     4   (0)| 00:00:01 | CXCSDB |      |
------------------------------------------------------------------------------------------------------------------

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

   5 - access("A1"."OBJECT_ID"=123456)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "OBJECT_ID","STATUS" FROM "T2" "A2" WHERE "OBJECT_ID"=123456 (accessing '!' )


Note
-----
   - fully remote statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      18837  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed
launch_225
关注
0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
在dblink对象上使用全局并行是无效，应使用普通形式

情况1：普通的未加/*+ DRIVING_SITE() */HINT,耗时最长40秒；select t2.status,t1.object_typefrom t2,aiki.t1@dblk_spcom_to_crm t1where t2.object_id=t1.object_idand t2.object_id=123456;Execution Plan--------------
复制链接

扫一扫