Oracle之Hint使用总结

叁歲伎倆 2021-06-26 16:06 402阅读 0赞

## 一、关于hint ##

### 1、为什么要引入`hint`？ ###

`Hint`是Oracle数据库提供的一种机制用来告诉优化器按照`hint`告诉它的方式生成执行计划，是很多DBA优化中常用的一个手段。

为什么Oracle引入优化器呢？

基于代价的优化器，在绝大多数情况下会选择正确的优化器，减轻DBA的负担。但是有时候会选择效率很差的执行计划，使某个语句变得很慢，此时就需要DBA认为干预，告诉优化器使用指定的存取路径或者连接类型生成执行计划，从而使语句高效地运行。

### 2、不能过分依赖`hint` ###

当遇到不好的执行计划时，应该优先考虑统计信息等问题，而非直接加`hint`。若统计信息无误，再考虑物理结构是否合理，即有没有合适的索引。只有在最后任然不能按照优化的sql执行计划执行时，才考虑`hint`。

因为使用`hint`，需要系统修改代码，`hint`只能解决一条sql的问题，并且由于数据分布的变化或其他原因(如索引改名等)，会导致`sql`再次出现性能问题。

### 3、hint的弊端 ###

(1)、`hint`是比较“暴力”的一种解决方式。需要开发人员手工修改代码。

(2)、`hint`不会去适应新的变化。比如数据结构、数据规模发生了重大变化，使用`Hint`的语句是不会感知变化并产生更优的执行计划。

(3)、`hint`随着数据库版本的变化，可能会有一些差异、甚至废弃的情况。此时，还需要人工取修改。

### 4、`hint`与注释的关系 ###

提示是Oracle为了不破坏和其他数据库引擎之间对SQL语句的兼容性而提供的一种扩展功能。

Oracle决定把提示作为一种特殊的注释来添加。它的特殊性表现在提示必须紧跟着`DELETE、INSERT、UPDATE`或`MERGE`关键字。

如`insert /*+append*/ into`

### 5、`hint`的功能 ###

通过`hint`可以灵活地调整语句的执行过程。

通过`Hint`，我们可以调整：优化器类型、优化器优化目标、数据读取方式(访问路径)、查询转换类型、表间关联的顺序、表间关联的类型、并行特性、其他特性。

## 二、`hint`的具体用法 ##

### 1、`hint`语法： ###

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

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

**（1）、关键字说明**

`hint`提示只能出现在4个关键字后面；

“+”号表示此注释是一个提示，必须紧跟“/\*”，且中间不能有空格；

如果包含多个提示，则每个提示之间需要用一个或多个空格隔开；

`text`是其他说明hint的注释性文本。

**（2）、`hint`中的错误**

`hint`中的语法错误不会报错，如果解析器不能解析，就会把它看做一个普通的注释处理。

`explain plan + dbms_xplan` ：使用`dbms_xplan`输出中的`note`选项。

`ora-10132`事件：在10g中，这个事件产生的输出文档的末尾有一部分内容专门讲提示。

通过它可以检查两个方面：  
一是每个用到的提示都会被列出来。如果漏掉了哪个，就说明这个提示没有被识别；  
二是检查是否有一些信息指明了出现提示错误(如果出错，err值将大于0)。

**（3）、`hint`中的对象**

SELECT /*+ INDEX(TABLE_NAME INDEX_NAME)*/ ...

`table_name`是必须要写的，且如果在查询中使用了表的别名，在`hint`也要用表的别名来代替表名。

注:  
a、`index_name`可以不必写，`Oracle`会根据统计值选一个索引。

b、如果索引名或表名写错了，那这个`hint`就会被忽略。

c、如果指定对象是视图，需要按此方法指定。`/*+hint view.table ...*/`，其中`table`是`view`中的表。

d、一个很常见的错误是，在使用提示的时候最易犯的错误是与表的别名有关。正确的规则是，当在提示中使用表时，只要表有别名就应该使用别名而不是表名。

### 2、`hint`的作用域 ###

**（1）、查询块**

初始化参数提示对整个sql语句起作用，其他的提示仅对查询块起作用。

仅仅对单个查询块起作用的提示，必须在它控制的查询块内指定。

with temp_tb1 as {
        select /*+ full(temp)*/id,count(1) cnt from temp group by id
    }
    
    select /*+ full(temp_tb2)*/ id,name,cnt from temp_tb2,temp_tb1 where temp_tb2.id=temp_tb1.id;

**（2）、例外-全局提示**

可以使用点号引用包含在其他块(假设这些块已命名)中的对象。类似Java中的静态成员。

with temp_tb1 as {
        select id,count(1) cnt from temp group by id
        }
    select /*+ full(temp) full(temp_tb1.temp)*/ id,name,cnt from temp_tb2,temp_tb1 where temp_tb2.id=temp_tb1.id;

**（3）、命名查询块**

既然`where`子句中的子查询是没有命名的，它们的对象就不能被全局提示引用。为了解决这个问题，10g中使用了另一种方法来解决-命名查询块。查询优化器可以给每个查

询生成一个查询块名，而且还可以使用提示`qb_name`手工为每个查询块命名。大多数提示都可以通过参数来指定在那个查询块中有效。

with temp1 as {
        select /*+ qb_name(sq)*/ id,count(1)cnt from temp group by id
    }
    
    select /*+ qb_name(main) full(@main temp2) full(@sq temp) */ temp.name,temp1.cnt 
        from temp,temp1 where temp.id=temp1.id;

在`hint`中通过@来引用一个查询块。

### 3、`hint`数据字典 ###

Oracle在11g的版本中提供了一个数据字典—`V$SQL_HINT`。通过这个数据字典可以看到提示的出现版本、概要数据版本、SQL特性以及相反提示等。

select * from v$sql_hint where version like '%11%';

## 三、hint的分类 ##

### 1、优化器相关的 ###

当对优化器为某个语句所制定的基本执行计划不满意时，最好的方法是通过提示来改变执行计划，并观察改变后是否达到 期望的程度。

`OPT_PARAM`：作用是使某条语句中指定某个系统参数值；

`ALL_EOWS`：实现查询语句整体最优化而引导优化器制定最少成本的执行计划。优化器会选择一条可最快检索所有查询行的路径，代价就是检索一行时，速度会很慢。

`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`(不管是否进行基于规则的优化，这两个提示都可使用)。

### 2、和访问路径相关的 ###

`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`来访问索引获得数据。当联合索引第一列的唯一值很少时，使用这种方式比全表扫描的方式效率要高。

### 3、和查询转换相关的 ###

`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`：使用该提示将引导优化器将不能实现合并的子查询放在最后执行。在子查询无法缩减主查询的查询范围，或者执行子查询开销较大的情况下，将这样的子查询放在最后执行可以在某种程度上提高整体的执行效率。也就是说，尽可能地使用其他查询条件最大程度地缩减查询范围之后，再执行子查询。

### 4、和表连接顺序相关的 ###

这些提示可以调整表连接的顺序。调整表连接的顺序并不是只能使用这些提示，在嵌套循环连接方式中也可以让提示来引导优化器使用由驱动查询条件所创建的索引。然而，该方法只有在使用的索引和表连接顺序同时被调整的情况下才比较有效。一般而言，这些提示主要在执行多表连接和表之间的连接顺序比较混乱的情况下才使用，也在排序合并连接或哈希连接方式下，为引导优化器优先执行数据量比较少得表时使用。

`LEADING`：在一个多表关联的查询中，这个`Hint`指定由哪个表作为驱动表，即告诉优化器首先要访问那个表上的数据。引导优化器使用`LEADING`指定的表作为表连接顺序中的第一个表。该提示既与`FROM`中所描述的表的顺序无关，也与作为调整表连接顺序的`ORDERED`提示不同，并且在使用该提示时并不需要调整`FROM`中所描述的表的顺序。当该提示与`ORDERED`提示同时使用时，该提示被忽略。

这个提示类似`ORDERED`提示，它允许指定驱动查询的表，然后由优化器来判断下一个要访问的表。如果使用这个提示指定多张表，那么就可以忽略这个提示。

`ORDERED`：引导优化器按照`FROM`中所描述的表的顺序执行连接。如果和`LEADING`提示被一起使用，则`LEADING`提示将被忽略。由于`ORDERED`只能调整表连接的顺序并不能改变表连接的方式，所以为了改变表的连接方式，经常将`USE_NL、USE_MERGE`提示与`ORDERED`提示放在一起使用。

### 5、和表连接操作相关的 ###

`USE_NL`：使用该提示引导优化器按照嵌套循环连接方式执行表连接。它只是指出表连接的方式，对于表连接顺序不会有任何影响。

`USE_MERGE`：引导优化器按照排序合并连接方式执行连接。在有必要的情况下，推荐将该提示与`ORDERED`提示一起使用。提示通常用于获得查询的最佳吞吐量。假设将两个表连接在一起，从每个表返回的行集将被排序，然后再被合并(也就是合并排序)，从而组成最终的结果集。由于每个行先被排序之后才进行合并，所以在给定查询中检索所有行时，速度将会最快。如果需要以最快速度返回第一行，就应该使用`USE_NL`提示。

`USE_HASH`：该提示引导优化器按照哈希连接方式执行连接。在执行哈希连接时，如果由于某一边的表比较小，从而可以在内存中实现哈希连接，那么就能够获得非常好的执行速度。由于在大部分情况下优化器会通过对统计信息的分析来决定`Build Input`和`Prove Input`，所以建议不要使用`ORDERED`提示随意改变表的连接顺序。但是当优化器没能做出正确判断时，或者像从嵌套视图中所获得的结果集合那样不具备统计信息时，可以使用该提示。

### 6、和并行相关的 ###

`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`：为了按照并行操作的方式对分区索引进行索引范围扫描而使用该提示，并且可以指定进程的个数。

### 7、其他相关的 ###

`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`：向优化器提供对某个查询语句的整体或部分的预测基数值，并通过参考该基数值来为查询语句制定执行计划。如果在该提示中没有指定表的名称，则该基数值将被视为从该查询语句所获得的最终结果行数。