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群，学习最实用的数据库技术。

   

时间： 2024-08-24 04:27:41

Oracle hint之DRIVING_SITE的相关文章

SQL优化过程中常见Oracle HINT的用法

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势

作者介绍 韩锋,宜信技术研发中心数据库架构师.精通多种关系型数据库,曾任职于当当网.TOM在线等公司,曾任多家公司首席DBA.数据库架构师等职,多年一线数据库架构.设计.开发经验.著有<SQL优化最佳实践>一书.   一.提示(Hint)概述   1为什么引入Hint?  Hint是Oracle数据库中很有特色的一个功能,是很多DBA优化中经常采用的一个手段.那为什么Oracle会考虑引入优化器呢?基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻DBA的负担.   但有时

【DBAplus】SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势

原创 2016-09-12 韩锋  作者介绍 韩锋,宜信技术研发中心数据库架构师.精通多种关系型数据库,曾任职于当当网.TOM在线等公司,曾任多家公司首席DBA.数据库架构师等职,多年一线数据库架构.设计.开发经验.著有<SQL优化最佳实践>一书. 一.提示(Hint)概述 1为什么引入Hint? Hint是Oracle数据库中很有特色的一个功能,是很多DBA优化中经常采用的一个手段.那为什么Oracle会考虑引入优化器呢?基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻

常见Oracle HINT的用法

转自 http://www.hackhome.com/InfoView/Article_166086.html    1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小

小麦苗BLOG文章索引

小麦苗BLOG文章索引            自从2014年7月1号开始写blog到2015年5月5日,历时10个月的时间,大概写了90篇文章,这blog多了就乱了,今天抽空出来整理整理,方便大家也方便自己阅读,本文将一直更新,另外,最后我把所有的blog文章全列出来,可能会有用.    小麦苗的所有文章:itpub文章链接-小麦苗.zip     2015年06月03日更新一次,我写的blog数量:109 篇    2015年07月03日更新一次,我写的blog数量:126 篇    2016

Oracle原厂老兵：从负面案例看Hint的最佳使用方式

作者介绍 罗敏,从事Oracle技术研究.开发和服务工作20余年,在Oracle中国公司的10多年,分别在顾问咨询部.技术服务部担任资深技术顾问.曾参与国内银行.电信.政府等多个行业大型IT系统的建设和运维服务工作,为国内主要软件开发商和集成商进行过多场Oracle高级技术应用培训和交流活动.著有书籍<品悟性能优化>.<感悟Oracle核心技术>.<Oracle数据库技术服务案例精选>.   Oracle真灵活     某银行广泛采用了Oracle和IBM DB2两种数

ORACLE中的的HINT详解_oracle

hints是oracle提供的一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划.我们可以用hints来实现:   1) 使用的优化器的类型   2) 基于代价的优化器的优化目标,是all_rows还是first_rows.   3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid.   4) 表之间的连接类型   5) 表之间的连接顺序   6) 语句的并行程度   2.HINT可以基于以下规则产生作用   表连接的顺序.表连接的方法.访问路径.并行度   3.HINT

【微信公众号● DBAplus】Oracle原厂老兵：从负面案例看Hint的最佳使用方式

原创 2016-10-12 罗敏  Oracle原厂老兵:从负面案例看Hint的最佳使用方式 作者介绍 罗敏,从事Oracle技术研究.开发和服务工作20余年,在Oracle中国公司的10多年,分别在顾问咨询部.技术服务部担任资深技术顾问.曾参与国内银行.电信.政府等多个行业大型IT系统的建设和运维服务工作,为国内主要软件开发商和集成商进行过多场Oracle高级技术应用培训和交流活动.著有书籍<品悟性能优化>.<感悟Oracle核心技术>.<Oracle数据库技术服务案例精选

Oracle数据库中SQL语句的优化技巧_oracle

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.