宜信-运维-数据库|SQL优化：一篇文章说清楚Oracle Hint的正确使用姿式
1、提示（Hint）概述
一、为何引入Hint?
Hint是Oracle数据库中颇有特点的一个功能，是不少DBA优化中常常采用的一个手段。那为何Oracle会考虑引入优化器呢？基于代价的优化器是很聪明的，在绝大多数状况下它会选择正确的优化器，减轻DBA的负担。数据库
但有时它也聪明反被聪明误，选择了不好的执行计划，使某个语句的执行变得奇慢无比。此时就须要DBA进行人为的干预，告诉优化器使用指定的存取路径或链接类型生成执行计划，从而使语句高效地运行。Hint就是Oracle提供的一种机制，用来告诉优化器按照告诉它的方式生成执行计划。缓存
二、不要过度依赖Hint
当遇到SQL执行计划很差的状况，应优先考虑统计信息等问题，而不是直接加Hint了事。若是统计信息无误，应该考虑物理结构是否合理，即没有合适的索引。只有在最后仍然不能SQL按优化的执行计划执行时，才考虑Hint。网络
毕竟使用Hint，须要应用系统修改代码，Hint只能解决一条SQL的问题，而且因为数据分布的变化或其余缘由(如索引改名)等，会致使SQL再次出现性能问题。数据结构
三、Hint的弊端
- Hint是比较"暴力"的一种解决方式，不是很优雅。须要开发人员手工修改代码。
- Hint不会去适应新的变化。好比数据结构、数据规模发生了重大变化，但使用Hint的语句是感知变化并产生更优的执行计划。
- Hint随着数据库版本的变化，可能会有一些差别、甚至废弃的状况。此时，语句自己是无感知的，必须人工测试并修正。
四、Hint与注释关系
提示是Oracle为了避免破坏和其余数据库引擎之间对SQL语句的兼容性而提供的一种扩展功能。Oracle决定把提示做为一种特殊的注释来添加。它的特殊性表如今提示必须紧跟着DELETE、INSERT、UPDATE或MERGE关键字。oracle
换句话说，提示不能像普通注释那样在SQL语句中随处添加。且在注释分隔符以后的第一个字符必须是加号。在后面的用法部分，会详细说明。分布式
五、Hint功能
Hint提供的功能很是丰富，能够很灵活地调整语句的执行过程。经过Hint，咱们能够调整：函数
- 优化器类型
- 优化器优化目标
- 数据读取方式(访问路径)
- 查询转换类型
- 表间关联的顺序
- 表间关联的类型
- 并行特性
- 其余特性
2、Hint用法
一、语法
1）关键字说明
- DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出如今这些关键字的后面，不然提示无效。
- "+"号表示该注释是一个提示，该加号必须当即跟在"/*"的后面，中间不能有空格。
- hint是下面介绍的具体提示之一，若是包含多个提示，则每一个提示之间须要用一个或多个空格隔开。
- text是其它说明hint的注释性文本
2）提示中的错误
提示中的语法错误不会报错，若是解析器不能解析它，就会把它看作一个普通注释处理。这也是容易形成困惑的一点，使用的Hint究竟是否起效？能够采用一些手段，检查提示的有效性。须要注意的是，那些语法正确但引用对象错误的提示是不会被报告的。性能
- explain plan + dbms_xplan
使用dbms_xplan输出中的note选项。测试
- 10132事件
在10g中，这个事件产生的输出文档的末尾有一部份内容专门讲提示。经过它能够检查两个方面:一是每一个用到的提示都会被列出来。若是漏掉了哪一个，就说明这个提示没有被识别；二是检查是否有一些信息指明了出现提示错误(若是出错，err值将大于0)。优化
3）提示中的对象
SELECT /+ INDEX(table_name index_name) / ...
- table_name是必需要写的，且若是在查询中使用了表的别名，在hint也要用表的别名来代替表名。
- index_name能够没必要写，Oracle会根据统计值选一个索引。
- 若是索引名或表名写错了，那这个hint就会被忽略。
若是指定对象是视图，须要按此方法指定。/*+hint view.table ...*/，其中table是view中的表。
一个很常见的错误时，在使用提示的时候最易犯的错误是与表的别名有关。正确的规则是，当在提示中使用表时，只要表有别名就应该使用别名而不是表名。
二、提示的做用域
- 查询块
初始化参数提示对整个SQL语句起做用，其余的提示仅仅对查询块起做用。仅仅对单个查询块起做用的提示，必须在它控制的查询块内指定。
- 例外 - 全局提示
可使用点号引用包含在其余查询块(假设这些块已命名)中的对象。全局提示的语法能够支持两层以上的引用，对象间必须用点号分隔。
- 命名查询块
既然where子句中的子查询是没有命名的，它们的对象就不能被全局提示引用。为了解决这个问题，10g中使用了另外一种方法来解决-命名查询块。查询优化器能够给每一个查询生成一个查询块名，并且还可使用提示qb_name手工为每一个查询块命名。大多数提示均可以经过参数来指定在那个查询块中有效。
*在提示中经过@来引用一个查询块。
三、提示数据字典
Oracle在11g的版本中提供了一个数据字典—V$SQL_HINT。经过这个数据字典能够看到提示的出现版本、概要数据版本、SQL特性以及相反提示等。
- INVERSE
这个hint相反操做的hint。
- VERSION
表明着这个hint正式公布引入的版本。
3、Hint分类
一、和优化器相关的
当对优化器为某个语句所制定的基本执行计划不满意时，最好的办法就是经过提示来转换优化器的模式，并观察其转换后的结果，看是否已经达到指望程度。若是只经过转换优化器的模式就能够得到很是好的执行计划，则就没有必要额外使用更为复杂的提示了。
- OPT_PARAM
这个提示的做用就是使咱们在某条语句中指定某个系统参数值。
- ALL_ROWS
为实现查询语句总体最优化而引导优化器制定最少成本的执行计划。这个提示会使优化器选择一条可最快检索全部查询行的路径，而代价就是在检索一行数据时，速度很慢。
- FIRST_ROWS
为得到最佳响应时间而引导优化器制定最少成本的执行计划。这个提示会使优化器选择可最快检索出查询的第一行(或指定行)数据的路径，而代价就是检索不少行时速度就会很慢。利用FIRST_ROWS来优化的行数，默认值为1，这个值介于10到1000之间，这个使用FIRST_ROWS(n)的新方法是彻底基于代价的方法。它对n很敏感，若是n值很小，CBO就会生成包含嵌套循环以及索引查找的计划；若是n很大，CBO会生成由哈希链接和全表扫描组成的计划(相似ALL_ROWS)。
- CHOOSE
依据SQL中所使用到的表的统计信息存在与否，来决定使用RBO仍是CBO。在CHOOSE模式下，若是可以参考表的统计信息，则将按照ALL_ROWS方式执行。除非在查询中的全部表都没有通过分析，不然choose提示会对整个查询使用基于代价的优化。若是在多表链接中有一个表通过分析过，那么就会对整个查询进行基于代价的优化。
- RULE
使用基于规则的优化器来实现最优化执行，即引导优化器根据优先顺序规则来决定查询条件中所使用到的索引或运算符的执行顺序来制定执行计划。这个提示强制oracle优先使用预约义的一组规则，而不是对数据进行统计；同时该提示还会使这个语句避免使用其余提示，除了DRIVING_SITE和ORDERED(无论是否进行基于规则的优化，这两个提示均可使用)。
二、和访问路径相关的
- FULL
告诉优化器经过全表扫描方式访问数据。这个提示只对所指定的表进行全表扫描，而不是查询中的全部表。FULL提示能够改善性能。这主要是由于它改变了查询中的驱动表，而不是由于全表扫描。在使用其余某些提示时，也必须使用FULL提示。只有访问整个表时，才可利用CACHE提示将表进行缓存。并行组中的某些提示也必须使用全表扫描。
- CLUSTER
引导优化器经过扫描聚簇索引来从索引表中读取数据。
- HASH
引导优化器按照哈希扫描的方式从表中读取数据。
- INDEX
告诉优化器对指定表经过索引的方式访问数据。当访问数据会致使结果集不完整时，优化器将忽略这个Hint。
- NO_INDEX
告诉优化器对指定表不容许使用索引。这个提示会禁止优化器使用指定索引。能够在删除没必要要的索引以前在许多查询中禁止索引。若是使用了NO_INDEX，可是没有指定任何索引，则会执行全表扫描。若是对某个索引同时使用了NO_INDEX和会之产生冲突的提示(如INDEX)，这时两个提示都会被忽略掉。
- INDEX_ASC
利用索引从表中读取数据时，引导优化器对提示中所指定索引的索引列值按照升序使用范围扫描。
- INDEX_COMBINE
告诉优化器强制选择位图索引。这个提示会使优化器合并表上的多个位图索引，而不是选择其中最好的索引(这是INDEX提示的用途)。还可使用index_combine指定单个索引(对于指定位图索引，该提示优先于INDEX提示)。对于B树索引，可使用AND_EQUAL提示而不是这个提示。
- INDEX_JOIN
索引关联，当谓词中引用的列上都有索引的时候，能够经过索引关联的方式来访问数据。这个提示能够将同一个表的各个不一样索引进行合并，这样就只须要访问这些索引就能够了，节省了回表查询的时间。但只能在基于代价的优化器中使用该提示。这个提示不只容许只访问表上的索引，这样能够扫描更少的代码块，而且它比使用索引并经过rowid扫描整个表快5倍。
- INDEX_DESC
利用索引从表中读取数据时，引导优化器对提示中所指定索引的索引列值按照降序使用范围扫描。
- INDEX_FFS
告诉优化器以INDEX FFS(index fast full scan)的方式访问数据。INDEX_FFS提示会执行一次索引的快速全局扫描。这个提示只访问索引，而不是对应的表。只有查询须要检索的信息都在索引上时，才使用这个提示。特别在表有不少列时，使用该提示能够极大地改善性能。
- INDEX_SS
强制使用index skip scan的方式访问索引。当在一个联合索引中，某些谓词条件并不在联合索引的第一列时(或者谓词并不在联合索引的第一列时)，能够经过index skip scan来访问索引得到数据。当联合索引第一列的惟一值不多时，使用这种方式比全表扫描的方式效率要高。
三、和查询转换相关的
- USE_CONCAT
将含有多个OR或者IN运算符所链接起来的查询语句分解为多个单一查询语句，并为每一个单一查询语句选择最优化查询路径，而后再将这些最优化查询路径结合在一块儿，以实现总体查询语句的最优化目的。只有在驱动查询条件中包含OR的时候，才可使用该提示。
- NO_EXPAND
引导优化器不要为使用OR运算符号(或IN运算符)的条件制定相互结合的执行计划。正好和USE_CONCAT相反。
- REWRITE
当表链接的对象是数据量比较大的表或者须要得到使用统计函数处理过的结果时，为了提升执行速度可预先建立物化视图。当用户要求查询某个查询语句时，优化器会在从表中和从物化视图中读取数据的两种方法中选择一个更有效的方法来读取数据。该执行方法称之为查询重写。使用REWRITE提示引导优化器按照该方式执行。
- MERGE
为了能以最优方式从视图或者嵌套视图中读取数据，经过变换查询语句来直接读取视图使用的基表数据，该过程被称之为视图合并。不一样的状况其具体使用类型也有所不一样。该提示主要在视图未发生合并时被使用。尤为是对比较复杂的视图或者嵌套视图(好比使用了GROUP BY或DISTINC的视图)使用该提示，有时会取得很是好的效果。
- UNNEST
提示优化器将子查询转换为链接的方式。也就是引导优化器合并子查询和主查询而且将其向链接类型转换。
- NO_UNNEST
引导优化器让子查询可以独立地执行完毕以后再跟外围的查询作FILTER。
- PUSH_PRED
使用该提示能够将视图或嵌套视图之外的查询条件推入到视图以内。
- NO_PUSH_PRED
使用该提示确保视图或嵌套视图之外的查询条件不被推入到视图内部。
- PUSH_SUBQ
使用该提示引导优化器为不能合并的子查询制定执行计划。不能合并的子查询被优先执行以后，该子查询的执行结果将扮演缩减主查询数据查询范围的提供者角色。一般在没法执行子查询合并的状况下，子查询扮演的都是检验者角色，因此子查询通常被放在最后执行。在没法被合并的子查询拥有较少的结果行，或者该子查询能够缩减主查询查询范围的状况下，可使用该提示引导优化器最大程度地将该子查询放在前面执行，以提升执行速度。但若是子查询执行的是远程表或者排序合并链接的一部分链接结果，则该提示将不起任何做用。
- NO_PUSH_SUBQ
使用该提示将引导优化器将不能实现合并的子查询放在最后执行。在子查询没法缩减主查询的查询范围，或者执行子查询开销较大的状况下，将这样的子查询放在最后执行能够在某种程度上提升总体的执行效率。也就是说，尽量地使用其余查询条件最大程度地缩减查询范围以后，再执行子查询。
四、和表链接顺序相关的
这些提示能够调整表链接的顺序。调整表链接的顺序并非只能使用这些提示，在嵌套循环链接方式中也可让提示来引导优化器使用由驱动查询条件所建立的索引。然而，该方法只有在使用的索引和表链接顺序同时被调整的状况下才比较有效。通常而言，这些提示主要在执行多表链接和表之间的链接顺序比较混乱的状况下才使用，也在排序合并链接或哈希链接方式下，为引导优化器优先执行数据量比较少得表时使用。
- LEADING
在一个多表关联的查询中，这个Hint指定由哪一个表做为驱动表，即告诉优化器首先要访问那个表上的数据。引导优化器使用LEADING指定的表做为表链接顺序中的第一个表。该提示既与FROM中所描述的表的顺序无关，也与做为调整表链接顺序的ORDERED提示不一样，而且在使用该提示时并不须要调整FROM中所描述的表的顺序。当该提示与ORDERED提示同时使用时，该提示被忽略。
这个提示相似ORDERED提示，它容许指定驱动查询的表，而后由优化器来判断下一个要访问的表。若是使用这个提示指定多张表，那么就能够忽略这个提示。
- ORDERED
引导优化器按照FROM中所描述的表的顺序执行链接。若是和LEADING提示被一块儿使用，则LEADING提示将被忽略。因为ORDERED只能调整表链接的顺序并不能改变表链接的方式，因此为了改变表的链接方式，常常将USE_NL、USE_MERGE提示与ORDERED提示放在一块儿使用。
五、和表链接操做相关的
- USE_NL
使用该提示引导优化器按照嵌套循环链接方式执行表链接。它只是指出表链接的方式，对于表链接顺序不会有任何影响。
- USE_MERGE
引导优化器按照排序合并链接方式执行链接。在有必要的状况下，推荐将该提示与ORDERED提示一块儿使用。提示一般用于得到查询的最佳吞吐量。假设将两个表链接在一块儿，从每一个表返回的行集将被排序，而后再被合并(也就是合并排序)，从而组成最终的结果集。因为每一个行先被排序以后才进行合并，因此在给定查询中检索全部行时，速度将会最快。若是须要以最快速度返回第一行，就应该使用USE_NL提示。
- USE_HASH
该提示引导优化器按照哈希链接方式执行链接。在执行哈希链接时，若是因为某一边的表比较小，从而能够在内存中实现哈希链接，那么就可以得到很是好的执行速度。因为在大部分状况下优化器会经过对统计信息的分析来决定Build Input和Prove Input，因此建议不要使用ORDERED提示随意改变表的链接顺序。可是当优化器没能作出正确判断时，或者像从嵌套视图中所得到的结果集合那样不具有统计信息时，可使用该提示。
六、和并行相关的
- PARALLEL
指定SQL执行的并行度，这个值将会覆盖表自身设定的并行度。若是这个值为default，CBO使用系统参数。从表中读取大量数据和执行DML操做时使用该提示来指定SQL的并行操做。
通常状况下须要在该提示中指定将要使用的并行线程个数。若是在该提示中没有指定并行度的个数，则优化器将使用PARALLEL_THREADS_PER_CPU参数所指定的值进行自动计算。若是在定义表时指定了PARALLEL，那么在可以使用并行操做的状况下，即便没有使用该提示，优化器也会按照指定的并行级别选择并行操做。
可是若是想在DELETE、INSERT、UPDATE、MERGE等DML操做中使用并行操做，则必需要在会话中设置ALTER SESSION ENABLE PARALLEL DML。在某个会话中所设置的并行级别也能够被引用在内部的GROUP BY或者排序操做中。在并行操做中若是出现了某个限制要素，则该提示将被忽略。
- NOPARALLEL/NO_PARALLEL
在SQL语句禁止使用并行。在有些版本中用NO_PARALLEL提示来代替NOPARALLEL提示。
- PQ_DISTRIBUTE
为了提升并行链接的执行速度，使用该提示来定义使用何种方法在主从进程之间(例如生产者进程和消费者进程)分配各链接表的数据行。
- PARALLEL_INDEX
为了按照并行操做的方式对分区索引进行索引范围扫描而使用该提示，而且能够指定进程的个数。
七、其余相关的
- APPEND
让数据库以直接加载的方式(direct load)将数据加载入库。这个提示不会检查当前是否有插入所须要的块空间，相反它会直接将数据添加到新块中。这样会浪费空间，但能够提升插入的性能。须要注意的是，数据将被存储在HWM之上的位置。
- APPEND_VALUES
在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES语句也可使用直接路径插入。
- CACHE
在全表扫描以后，数据块将留在LRU列表的最活跃端。若是设置表的CACHE属性，它的做用和HINT同样。这个提示会将全表扫描所有缓存到内存中。若是表很大，会占用大量内存。所以适用于用户常常访问的较小的表。
- NOCACHE
引导优化器将经过全表扫描方式获取的数据块缓存在LRU列表的最后位置，这样可让数据库实例缓存中的这些数据块被优先清除。这是优化器在Buffer Cache中管理数据块的默认方法(仅针对全表扫描)。
- QB_NAME
使用该提示为查询语句块命名，在其余查询语句块能够直接使用该查询语句块的名称。
- DRIVING_SITE
这个提示在分布式数据库操做中有用。指定表是处理链接所在的位置。能够限制经过网络处理的信息量。此外，还能够创建远程表的本地视图来限制从远程站点检索的行。本地视图应该有where子句，从而视图能够在将行发送回本地数据库以前限制从远程数据库返回的行。
- DYNAMIC_SAMPLING
提示SQL执行时动态采样的级别。这个级别为0~10，它将覆盖系统默认的动态采样级别。等级越高，所得到统计信息的准确率越高。该提示的功能就是为了确保将动态采样原理应用在单个SQL中。
- AND_EQUAL
这个提示会使优化器合并表上的多个索引，而不是选择其中最好的索引(这是INDEX提示的用途)。这个提示与前面的INDEX_JOIN提示有区别，以此指定的合并索引随后需访问表，而INDEX_JOIN提示则只需访问索引。若是发现需常常用到这个提示，可能须要删除这些单个索引而改用一个组合索引。须要查询条件里面包括全部索引列，而后取得每一个索引中获得的rowid列表。而后对这些对象作merge join，过滤出相同的rowid后再去表中获取数据或者直接从索引中得到数据。在10g中，and_equal已经废弃了，只能经过hint才能生效。
- CARDINALITY
向优化器提供对某个查询语句的总体或部分的预测基数值，并经过参考该基数值来为查询语句制定执行计划。若是在该提示中没有指定表的名称，则该基数值将被视为从该查询语句所得到的最终结果行数。
4、Hint使用示例
下面经过一个例子说明一下提示的使用及在什么状况下提示会被忽略。
一、构建表
二、使用INDEX提示
*在某些状况下，若是CBO认为Hint会致使错误结果，那么Hint则会忽略。该例子中由于ID字段可能为空，而索引是保存空值的，所以count(*)使用索引将致使错误的结果，故而使用了全表扫描，忽略了Hint。
三、使用INDEX提示（非空字段）
*ID字段不可为空，所以COUNT可用索引扫描的方式处理，Hint生效了。
做者:韩锋