`

Oracle中Hint深入理解(转)

阅读更多
Hint概述
基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。

此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从 而使语句高效的运行。例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们就可以指示优化器使用全表扫描。在Oracle 中,是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。
不建议在代码中使用hint,在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟 数据是不断变化的, 10g以后的CBO也越来越完善,大多数情况下我们该让Oracle自行决定采用什么执行计划。

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

除了”RULE”提示外,一旦使用的别的提示,语句就会自动的改为使用CBO优化器,此时如果你的数据字典中没有统计数据,就会使用缺省的统计数据。所以建议大家如果使用CBO或Hints提示,则最好对表和索引进行定期的分析。

如何使用Hints:

Hints只应用在它们所在sql语句块(statement block,由select、update、delete关键字标识)上,对其它SQL语句或语句的其它部分没有影响。如:对于使用union操作的2个sql语句,如果只在一个sql语句上有Hints,则该Hints不会影响另一个sql语句。

我们可以使用注释(comment)来为一个语句添加Hints,一个语句块只能有一个注释,而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面

使用Oracle Hints的语法:

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解:
1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字,包含提示的注释只能出现在这些关键字的后面,否则提示无效。
2) “+”号表示该注释是一个Hints,该加号必须立即跟在”/*”的后面,中间不能有空格。
3) hint是下面介绍的具体提示之一,如果包含多个提示,则每个提示之间需要用一个或多个空格隔开。
4) text 是其它说明hint的注释性文本

5)使用表别名。如果在查询中指定了表别名,那么提示必须也使用表别名。例如:select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名称:如果在提示中指定了模式的所有者,那么提示将被忽略。例如:
select /*+ index(scott.emp,dept_idx) */ * from emp


注意:如果你没有正确的指定Hints,Oracle将忽略该Hints,并且不会给出任何错误。
hint被忽略

如果CBO认为使用hint会导致错误的结果时,hint将被忽略,详见下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50366 |    57   (2)| 00:00:01 |
-------------------------------------------------------------------

因为我们是对记录求总数,且我们并没有在建立索引时指定不能为空,索引如果CBO选择在索引上进行count时,但索引字段上的值为空时,结果将不准确,故CBO没有选择索引。
SQL>  select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |   285   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE  |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| T_IND | 50366 |   245K|   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

因为我们只对id进行count,这个动作相当于count索引上的所有id值,这个操作和对表上的id字段进行count是一样的(组函数会忽略null值)


Hint的具体用法


和优化器相关的hint

1、/*+ ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2、/*+ FIRST_ROWS(n) */
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3、/*+ RULE*/
表明对语句块选择基于规则的优化方法.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
和访问路径相关的hint

1、/*+ FULL(TABLE)*/
表明对表选择全局扫描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
2、/*+ INDEX(TABLE INDEX_NAME) */
表明对表选择索引的扫描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';
5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
6、/*+ INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */  * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
当谓词中引用的列都有索引的时候,可以通过指定采用索引关联的方式,来访问数据
select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'
8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
9、/*+ INDEX_FFS(TABLE INDEX_NAME) */
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

10、/*+ INDEX_SS(T T_IND) */
从9i开始,oracle引入了这种索引访问方式。当在一个联合索引中,某些谓词条件并不在联合索引的第一列时,可以通过Index Skip Scan来访问索引获得数据。当联合索引第一列的唯一值个数很少时,使用这种方式比全表扫描效率高。

SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> insert into t select 2,object_name from dba_objects;      
50366 rows created.
SQL> insert into t select 3,object_name from dba_objects;      
50366 rows created.
SQL> insert into t select 4,object_name from dba_objects;      
50366 rows created.
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
执行全表扫描
SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   135 |   215   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |     5 |   135 |   215   (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
           0  recursive calls
           0  db block gets
         942  consistent gets
           0  physical reads
           0  redo size
         538  bytes sent via SQL*Net to client
         385  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           6  rows processed
不采用hint
SQL>  select * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     5 |   135 |     5   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | T_IND |     5 |   135 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - access("OBJECT_NAME"='EMPLOYEES')
        filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
           1  recursive calls
           0  db block gets
          17  consistent gets
           1  physical reads
           0  redo size
         538  bytes sent via SQL*Net to client
         385  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           6  rows processed
当全表扫描扫描了942个块,联合索引只扫描了17个数据块。可以看到联合索引的第一个字段的值重复率很高时,即使谓词中没有联合索引的第一个字段,依然会使用index_ss方式,效率远远高于全表扫描效率。但当 第一个字段的值重复率很低时,使用 index_ss的效率要低于 全表扫描,读者可以自行实验
和表的关联相关的hint

/*+ leading(table_1,table_2) */

在多表关联查询中,指定哪个表作为驱动表,即告诉优化器首先要访问哪个表上的数据。
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */

让Oracle根据from后面表的顺序来选择驱动表,oracle建议使用leading,他更为灵活
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */
在多表关联查询中,指定使用nest loops方式进行多表关联。

select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_hash(table_1,table_2) */
在多表关联查询中,指定使用hash join方式进行多表关联。

select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;



在多表关联查询中,指定使用hash join方式进行多表关联,并指定表t为驱动表。

select /*+ use_hash(t,t1) leading(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_merge(table_1,table_2) */
在多表关联查询中,指定使用merge join方式进行多表关联。

select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_nl(table_1,table_2) */
在多表关联查询中,指定不使用nest loops方式进行多表关联。

select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_hash(table_1,table_2) */
在多表关联查询中,指定不使用hash join方式进行多表关联。

select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_merge(table_1,table_2) */
在多表关联查询中,指定不使用merge join方式进行多表关联。

select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

其他常用的hint

/*+ parallel(table_name n) */


在sql中指定执行的并行度,这个值将会覆盖自身的并行度


select /*+ parallel(t 4) */ count(*)  from t;

/*+ no_parallel(table_name) */


在sql中指定执行的不使用并行


select /*+ no_parallel(t) */ count(*)  from t;

/*+ append */以直接加载的方式将数据加载入库

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

设置sql执行时动态采用的级别,这个级别为0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234


/*+ cache(table_name) */
进行全表扫描时将table置于LRU列表的最活跃端,类似于table的cache属性


select /*+ full(employees) cache(employees) */ last_name from employees





参考至:《让Oracle跑得更快》谭怀远著


              http://www.oradev.com/hints.jsp


              http://hi.baidu.com/lyq168/blog/item/c813452c29d307e48a1399b1.html


              http://database.51cto.com/art/200911/163085.htm

              http://oracle.chinaitlab.com/induction/398193.html
本文原创,转载请注明出处
如有错误,欢迎指正
邮箱:czmcj@163.com
分享到:
评论

相关推荐

    Oracle 中HINT的使用

    Oracle中的HINT是一种非常强大的工具,它允许数据库管理员和开发人员通过在SQL语句中...然而,正确使用HINT需要深入理解SQL语句的执行过程和数据库的工作原理,同时也需要注意避免过度依赖,保持查询的灵活性和适应性。

    oracle_hint教程汇总

    Oracle Hint是Oracle数据库系统中的一种特性,它允许数据库管理员或开发人员通过在SQL语句中...在"Oracle Hint.doc"和"Oracle_Hint使用实例.pdf"中,你可以找到更具体的示例和深入的解释,以帮助理解和掌握这些技巧。

    Oracle hint的用法

    ### Oracle Hint 的深入解析与应用 #### 一、概述 Oracle Hint 是一种特殊的注释语法,用于指导Oracle数据库优化器如何执行SQL语句。通过合理使用Hint,可以显著提高SQL语句的执行效率,减少不必要的资源消耗。...

    Oracle数据库优化器Hint的全面实战解析

    通过案例和详细的解释,帮助读者理解为什么Oracle引入Hint,以及如何在查询优化中使用不同的Hint类型,如与优化器相关、访问路径、查询转换、表连接顺序、表连接操作、并行操作及其他提示。每个类型的Hint都详细说明...

    小菜鸟系列-Oracle的优化器与hint

    通过深入理解Oracle的优化器和hint,开发者和DBA可以更好地优化数据库性能,提升系统的整体运行效率。这不仅有助于提高数据查询速度,还能减少服务器资源的消耗,从而节省成本。在日常工作中,不断学习和掌握这些...

    oracle CBO HINT

    Oracle的Cost-Based Optimizer (CBO) 是一种自动选择最优执行计划的机制,它通过评估不同执行路径的成本来决定...然而,由于CBO的复杂性,深入理解和实践是至关重要的。务必在生产环境中谨慎应用,以防止可能的反效果。

    Oracle Hint的用法

    ### Oracle Hint 的深入解析与应用 #### 一、概述 Oracle Hint是一种用于指导Oracle数据库优化器如何执行查询的特殊语法。通过使用特定的Hint,开发人员可以显式地告诉优化器采用哪种策略来处理SQL语句,从而实现...

    ORACLE中的的HINT详解

    本文将深入探讨Oracle HINT的使用和功能。 1. **HINT的基本概念** HINT是Oracle提供的一种特殊指令,它们被插入到SQL语句中,用于指导查询优化器选择特定的执行策略。优化器是Oracle处理查询时的一个组件,它的...

    Oracle相关SQL葵花宝典

    在《Oracle相关SQL葵花宝典》中,读者将学习到如何定义和调用PL/SQL块,理解变量、常量、游标、异常处理等核心概念。书中还将深入探讨PL/SQL中的复合数据类型,如记录和表类型,以及如何在PL/SQL中进行错误处理和...

    Oracle数据库性能分析

    ### Oracle数据库性能分析 #### 一、Oracle体系结构 Oracle数据库是一个复杂且功能强大的...通过以上几个方面的深入理解和实践,你可以更好地掌握Oracle数据库性能分析的基本方法,从而有效地管理和优化数据库性能。

    oracle数据库性能优化.pdf

    Oracle数据库因其先进、完整和集成的特性,在市场中占据主导地位,因此深入理解和掌握Oracle的优化技术至关重要。 首先,数据库服务器内存参数的调整是优化的首要任务。系统全局区(SGA)是内存调整的重点,它包括...

    Oracle执行计划介绍与测试.pdf

    通过深入理解Oracle执行计划的查看、控制和优化方法,数据库管理员和开发人员能够更有效地管理数据库性能,确保在不同场景下都能获得最佳的查询效率。此外,合理利用HINT和OUTLINE不仅能提升单个查询的速度,还能...

    用智能优化限制提高Oracle数据库性能

    通过深入理解并灵活运用CBO、智能搜索限制和Hint技术,IT专业人士可以有效地优化SQL执行计划,缩短查询响应时间,提升数据库整体运行效率。在实践中,应根据具体的应用场景和数据特征,综合考虑各项优化措施,实现...

    Oracle数据库优化详解

    Oracle数据库优化是一门深入的技术领域,它关系到数据库性能的提升和资源的高效利用。...通过对CBO优化器的深入理解和实践,可以有效地制定和执行数据库优化策略,确保数据库系统能够高效、稳定地运行。

    Oracle日常维护手册

    理解GRANT、REVOKE命令以及如何管理Oracle网络安全,如网络服务命名和监听器配置,都是维护工作中不可或缺的部分。 6. **故障排查**:当遇到错误或异常时,了解如何使用日志文件、trace文件、报警日志和`gdb`调试器...

    oracle之SQL优化

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统之一,SQL(结构化查询语言)是与数据库交互的基础工具。...通过对SQL的深入理解和优化,我们可以显著提升Oracle数据库的运行效率,确保系统的稳定性和高性能。

    ORACLESQL性能优化.pptx

    SQL 语句优化的过程包括定位有问题的语句、检查执行计划、检查执行过程中优化器的统计信息、分析相关表的记录数、索引情况、改写 SQL 语句、使用 HINT、调整索引、表分析等。只有通过这种方式,才能达到最佳执行...

    Oracle SQL 优化与调优技术详解-附录:SQL提示

    为了能够合理使用这些提示,DBA需要深入理解Oracle优化器的工作原理以及表和索引的存储结构。合理利用提示,可以在特定情况下优化性能,但也需注意,不当的使用提示可能会产生性能问题,甚至导致数据不一致。 这里...

    《Oracle高性能SQL调整》PDF

    《Oracle高性能SQL调整》这本书是Oracle数据库管理员和开发人员的重要参考资料,它深入探讨了如何优化SQL查询以获得最佳性能。在Oracle数据库系统中,SQL性能是决定应用响应速度和整体系统效率的关键因素。本书旨在...

Global site tag (gtag.js) - Google Analytics