ITPub博客

首页 > 数据库 > Oracle > 常见Oracle HINT的用法

常见Oracle HINT的用法

Oracle 作者:kiswind 时间:2013-12-13 13:14:05 0 删除 编辑
http://www.cnblogs.com/ebs-blog/archive/2011/09/05/2167730.html
在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*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
10. /*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';
15. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /*+NOWRITE*/
禁止对查询块的查询重写操作.
18. /*+REWRITE*/
可以将视图作为参数.
19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
将指定的表与其他行源通过哈希连接方式连接起来.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
强制与ORACLE所选择的位置不同的表进行查询执行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
将指定的表作为连接次序中的首表.
27. /*+CACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;








在使用Hint时需要注意的一点是,并非任何时刻Hint都起作用。 导致HINT 失效的原因有如下2点:

(1) 如果CBO 认为使用Hint 会导致错误的结果时,Hint将被忽略。

如索引中的记录因为空值而和表的记录不一致时,结果就是错误的,会忽略hint。

(2) 如果表中指定了别名,那么Hint中也必须使用别名,否则Hint也会忽略。

Select /*+full(a)*/ * from t a; -- 使用hint

Select /*+full(t) */ * from t a; --不使用hint

根据hint的功能,可以分成如下几类:

Hint Hint 语法
优化器模式提示 ALL_ROWS Hint
  FIRST_ROWS Hint
  RULE Hint
访问路径提示 CLUSTER Hint
  FULL Hint
  HASH Hint
  INDEX Hint
  NO_INDEX Hint
  INDEX_ASC Hint
  INDEX_DESC Hint
  INDEX_COMBINE Hint
  INDEX_FFS Hint
  INDEX_SS Hint
  INDEX_SS_ASC Hint
  INDEX_SS_DESC Hint
  NO_INDEX_FFS Hint
  NO_INDEX_SS Hint
  ORDERED Hint
  LEADING Hint
  USE_HASH Hint
  NO_USE_HASH Hint
表连接顺序提示 USE_MERGE Hint
  NO_USE_MERGE Hint
  USE_NL Hint
  USE_NL_WITH_INDEX Hint
  NO_USE_NL Hint
表关联方式提示 PARALLEL Hint
  NO_PARALLEL Hint
  PARALLEL_INDEX Hint
  NO_PARALLEL_INDEX Hint
  PQ_DISTRIBUTE Hint
并行执行提示 FACT Hint
  NO_FACT Hint
  MERGE Hint
  NO_MERGE Hint
  NO_EXPAND Hint
  USE_CONCAT Hint
查询转换提示 REWRITE Hint
  NO_REWRITE Hint
  UNNEST Hint
  NO_UNNEST Hint
  STAR_TRANSFORMATION Hint
  NO_STAR_TRANSFORMATION Hint
  NO_QUERY_TRANSFORMATION Hint
  APPEND Hint
  NOAPPEND Hint
  CACHE Hint
  NOCACHE Hint
  CURSOR_SHARING_EXACT Hint
其他Hint DRIVING_SITE Hint
  DYNAMIC_SAMPLING Hint
  PUSH_PRED Hint
  NO_PUSH_PRED Hint
  PUSH_SUBQ Hint
  NO_PUSH_SUBQ Hint
  PX_JOIN_FILTER Hint
  NO_PX_JOIN_FILTER Hint
  NO_XML_QUERY_REWRITE Hint
  QB_NAME Hint
  MODEL_MIN_ANALYSIS Hint

一. 和优化器相关的Hint

Oracle 允许在系统级别,会话级别和SQL中(hint)优化器类型:

系统级别:

 1: SQL>alter system set optimizer_mode=all_rows;

会话级别:

SQL>alter system set optimizer_mode=all_rows;

关于优化器,参考:

Oracle Optimizer CBO RBO

http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx

1.1 ALL_ROWS 和FIRST_ROWS(n) -- CBO 模式

对于OLAP系统,这种系统中通常都是运行一些大的查询操作,如统计,报表等任务。 这时优化器模式应该选择ALL_ROWS. 对于一些分页显示的业务,就应该用FIRST_ROWS(n)。 如果是一个系统上运行这两种业务,那么就需要在SQL 用hint指定优化器模式。

如:

SQL> select /* + all_rows*/ * from dave;

SQL> select /* + first_rows(20)*/ * from dave;

1.2 RULE Hint -- RBO 模式

尽管Oracle 10g已经弃用了RBO,但是仍然保留了这个hint。 它允许在CBO 模式下使用RBO 对SQL 进行解析。

如:

SQL> show parameter optimizer_mode

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode string ALL_ROWS

SQL> set autot trace exp;

SQL> select /*+rule */ * from dave;

执行计划

----------------------------------------------------------

Plan hash value: 3458767806

----------------------------------

| Id | Operation | Name |

----------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| DAVE |

----------------------------------

Note

-----

- rule based optimizer used (consider using cbo) -- 这里提示使用RBO

SQL>

二. 访问路径相关的Hint

这一部分hint 将直接影响SQL 的执行计划,所以在使用时需要特别小心。 该类Hint对DBA分析SQL性能非常有帮助,DBA 可以让SQL使用不同的Hint得到不同的执行计划,通过比较不同的执行计划来分析当前SQL性能。

2.1 FULL Hint

该Hint告诉优化器对指定的表通过全表扫描的方式访问数据。

示例:

SQL> select /*+full(dave) */ * from dave;

要注意,如果表有别名,在hint里也要用别名, 这点在前面已经说明。

2.2 INDEX Hint

Index hint 告诉优化器对指定的表通过索引的方式访问数据,当访问索引会导致结果集不完整时,优化器会忽略这个Hint。

示例:

SQL> select /*+index(dave index_dave) */ * from dave where id>1;

谓词里有索引字段,才会用索引。

2.3 NO_INDEX Hint

No_index hint 告诉优化器对指定的表不允许使用索引。

示例:

SQL> select /*+no_index(dave index_dave) */ * from dave where id>1;

2.4 INDEX_DESC Hint

该Hint 告诉优化器对指定的索引使用降序方式访问数据,当使用这个方式会导致结果集不完整时,优化器将忽略这个索引。

示例:

SQL> select /*+index_desc(dave index_dave) */ * from dave where id>1;

2.5 INDEX_COMBINE Hint

该Hint告诉优化器强制选择位图索引,当使用这个方式会导致结果集不完整时,优化器将忽略这个Hint。

示例:

SQL> select /*+ index_combine(dave index_bm) */ * from dave;

2.6 INDEX_FFS Hint

该hint告诉优化器以INDEX_FFS(INDEX Fast Full Scan)的方式访问数据。当使用这个方式会导致结果集不完整时,优化器将忽略这个Hint。

示例:

SQL> select /*+ index_ffs(dave index_dave) */ id from dave where id>0;

2.7 INDEX_JOIN Hint

索引关联,当谓词中引用的列上都有索引时,可以通过索引关联的方式来访问数据。

示例:

SQL> select /*+ index_join(dave index_dave index_bm) */ * from dave where id>0 and name='安徽安庆';

2.8 INDEX_SS Hint

该Hint强制使用index skip scan 的方式访问索引,从Oracle 9i开始引入这种索引访问方式,当在一个联合索引中,某些谓词条件并不在联合索引的第一列时(或者谓词并不在联合索引的第一列时),可以通过index skip scan 来访问索引获得数据。 当联合索引第一列的唯一值很小时,使用这种方式比全表扫描效率要高。当使用这个方式会导致结果集不完整时,优化器将忽略这个Hint。

示例:

SQL> select /*+ index_ss(dave index_union) */ * from dave where id>0;

三. 表关联顺序的Hint

表之间的连接方式有三种。 具体参考blog:

多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

3.1 LEADING hint

在一个多表关联的查询中,该Hint指定由哪个表作为驱动表,告诉优化器首先要访问哪个表上的数据。

示例:

SQL> select /*+leading(t1,t) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

SQL> select /*+leading(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00

| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00

|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00

| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00

--------------------------------------------------------------------------------

3.2 ORDERED Hint

该hint 告诉Oracle 按照From后面的表的顺序来选择驱动表,Oracle 建议在选择驱动表上使用Leading,它更灵活一些。

SQL> select /*+ordered */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

四. 表关联操作的Hint

4.1 USE_HASH,USE_NL,USE_MERGE hint

表之间的连接方式有三种。 具体参考blog:

多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

这三种关联方式是多表关联中主要使用的关联方式。 通常来说,当两个表都比较大时,Hash Join的效率要高于嵌套循环(nested loops)的关联方式。

Hash join的工作方式是将一个表(通常是小一点的那个表)做hash运算,将列数据存储到hash列表中,从另一个表中抽取记录,做hash运算,到hash 列表中找到相应的值,做匹配。

Nested loops 工作方式是从一张表中读取数据,访问另一张表(通常是索引)来做匹配,nested loops适用的场合是当一个关联表比较小的时候,效率会更高。

Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配,因为merge join需要做更多的排序,所以消耗的资源更多。 通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能。

USE_HASH,USE_NL,USE_MERGE 这三种hint 就是告诉优化器使用哪种关联方式。

示例如下:

SQL> select /*+use_hash(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

SQL> select /*+use_nl(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

SQL> select /*+use_merge(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

4.2 NO_USE_HASH,NO_USE_NL,NO_USE_MERGE HINT

分别禁用对应的关联方式。

示例:

SQL> select /*+no_use_merge(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

SQL> select /*+no_use_nl(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

SQL> select /*+no_use_hash(t,t1) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

五. 并行执行相关的Hint

5.1 PARALLEL HINT

指定SQL 执行的并行度,这个值会覆盖表自身设定的并行度,如果这个值为default,CBO使用系统参数值。

示例:

SQL> select /*+parallel(t 4) */ * from scott.dept t;

关于表的并行度,我们在创建表的时候可以指定,如:

SQL> CREATE TABLE Anqing

2 (

3 name VARCHAR2 (10)

4 )

5 PARALLEL 2;

表已创建。

SQL> select degree from all_tables where table_name = 'ANQING'; -- 查看表的并行度

DEGREE

--------------------

2

SQL> alter table anqing parallel(degree 3); -- 修改表的并行度

表已更改。

SQL> select degree from all_tables where table_name = 'ANQING';

DEGREE

--------------------

3

SQL> alter table anqing noparallel; -- 取消表的并行度

表已更改。

SQL> select degree from all_tables where table_name = 'ANQING';

DEGREE

--------------------

1

5.2 NO_PARALLEL HINT

在SQL中禁止使用并行。

示例:

SQL> select /*+ no_parallel(t) */ * from scott.dept t;

六. 其他方面的一些Hint

6.1 APPEND HINT

提示数据库以直接加载的方式(direct load)将数据加载入库。

示例:

Insert /*+append */ into t as select * from all_objects;

这个hint 用的比较多。 尤其在插入大量的数据,一般都会用此hint。

Oracle 插入大量数据

http://blog.csdn.net/tianlesoftware/archive/2009/10/30/4745144.aspx

6.2 DYNAMIC_SAMPLING HINT

提示SQL 执行时动态采样的级别。 这个级别从0-10,它将覆盖系统默认的动态采样级别。

示例:

SQL> select /*+ dynamic_sampling(t 2) */ * from scott.emp t where t.empno>0;

6.3 DRIVING_SITE HINT

这个提示在分布式数据库操作中比较有用,比如我们需要关联本地的一张表和远程的表:

Select /* + driving_site(departmetns) */ * from employees,departments@dblink where

employees .department_id = departments.department_id;

如果没有这个提示,Oracle 会在远端机器上执行departments 表查询,将结果送回本地,再和employees表关联。 如果使用driving_site(departments), Oracle将查询本地表employees,将结果送到远端,在远端将数据库上的表与departments关联,然后将查询的结果返回本地。

如果departments查询结果很大,或者employees查询结果很小,并且两张表关联之后的结果集很小,那么就可以考虑把本地的结果集发送到远端。 在远端执行完后,在将较小的最终结果返回本地。

6.4 CACHE HINT

在全表扫描操作中,如果使用这个提示,Oracle 会将扫描的到的数据块放到LRU(least recently Used: 最近很少被使用列表,是Oracle 判断内存中数据块活跃程度的一个算法)列表的最被使用端(数据块最活跃端),这样数据块就可以更长时间地驻留在内存当中。 如果有一个经常被访问的小表,这个设置会提高查询的性能;同时CACHE也是表的一个属性,如果设置了表的cache属性,它的作用和hint一样,在一次全表扫描之后,数据块保留在LRU列表的最活跃端。

示例:

SQL> select /*+full(t) cache (t) */ * from scott.emp;

小结:

对于DBA来讲,掌握一些Hint操作,在实际性能优化中有很大的好处,比如我们发现一条SQL的执行效率很低,首先我们应当查看当前SQL的执行计划,然后通过hint的方式来改变SQL的执行计划,比较这两条SQL 的效率,作出哪种执行计划更优,如果当前执行计划不是最优的,那么就需要考虑为什么CBO 选择了错误的执行计划。当CBO 选择错误的执行计划,我们需要考虑表的分析是否是最新的,是否对相关的列做了直方图,是否对分区表做了全局或者分区分析等因素。

关于执行计划参考:

Oracle Explain Plan

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5827245.aspx

总之,在处理问题时,我们要把问题掌握在可控的范围内,不能将问题扩大化,甚至失控。 作为一个DBA,需要的扎实的基本功,还有胆大心细,遇事不慌。











来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/394664/viewspace-1063176/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-05-06

  • 博文量
    43
  • 访问量
    312893