再議包含DBLINK的查詢優化

space6212發表於2019-05-31
優化
包含DBLINK的查詢有兩種：
1、所有的表都是DBLINK得到資料的遠端表
2、既包含本地表，又包含遠端表

在http://space6212.itpub.net/post/12157/304213中提到如果所有表都是遠端表的話，那麼該語句在遠端執行，在得到結果後返回撥用端。但是隨便找了一個機器上做了幾個測試就匆匆下結論了，今天遇到一個案例，推翻了這個結論。

SQL> SELECT A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,TEST.ORD_HIT_COMM@TEST.US.ORACLE.COM B WHERE A.HIT_COMM_ID=B.RECORD_ID
UNION ALL
SELECT
A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,
(SELECT *
FROM (SELECT B.RECORD_ID,
B.PRODUCT_ID,
B.SOURCE_ID,
ROW_NUMBER() OVER(PARTITION BY RECORD_ID ORDER BY B.CREATE_DATE DESC) RN
FROM TEST.ORD_LOG_HIT_COMM@TEST.US.ORACLE.COM B) WHERE RN = 1) B
2 3 4 5 6 7 8 9 10 11 WHERE A.HIT_COMM_ID = B.RECORD_ID;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34580 Card=13861 Byt
es=3589029)

1 0 UNION-ALL
2 1 NESTED LOOPS (Cost=19433 Card=6472 Bytes=1630944)
3 2 REMOTE* (Cost=17 Card=6472 Bytes=1126128) TEST.U
S.ORACLE
.COM

4 2 REMOTE* (Cost=3 Card=1 Bytes=78) TEST.U
S.ORACLE
.COM

5 1 MERGE JOIN (Cost=15147 Card=7389 Bytes=1958085)
6 5 VIEW (Cost=14963 Card=566129 Bytes=51517739)
7 6 WINDOW (SORT PUSHED RANK) (Cost=14963 Card=566129 By
tes=49253223)

8 7 REMOTE* (Cost=7366 Card=566129 Bytes=49253223) TEST.U
S.ORACLE
.COM

9 5 SORT (JOIN) (Cost=184 Card=6472 Bytes=1126128)
10 9 REMOTE* (Cost=17 Card=6472 Bytes=1126128) TEST.U
S.ORACLE
.COM

3 SERIAL_FROM_REMOTE SELECT "ID","HIT_COMM_ID","LAST_HIT_PRICE","
HIT_PRICE","LAST_RETAIL_PRICE","RETA

4 SERIAL_FROM_REMOTE SELECT "RECORD_ID","PRODUCT_ID","SOURCE_ID"
FROM "TEST"."ORD_HIT_COMM" "B" WHE

8 SERIAL_FROM_REMOTE SELECT "RECORD_ID","PRODUCT_ID","SOURCE_ID",
"CREATE_DATE" FROM "TEST"."ORD_LOG

10 SERIAL_FROM_REMOTE SELECT "ID","HIT_COMM_ID","LAST_HIT_PRICE","
HIT_PRICE","LAST_RETAIL_PRICE","RETA
這是一個真實的案例，只是把使用者名稱替換稱TEST。
從執行計劃可以看出，這個語句是把資料從遠端拉到本地再進行過濾的，由於源表比較大，使得這個查詢執行起來非常緩慢。測試了一下，按照這種方式，需要50分鐘才能出來結果。
通過trace看到，時間都消耗在網路傳輸上了：
WAIT #14: nam='SQL*Net more data from dblink' ela= 20 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 22 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 24 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 2077 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 18 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 23 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 21 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 4157 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 20 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 19 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 23 p1=675562835 p2=16 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=16 p3=0

幸運的是，這個語句返回結果很少，我們可以通過把驅動庫設定為遠端庫的方式減少網路消耗，提高效率。

SQL> SELECT /*+ driving_site(a) */ A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,TEST.ORD_HIT_COMM@TEST.US.ORACLE.COM B WHERE A.HIT_COMM_ID=B.RECORD_ID
UNION ALL
SELECT
2 3 4 A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,
(SELECT *
5 6 FROM (SELECT B.RECORD_ID,
B.PRODUCT_ID,
B.SOURCE_ID,
7 8 9 ROW_NUMBER() OVER(PARTITION BY RECORD_ID ORDER BY B.CREATE_DATE DESC) RN
FROM TEST.ORD_LOG_HIT_COMM@TEST.US.ORACLE.COM B) WHERE RN = 1) B
WHERE A.HIT_COMM_ID = B.RECORD_ID 10 11 ;

5638 rows selected.

Elapsed: 00:01:08.38

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=34344 Card=
13861 Bytes=3569613)

1 0 UNION-ALL
2 1 NESTED LOOPS (Cost=19433 Card=6472 Bytes=1611528)
3 2 TABLE ACCESS (FULL) OF 'ORD_HIT_PRICE_HISTORY' (Cost=1 TEST.U
7 Card=6472 Bytes=1126128) S.ORACLE
.COM

4 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ORD_HIT_COMM' TEST.U
(Cost=3 Card=1 Bytes=75) S.ORACLE
.COM

5 4 INDEX (UNIQUE SCAN) OF 'PK_ORD_HIT_COMM' (UNIQUE) (C TEST.U
ost=2 Card=1) S.ORACLE
.COM

6 1 MERGE JOIN (Cost=14911 Card=7389 Bytes=1958085)
7 6 VIEW (Cost=14721 Card=566129 Bytes=51517739)
8 7 WINDOW (SORT PUSHED RANK) (Cost=14721 Card=566129 By
tes=46422578)

9 8 PARTITION RANGE (ALL)
10 9 TABLE ACCESS (FULL) OF 'ORD_LOG_HIT_COMM' (Cost= TEST.U
7366 Card=566129 Bytes=46422578) S.ORACLE
.COM

11 6 SORT (JOIN) (Cost=190 Card=6472 Bytes=1126128)
12 11 TABLE ACCESS (FULL) OF 'ORD_HIT_PRICE_HISTORY' (Cost TEST.U
=17 Card=6472 Bytes=1126128) S.ORACLE
.COM

修改後，只需要1分多鐘SQL就執行完了。

從SELECT STATEMENT (REMOTE)也可以看出，修改後的SQL語句是先在遠端庫得到結果後再返回給發起端的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63844/，如需轉載，請註明出處，否則將追究法律責任。

相關文章