ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 再议包含DBLINK的查询优化

再议包含DBLINK的查询优化

原创 Linux操作系统 作者:space6212 时间:2019-05-31 13:33:04 0 删除 编辑
包含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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    193
  • 访问量
    142422