在2022年Oracle Cloud World 总共一千两百多个各类演讲中，通过“Performance Tuning”（性能优化）筛选后有高达两百个，即将近六分之一的演讲都和性能有关，说明在强调机器学习、人工智能的自动化时代，优化依然是绕不开的一个热门话题。相信所有做过Oracle数据库优化的人都曾经或多或少使用过提示（Hint），而一提起提示，所有的DBA和开发人员都是又爱又恨。本文将通过回顾Oracle资深产品经理Maria Colgan的演讲，探讨如何利用 Oracle 数据库中优化器提示的强大功能来帮助提高 SQL 语句的性能。

在开始之前，您首先应该正确设定您的期望。通过阅读本文，不会立即使您成为优化器提示的专家，添加提示也不会神奇地使您遇到的“每个” SQL 语句更快。我们建议您仅在用尽所有其他 SQL 调优技术（包括收集准确的统计数据、使用 SQL 调优顾问、利用 SQL 计划管理等）后才考虑使用优化器提示。

非优化器提示

当我们在 Oracle 8i 中从基于规则的优化器切换到基于成本的优化器时，Oracle 引入了提示。提示的目的实际上并不是调整 SQL 查询，而是帮助我们测试优化器可以选择的所有其他执行计划选项。尽管最初引入提示是为了帮助测试优化器代码，但优化器并不是 Oracle 数据库中唯一使用它的地方。

Oracle 数据库中提供了几个非优化器的提示。例如，如果要执行直接路径加载操作，则需要使用 APPEND 提示。或者，如果您希望将对象缓存在缓冲区缓存中，您可以将 CACHE 提示。但是一些最有用的非优化器提示可以帮助我们理解执行 SQL 语句时会发生什么，比如下图中的GATHER_PLAN_STATISTICS提示：

通过这个提示，并且在通过DBMS_XPLAN显示执行计划时带上 ‘ALLSTATS LAST’ 这个选项，Oracle 将自动显示额外的列，以便在计划表中展现执行统计信息。其中最关键的是 E-Rows 和 A-Rows 列。E-Rows 列将显示优化器估计的应该从计划中的每个操作返回的行，这通常被称为基数估计。A-Rows 是实际返回的行，在执行后才能看到。这是非常有用的信息，因为 SQL 性能不佳的主要原因是优化器由于基数估计错误而选择了错误的计划。并排比较这两个值可以很容易地看出优化器出错的地方。

优化器提示

优化器提示影响的方方面面

优化器提示允许您影响优化器的各个方面。这意味着每次优化器必须在多个可能性之间进行选择时，您可以通过提示告诉它要做出何种选择。让我们仔细看看可以用来影响优化器各个方面的提示。

优化器所做的第一件事是尝试转换或重写您的 SQL 语句。查询转换旨在为优化器打开额外的访问或连接方法，以及连接顺序。一些查询转换总是根据查询的形状完成，但大多数都是基于成本的。这意味着优化器将在有无转换的情况下计算执行计划的成本，并选择成本最低的计划。提示可用于影响优化器基于成本的转换。这里列出了一些最常见的查询转换提示：

• MERGE
• USE_CONCAT
• REWRITE
• STAR_TRANSFORMATION
• UNNEST
• NO_QUERY_TRANSFORMATION

例如，MERGE 提示告诉优化器将视图定义插入查询并优化其连接顺序，包括视图中和视图外的所有表，而不是先执行视图的查询。一旦优化器尽可能地转换了查询，它就会继续确定执行计划。

您可以使用提示来影响执行计划的各个方面，从基数估计、访问方法、连接方法，最后是连接顺序。下图列出了每个方面最常见的一些提示，我们将在后面更详细地介绍其中重要的部分。另外，每个提示都有一个相应的否定提示（加上 NO_ 前缀）。例如，NO_FULL 提示会告诉优化器不要进行全表扫描。您可以在 SQL 参考指南的第 2 章和 SQL 调优指南的第 19 章中获得有关可用提示的更多信息。

优化器提示的作用范围

优化器提示可以根据提示的范围分为四类：

1.单表 - 在一个表或视图上指定的提示。比如：FULL、INDEX、USE_NL

2.多表 - 可以在一个或多个表或视图上指定的提示，用于影响表连接顺序。比如：LEADING、ORDERED

3.查询块 - 对单个查询块（Query Block）进行操作的提示。比如：STAR_TRANSFORMATION、UNNEST

4.语句 - 适用于整个 SQL 语句的提示。比如：ALL_ROWS、OPTIMIZER_FEATURES_ENABLE

在第一个类别中看到 USE_NL 您可能会感到惊讶，因为许多人没有意识到 USE_NL 提示只允许您指定应该在 NL 连接右侧使用的表。这通常被称为内表。

如何使用提示

提示的文本需要紧跟在SQL语句中的关键字之后，如SELECT、INSERT等。提示的语法与注释非常相似，只是需要在前缘加+号来标识。

尽管您可以在 SQL 语句中将提示和注释组合在一起，但不建议这样做，因为可能导致优化器在解析时找不到有效提示。这种情况最好创建两个单独的块，一个用于紧跟在 SQL 语句中的关键字之后的提示，另一个用于 SQL 语句中其他位置的注释。比如：

SELECT /*+ FULL(s) */ count(*) FROM sales s /* 这是注释 */;

您还需要正确引用提示中使用的表。如果表在 FROM子句中指定了别名，则在对该表指定任何提示时必须使用该别名。比如：

SELECT /*+ index(e pk_emp)*/ * FROM emp e;

在此示例中，emp 表在 FROM子句中指定了别名 e，因此索引提示也需要使用该别名指定。

既然提示应该放到SELECT等关键字的后面，那是不是所有提示都要放在最开始的关键字后面呢？来看看这个SQL：

在此查询中，employees （员工）表和 departments（部门）表都在 FROM子句中使用别名。提示的语法没有问题，但是为什么执行计划没有使用部门表上的全表扫描提示？这是因为是在错误的查询块中指定了提示。提示放在了外部查询块中，而部门表只出现在子查询中，被视为单独的查询块。因此，FULL 表扫描提示无效。关于如何确定提示是否被使用，我们在下一节再专门讨论。

把提示移动到合适的查询块之后即可解决这个问题：

通过将部门表上的 FULL 提示下移到子查询，就得到了期望的计划。

如果您不想在每个查询块中指定不同的提示集，也可以在外部查询中指定所有提示，前提是您要指明它们属于哪些查询块。Oracle 根据使用的关键字自动命名 SQL 语句中的每个查询块。例如，SELECT 命令中的外部查询块名称将称为 SEL$1。通过 DBMS_XPLAN 显示执行计划时，可以使用 +alias 格式参数在语句中找到每个查询块的名称，如下图所示：

通过使用 +alias 格式参数，计划表下将出现一个名为 Query Block Name / Object Alias 的新部分。在这里，您将看到哪个表别名出现在哪个查询块中。在此示例中，我们看到员工表 E 出现在 SEL$1 中，而部门表 D 出现在 SEL$2 中。

有了这个信息，在主查询的提示里就可以通过 @查询块名称 来引用子查询中的部门表：

或者，也可以利用 QB_NAME 提示为每个查询块指定唯一名称，然后在外部查询中引用它。这里通过 QB_NAME 提示将子查询查询块命名为 MY_SUBQ，然后在外部查询中使用 @MY_SUBQ 引用它：

如何确定提示是否被使用

您可以从两个地方获取有关未使用提示的原因。在 19c 之前，您必须通过10053 Trace（优化器跟踪文件）确定未使用提示的原因。自从 19c 引入了提示报告，事情变得容易多了，当您通过 DBMS_XPLAIN 显示它时，它会自动打印在执行计划下。

让我们用一个示例来说明当提示有语法错误时，您可以在 10053 Trace 文件中找到哪些信息：

在这个例子中，故意拼错了部门表上的 FULL 提示。要查找提示信息，请搜索“Dumping Hints”部分。在那里，您将找到每个指定的有效提示的条目。在这里，我们看到外部查询中员工表的 FULL 提示已解析并使用。但是，部门表上拼写有错误的提示并没有显示在其中，因为它被优化器当成了注释。

19c开始提供的新的提示报告（Hint Report）让您更好地了解每个提示发生了什么：

在这里，我们看到在查询块 SEL$2 中指定的提示中识别出 1 个语法错误，其中FULL提示缺少一个L。

如您所知，哪怕是对简单的 SQL 语句，优化器也会做出许多关于优化的决策。这些决定中的每一个都可能使您的提示不再适用。确保每次都获得相同计划的唯一方法是提供一套完整的提示来指导优化器的每一个决策。这套完整的提示通常被称为大纲（Outline）。当您使用 DBMS_XPLAN 显示计划时，您可以通过指定 +outline 格式参数来查看计划的大纲。

一些有用的优化器提示

优化器模式（Optimizer Mode）

让我们从控制优化器模式的三个简单提示开始。Oracle 优化器可以在三种模式下运行：All_ROWS、FIRST_ROWS(n) 和 RULE。All_ROWS是默认行为，优化器假定您希望使用尽可能低的成本计划或使用最少资源的计划获取此查询返回的所有行。

FIRST_ROWS(n) 模式允许优化器优化计划以仅返回前 n 行，在分页查询的应用场景中可以通过这个提示来获得一个尽快返回前几行数据的执行计划。

Oracle不建议您使用RULE提示，除非这是您的最后手段。基于规则的优化器在提示发明前就已经存在。因此，如果您希望RULE提示生效，它是您可以指定的唯一提示。您也不能利用 Oracle 7.3 之后数据库的任何功能或特性。

OPT_PARAM提示

一个很好用的提示是 OPT_PARAM 提示，它允许您为一个查询更改一个或多个优化器参数的值。当您遇到问题时，利用此提示是一种更优雅的方法来更改参数的默认设置，而不是为整个系统禁用该参数。通过这个提示，只能设置以下和优化器相关的 init.ora 参数：

• APROX_FOR_*
• OPTIMIZER_DYNAMIC_SAMPLING
• OPTIMIZER_INDEX_CACHING
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_USE_PENDING_STATISTICS
• OPTIMIZER_USE_INVISIBLE_INDEXES
• OPTIMIZER_SECURE_VIEW_MERGING
• OPTIMIZER_ADAPTIVE_PLANS
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY
• OPTIMIZER_ADAPTIVE_STATISTICS
• OPTIMIZER_INMEMORY_AWARE
• STAR_TRANSFORMATION_ENABLED
• PARALLEL_DEGREE_POLICY
• PARALLEL_DEGREE_LIMIT
• _fix_control
• 优化器相关的下划线参数（以 _optimizer开头的参数）

OPTIMIZER_FEATURES_ENABLE提示

OPTIMIZER_FEATURES_ENABLE 允许您在优化器版本之间切换。指定此提示会将优化器恢复到以前的数据库版本，禁用该版本中不可用的任何优化器功能。此提示允许您仅针对遇到问题的 SQL 语句将优化器恢复到旧版本来快速解决升级到新版本后可能看到的任何意外行为。系统的其余部分可以受益于最新的优化器功能。

为什么优化器提示会被忽略？

优化器提示被忽略的常见原因有以下几种情况：

1.提示不符合规范或者有拼写错误。请记住，您需要正确拼写提示和引用的对象，并使用 FROM子句中指定的表别名。

2.指定 INDEX 提示时表上需要有索引，否则Hint Reports会报Unresolved错误。这意味着提示虽然指定正确，但却是无效的，因为没有在对应的表上创建索引。

3.USE_HASH 提示如果用于非等值连接，Hint Reports会报Unused错误。只有当连接条件被指定为相等时，才能使用哈希连接。

4.提示的组合不合理。比如如果同时使用这两个提示: index(e empno_pk_ind) parallel(e 8) ，在Hint Reports会报Unused错误，指示parallel(e 8)未被使用。除非索引已分区，否则任何索引范围扫描都无法并行执行。

提示会增加代码维护的难度

在结束之前，再分享一些关于实现提示的最佳方式的建议，而无需更改应用程序或造成维护问题。如果可以通过提示解决性能问题，请通过使用 SQL 计划基线（Plan Baseline）来实现，特别是当您无法修改应用程序代码时很有用。即使可以修改代码，随着时间的推移，在SQL中添加的提示也很难管理，因为绝大多数人都不会在代码中添加大量注释来解释为什么原先需要这些提示。因此，提示一旦加上，每个人都不敢轻易再删除它们。强烈建议您利用 SQL 计划管理（SQL Plan Management - SPM，标准版和企业版均提供的免费工具）来影响优化器以选择您期望的使用了提示的计划，而无需直接修改 SQL 语句来添加提示。

总结

做为总结，你应该记住以下三点：

1.仅在必要时使用优化器提示，并且您确切地知道提示控制了什么。

2.如果必须使用提示，请确保使用完整的提示或大纲，以保证每次获得相同的执行计划。

3.如果您过去使用过优化器提示并且现在正在升级到新版本，比如 19c，请尝试通过利用隐含参数 _OPTIMIZER_IGNORE_HINTS 以测试在没有这些旧提示的情况下您的查询性能，以查看 19c 优化器是否可以自己做得更好。