分散式查詢優化SERIAL_FROM_REMOTE

在分散式查詢中，對於分佈在兩個節點上的兩張表進行巢狀迴圈連線將會非常慢，這是由於Oracle將
所有的遠端資料傳到本地來處理。所以對於這類查詢最好換成排序合併連線或者hash連線。

下面例子中的表aaa、bbb分別位於兩個庫,aaa所在庫通過資料庫鏈db244訪問bbb表：

create table aaa as select * from dba_objects;
create table bbb as select * from dba_objects;

1、巢狀迴圈連線

SQL> select count(*) from aaa a
where exists
(select * from bbb@db244 b where a.object_id=b.object_id);

COUNT(*)
----------
27165

Elapsed: 00:04:04.74

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'AAA'
4 2 REMOTE* DB244.US.ORACLE.COM
4 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B" WHERE "OBJECT_ID"=:1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
437 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2、排序合併連線

SQL> select count(*) from aaa a
where a.object_id in
(select object_id from bbb@db244 b);

COUNT(*)
----------
27165

Elapsed: 00:00:00.59

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'AAA'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 SORT (UNIQUE)
8 7 REMOTE* DB244.US.ORACLE.COM
8 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
437 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

3、通過提示改用hash連線

SQL> select /*+ use_hash(a,b) */count(*) from aaa a
where exists
(select * from bbb@db244 b
where a.object_id=b.object_id);

COUNT(*)
----------
27165

Elapsed: 00:00:00.51

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=91 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=91 Card=27172 Bytes=706472)
3 2 TABLE ACCESS (FULL) OF 'AAA' (Cost=47 Card=27172 Bytes=353236)

4 2 VIEW OF 'VW_SQ_1' (Cost=42 Card=32509 Bytes=422617)
5 4 REMOTE* (Cost=42 Card=32509 Bytes=422617)
DB244.US.ORACLE.COM
5 SERIAL_FROM_REMOTE SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
468 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:456820211101

Followup:
the query plan showed us that -- two remote operations followed by a filter.
gives us the remote query that was executed and all.

it is basically taking:

2 select tracknum, status, transdate, insseq
10:09:24 3 from workhistory@wkl
10:09:24 4 where tracknum in (select distinct tracknum from workhistory@wkl
where insseq = 142);

and executing it like this:


for x in ( select * from remote_table )
loop
query remote table again by tracknum to get tracknum and insseq

if one of the the returned insseq's is 142
then
output record
end if
end loop

Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=172346 Card=57009 By
tes=2052324)

1 0 FILTER
2 1 REMOTE* (Cost=1319 Card=57009 Bytes=2052324) WKL
3 1 REMOTE* (Cost=3 Card=1 Bytes=24) WKL


2 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "SYS_ALIAS_1"

3 SERIAL_FROM_REMOTE SELECT "TRACKNUM","INSSEQ" FROM "WORKHISTORY
" "WORKHISTORY" WHERE "TRACKNUM"=:1