ORACLE SQL优化器HINT介绍

2012/12/01 by Leave a Comment

在适当的时候使用提示HINT

  • 为以下项指定提示HINT：
  • 优化程序模式
    • 查询转换
    • 访问路径
    • 联接顺序
    • 联接方法

 

优化程序提示HINT：概览

优化程序提示HINT：

  • 影响优化程序的决定
  • 示例：

SELECT /*+ INDEX(e empfirstname_idx) skewed col */ *

FROM employees e

WHERE first_name=’David’

  • 不到万不得已，不要使用提示HINT。
  • 使用提示HINT时，同时添加有关该提示HINT的注释是一个好习惯。

 

通过提示HINT，您可以左右优化程序所做的决定。提示HINT提供了一种机制，用以指示优化程序基于特定标准选择特定查询执行计划。

例如，您可能知道某个索引对于特定查询更具可选性。基于此信息，您可能能够选择一个比优化程序推荐的计划更高效的执行计划。在这种情况下，应使用提示HINT强制优化程序使用最佳执行计划。幻灯片示例展示了这种情况，在示例中强制优化程序使用 EMPFIRSTNAME_IDX 索引检索数据。正如您所看到的，您可以在 SQL 语句中使用注释将指令传递给优化程序。

加号 (+) 会使系统将注释解释为提示HINT列表。加号必须紧跟在注释分隔符之后，不留空格。

应少用提示HINT，仅在收集了相关表的统计信息，并使用 EXPLAIN PLAN 语句评估了没有提示HINT的优化程序计划后使用。在后续版本中，变化的数据库条件以及查询性能增强对代码中的提示HINT如何影响性能产生了重要影响。

另外，使用提示HINT还涉及必须进行管理、检查和控制的额外代码。

 

提示HINT类型

单表提示HINT 指定用于一个表或一个视图
多表提示HINT 指定用于多个表或视图
查询块提示HINT 作用于单个查询块
语句提示HINT 应用于整个 SQL 语句

 

 

单表：单表提示HINT指定用于一个表或一个视图。INDEX 和 USE_NL 是单表提示HINT的示例。

多表：除了指定用于一个或多个表或视图，多表提示HINT类似于单表提示HINT。LEADING 是一个多表提示HINT的示例。

查询块：查询块提示HINT作用于单个查询块。STAR_TRANSFORMATION 和 UNNEST 是查询块提示HINT的示例。

语句：语句提示HINT应用于整个 SQL 语句。ALL_ROWS 是语句提示HINT的示例。

注： USE_NL(table1 table2) 不是多表提示HINT，因为它实际上是 USE_NL(table1) 和 USE_NL(table2) 的快捷方式。

 

指定提示HINT

提示HINT仅应用于一个语句块的优化：

  • 针对表的自包含 DML 语句
  • 顶层 DML 或子查询

oracle_sql_hint1

 

提示HINT仅应用于其所在语句块的优化。语句块包括：

  • 简单的 MERGE、SELECT、INSERT、UPDATE 或 DELETE 语句
  • 父语句或复杂语句的子查询
  • 使用集合运算符（UNION、MINUS、INTERSECT）的复合查询的一部分

例如，使用 UNION 运算符将两个组件查询组合在一起即构成一个复合查询，该复合查询有两个块，每个组件查询使用一个块。因此，第一个组件查询中的提示HINT仅应用于其自身的优化，而不应用于第二个组件查询的优化。

优化程序提示HINT语法

将提示HINT包在 SQL 语句的注释内。可以使用两种注释样式中的任意一种。提示HINT分隔符 (+) 必须紧跟在注释分隔符之后。如果它们之间留有空格，则优化程序无法识别包含提示HINT的
注释。

 

提示HINT规则

  • 提示HINT应紧跟在语句块的第一个 SQL 关键字之后。
  • 每个语句块只能有一个提示HINT注释，但该注释可以包含多个提示HINT。
  • 提示HINT仅应用于其所在语句块。
  • 如果语句使用别名，则提示HINT必须引用别名，而不是表名称。
  • 优化程序忽略没有正确指定的提示HINT，而不显示错误。
  • 必须使提示HINT注释紧跟在 SQL 语句块的第一个关键字（MERGE、SELECT、INSERT、DELETE 或 UPDATE）之后。
  • 一个语句块只能有一个包含提示HINT的注释，但可以在该注释内包含多个由空格分隔的
    提示HINT。
  • 提示HINT仅应用于其所在语句块，覆盖实例级或会话级的参数。
  • 如果 SQL 语句使用别名，则提示HINT必须引用别名，而不是表名称。

Oracle 服务器忽略没有正确指定的提示HINT。但是，请注意下列事实：

  • 您从不会收到错误消息。
  • 系统会考虑同一注释中的其它（正确指定的）提示HINT。
  • Oracle 服务器还忽略互相冲突的提示HINT的组合。

 

提示HINT建议

  • 请慎重使用提示HINT，因为它们会导致很高的维护负荷。
  • 当硬编码提示HINT的有效性降低时，请注意它们对性能的影响。
  • 不到万不得已，不使用提示HINT作为优化 SQL 语句的手段。
  • 提示HINT可能会阻止优化程序使用更好的执行计划。
  • 当数据库结构或内容发生变化时，提示HINT的有效性可能会降低（甚至无效）。

 

 

优化程序提示HINT语法：示例

UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/  
products p 
SET   p.prod_min_price = 
        (SELECT  
         (pr.prod_list_price*.95) 
FROM products pr 
WHERE p.prod_id = pr.prod_id) 
WHERE p.prod_category = 'Men' 
AND   p.prod_status = 'available, on stock' 
/

 

显示了一个示例，其中的提示HINT建议基于成本的优化程序 (CBO) 使用索引。执行计划如下所示：

Execution Plan

———————————————————-

0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3 …)

1    0   UPDATE OF ‘PRODUCTS’

2    1     TABLE ACCESS (BY INDEX ROWID) OF ‘PRODUCTS’ (TABLE) (Cost…)

3    2      INDEX (RANGE SCAN) OF ‘PRODUCTS_PROD_CAT_IX’ (INDEX)

(cost…)

4    1     TABLE ACCESS (BY INDEX ROWID) OF ‘PRODUCTS’ (TABLE) (Cost…)
5    4       INDEX (UNIQUE SCAN) OF ‘PRODUCTS_PK’ (INDEX (UNIQUE))
(Cost=0 …)

示例中显示的提示HINT只有在名为 PRODUCTS_PROD_CAT_IX 的索引存在于 PRODUCTS 表的 PROD_CATEGORY 列中才起作用。

 

 

提示HINT类别

提示HINT可用于以下项：

  • 优化方法和目标
  • 访问路径
  • 查询转换
  • 联接顺序
  • 联接操作
  • 并行执行
  • 其它提示HINT

 

 

优化目标和方法

ALL_ROWS 选择一个基于成本的方法以获得最佳吞吐量
FIRST_ROWS(n) 指示 Oracle 服务器优化单条 SQL 语句以进行快速响应

 

注：ALTER SESSION…SET OPTIMIZER_MODE 语句不影响在 PL/SQL 内运行的 SQL。

 

ALL_ROWS：ALL_ROWS 提示HINT显式选择一个基于成本的方法来优化一个语句块，目标是获得最佳吞吐量。即，使资源总消耗降到最低。

FIRST_ROWS(n)：FIRST_ROWS(n) 提示HINT（其中 n 是任意正整数）指示 Oracle 服务器优化单条 SQL 语句以进行快速响应。它指示服务器选择可最高效地返回前 n 行的计划。FIRST_ROWS 提示HINT是为了实现向后兼容性并保持计划稳定而保留的，该提示HINT针对最佳计划进行优化以返回第一个单行。在包含任何阻塞操作（如排序或分组）的 SELECT 语句块中，优化程序会忽略此提示HINT。不会为了获得最佳响应时间而优化此类语句，因为 Oracle DB 在返回第一个行之前必须检索该语句访问的所有行。如果在任何此类语句中指定此提示HINT，则数据库会以获得最佳吞吐量为目标进行优化。

如果在 SQL 语句中指定 ALL_ROWS 或 FIRST_ROWS(n) 提示HINT，并且数据字典中没有有关该语句访问的表的统计信息，则优化程序会使用默认统计值估计缺少的统计信息，随后选择一个执行计划。

如果与 ALL_ROWS 或 FIRST_ROWS(n) 提示HINT一起指定了用于访问路径或联接操作的提示HINT，则优化程序会优先处理提示HINT指定的访问路径和联接操作。

注：FIRST_ROWS 提示HINT很可能是最有用的提示HINT。

 

访问路径的提示HINT

FULL 执行全表扫描
CLUSTER 通过聚簇扫描访问表
HASH 通过散列扫描访问表
ROWID 按 ROWID 访问表
INDEX 以升序扫描索引
INDEX_ASC 以升序扫描索引
INDEX_COMBINE 显式选择位图访问路径

 

 

只有某个索引存在从而确保指定访问路径的可用且访问路径位于 SQL 语句的语法结构中时，指定以上提示HINT之一才会促使优化程序选择指定的访问路径。如果提示HINT指定了一个不可用的访问路径，则优化程序会忽略它。您必须指定当出现在语句中时可以正确进行访问的表。如果语句使用表的别名，则提示HINT中应使用别名，而不是表名。如果语句中出现方案名称，则提示HINT中的表名不应包括方案名称。

FULL：FULL 提示HINT显式为指定表选择全表扫描。例如：

SELECT /*+ FULL(e) */ employee_id, last_name

FROM hr.employees e WHERE last_name LIKE ‘K%’;

Oracle 服务器对 employees 表执行全表扫描以执行此语句，即使 last_name 列上存在由 WHERE 子句中的条件启用的索引。

CLUSTER：CLUSTER 提示HINT指示优化程序使用聚簇扫描访问指定表。此提示HINT仅适用于聚
簇表。

HASH：HASH 提示HINT指示优化程序使用散列扫描访问指定表。此提示HINT仅适用于存储在表聚簇中的表。

ROWID：ROWID 提示HINT显式为指定表选择按 ROWID 扫描表。

 

INDEX：INDEX 提示HINT显式为指定表选择索引扫描。您可以为域、B* 树、位图和位图联接索引使用 INDEX 提示HINT。但是，最好为位图索引使用 INDEX_COMBINE 而不是 INDEX，因为前者是更通用的提示HINT。此提示HINT可根据需要指定一个或多个索引。

如果此提示HINT指定一个可用索引，则优化程序会按此索引执行扫描。优化程序不考虑执行全表扫描，也不按表中的其它索引执行扫描。

如果此提示HINT指定了一个可用索引列表，则优化程序会考虑按列表中的各个索引执行扫描的成本，然后执行成本最低的索引扫描。如果这样的访问路径具有最低成本，优化程序还会选择扫描此列表中的多个索引，并合并结果。优化程序不考虑执行全表扫描，也不按提示HINT中没有列出的索引进行扫描。

如果此提示HINT未指定任何索引，则优化程序会考虑按表的每个可用索引扫描的成本，然后执行成本最低的索引扫描。如果这样的访问路径具有最低成本，优化程序还会选择扫描多个索引，并合并结果。优化程序不考虑执行全表扫描。

INDEX_ASC：INDEX_ASC 提示HINT显式为指定表选择索引扫描。如果语句使用索引范围扫描，Oracle 服务器会按其索引值的升序扫描索引条目。由于服务器对范围扫描的默认行为是按其索引值的升序扫描索引条目，因此此提示HINT指定的内容不比 INDEX 提示HINT指定的多。但是，如果此默认行为发生更改，则您也许需要使用 INDEX_ASC 提示HINT显式指定升序的范围扫描。

INDEX_DESC：INDEX_DESC 提示HINT指示优化程序为指定表使用降序索引扫描。如果语句使用索引范围扫描，并且索引是升序的，则系统按其索引值的降序扫描索引条目。在分区索引中，结果按降序排列在各个分区中。对于降序索引，此提示HINT可有效消除降序，实现按升序扫描索引条目。

INDEX_COMBINE：INDEX_COMBINE 提示HINT显式为表选择位图访问路径。如果没有为 INDEX_COMBINE 提示HINT提供索引作为参数，则优化程序会为表使用具有最佳估计成本的位图索引布尔型组合。如果提供了某些索引作为参数，则优化程序会尝试使用这些特定位图索引的某种布尔型组合。

例如：

SELECT /*+INDEX_COMBINE(customers cust_gender_bix cust_yob_bix)*/ *

FROM customers WHERE cust_year_of_birth < 70 AND cust_gender = ‘M’;

注：INDEX、INDEX_FFS 和 INDEX_SS 具有反义提示HINT，分别为 NO_INDEX、NO_INDEX_FFS 和 NO_INDEX_SS，用于防止使用这些路径。

 

访问路径的提示HINT

INDEX_JOIN 指示优化程序使用索引联接作为访问路径
INDEX_DESC 为指定表选择索引扫描
INDEX_FFS 执行快速完全索引扫描
INDEX_SS 执行索引跳过扫描
NO_INDEX 不允许使用索引集
AND_EQUAL 合并单列索引

 

INDEX_JOIN：INDEX_JOIN 提示HINT显式地指示优化程序使用索引联接作为访问路径。为了让提示HINT产生积极的影响，索引的数目要尽可能少，但必须包括解析查询所必需的列的所有索引。

例如，下列查询使用索引联接访问 employee_id 和 department_id 列，这两列在员工表中都已被编制索引。

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/ employee_id, department_id
FROM hr.employees WHERE department_id > 50;

INDEX_DESC：INDEX_DESC 提示HINT显式为指定表选择索引扫描。如果语句使用索引范围扫描，则 Oracle 服务器将按索引值的降序扫描索引条目。在分区索引中，结果按降序排列在各个分区中。

例如：

SELECT /*+ INDEX_DESC(a ord_order_date_ix) */ a.order_date, a.promotion_id, a.order_id
FROM oe.orders a WHERE a.order_date < ’01-jan-1985′;

 

INDEX_FFS：INDEX_FFS 提示HINT会导致执行快速完全索引扫描，而不是全表扫描。

例如：

SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*)
FROM order_items l, orders o
WHERE l.order_id > 50 AND l.order_id = o.order_id;

INDEX_SS：INDEX_SS 提示HINT指示优化程序为指定表的指定索引执行索引跳过扫描。如果语句使用索引范围扫描，则系统将按索引值的升序扫描索引条目。在分区索引中，结果按升序排列在各个分区中。还有 INDEX_SS_ASC 和 INDEX_SS_DESC 提示HINT。

NO_INDEX：NO_INDEX 提示HINT显式禁止指定表使用索引集。

  • 如果此提示HINT指定了单个可用索引，则优化程序将不考虑按此索引执行扫描，但仍考虑其它没有指定的索引。
  • 如果此提示HINT指定了一个可用索引列表，则优化程序将不考虑按其中的任何指定索引执行扫描，但仍考虑列表中没有指定的其它索引。
  • 如果此提示HINT没有指定任何索引，则优化程序不考虑按表的任何索引执行扫描。这种行为与指定了表的所有可用索引列表的 NO_INDEX 提示HINT的行为相同。

NO_INDEX 提示HINT适用于基于函数的索引、B* 树索引、位图索引或域索引。如果 NO_INDEX 提示HINT和某个索引提示HINT（INDEX、INDEX_ASC、INDEX_DESC、 INDEX_COMBINE 或 INDEX_FFS）同时指定了相同的索引，则这些指定索引的 NO_INDEX 提示HINT和索引提示HINT都会被忽略，优化程序会考虑这些指定的索引。

例如：

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees WHERE employee_id > 200;

AND_EQUAL：AND_EQUAL 提示HINT显式选择一个执行计划，该计划使用可合并几个单列索引的扫描的访问路径，在该路径中您可以指定：

  • 与要合并的索引相关联的表的名称或别名。
  • 要执行索引扫描的索引。必须至少指定两个索引，但不能多于五个。

 

INDEX_COMBINE 提示HINT：示例

SELECT –+INDEX_COMBINE(CUSTOMERS)

       cust_last_name

FROM  SH.CUSTOMERS

WHERE ( CUST_GENDER= ‘F’ AND

CUST_MARITAL_STATUS =  ‘single’)

OR     CUST_YEAR_OF_BIRTH BETWEEN ‘1917’ 
AND ‘1920’;

 

INDEX_COMBINE 提示HINT设计用于位图索引操作。请牢记以下内容：

  • 如果为提示HINT提供了某些索引作为参数，则优化程序会尝试使用这些特定位图索引的某种组合。
  • 如果提示HINT中没有列出任何索引，则认为所有索引都包含在提示HINT中。
  • 优化程序总是尝试使用提示HINT的索引，无论它们是否具有成本效益。

在幻灯片的示例中，假定幻灯片中语句的 WHERE 谓词引用的三个列（CUST_MARITAL_STATUS、CUST_GENDER 和 CUST_YEAR_OF_BIRTH）都有一个位图索引。启用 AUTOTRACE 时，语句的执行计划可以显示出来，如下一张幻灯片所示。

 

INDEX_COMBINE 提示HINT：示例

 

Execution Plan

—————————————————

|   0 | SELECT STATEMENT              |

|   1 |  TABLE ACCESS BY INDEX ROWID  | CUSTOMERS

|   2 |   BITMAP CONVERSION TO ROWIDS |

|   3 |    BITMAP OR                  |

|   4 |     BITMAP MERGE              |

|   5 |      BITMAP INDEX RANGE SCAN  | CUST_YOB_BIX

|   6 |     BITMAP AND                |

|   7 |      BITMAP INDEX SINGLE VALUE| CUST_MARITAL_BIX

|   8 |      BITMAP INDEX SINGLE VALUE| CUST_GENDER_BIX

 

 

查询转换的提示HINT

NO_QUERY_TRANSFORMATION 跳过所有查询转换
USE_CONCAT 将 OR 重写成 UNION ALL 并禁用 INLIST 处理
NO_EXPAND 阻止 OR 扩展
REWRITE 根据实体化视图重写查询
NO_REWRITE 关闭查询重写
UNNEST 将子查询主体合并到外围查询块中
NO_UNNEST 关闭取消嵌套

 

NO_QUERY_TRANSFORMATION：NO_QUERY_TRANSFORMATION 提示HINT指示优化程序跳过所有查询转换，包括但不限于 OR 扩展、视图合并、子查询取消嵌套、星形转换和实体化视图重写。

USE_CONCAT：USE_CONCAT 提示HINT使用 UNIONALL 集合运算符强制将查询的 WHERE 子句中的组合 OR 条件转换成复合查询。通常，只有当使用串联的查询成本比不使用串联的查询成本低时，才发生此转换。USE_CONCAT 提示HINT禁用 IN 列表处理。

NO_EXPAND：NO_EXPAND 提示HINT阻止基于成本的优化程序考虑为 WHERE 子句中有 OR 条件或 IN 列表的查询使用 OR 扩展。通常，优化程序会考虑使用 OR 扩展，如果它确定使用此方法的成本低于不使用此方法的成本，就会使用该方法。

REWRITE：REWRITE 提示HINT指示优化程序根据实体化视图在可能时重写查询，而不考虑成本。使用 REWRITE 提示HINT时，可以指定视图列表，也可以不指定视图列表。此课程不介绍实体化视图。

UNNEST：UNNEST 提示HINT允许优化程序在评估路径和联接时同时考虑子查询及其外围查询，指示优化程序对子查询取消嵌套，并将子查询主体合并到包围它的查询块的主体中。

 

查询转换的提示HINT 

MERGE 将复杂视图或子查询与其外围查询合并
NO_MERGE 阻止合并可合并的视图
STAR_TRANSFORMATION 让优化程序使用可在其中使用转换的最佳计划
FACT 指示应将被提示HINT表当作事实表
NO_FACT 指示不应将被提示HINT表当作事实表

 

MERGE：MERGE 提示HINT允许您为每个查询合并一个视图。如果某个视图的查询包含了 GROUP BY 子句或者在 SELECT 列表中使用了 DISTINCT 运算符，则只有启用了复杂视图合并时优化程序才能将视图的查询合并到正在访问的语句中。这是默认行为，但您可以使用 NO_MERGE 之类的提示HINT禁用此机制。也可以使用复杂合并将 IN 子查询合并到正在访问的语句中（如果该子查询没有关联）。

在不提供参数的情况下使用 MERGE 提示HINT时，应将该提示HINT放在视图查询块中。在提供视图名称作为参数的情况下使用 MERGE 时，应将该提示HINT放在外围查询中。

NO_MERGE：NO_MERGE 提示HINT会使 Oracle 服务器不合并可以被合并的视图。通过此提示HINT，用户可以对视图的访问方式施加更大的影响。在不提供参数的情况下使用 NO_MERGE 提示HINT时，应将该提示HINT放在视图查询块中。在提供视图名称作为参数的情况下使用 NO_MERGE 时，应将该提示HINT放在外围查询中。

 

STAR_TRANSFORMATION：STAR_TRANSFORMATION 提示HINT可使优化程序使用其中使用了转换的最佳计划。如果没有提示HINT，优化程序可能会基于成本作出决定，使用在没有转换时生成的最佳计划，而不使用已转换查询的最佳计划。

即使使用提示HINT，也不能保证转换一定会发生。优化程序仅在它认为合理时才生成子查询。如果没有生成子查询，则没有转换的查询，此时不管是否有提示HINT，都使用未转换查询的最佳计划。

FACT：FACT 提示HINT在星形转换上下文中使用，用于指示转换时应将被提示HINT表作为事实表。

NO_FACT：NO_FACT 提示HINT在星形转换上下文中使用，用于指示转换时不应将提示HINT表作为事实表。

 

联接顺序的提示HINT

ORDERED 使 Oracle 服务器按表在 FROM 子句中出现的顺序联接表
LEADING 将指定表用作联接顺序中的第一个表

 

下列提示HINT用于建议联接顺序：

ORDERED：ORDERED 使 Oracle 服务器按表在 FROM 子句中出现的顺序联接这些表。如果在执行联接的 SQL 语句中省略了 ORDERED 提示HINT，则优化程序会选择表的联接顺序。如果您知道某些信息（如从每个表选择多少行），而优化程序不了解这些信息，则可能需要使用 ORDERED 提示HINT指定联接顺序。以一个嵌套循环为例，最准确的方法是在 FROM 子句中按索引中的关键字的顺序排列这些表，大表放在最后。然后使用下列提示HINT：

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

这里，facts 是表，而 fact_concat 是索引。更常用的方法是使用 STAR 提示HINT。

LEADING：LEADING 提示HINT指示优化程序在执行计划中首先联接指定的表集。如果由于联接图中的相关性，不能首先按照指定的顺序联接指定表，则忽略 LEADING 提示HINT。如果针对不同的表指定了两个或多个 LEADING 提示HINT，则所有提示HINT都将被忽略。如果指定了 ORDERED 提示HINT，则将覆盖所有 LEADING 提示HINT。

 

联接操作的提示HINT

USE_NL 使用嵌套循环联接来联接指定表
NO_USE_NL 不使用嵌套循环执行联接
USE_NL_WITH_INDEX 类似于 USE_NL，但必须能够将一个索引用于联接
USE_MERGE 使用排序合并联接来联接指定表
NO_USE_MERGE 不为联接执行排序合并操作
USE_HASH 使用散列联接来联接指定表
NO_USE_HASH 不使用散列联接
DRIVING_SITE 指示优化程序在数据库选择的站点之外的站点执行查询

 

这里描述的每个提示HINT都建议了一种针对表的联接操作。在提示HINT中，指定表的方式必须与在语句中指定表的方式完全相同。如果语句使用表的别名，则在提示HINT中必须使用该别名，而不是表名。但是，提示HINT中的表名称不应包含方案名称（如果语句中包含方案名称）。建议将 USE_NL 和 USE_MERGE 提示HINT与 ORDERED 提示HINT组合使用。当引用的表被强制成为联接的内部表时，Oracle 服务器使用这些提示HINT；如果引用的表是外部表，则忽略提示HINT。

USE_NL：USE_NL 提示HINT促使 Oracle 服务器使用嵌套循环联接（将指定表用作内部表）将每一个指定表与另一个行来源相联接。如果需要优化语句以获得最佳响应时间，或使返回查询所选的第一行所需的用时最小，而不是为了获得最佳吞吐量，则可以通过使用 USE_NL 提示HINT，强制优化程序选择嵌套循环联接。

USE_NL_WITH_INDEX：USE_NL_WITH_INDEX 提示HINT类似于 USE_NL 提示HINT。但是，如果没有指定索引，优化程序必须能够使用某个索引，且该索引使用至少一个联接谓词作为索引关键字。如果指定了索引，优化程序必须能够使用该索引，且该索引使用至少一个联接谓词作为索引关键字。

NO_USE_NL：NO_USE_NL 提示HINT促使优化程序排除嵌套循环联接。但是，在某些情况下只能使用嵌套循环来联接表。在这类情况下，优化程序将忽略这些表的提示HINT。

 

在许多情况下，嵌套循环联接能比排序合并联接更快地返回第一行。在从一个表中读取第一个选定行，从另一个表中读取第一个匹配行，然后将它们组合在一起后，嵌套循环联接即可以返回第一个行。但是排序合并联接需要读取两个表的所有选定行并对它们进行排序，将每个已排序行来源的第一行组合在一起，所有这些完成后才能返回第一行。

在下面的语句中，通过一个提示HINT强制实施了嵌套循环，通过全表扫描访问 orders，并将 l.order_id = h.order_id 筛选条件应用于每个行。针对满足筛选条件的每个行，都会通过索引 order_id 访问 order_items。

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM oe.orders h ,oe.order_items l
WHERE l.order_id = h.order_id;

在查询中添加 INDEX 提示HINT可以避免对 orders 执行全表扫描，这会导致执行计划类似于较大系统上使用的执行计划，即使在这里它可能并不是特别高效。

USE_MERGE：USE_MERGE 提示HINT促使 Oracle 服务器使用排序合并联接将每个指定表与另一个行来源联接，如下面的示例所示：

SELECT /*+USE_MERGE(employees departments)*/ * FROM employees, departments WHERE employees.department_id = departments.department_id;

NO_USE_MERGE：NO_USE_MERGE 提示HINT促使优化程序排除排序合并联接，并使用指定表作为内部表将每个指定表联接到另一个行来源。

USE_HASH：USE_HASH 提示HINT促使 Oracle 服务器使用散列联接将每个指定表与另一个行来源联接，如下例所示：

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id,
l2.product_id, SUM(l2.unit_price*quantity)
FROM oe.orders l, oe.order_items l2
WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;

下面是另一个示例：

SELECT /*+use_hash(employees departments)*/ *
FROM hr.employees, hr.departments
WHERE employees.department_id = departments.department_id;

NO_USE_HASH：NO_USE_HASH 提示HINT促使优化程序排除散列联接，并使用指定表作为内部表将每个指定表联接到另一个行来源。

DRIVING_SITE：此提示HINT指示优化程序在数据库选择的站点之外的站点执行查询。如果您使用分布式查询优化决定应在哪个站点执行联接，则此提示HINT很有用。

 

在许多情况下，嵌套循环联接能比排序合并联接更快地返回第一行。在从一个表中读取第一个选定行，从另一个表中读取第一个匹配行，然后将它们组合在一起后，嵌套循环联接即可以返回第一个行。但是排序合并联接需要读取两个表的所有选定行并对它们进行排序，将每个已排序行来源的第一行组合在一起，所有这些完成后才能返回第一行。

在下面的语句中，通过一个提示HINT强制实施了嵌套循环，通过全表扫描访问 orders，并将 l.order_id = h.order_id 筛选条件应用于每个行。针对满足筛选条件的每个行，都会通过索引 order_id 访问 order_items。

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM oe.orders h ,oe.order_items l
WHERE l.order_id = h.order_id;

在查询中添加 INDEX 提示HINT可以避免对 orders 执行全表扫描，这会导致执行计划类似于较大系统上使用的执行计划，即使在这里它可能并不是特别高效。

USE_MERGE：USE_MERGE 提示HINT促使 Oracle 服务器使用排序合并联接将每个指定表与另一个行来源联接，如下面的示例所示：

SELECT /*+USE_MERGE(employees departments)*/ * FROM employees, departments WHERE employees.department_id = departments.department_id;

NO_USE_MERGE：NO_USE_MERGE 提示HINT促使优化程序排除排序合并联接，并使用指定表作为内部表将每个指定表联接到另一个行来源。

USE_HASH：USE_HASH 提示HINT促使 Oracle 服务器使用散列联接将每个指定表与另一个行来源联接，如下例所示：

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id,
l2.product_id, SUM(l2.unit_price*quantity)
FROM oe.orders l, oe.order_items l2
WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;

下面是另一个示例：

SELECT /*+use_hash(employees departments)*/ *
FROM hr.employees, hr.departments
WHERE employees.department_id = departments.department_id;

NO_USE_HASH：NO_USE_HASH 提示HINT促使优化程序排除散列联接，并使用指定表作为内部表将每个指定表联接到另一个行来源。

DRIVING_SITE：此提示HINT指示优化程序在数据库选择的站点之外的站点执行查询。如果您使用分布式查询优化决定应在哪个站点执行联接，则此提示HINT很有用。

 

其它提示HINT

APPEND 启用直接路径 INSERT
NOAPPEND 启用常规 INSERT
ORDERED_PREDICATES 强制优化程序保留谓词计算顺序
CURSOR_SHARING_EXACT 阻止用绑定变量替换文字
CACHE 覆盖表的默认高速缓存规范
PUSH_PRED 将联接谓词推入视图
PUSH_SUBQ 首先计算未合并的子查询
DYNAMIC_SAMPLING 控制动态采样以改善服务器性能

 

APPEND：APPEND 提示HINT允许您在数据库以串行模式运行时启用直接路径 INSERT。如果使用的不是企业版，则数据库处于串行模式。常规 INSERT 是串行模式下的默认设置，而直接路径 INSERT 是并行模式下的默认模式。在直接路径 INSERT 中，数据被附加到表的末尾，而不使用当前分配给表的现有空间。因此，直接路径 INSERT 比常规 INSERT 快很多。

NOAPPEND：NOAPPEND 提示HINT通过在 INSERT 语句执行期间禁用并行模式来启用直接路径 INSERT。（常规 INSERT 是串行模式下的默认设置，而直接路径 INSERT 是并行模式下的默认模式。）

ORDERED_PREDICATES：ORDERED_PREDICATES 提示HINT强制优化程序保留谓词的计算顺序，但谓词被用作索引关键字时除外。可在 SELECT 语句的 WHERE 子句中使用此提示HINT。

 

如果不使用 ORDERED_PREDICATES 提示HINT，Oracle 服务器将按下列顺序计算所有谓词：

  1. 首先按照 WHERE 子句中指定的顺序计算不带用户定义函数、类型方法或子查询的
    谓词。
  2. 接下来按照成本的升序计算带用户定义函数的谓词和带具有用户计算成本的类型方法的谓词。
  3. 随后按 WHERE 子句中指定的顺序计算带用户定义函数的谓词和带没有用户计算成本的类型方法的谓词。
  4. 然后计算 WHERE 子句中没有指定的谓词（例如，优化程序生成的过渡性谓词）。
  5. 最后按 WHERE 子句中指定的顺序计算不带子查询的谓词。

CURSOR_SHARING_EXACT：Oracle 服务器可以用绑定变量替换 SQL 语句中的文字（如果这样做是安全的）。此操作是由 CURSOR_SHARING 启动参数控制的。CURSOR_SHARING_EXACT 提示HINT可禁用此行为。换句话说，Oracle 服务器执行 SQL 语句时将不尝试用绑定变量替换文字。

CACHE：CACHE 提示HINT指示优化程序在执行全表扫描时将为表检索的块放在缓冲区高速缓存的相应热部分中。此提示HINT对于小型查找表很有用。

如 V$SYSSTAT 数据字典视图中所示，CACHE 和 NOCACHE 提示HINT会影响系统统计信息表扫描（长表）和表扫描（短表）。

PUSH_PRED：PUSH_PRED 提示HINT指示优化程序将联接谓词推到视图中。

PUSH_SUBQ：PUSH_SUBQ 提示HINT指示优化程序在执行计划中尽可能早地计算未合并的子查询。如果按常规，在执行计划中最后才会执行未合并的子查询。如果执行子查询的成本相对便宜，并且可以显著减少行的数量，则较早计算子查询可以改善性能。如果子查询应用于远程表，或应用于使用合并联接联接的表，则此提示HINT不会产生任何影响。

DYNAMIC_SAMPLING：DYNAMIC_SAMPLING 提示HINT使您可以通过确定更准确的选择性和基数估计值来控制动态采样，从而改善服务器性能。可以将 DYNAMIC_SAMPLING 的值设置为 0 到 10 之间的值。级别越高，编译人员对动态采样施加的影响越大，应用的范围越广。采样默认使用游标级别，除非您指定了一个表。

请看以下示例：

SELECT /*+ dynamic_sampling(1) */ * FROM …

如果满足所有下列条件，此示例将启用动态采样：

  • 查询中有多个表。
  • 至少一个表未被分析，且没有索引。
  • 优化程序确定需要对尚未进行分析的表执行一个成本相对较高的表扫描。

 

其它提示HINT

MONITOR 强制实施实时查询监控
NO_MONITOR 禁用实时查询监控
RESULT_CACHE 高速缓存查询或查询片段的结果
NO_RESULT_CACHE 为查询或查询片断禁用结果高速
缓存
OPT_PARAM 针对查询持续时间设置初始化参数

 

MONITOR：MONITOR 提示HINT为查询强制实施实时 SQL 监控，即使语句不是长时间运行的。只有在 CONTROL_MANAGEMENT_PACK_ACCESS 参数设置为 DIAGNOSTIC+TUNING 时，此提示HINT才有效。

NO_MONITOR：NO_MONITOR 提示HINT为查询禁用实时 SQL 监控。

RESULT_CACHE：RESULT_CACHE 提示HINT指示数据库将当前查询或查询片断的结果高速缓存在内存中，然后在今后执行查询或查询片断时使用已缓存的结果。

NO_RESULT_CACHE：如果将 RESULT_CACHE_MODE 初始化参数设置为 FORCE，则优化程序会将查询结果高速缓存在结果高速缓存中。在这种情况下，NO_RESULT_CACHE 提示HINT会为当前查询禁用此类高速缓存。

OPT_PARAM：OPT_PARAM 提示HINT使您可以仅针对当前查询的持续时间设置初始化参数。此提示HINT仅对以下参数有效：OPTIMIZER_DYNAMIC_SAMPLING、OPTIMIZER_INDEX_CACHING、OPTIMIZER_INDEX_COST_ADJ、OPTIMIZER_SECURE_VIEW_MERGING 和 STAR_TRANSFORMATION_ENABLED

 

提示HINT和视图

  • 不要使用视图中的提示HINT。
  • 使用视图优化技术：

–语句转换

–像访问表一样访问结果

  • 可以使用关于可合并视图和不可合并视图的提示HINT。

由于可以在一个上下文中定义视图，然后将其用于其它上下文中，因此不应使用视图中的提示HINT或使用关于视图的提示HINT；这样的提示HINT可能会导致意外的计划。尤其要注意的是，根据视图是否可合并到顶层查询中，视图中的提示HINT的处理方式不同于关于视图的提示HINT的处理
方式。

视图优化

该语句通常会转换成访问视图基表的等效语句。优化程序可以使用下列技术之一转换语句：

  • 将视图的查询合并到访问语句中的引用查询块中。
  • 将引用查询块的谓词推到视图中。

当上述转换不可能发生时，将执行视图的查询，并像访问表一样访问结果。这发生在执行计划的 VIEW 步骤。

 

可合并视图

如果视图定义不包含以下项，则优化程序可以将视图合并到引用查询块中：

  • 集合运算符（UNION、UNION ALL、INTERSECT、MINUS）
  • CONNECT BY 子句
  • ROWNUM 伪列
  • 在选择列表中有组函数（AVG、COUNT、MAX、MIN、SUM）

提示HINT和可合并视图

优化方法和目标提示HINT可能出现在顶层查询或视图中：

  • 如果顶层查询中存在这样的提示HINT，则不管视图中是否包含此类提示HINT，都使用此提示HINT。
  • 如果没有顶层优化程序模式提示HINT，只要视图中的所有模式提示HINT是一致的，就使用被引用视图中的模式提示HINT。
  • 如果被引用视图中有两个或多个模式提示HINT相互冲突，则放弃视图中的所有模式提示HINT，而使用默认的或用户指定的会话模式。

关于被引用视图的访问方法提示HINT和联接提示HINT将被忽略，除非视图只包含一个表（或引用只包含一个表的另一视图）。对于这样的单表视图，关于视图的访问方法提示HINT或联接提示HINT将应用于视图中的表。

访问方法提示HINT和联接提示HINT也可能出现在视图定义中：

  • 如果视图是子查询（即，如果视图出现在 SELECT 语句的 FROM 子句中），则当视图与顶层查询合并时将保留视图中的所有访问方法提示HINT和联接提示HINT。
  • 如果视图不是子查询，则仅当顶层查询没有引用其它表或视图时（即，如果 SELECT 语句的 FROM 子句仅包含此视图），才会保留视图中的访问方法提示HINT和联接提示HINT。

提示HINT和不可合并视图

使用不可合并视图时，视图中的优化程序模式提示HINT将被忽略。由顶层查询来决定优化模式。

由于不可合并视图是独立于顶层查询单独进行优化的，因此视图中的访问方法提示HINT和联接提示HINT总是被保留。出于同样的原因，顶层查询中关于视图的访问方法提示HINT将被忽略。

但是，由于（在这种情况下）不可合并视图与表类似，因此会保留顶层查询中关于视图的联接提示HINT。

 

全局表提示HINT

  • 扩展的提示HINT语法使您可以为出现在视图中的表指定提示HINT
  • 使用递归点表示法在提示HINT中引用表名称

CREATE view city_view AS

SELECT *

FROM   customers c

WHERE  cust_city like ‘S%’;

 

SELECT /*+ index(v.c cust_credit_limit_idx) */

    v.cust_last_name, v.cust_credit_limit

FROM   city_view v

WHERE  cust_credit_limit > 5000;

 

常规情况下，指定了表的提示HINT引用此提示HINT所在的 DELETE、SELECT 或 UPDATE 查询块中的表，而不是语句所引用的视图内的表。如果需要为视图内的表指定提示HINT，建议您使用全局提示HINT，而不要将提示HINT嵌入到视图中。

如幻灯片所示，通过使用扩展的表指定语法（该语法包括视图名称和表名称），可将表提示HINT转换成全局提示HINT。另外，在指定表之前还可以选择指定一个查询块名称。

例如，通过使用全局提示HINT结构，就不需要在视图主体中指定索引提示HINT，从而避免修改视图。

注：如果全局提示HINT引用了在同一查询中使用了两次的表名称或别名（例如，在 UNION 语句中），则提示HINT仅应用于表（或别名）的第一个实例。

 

 

在提示HINT中指定一个查询块

explain plan for

select /*+ FULL(@strange dept) */ ename

from emp e, (select /*+ QB_NAME(strange) */ * 

             from dept where deptno=10) d

where e.deptno = d.deptno and d.loc = ‘C’;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ‘ALL’));

 

Plan hash value: 615168685

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

—————————————————————
|   0 | SELECT STATEMENT   |      |     1 |    41 |     7 (15)|

|*  1 |  HASH JOIN         |      |     1 |    41 |     7 (15)|

|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    21 |     3  (0)|
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    60 |     3  (0)|
—————————————————————

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$DB579D14

   2 – SEL$DB579D14 / DEPT@STRANGE

   3 – SEL$DB579D14 / E@SEL$1

 

在许多提示HINT中都可以指定一个可选的查询块名称，以指定提示HINT所应用于的查询块。此语法使您可以在外层查询中指定一个应用于内嵌视图的提示HINT。

查询块变量的语法采用 @queryblock 形式，其中 queryblock 是一个标识符，用于指定查询中的一个查询块。queryblock 标识符可以是系统生成的，也可以是用户指定的。在查询块自身中指定一个提示HINT，也可以将提示HINT应用于该查询块，并不一定要指定 @queryblock 语法。

幻灯片给出了一个示例。可以看到 SELECT 语句使用了一个内嵌视图。通过使用 QB_NAME 提示HINT，为相应查询块指定名称 strange。

本示例假设，DEPT 表的 DEPTNO 列有一个索引，这样优化程序通常可以选择该索引来访问 DEPT 表。不过，由于您指定 FULL 提示HINT应用于主查询块中的 strange 查询块，所以优化程序不使用上述索引。可以看到执行计划显示出对 DEPT 表执行了一个全表扫描。另外，计划的输出还清楚显示了原始查询中的各个查询块的系统生成名称。

 

指定完整的提示HINT集

SELECT /*+ LEADING(e2 e1) USE_NL(e1)
   INDEX(e1 emp_emp_id_pk) USE_MERGE(j) FULL(j) */

    e1.first_name, e1.last_name, j.job_id,
     sum(e2.salary) total_sal

FROM hr.employees e1, hr.employees e2, hr.job_history j

WHERE e1.employee_id = e2.manager_id

AND e1.employee_id = j.employee_id

AND e1.hire_date = j.start_date

GROUP BY e1.first_name, e1.last_name, j.job_id

ORDER BY total_sal;

 

使用提示HINT时，您有时可能需要指定一个完整的提示HINT集，以保证使用最佳执行计划。例如，如果您有一个包含许多表联接的非常复杂的查询，并且，如果您仅为给定表指定 INDEX 提示HINT，则优化程序需要自己确定要使用的访问路径以及相应的联接方法等等。因此，即使您给出 INDEX 提示HINT，优化程序也不一定使用该提示HINT，因为优化程序可能已确定它选定的联接方法和访问路径不能使用请求的索引。

在本例中，LEADING 提示HINT指定了要使用的确切联接顺序。同时还指定了对不同的表要使用的联接方法。

 

 

Filed Under: Oracle, Oracle SQL性能调优

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号