Oracle hint之DRIVING_SITE
Oracle hint之DRIVING_SITE
日常工作中经常会用到分布式数据库查询，即通过DBLINK同时查询本地表和远程表。分布式查询一般有两种处理方式：一种将远程表数据取回本地，然后和本地表关联查询，获取最终结果；另一种将本地表数据传到远程和远程表关联查询后，再将关联结果取回。前一种处理方式可理解为只有一次网络传输操作比后一种少，也就作为了数据库的默认处理方式；driving_site提示能够指定执行计划在远程还是本地做，使用driving_site，特别是本地小结果集，远程大结果集，最终结果集较小时，希望计划在远程驱动，这样远程执行完毕，将结果集传输到本地，避免了大结果集的网络传输，从而达到整体优化的效果。
但是注意对于DML，DDL语句，driving_site提示是失效的，会自动被ORACLE忽略掉，此时将以目标表所在库为主计划驱动，相当于driving_site(目标表库)；DML，DDL中如果是对本地表做DML，主计划总是在本地做，会将远程数据拉到本地，相当于driving_site(本地表)；如果是对远程表做DML，主计划总是在远程做，会将本地数据送到远程，相当于自动driving_site(远程表)。
1. driving_site优化，以减少总体的网络传输数据量为目标；
2. 当driving_site驱动的对象嵌套在视图中时，可通过driving_site(V.T)方式来指定，其中V表示视图别名或名称，T表示视图里表的别名或名称；
3. DML，DDL语句中driving_site提示无效，总是会以目标表所在的库为主计划驱动，此时可以通过视图转换来达到优化目的。
这个提示在分布式数据库操作中有用，指定表处理连接所在的位置。可以限制通过网络处理的信息量。此外，还可以建立远程表的本地视图来限制从远程站点检索的行。本地视图应该有where子句，从而视图可以在将行发送回本地数据库之前限制从远程数据库返回的行。
driving_site用于分布式查询中，指定数据集发送到那个数据库上执行。在某些情况下可以大大提高SQL的性能。下面是一个小测试：
1. 在远程数据库上，创建测试表test_remote
|
pd@DWTEST>create table test_remote
2 as
3 select rownum rn, a.* from user_objects a;
Table created.
pd@DWTEST>insert into test_remote select * from test_remote;
10 rows created.
pd@DWTEST>/
20 rows created.
pd@DWTEST>/
40 rows created.
pd@DWTEST>/
...
655360 rows created.
pd@DWTEST>update test_remote set rn = rownum;
1310720 rows updated.
pd@DWTEST>commit;
Commit complete.
|
2. 在本地数据库，创建测试表test_local：
|
C:/Documents and Settings/yuechao.tianyc>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 4月 29 14:37:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table test_local purge;
表已删除。
SQL> create table test_local
2 as
3 select rownum rn, a.* from user_objects a;
表已创建。
SQL> insert into test_local select * from test_local;
已创建48行。
SQL> /
...
已创建768行。
SQL> update test_local set rn = rownum;
已更新1536行。
SQL> commit;
提交完成。
|
3. 通过driving_site，比较数据在远程和本地执行速度的不同：
|
-- 1. 直接执行SQL，耗时0.93m，通过执行计划发现是将远程表test_remote拉到本地后执行hash join的。
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 100
SQL> explain plan for
2 select count(*) from test_local l, test_remote@to_s12 r
3 where l.rn = r.rn;
已解释。
已用时间: 00: 00: 00.00
SQL> select count(*) from test_local l, test_remote@to_s12 r
2 where l.rn = r.rn;
COUNT(*)
----------
1536
已用时间: 00: 00: 00.93
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2814429697
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 10 (10)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 2 | HASH JOIN | | 327 | 8502 | 10 (10)| 00:00:01 | | |
| 3 | REMOTE | TEST_REMOTE | 327 | 4251 | 2 (0)| 00:00:01 | TO_S12 | R->S |
| 4 | TABLE ACCESS FULL| TEST_LOCAL | 1536 | 19968 | 7 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."RN"="R"."RN")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "RN" FROM "TEST_REMOTE" "R" (accessing
'TO_S12.REGRESS.RDBMS.DEV.US.ORACLE.COM' )
Note
-----
- dynamic sampling used for this statement
已选择27行。
已用时间: 00: 00: 00.01
-- 2. 通过driving_site，将本地表test_local发送到远程执行，再将结果集返回本地。耗时0.34m
SQL> select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
2 where l.rn = r.rn;
COUNT(*)
----------
1536
已用时间: 00: 00: 00.34
SQL> explain plan for
2 select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
3 where l.rn = r.rn;
已解释。
已用时间: 00: 00: 00.14
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3396146028
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 1 | 26 | 4970 (1)| 00:01:00 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 2 | HASH JOIN | | 327 | 8502 | 4970 (1)| 00:01:00 | | |
| 3 | REMOTE | TEST_LOCAL | 327 | 4251 | 3 (0)| 00:00:01 | ! | R->S |
| 4 | TABLE ACCESS FULL | TEST_REMOTE | 1130K| 14M| 4961 (1)| 00:01:00 | DWTEST | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A2"."RN"="A1"."RN")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "RN" FROM "TEST_LOCAL" "A2" (accessing '!' )
Note
-----
- fully remote statement
- dynamic sampling used for this statement
已选择27行。
已用时间: 00: 00: 00.01
|
4. 通过上面的测试，可以发现二者的执行时间是不一样的。我们再将二者分别执行100次，比较一下平均时间：
|
-- 将远程表拉到本地执行，耗时65.71（单位为1/100秒）
SQL> set serveroutput on
SQL> declare
2 n_count number;
3 n_begin_time number;
4 n_sum_time number;
5 begin
6 n_sum_time := 0;
7 for n_loop in 1..100 loop
8 n_begin_time := dbms_utility.get_cpu_time;
9 select count(*)
10 into n_count
11 from test_local l, test_remote@to_s12 r
12 where l.rn = r.rn;
13 n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
14 end loop;
15 dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
16 end;
17 /
avg cpu_time:65.71
PL/SQL 过程已成功完成。
已用时间: 00: 01: 28.39
-- 将本地表发送到远程执行，再将结果返回到本地，耗时0.05（单位为1/100秒）
SQL> declare
2 n_count number;
3 n_begin_time number;
4 n_sum_time number;
5 begin
6 n_sum_time := 0;
7 for n_loop in 1..100 loop
8 n_begin_time := dbms_utility.get_cpu_time;
9 select/*+driving_site(r)*/ count(*)
10 into n_count
11 from test_local l, test_remote@to_s12 r
12 where l.rn = r.rn;
13 n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
14 end loop;
15 dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
16 end;
17 /
avg cpu_time:.05
PL/SQL 过程已成功完成。
已用时间: 00: 00: 23.14
|
5. 结论
在分布式查询中，当一张表比较小，而且最终得到的结果集也比较小的话，使用driving_site将小表发送到大表端执行是比较快的。
About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-03-24 09:00 ~ 2017-03-24 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
driving_site用于分布式查询中，指定数据集发送到那个数据库上执行。在某些情况下可以大大提高SQL的性能。下面是一个小测试：
1. 在远程数据库上，创建测试表test_remote
|
pd@DWTEST>create table test_remote
2 as
3 select rownum rn, a.* from user_objects a;
Table created.
pd@DWTEST>insert into test_remote select * from test_remote;
10 rows created.
pd@DWTEST>/
20 rows created.
pd@DWTEST>/
40 rows created.
pd@DWTEST>/
...
655360 rows created.
pd@DWTEST>update test_remote set rn = rownum;
1310720 rows updated.
pd@DWTEST>commit;
Commit complete.
|
2. 在本地数据库，创建测试表test_local：
|
C:/Documents and Settings/yuechao.tianyc>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 4月 29 14:37:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table test_local purge;
表已删除。
SQL> create table test_local
2 as
3 select rownum rn, a.* from user_objects a;
表已创建。
SQL> insert into test_local select * from test_local;
已创建48行。
SQL> /
...
已创建768行。
SQL> update test_local set rn = rownum;
已更新1536行。
SQL> commit;
提交完成。
|
3. 通过driving_site，比较数据在远程和本地执行速度的不同：
|
-- 1. 直接执行SQL，耗时0.93m，通过执行计划发现是将远程表test_remote拉到本地后执行hash join的。
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 100
SQL> explain plan for
2 select count(*) from test_local l, test_remote@to_s12 r
3 where l.rn = r.rn;
已解释。
已用时间: 00: 00: 00.00
SQL> select count(*) from test_local l, test_remote@to_s12 r
2 where l.rn = r.rn;
COUNT(*)
----------
1536
已用时间: 00: 00: 00.93
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2814429697
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 10 (10)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 2 | HASH JOIN | | 327 | 8502 | 10 (10)| 00:00:01 | | |
| 3 | REMOTE | TEST_REMOTE | 327 | 4251 | 2 (0)| 00:00:01 | TO_S12 | R->S |
| 4 | TABLE ACCESS FULL| TEST_LOCAL | 1536 | 19968 | 7 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."RN"="R"."RN")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "RN" FROM "TEST_REMOTE" "R" (accessing
'TO_S12.REGRESS.RDBMS.DEV.US.ORACLE.COM' )
Note
-----
- dynamic sampling used for this statement
已选择27行。
已用时间: 00: 00: 00.01
-- 2. 通过driving_site，将本地表test_local发送到远程执行，再将结果集返回本地。耗时0.34m
SQL> select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
2 where l.rn = r.rn;
COUNT(*)
----------
1536
已用时间: 00: 00: 00.34
SQL> explain plan for
2 select/*+driving_site(r)*/ count(*) from test_local l, test_remote@to_s12 r
3 where l.rn = r.rn;
已解释。
已用时间: 00: 00: 00.14
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3396146028
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 1 | 26 | 4970 (1)| 00:01:00 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 2 | HASH JOIN | | 327 | 8502 | 4970 (1)| 00:01:00 | | |
| 3 | REMOTE | TEST_LOCAL | 327 | 4251 | 3 (0)| 00:00:01 | ! | R->S |
| 4 | TABLE ACCESS FULL | TEST_REMOTE | 1130K| 14M| 4961 (1)| 00:01:00 | DWTEST | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A2"."RN"="A1"."RN")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "RN" FROM "TEST_LOCAL" "A2" (accessing '!' )
Note
-----
- fully remote statement
- dynamic sampling used for this statement
已选择27行。
已用时间: 00: 00: 00.01
|
4. 通过上面的测试，可以发现二者的执行时间是不一样的。我们再将二者分别执行100次，比较一下平均时间：
|
-- 将远程表拉到本地执行，耗时65.71（单位为1/100秒）
SQL> set serveroutput on
SQL> declare
2 n_count number;
3 n_begin_time number;
4 n_sum_time number;
5 begin
6 n_sum_time := 0;
7 for n_loop in 1..100 loop
8 n_begin_time := dbms_utility.get_cpu_time;
9 select count(*)
10 into n_count
11 from test_local l, test_remote@to_s12 r
12 where l.rn = r.rn;
13 n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
14 end loop;
15 dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
16 end;
17 /
avg cpu_time:65.71
PL/SQL 过程已成功完成。
已用时间: 00: 01: 28.39
-- 将本地表发送到远程执行，再将结果返回到本地，耗时0.05（单位为1/100秒）
SQL> declare
2 n_count number;
3 n_begin_time number;
4 n_sum_time number;
5 begin
6 n_sum_time := 0;
7 for n_loop in 1..100 loop
8 n_begin_time := dbms_utility.get_cpu_time;
9 select/*+driving_site(r)*/ count(*)
10 into n_count
11 from test_local l, test_remote@to_s12 r
12 where l.rn = r.rn;
13 n_sum_time := n_sum_time + (dbms_utility.get_cpu_time - n_begin_time);
14 end loop;
15 dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
16 end;
17 /
avg cpu_time:.05
PL/SQL 过程已成功完成。
已用时间: 00: 00: 23.14
|
5. 结论
在分布式查询中，当一张表比较小，而且最终得到的结果集也比较小的话，使用driving_site将小表发送到大表端执行是比较快的。
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
