情况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
在dblink对象上使用全局并行是无效,应使用普通形式
最新推荐文章于 2023-05-04 21:16:27 发布