再議包含DBLINK的查詢優化
包含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語句是先在遠端庫得到結果後再返回給發起端的。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢優化優化
- MySQL 的查詢優化MySql優化
- pgsql查詢優化之模糊查詢SQL優化
- HBase查詢優化優化
- Oracle in 查詢優化Oracle優化
- join 查詢優化優化
- MySQL查詢優化MySql優化
- 關於樹結構的查詢優化，及許可權樹的查詢優化優化
- mysql查詢優化檢查 explainMySql優化AI
- MySQL優化COUNT()查詢MySql優化
- EntityFramework優化：查詢WITH(NOLOCK)Framework優化
- EntityFramework優化：查詢效能Framework優化
- 優化sql查詢速度優化SQL
- 分頁查詢優化優化
- MySQL 慢查詢優化MySql優化
- KunlunDB 查詢優化（一）優化
- MySQL調優之查詢優化MySql優化
- TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理TiDB優化
- MySQL——優化巢狀查詢和分頁查詢MySql優化巢狀
- MySQL-效能優化-索引和查詢優化MySql優化索引
- exists與in子查詢優化優化
- 效能優化之分頁查詢優化
- MySQL索引與查詢優化MySql索引優化
- APP查詢圖片優化APP優化
- MySQL查詢優化利刃-EXPLAINMySql優化AI
- MySQL分頁查詢優化MySql優化
- 資料庫查詢優化資料庫優化
- 【資料庫】查詢優化之子連線優化資料庫優化
- TiDB 查詢優化及調優系列（二）TiDB 查詢計劃簡介TiDB優化
- MySQL查詢中分頁思路的優化BFMySql優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- 十七、Mysql之SQL優化查詢MySql優化
- 【PostgreSQL】 字首模糊查詢級優化SQL優化
- 【資料庫】MySQL查詢優化資料庫MySql優化
- Mysql 慢查詢優化實踐MySql優化
- KunlunDB查詢優化（三）排序下推優化排序
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- mysql查詢效能優化總結MySql優化