Oracle性能诊断艺术-第7章

第7章

SQL优化技术

每

当查询优化器（query optimizer）不能自动产生高效的执行计划时，就需要一些手工的优化技术。为此，Oracle提供了多项技术，表7-1对它们进行了汇总。本章的目的不仅是要详述这些技术，还将解释每一项技术能做什么，以及在哪些情况下可以利用它们。为了从众多技术中做出选择，绝对有必要先问自己如下三个基础的问题。

q   这条SQL语句是已知并且确定不变的吗？

q   即将采用的措施会影响到单个会话（甚至整个系统）的某一条SQL语句还是全部SQL语句？

q   有可能改变这条SQL语句吗？

我来解释一下为什么这三个问题如此重要。首先，有时一些SQL语句在运行时才被生成并且常常是随每次执行而变化，它们几乎是不可知的。在另外一些情况下，查询优化器无法正确地处理被大量SQL语句所使用的特定结构（例如，WHERE子句中的一个无法使用索引的限制条件）。在这些情况下，你就要采用一些技术在会话级或系统级，而不是SQL语句级来解决问题。这个事实导致了两个主要的问题。一方面，就像在表7-1中概括的那样，有些技术只能够对某些特定的SQL语句使用，而几乎不可能适用于会话级和系统级。另一方面，在第5章中已经解释过，只要数据库没什么问题，而且查询优化器的配置也能正确执行，你通常只需针对一小部分的SQL语句进行优化。所以，要避免使用那些会影响查询优化器能够自动为其提供高效执行计划的SQL语句的技术。

其次，无论何时，处理一个无法控制SQL语句的应用程序（因为代码如同在一个封装好的应用程序中那样不可得；或是因为SQL语句是在运行时产生的），你都无法采用需要改变代码的技术。总的说来，你的选择通常是受限的。

表7-1　SQL调优技术及其影响

技　　术	系统级	会话级	SQL语句级	可用性
调整访问结构	ü			所有版本
调整SQL语句			ü*	所有版本
提示			ü*	所有版本
调整执行环境		ü	ü*	所有版本
SQL性能概要			ü	从10g†开始
存储纲要			ü	所有版本
SQL计划基线			ü	从11g‡开始

*          为了使用这项技术需要调整SQL语句。

†          需要调优包，因此需要企业版。

‡          需要企业版。

本章的目的不是讲述如何找出一个给定SQL语句的最优执行计划，比如，解释在哪种情况下哪个特殊的访问路径或连接方法应该被采用——这些分析放在第四部分。本章的唯一目标是描述可用的SQL优化技术。值得一提的是，除了改变访问结构和改变运行环境外，所有的SQL优化技术都是基于这样一个事实：由于查询优化器自身的局限导致它不能识别出一个高效执行计划。当然，前提是所有配置都已正常运行。在本章中，将假设所有初始化参数都已正确配置，并且所有必要的系统和对象统计信息都已就绪。

接下来，描述SQL优化技术的每一节都以相同的方式展现：首先是一个简短的介绍，接着叙述这项技术的运行机制以及何时应该使用它。最后，每一节的结尾都将介绍该项技术的缺陷和谬误。

7.1　改变访问结构

这项技术不和任何特性相联系，而仅仅是基于一个事实，SQL语句的响应时间往往不仅取决于这些数据是如何存储的，而且也取决于这些数据是如何访问的。

l        7.1.1　运行机制

当你质疑一条SQL语句的性能时，首先需要做的是检查当前的访问结构，基于从数据字典中获得的信息，回答下面几个问题。

q   语句中涉及的表的结构类型是什么？是堆表、索引组织表，还是外部表？以及表是否存储在聚簇中？

q   包含所需数据的物化视图可用吗？

q   在表、聚簇和物化视图中存在哪些索引？这些索引建立在哪些字段上，这些字段的顺序又如何？

q   所有这些数据段[①]是如何被分区的？

接下来需要评估，为了高效地处理你正在调优的这条SQL语句，目前可用的访问结构是否合适。比如，在分析过程中，你或许会发现再加一个额外的索引，对支持WHERE子句高效运作是非常必要的。假如你正在研究下面这条查询语句的性能：

 

通常，查询优化器会考虑采用下面的两种执行计划来运行它。第一种采用全表扫描来访问，第二种通过一个索引来访问。当然，后者必须是在索引存在的前提下。

 

 

关于这个话题我们不在此深入下去，因为将在第四部分仔细探讨，包括详细分析何时以及如何使用这些不同的访问结构。此刻，唯一重要的是，认识到这是一项基本的SQL优化技术。

l        7.1.2　何时使用

如果没有采用必要的访问结构，要优化一条SQL语句并使其高效执行几乎是不可能的。因此，只要你能改变访问结构，就应该考虑使用这项技术。不幸的是，并不是每次都有这样的机会，比如，你正在使用一个封装的应用程序，而卖家又没有提供对改变访问结构的支持。

l        7.1.3　缺陷和谬误

当打算改变访问结构时，最重要的是仔细考虑可能的副作用。一般来说，每个改变访问结构的做法都是一把双刃剑。事实上，采取这种调优所带来的影响不太可能仅仅作用于一条单一的SQL语句，很难找到不是这种情形的例子。例如，如同上例的第二个访问结构中那样，添加了索引，那么就必须考虑，索引会减慢对索引表进行INSERT和DELETE操作的速度，以及对索引列进行更新（UPDATE）操作的速度。你还需要检查是否有足够的空间可用于添加访问结构。综合考虑，在改变访问结构之前权衡利弊是十分必要的。

7.2　修改SQL语句

SQL是一种非常强大且灵活的查询语言。常常可以通过各种不同的方法提交一个完全相同的请求。对开发者来说，这是非常有用的。然而，对查询优化器来说，要为所有不同种类的SQL语句都提供高效的执行计划是一个真正的挑战。记住，灵活是性能之敌！

l        7.2.1　运行机制

假设你正用scott模式来执行一个查询以找出所有还没有员工的部门。在depts_wo_emps.sql这个脚本中有四条SQL语句，都能返回你需要的信息：

 

 

这些SQL语句的目的是相同的，它们返回的结果也完全一样。因此，你可能希望查询优化器也都能够提供相同的执行计划。然而，事实并非如此。实际上，只有第二和第四条语句使用了相同的执行计划，其他的都不尽相同。请注意，这些执行计划是在Oracle 10gR2中产生的，在其他版本中可能会有差异。

 

 

基本上，虽然访问数据的方法总是相同的，但对数据进行组合以产生结果集的方法却大相径庭。在这个特殊的例子中，两张表数据量都很小，可能让你注意不到这三种执行计划所导致的性能上的真实差异。但是，如果处理的表很大，就不是这个结果了。一般来讲，只要处理大批量的数据，执行计划上的任何微小差异都有可能导致响应时间和资源利用发生巨大的改变。

认识到完全一样的数据可以用多种不同的方法抽取是此处的关键点。无论何时，只要你准备对一条SQL语句进行调优，都要问自己是否有等价的SQL语句存在。如果有，仔细比较它们，从而确定谁能提供最佳的性能。

l        7.2.2　何时使用

只要能够把SQL语句修改得更好，就应该考虑使用这项技术，我实在想不出不这么做的理由。

l        7.2.3　缺陷和谬误

SQL语句由代码组成。编写代码的首要原则是让其可维护。要做到这一点，首先意味着代码需要有可读性和简洁性。不幸的是，对于SQL语言来说由于上文中提及的原因，最简洁可读的写法，并不总能带来最高效的执行计划。因此，在某些情况下你可能必须为了性能而牺牲可读和简洁，尽管只是在真正必要和有益的情况下才需要这样做。

7.3　提示

根据韦氏在线词典的解释，提示（hint）是指间接的或概要性的建议。根据Oracle的说法，提示的定义则稍有不同。简单来说，提示是为了影响查询优化器的决定而添加到SQL语句中的指示。换句话说，提示是这样一种东西，它对行为产生推动，而不仅仅是给出建议。在我看来，Oracle选择“hint”一词来对此特性命名似乎不是很恰当。不管怎样，名称并不重要，提示能为你做什么却很重要，只是不要让名称误导了你。

注意   因为提示仅是一个指示，所以并不意味着查询优化器总会利用它。但是，换一个角度来看，不能仅仅因为查询优化器没有采纳一个具体的提示，就认为提示仅仅是一个建议。在接下来的描述中将看到这样一些例子，它们使用了几乎无关或无效的提示。因此，根本没有影响到查询优化器产生的执行计划。

l        7.3.1　运行机制

接下来的小节描述提示是什么，有哪些种类，以及如何使用它们。在详细阅读后面的内容之前，有一件重要的事情需要注意，就是使用提示也许不像你想象得那么容易。实际上，在开发中经常见到有人错误地使用它。

1．提示是什么

当优化一条SQL语句的时候，查询优化器可能不得不考虑大量不同的执行计划。理论上，它需要考虑所有可能的执行计划。而现实中，除了一些简单的SQL语句外，为了使优化耗时保持在适度的范围内，考虑太多的组合方案是不可行的。随之而来的结果是，查询优化器未经检验就较早地排除了一部分执行计划。当然，完全忽略这部分计划的决定可能带来严重后果，而且查询优化器的可信度也会遭到质疑。

无论何时，当指定了一个提示，你的目标是减少查询优化器要评估的执行计划的数目。基本上，通过使用提示你可以告诉优化器，在优化某条指定的SQL语句时，应该考虑哪些操作，哪些则不必考虑。例如，假设优化器将为下面这条SQL语句产生一个执行计划。

 

如果emp表是堆表（heap table），并且列empno为索引列，那么查询优化器将至少考虑两种执行计划。第一种是通过一个全表扫描来读入emp表全部记录：

 

第二种是基于WHERE子句中的谓词（empno=7788）来进行索引查找，再通过索引返回的rowid来访问表。

 

在这个例子中，为了控制查询优化器给出的执行计划，可以添加一个提示来指定使用全表扫描还是索引扫描来进行访问。你需要理解的最重要的一点是，不能够告诉查询优化器“我想在emp表上进行全表扫描，你帮我找一个满足我需求的执行计划”。但你可以这么说：“如果你需要在全表扫描和索引扫描间做出选择，那么就选全表扫描吧。”这是一个很微小但却是实质性的差异。当查询优化器要在多个执行计划间做出选择的时候，提示可以帮你影响它的决定。

为了进一步强调这个要点，让我们基于图7-1中的决策树举一个例子。请注意，即使这个查询优化器以决策树的形式运作，这也是一个具有普遍性的例子，它并不和Oracle直接相关。在图7-1中，目的是从根节点（1）开始向下延伸，直到叶节点（111～123）为止。换句话说，目标是选择一条从A点到B点的路径。假设，因为某种原因，必须经过节点122。为此，用Oracle的说法，可以添加两个提示来剪去从节点12到节点121和123之间的分枝。这样，只剩下一条从节点12到节点122的路。但是，这还不足以保证路径一定经过节点122。实际上，如果节点1穿过了节点11而不是节点12，上面的两个提示将毫无作用。因此，为了保证经过节点122，你需要添加额外的提示来剪去从节点1到节点11间的分枝。

 

图7-1　修剪决策树

一些类似的事情也会发生在查询优化器的身上。实际上，只有当提示致力于让查询优化器接受一个不得不采纳的决定时，它才会被评估。提示要不多也不少。因为一旦你指定了某个提示，就可能被迫添加更多的提示来确保其工作。并且在实践中，随着产生的执行计划的复杂性的增加，要让找到的提示能带来期望中的执行计划会变得越来越困难。

2．指定提示

提示是Oracle为了不破坏和其他数据库引擎之间对SQL语句的兼容性而提供的一种扩展功能。Oracle决定把提示当作一种特殊的注释来添加。它和注释之间的区别如下。

q   提示必须紧跟着DELETE、INSERT、MERGE或UPDATE关键字。换句话说，提示不能像注释那样在SQL语句内随处可加。

q   在注释分隔符之后的第一个字符必须是加号（+）。

提示中的语法错误不会报错，如果解析器不能解析它，就会把它看作是一个真正的注释。也可以把注释和提示混在一起写。下面是两个例子，展示了如何对前一小节中讨论过的emp表的查询强制采用全表扫描：

 

3．提示的分类

关于提示分类有多种方法（或观点），就我个人而言，我喜欢使用下面的分法。

q   初始化参数提示可以覆盖在系统级或会话级定义的部分初始化参数。我将下列提示归于此类：all_rows，cursor_sharing_exact，dynamic_sampling，first_rows，gather_plan_statistics，no_cpu_costing，optimizer_features_enable，opt_param，(no_)result_cache，以及rule。我将在7.4节讲述这些提示，而gather_plan_statistics提示则放在第6章。请注意，这些提示被指定时，它们都会覆盖在实例级或会话级设置的值。

q   查询转化提示在逻辑优化阶段控制查询转化技术的使用。我将下列提示归于此类：(no_)elimi- nate_join，no_expand，(no_)merge，(no_)outer_join_to_inner，(no_)push_pred，(no_)push_ subq，no_query_transformation，(no_)rewrite，(no_)unnest，no_xmlindex_rewrite，no_xml_ query_rewrite，以及use_concat。我将在本章的稍后介绍其中部分提示，其他放在第10、11章叙述。

q   访问路径提示控制访问数据的方法（例如，是否使用索引）。我将下列提示归于此类：cluster，full，hash，(no_)index，index_asc，index_combine，index_desc，(no_)index_ffs，index_join，(no_)index_ss，index_ss_asc，以及index_ss_desc。我将在第9章探讨访问方法时，一起介绍这部分提示。

q   连接提示不仅控制连接的方法，而且控制连接表的顺序。我将下列提示归于此类：leading，(no_)nlj_batching，ordered，(no_)star_transformation，(no_)swap_join_inputs，(no_)use_hash，(no_)use_merge，use_merge_cartesian，(no_)use_nl，以及use_nl_with_index。我将在第10章中把它们和连接方法一起叙述。

q   并行处理提示控制如何使用并行处理。我将下列提示归于此类：(no_)parallel，(no_)parallel_ index，pq_distribute，以及(no_)px_join_filter。我将在第11章中随并行处理一起讲述它们。而关于提示pq_distribute的一个可能用法，将放到第10章中和partition-wise连接一起说明。

q   其他提示控制没有归到前几种分类的其他一些特性的使用。我将下列提示归于此类：(no_)append，(no_)cache，driving_site，model_min_analysis，(no_)monitor，以及qb_name。我将在本章稍后介绍qb_name提示，其余的放到第11章。

虽然我在本书中叙述和展示了大量关于这些提示的例子，但这仍不足以使本书成为关于提示的万能参考或语法大全。可以在SQL Reference手册的第2章中找到那些内容。

值得一提的是，有许多的提示（它们以no_为前缀）可以禁用某个操作或特性。这是很令人欣喜的，因为很多时候禁用一项操作或特性比启用它们更容易。

刚才列出的提示还不齐全。事实上，还有其他一些提示并没有在文档中提及，它们只在Oracle内部使用。你将在7.5节中看到一些案例。从Oracle 11g以后，你可以查询v$sql_hint视图来得到一个较齐全的列表。

4．提示的有效域

简单的SQL语句只有一个单独的查询块。当使用视图或类似于子查询、内联视图（in-line view）、集合操作符这样的结构时，就会出现多个查询块。譬如，下面这个查询有两个查询块（我使用分解的子查询替代一个真正的视图只是出于举例的考虑）。第一个是引用了dept表的主查询，第二个是引用了emp表的子查询：

 

初始化参数提示对整个SQL语句都有效。所有其他的提示仅仅对单个查询块起作用。仅仅对单个查询块起作用的提示，必须在它控制的查询块内指定。例如，如果你想对上面查询中的两个表都指定访问路径提示，则一个提示必须被加到主查询上，另外一个要加到子查询上。这两个提示的有效域都被严格限制在它们所在的查询块内。

 

这个规则也有例外，即全局提示。通过它们，可以使用点号（.）来引用包含在其他查询块（假设这些块已被命名）中的对象。比如，在下面这条SQL语句中，主查询就包括了一个为子查询准备的提示。请注意在引用时，子查询名是如何被使用的。

 

全局提示的语法可以支持两层以上的引用（比如，在一个视图中引用另一个视图）。对象间必须用点号（.）分隔。

既然WHERE子句中的子查询是没有命名的，它们的对象就不能被全局提示引用。为了解决这个问题，在Oracle 10g中，有另一种方法可以达到相同的目的。事实上，大多数提示都可以接受一个参数，指明它在哪个查询块中有效。通过这个方法，提示需要在SQL语句的开始就明确地给出，且只需要指明它所作用的查询块。为了配合这个动作，不仅查询优化器可以给每个查询块生成一个查询块名，而且还可以用提示qb_name手动为每个查询块命名。举例，在下面这个查询中，这两个查询块分别叫做main和sq。然后，在提示full里，通过前缀“at”符号（@）来引用它。注意在主查询中是如何指明对子查询中表emp的访问路径的。

 

上面这个例子展示了如何给查询块命名。现在来看如何使用查询优化器产生的命名。首先，必须知道它们叫什么。为此，可以使用SQL语句EXPLAIN PLAN和dbms_xplan包，就像下面这个例子中展示的这样。注意传递给函数display的alias选项是为了保证查询块名和别名被输出。

 

 

 

系统产生的查询块名是由一个前缀加一个由字母和数字组成的字符串构成。前缀是基于子查询中操作的类型。表7-2对此做了汇总。字符串是一个查询块的编号，它是按照SQL语句解析阶段查询块出现的位置（从左到右）来进行的。在上面的例子中，主查询块被命名为SEL$2，子查询块被命名为SEL$1。

表7-2　查询块名的前缀

前　　缀	用于语句
CRI$	CREATE INDEX语句
DEL$	DELETE语句
INS$	INSERT语句
MISC$	多类SQL语句，比如LOCK TABLE
MRG$	MERGE语句
SEL$	SELECT语句
SET$	集合操作，比如UNION和MINUS
UPD$	UPDATE语句

就像下面展示的这样，使用系统产生的查询块名的方法和使用用户自定义查询块名的方法没什么不同。

 

还要做最后一个说明，关于查询转换阶段产生的查询块的命名。由于在解析阶段SQL语句中还不包含它们，所以也就不能像其他部分那样给它们编号。在这种情况下，查询优化器为它们生成一个八个字符的哈希值。下面这个例子说明了这种情况。这里，系统产生的查询块名为SEL$5DA710D3。

 

 

在上面的输出中，可以注意到一种有意思的情况：当这样的转换发生时，执行计划中的某些行有两个查询块名。它们都可以在提示中使用。然而，从查询优化器的角度来说，转换之后生成的查询块名（这里是SEL$5DA710D3）只当有一个完全相同的转换发生时，才会再次被用到。

l        7.3.2　何时使用

提示有双重目的。第一，当查询优化器不能自动产生一个高效的执行计划时，它可以作为过渡。那时，可以用它得到一个更高效的执行计划。需要重点强调的是，提示能解一时之围，但不要把它当作一个长期的解决方案。不过在有些情况下，它却也是解决问题的唯一途径。第二，提示能产生可替代的执行计划，因此它对评估查询优化器的决定很有效。这样，你就可以用它做一些假设分析。

l        7.3.3　缺陷和谬误

每当你希望通过访问路径提示（access path hint）、连接提示（join hint）或并行执行提示（parallel processing hint）产生一个特定的执行计划时，必须很小心地使用足够多的提示来达到执行计划的稳定性。这里的稳定性是指即使对象统计信息发生改变，甚至在某种程度上访问结构也发生了改变时，执行计划也保持不变。为了获得特定的执行计划，通常不仅要给SQL语句中的每个表加上访问路径提示，同时还要再加上几个连接提示来控制连接的方法和顺序。注意，其他类型的提示（比如，初始化参数提示和查询转换提示）通常并不存在这样的问题。

执行SQL语句时，解析器会检查提示的语法。尽管这样做了，但是提示出现语法错误时仍不会抛出错误。这暗示着解析器将这些错误的提示看作是注释了。从一方面看，如果这是打字错误引起的就很令人讨厌。从另一方面看，这样又是有好处的，不会因为改变了一个提示中引用的访问结构（比如，提示index会引用一个索引名）或升级到一个新的数据库版本，而破坏已部署好的应用程序。所以，我很欢迎一种确认提示有效性的方法。比如，通过EXPLAIN PLAN语句，它用简单易行的方式为可能的错误提供了警告（比如，在dbms_xplan的输出中有一个新的note）。其他我所知道的能够部分达到此目的的方法只有10132事件。事实上，在Oracle 10g中，此事件产生的输出文档的末尾有一部分内容专门讲提示。通过它可以检查两件事情。第一，每一个用到的提示都会被列出来，如果哪个提示漏了，意味着它没有被识别。第二，检查是否有一些信息指明出现了提示错误（如果出错，err值将大于0）。注意下面的输出，两个初始化参数提示是相互矛盾的。

 

 

当使用这个方法时要十分小心，那些语法正确但引用对象错误的提示是不会被报告出错的。所以，这不是一个可以让人高枕无忧的权威性检查。

在使用提示时最易犯的一个错误与表的别名相关。正确的规则是，当在提示中使用表时，只要表有别名就应该使用别名而不是表名。在下面这个例子中，可以看到如何给表emp添加别名（e）的。此时，当提示full通过表名引用表时，提示就无效了。注意在第一个例子中，索引扫描是如何替代了原先期望的全表扫描的：

 

 

还有一个应该被检查但却经常被遗忘的事情是数据库升级对提示的影响。虽然提示的作用是在查询优化器不能自动产生高效执行计划时提供便捷的解决方法，但是它的影响却取决于查询优化器所使用的决策树（参考图7-1）。无论何时，当使用了提示的查询语句在另一个版本的数据库（因此，也就是在另一个查询优化器版本）执行时，它们都需要进行仔细地校验。换句话说，当校验一个迁到新版本的应用程序时，最佳的做法是重新校验和测试所有包含提示的SQL语句。

因为视图可能在不同的环境中使用，通常不推荐在视图中使用提示。如果你不得不在视图中添加提示，需要确保所用的提示对所在的模块都是合理的。