作者：黄玮
出版社：机械工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Oracle高性能SQL引擎剖析：SQL优化与调优机制详解试读：
前言
作为一个数据库管理系统来说，Oracle不仅具备为用户存储和管理海量数据的能力，还能够解析用户提交结构化查询语言（Structured Query Language,SQL）的请求，高效和快速地响应请求。为了保证语句的查询效率，Oracle开发了许多技术，从各个方面提供支持，例如，物理设计、内存管理和SQL语句本身的自动优化与调优。
各种基于数据库的系统中，绝大多数功能都需要通过数据库管理系统查询和操作数据，因而后台的性能对系统整体性能的影响是相当关键的。而要实现对数据的管理与查询，程序需要通过SQL语句对数据库发起相应的请求。也就是说，SQL语句的性能成为影响系统性能至关重要的因素。
Oracle数据库作为目前市场占有量最大的关系型数据库管理系统，拥有成千上万的专利技术保证数据库系统的性能。而对于应用系统性能影响重大的SQL引擎，Oracle更是提供了强大的技术保障，不仅采用了主流数据库系统当中优化效果最好的优化模式——基于代价的优化模式，还引入了数量庞大的、有别于其他数据库系统的专利优化技术。这些优化技术使得Oracle在SQL优化和调优方面独树一帜。
然而，尽管Oracle可能已经在内部帮助我们完成了99%的优化工作，但剩余的1%仍然可能成为导致性能下降的决定因素。因此，在Oracle数据库系统当中，发生性能问题的例子还是层出不穷。并且，以作者的个人经历来看，这些性能问题绝大多数是由SQL语句引起的。因此，深入理解和掌握Oracle的SQL语句优化和调优技术，是开发人员和数据库管理员都应掌握的，也是开发出性能高效的数据库系统的必要前提。
本书结合作者多年来对Oracle的SQL内部优化机制的研究以及SQL调优的经验，深入分析了Oracle的各项SQL语句的优化与调优技术，侧重于对这些技术的内部机制的介绍，目的是帮助读者更加深入地了解和消化这些技术，使读者在面对和解决由SQL引起的性能问题时，能透彻地看清问题的本质，迅速找到有效的解决方法。
SQL优化是指在语句运行之前，由SQL引擎对语句进行解析，采用各项技术对查询进行优化，找到其最佳的执行路径，即执行计划；SQL调优则是对已经存在执行计划的语句进行进一步调整，使其运行性能更加接近性能指标的要求，达到性能改进的目的。SQL优化与SQL调优是在改善SQL语句性能的过程中，两个不同阶段实施的技术与方法。SQL优化，基本上是由优化器根据当前环境和数据实施的内部优化措施；SQL调优，则是通过对语句、物理对象以及优化环境的干预，使得优化器能够选择到最优的SQL执行方式。因此，这两个方面的技术内容是相辅相成的。
在RBO时期（9i之前），由于优化技术的限制，使得SQL语句往往不能获得最佳的执行计划，从而导致性能低下，需要依赖于开发人员或数据库管理员的经验和技能，对语句进行人工干预，从而调整其性能。可以说，这个时期的优化技术是有限的，并且优化与调优过程比较独立。进入CBO时代后，统计数据（Statistics Data）为优化器采用更加智能和复杂的优化技术提供了有力的数据保障，越来越多的基于代价的优化技术被应用到了Oracle的SQL引擎当中。同时，这也使得自动和智能化的优化技术成为可能。在10g、11g当中，Oracle引入了多项新的优化技术。这些优化技术不仅仅能针对当前的运行结果进行调优，而且能对优化环境和物理设计等影响优化效果的因素进行深入检查，提供短期和长期的各种调优建议。并且，部分调优结果还可以在语句的下一个执行周期中影响其优化方法。这就使得优化与调优技术更加紧密地结合在一起，为SQL语句提供一个稳定、高效的运行性能。
简单地说，我们在调整SQL语句性能时，需要灵活地使用SQL调优技术，使得SQL语句在优化阶段能够获得真正最佳性能的执行计划。图0-1描述了SQL优化与SQL调优在改进性能过程中所处的位置以及相互作用的方式。希望读者可以先通过该图体会到这两方面技术之间的关系，在后续的阅读和学习过程中能清楚地知道各种技术在提高性能过程中的作用。
无论是SQL优化还是调优，它们的核心内容都是执行计划（Execution Plan）。许多相关技术，例如查询转换、SQL调用配置（SQL Profile）都是围绕执行计划这一要素展开的。本书将从执行计划开始，逐步剖析Oracle的SQL优化与调优的相关技术。
本书分为三篇、共八章。
第一篇解释什么是SQL语句的执行计划。主要内容包括：执行计划在SQL语句执行的生命周期中所处的位置和作用；SQL引擎如何生成执行计划以及如何手工生成一条语句的执行计划；如何从各种数据源显示和查看已经生成的执行计划。本篇的重点部分则是向读者解释如何读懂执行计划，包含执行计划结构解析、各种数据所代表的含义、执行计划各种操作的含义和示例，以及执行计划与内部函数之间的本质关系。
第二篇深入分析Oracle的SQL优化技术。总体上来说，SQL优化技术可以分为两类：逻辑优化技术和物理优化技术。其中，逻辑优化主要是指查询转换技术。本篇当中详尽地分析了10g、11g中现有的各种查询转换技术，并给出实际示例帮助读者理解这些技术。而物理优化则指的是优化器通过代价估算来选择最佳的执行计划。优化器要正确估算执行计划及其操作的代价，则需要准确的统计数据的支持。因此本书在分析优化器的代价估算方法之前，先分析Oracle如何收集、统计系统和对象的统计数据。然后，结合作者推导出的各种代价估算公式，演示了各种情形下的代价计算方法。图 0-1 SQL性能改进过程
第三篇详细介绍和分析Oracle提供的各项调优技术。在进行SQL调优时，尤其是对已经产生了实际的性能问题的SQL语句进行调优时，我们需要参考SQL语句的实际运行的性能统计数据。因此，本篇先对语句实际运行的性能统计数据进行了深度分析，向读者介绍了各项统计数据是由什么操作导致的以及如何统计。其次，介绍Oracle的各项调优技术，深入分析这些技术如何对SQL语句进行优化，以及如何使用这些技术帮助SQL语句获得稳定、高效的性能。最后，依据对SQL优化及调优技术的分析，向读者介绍了如何快速优化SQL的思路。
在本书中有不少演示代码用于解释相关知识点。读者请执行脚本00_01_Prepare.sql（进入Oracle 11g中再执行00_01_Prepare_11g.sql）准备演示环境。本书所介绍的内容将基于10g和11g的版本特性，其中有部分脚本只能用于相应版本，请注意提示。
www.HelloDBA.com是作者专属的个人网站，是作者分享个人经验和心得的重要平台。本书中使用的所有代码脚本都会放在本网站上供读者免费下载。此外，因篇幅问题而删减的附录“SQL提示的含义和示例”也将以电子文档的形式提供读者免费阅读。读者在阅读本书过程中，如有任何疑问，可以给作者发送电子邮件：fuyuncat@gmail.com，也可以关注作者的微博（http：//www.weibo.com/u/1407854870）。第一篇执行计划
执行计划是指示Oracle如何获取和过滤数据、产生最终结果集，是影响SQL语句执行性能的关键因素。我们在深入了解执行计划之前，首先需要知道执行计划是在什么时候产生的，以及如何让SQL引擎为语句生成执行计划。
在深入了解执行计划之前，我们先了解SQL语句的处理执行过程。当一条语句提交到Oracle后，SQL引擎会分为三个步骤对其处理和执行：解析（Parse）、执行（Execute）和获取（Fetch），分别由SQL引擎的不同组件完成。SQL引擎的组件如图1-1所示。图 1-1 SQL引擎结构及其组件示意图
1.SQL编译器（SQL Compiler）
将语句编译到一个共享游标中。SQL编译器由解析器（Parser）、查询优化器（Query Optimizer）和行源生成器（Row Source Generator）组成。
❑解析器（Parser）——执行对SQL语句的语法、语义分析，将查询中的视图展开、划分为小的查询块。
❑查询优化器（Query Optimizer）——为语句生成一组可能被使用的执行计划，估算出每个执行计划的代价，并调用计划生成器（Plan Generator）生成计划，比较计划的代价，最终选择选择一个代价最小的计划。查询优化器由查询转换器（Query Transform）、代价估算器（Estimator）和计划生成器（Plan Generator）组成。
注意，上述优化器实际上指的是基于代价的优化器（Cost Based Optimizer,CBO），CBO也是当前采用的所有优化和调优技术的核心基础。
查询转换器（Query Transformer）——查询转换器决定是否重写用户的查询（包括视图合并、子查询反嵌套），以生成更好的查询计划。
代价估算器（Estimator）——代价估算器使用统计数据来估算操作的选择率（Selectivity）、返回数据集的势（Cardinality）和代价，并最终估算出整个执行计划的代价。
计划生成器（Plan Generator）——计划生成器会考虑可能的访问路径（Access Path）、关联方法和关联顺序，生成不同的执行计划，让查询优化器从这些计划中选择出代价最小的一个计划。
❑行源生成器（Row Source Generator）——行源生成器从优化器接收到优化的执行计划后，为该计划生成行源（Row Source）。行源是一个可被迭代控制的结构体，它能以迭代方式处理一组数据行、并生成一组数据行。
2.SQL执行引擎（SQL Execution Engine）
SQL执行引擎依照语句的执行计划进行操作，产生查询结果。在每一个操作中，SQL执行引擎会以迭代方式执行行源、生成数据行。
提示：当Oracle引入一些新的优化技术时，会出现一些新的组件，例如，SQL进化管理器（SPM）、SQL性能分析器（SPA）等，这些组件会与SQL引擎的组件融合，提供更好的优化和调优方法。第1章生成和显示执行计划
任何一条SQL语句要正确运行并返回结果，SQL执行引擎都必须获得一个相应的执行计划。当缓存当中找不到与当前环境相匹配的执行计划时，SQL编译器会解析和生成一个相应的执行计划。已经生成的执行计划会驻留在缓存当中，直至其失效或者被清出缓存。
如果想要生成和显示一条语句的执行计划，方法有多种。大致上分为两类：从内存或者历史数据中读取曾经执行语句的执行计划；使用Explain Plan命令解析语句后，从表PLAN_TABLE获得生成的执行计划。
在本章中，我们将会了解到以下内容：
❑在Oracle中，SQL语句如何生成执行计划。
❑如何获取和显示SQL语句的执行计划。1.1 生成执行计划
在Oracle中，任何一条语句在解析过程中都会生成一个唯一的数值标识，即SQL_ID。而同一条语句，在解析过程中，可能会因为执行环境的改变（例如某些优化参数被改变）而生成多个版本的游标，不同的游标会有不同的执行计划。每个游标都会按顺序赋予一个序列号，即CHILD_NUMBER，一条语句生成的第一个游标的CHILD_NUMBER为0；相应的，Oracle会为每个执行计划生成一个哈希值以作区分。而多个不同版本的游标，其执行计划可能会相同，也可能不同。
因此，我们可以知道，一条合法的SQL语句在执行过后，在内存中最少会有一个执行计划与其游标相对应。当前实例内存（Library Cache）中的执行计划可以通过视图V$SQL_PLAN读取（RAC环境中，可以通过视图gv$sql_plan查看RAC当中其他实例上的执行计划）。在启用了自动负载知识库（Automatic Workload Repository,AWR，10g及以后版本），Oracle会将内存中的执行计划存储在历史数据当中，我们可以通过查询语句或者Oracle提供的包DBMS_XPLAN从历史数据中读取。此外，从10g开始，Oracle还提供一个自动优化工具DBMS_SQLTUNE对单个或一组语句进行自动优化，它可以在一段时间内捕捉内存中语句和执行计划来生成一组SQL集（或者称SQL调优集，SQL Tuning Set），我们同样可以从SQL集中读取和显示语句的执行计划。在11g当中，Oracle又引入了SQL执行计划管理（SQL Plan Management）的特性，可以将语句的一个或多个执行计划存储在一个执行计划基线（Plan Baseline）当中，我们同样可以读取基于执行计划基线生成的计划。
提示：AWR的历史数据、执行计划基线都是有保存期限的，可以通过相关参数设置。
除了通过执行SQL让Oracle处理引擎在内存中生成执行计划外，我们还可以通过命令Explain Plan让优化器仅对SQL语句进行解释，生成查询计划。由于语句并不会实际执行，因此它可以含有没有赋值的绑定变量。
执行Explain Plan命令后，Oracle会将解释生成的查询计划插入表SYS.PLAN_TABLE$（10g之前，表名为PLAN_TABLE；10g之后，通过公共同义词PLAN_TABLE指向SYS.PLAN_TABLE$）中。我们就可以通过查询语句或者Oracle提供的包DBMS_XPLAN从该表中读取查询计划。注意，通过Explain Plan解释出来的查询计划不会被缓存到内存中以便在语句执行时重用，我们在缓存当中看到的是类似“explain plan for＜SQL＞”的形式。
要注意的是，如果要解析语句的执行计划，用户必须拥有语句中对象及其依赖对象的权限。如果语句中存在视图，用户必须有对视图依赖表的查询权限。例如，当一个用户A基于表T创建了一个视图V，并将视图的查询权限赋予了用户B，那么用户B仅能通过视图查询表的数据，但无法直接调用Explain Plan命令解析基于该视图的查询的执行计划。1.2 显示执行计划
我们现在知道，有三个途径可以获取查询计划：v$sql_plan、dba_hist_sql_plan和PLAN_TABLE。如果需要读取一条SQL语句的执行计划，就需要知道该条语句的SQL_ID，如果该语句存在多个游标或者执行计划，则还需要知道游标的CHILD_NUMBER或计划的哈希值（可选）。而无论我们通过哪个途径来获取执行计划，显示方式主要是两种：语句查询和包DBMS_XPLAN显示。1.2.1 通过查询语句显示计划
通过查询语句从一些视图里读出执行计划并作格式化输出的方法都非常相似，这里以v$sql_plan视图为例，示例程序见代码清单1-1。
代码清单1-1 显示执行计划（查询语句）
HELLODBA.COM＞col"Query Plan_Table"format a30
--提示：SQL_ID可以从视图v$sql_text和dba_hist_sqltext（或stats$sqltext）等视图中查询获得。
HELLODBA.COM＞select id,lpad（''，2*（level-1））||operation||''||options||''||
2 object_name||''||decode（id，0，'Cost='||cost）"Query Plan_Table"
3 from v$sql_plan
4 start with id=0
5 and sql_id='dq7gjn1yrpcyz'
6 and plan_hash_value=616708042
7 connect by prior id=parent_id
8 and sql_id='dq7gjn1yrpcyz'
9 and plan_hash_value=616708042；
ID Query Plan_Table
----------------------------------------
0 SELECT STATEMENT Cost=2
1 TABLE ACCESS FULL T_USERS1.2.2 通过包DBMS_XPLAN显示计划
这个包可以根据我们选择的函数以及输入的参数来格式化显示相关的执行计划，在我们随后的内容中，主要会使用（也推荐读者使用）该工具显示执行计划。
DBMS_XPLAN含有5个函数用于输出格式化的执行计划，display、display_cursor、display_awr、display_sqlset和display_sql_plan_baseline，分别用于显示Explain Plan命令解释的计划、内存中的执行计划、AWR历史数据中的计划、SQL优化集中语句的计划、执行计划基线（关于SQL优化集和执行计划基线，我们会在后面第7章中具体介绍）。它们都是管道化表函数（Pipelined Table Function），返回的结果是一个系统自定义的集合数据类型dbms_xplan_type_table。我们可以通过表函数（Table）进行映射后进行查询。
1.2.2.1 DISPLAY
DISPLAY函数用于显示存储在PLAN_TABLE中的执行计划，或与PLAN_TABLE拥有相同结构的表中的执行计划。此外，如果从视图v$sql_plan_statistics_all可以获得该执行计划的相关统计数据，DISPLAY也可以格式化输出这些数据。
参数描述：
❑TABLE_NAME：存储查询计划的表名（不区分大小写），默认值为PLAN_TABLE。
❑STATEMENT_ID：SQL语句ID。在PLAN_TABLE中，每条语句的执行计划都会有一个唯一的ID来标识。这个ID可以在执行Explain Plan命令时，通过Set Statement_id子句来指定。如果输入为NULL，则会获取最近一条被解释的语句。
❑FORMAT：输出格式。在DISPLAY函数中，有以下预定义的格式（模板）可供选择：
❍'BASIC'：基本格式。输出的内容最少，仅仅输出查询计划中每个操作的ID、名称和选项以及操作的对象名。
❍'TYPICAL'：典型格式。输出的内容是我们进行语句调优时大多数情况下所需要的信息。除了基本格式中的内容外，还会输出优化器估算出的每个操作的记录行数、字节数、代价和时间，以及相关的提示信息（如远程SQL、优化器建议等）。如果存在谓词（Predicate）条件，还会输出每个操作中的过滤（Filter）条件和访问（Access）条件。此外，如果查询涉及分区表，还会输出分区裁剪信息；如果查询涉及并行查询，还会输出并行操作的相关信息（如表队列信息、并行查询分布方式等）。这种格式是默认格式。
❍'SERIAL'：串行执行格式。这种格式和典型格式的输出内容基本一致，不同之处在于，对并行查询，它不会输出相关的并行内容。
❍'ALL'：完全格式。输出的内容相对完整。除了典型格式的内容以外，还会输出字段投射信息和别名信息。
除了这些预定义的格式外，用户还可以通过在格式化字符串中添加或者屏蔽一些关键词进行细化输出。每一个细化选项代表了输出内容中的单个信息（可能是执行计划表中的一个列，也可能是一个附加信息）。在DISPLAY函数中，以下细化控制选项可供选择：
❍ROWS：优化器估算出的记录行数；
❍BYTES：优化器估算出的字节数；
❍COST：优化器估算出的代价；
❍PARTITION：分区裁剪；
❍PARALLEL：并行查询；
❍PREDICATE：谓词；
❍PROJECTION：字段投射；
❍ALIAS：别名；
❍REMOTE：分布式查询信息；
❍NOTE：相关注释信息。
细化控制选项和预定格式一起使用。例如，如果你希望输出基本格式内容，并输出优化器估算出的记录行数，可以用“BASIC ROWS”作为格式字符串；而如果希望输出典型格式，但不要其中的谓词条件，则可以输入“TYPICAL-PREDICATE”作为格式字符串，即在希望被屏蔽信息的对应控制选项前加上“-”。
❑FILTER_PREDS：该参数接收合法的谓词过滤条件（可以是谓词逻辑表达式，也可以包含子查询），以过滤从查询计划表中读取的内容。例如，可以输入“COST＞10”以限制输出所有估算代价大于10的操作。
示例见代码清单1-2。
代码清单1-2 显示执行计划（DISPLAY函数）
1.2.2.2 DISPLAY_CURSOR
DISPLAY_CURSOR函数可以显示内存中一个或者多个游标的执行计划。同样，可以通过输入参数限定SQL、游标以及输出格式。
用户必须对视图V$SQLV、$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL的SELECT有权限，才能正常调用DISPLAY_CURSOR函数。
参数描述：
❑SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从V$SQL.SQL_ID、V$SESSION.SQL_ID或者V$SESSION.PREV_SQL_ID获得。如果没有指定SQL_ID（指定NULL），则默认会显示当前会话中最后一条执行的SQL语句。
❑CURSOR_CHILD_NO：语句的子游标序号。我们知道，受到执行环境的影响，一条SQL语句可能会产生多个版本的子游标，每个子游标都会与一个执行计划相映射（多个子游标也可能映射同一个执行计划）。通过CURSOR_CHILD_NO可以限制仅显示某一个子游标的执行计划。如果不指定该参数，则会显示该语句的所有子游标的执行计划。
❑FORMAT：格式化控制字符串。DISPLAY函数的格式化控制字符串的所有选项都适用于DISPLAY_CURSOR函数。由于运行语句还可以通过提示GATHER_PLAN_STATISTICS或设置系统参数STATISTICS_LEVEL为“ALL”收集语句运行的性能统计数据，因此在细化选项中还有额外的选项，以选择是否输出这些数据。
❍IOSTATS：是否输出计划的输入输出（IO）统计数据；
❍MEMSTATS：在启用了PGA自动管理（参数pga_aggregate_target的值大于0）的情况下，是否输出计划的输入内存统计数据（操作的内存使用量、内存读次数等）；
❍ALLSTATS：包含了IOSTATS和MEMSTATS的全部内容；
❍LAST：以上三个选项输出的统计数据都是实际产生的数据，而非估算数据，它们是该游标所有执行所产生的数据的总和。你可以增加LAST选项以限定仅显示最后一次运行的统计数据。
此外，还有一些未公布的选项可用于该函数的输出控制。首先是预定义格式：
❍'ADVANCED'：高级格式。高级格式除了会输出完全格式中的所有内容外，还会视情况输出绑定变量窥视信息和计划概要（Outline）信息；
❍OUTLINE：是否以提示（HINT）的方式显示计划概要；
❍PEEKED_BINDS：是否显示绑定变量窥视信息；
❍BUFFSTATS：是否显示内存读次数（包括一致性读和当前读次数），该信息为IOSTATS的一部分；
❍PLAN_HASH：是否显示计划的哈希值，该选项同样适用于DISPLAY函数。示例见代码清单1-3。
代码清单1-3 显示执行计划（DISPLAY_CURSOR函数）
1.2.2.3 DISPLAY_AWR
DISPLAY_AWR函数显示存储在AWR历史数据的执行计划。
提示：要正常调用DISPLAY_AWR参数，必须对以下视图有权限：DBA_HIST_SQL_PLAN和DBA_HIST_SQLTEXT的SELECT。
参数描述：
❑SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从DBA_HIST_SQL_PLAN.SQL_ID或DBA_HIST_SQLTEXT.SQL_ID获得，该参数必须指定非空值，没有默认值；
❑PLAN_HASH_VALUE：执行计划的哈希值。我们之前提到，每个执行计划都有一个哈希值。通过该值，可以显示SQL语句的特定执行计划。如果该参数未指定或为NULL，则会显示语句的所有执行计划；
❑DB_ID：指定显示哪个数据库的执行计划，默认为本地数据库ID；
提示：我们可以将其他数据库的AWR数据导入本地数据库进行分析。
❑FORMAT：格式化控制字符串。与DISPLAY的相同选项类似。
示例见代码清单1-4。
代码清单1-4 显示执行计划（DISPLAY_AWR函数）
1.2.2.4 DISPLAY_SQLSET
DISPLAY_SQLSET函数显示存储在一个SQL调优集中的语句的执行计划。
提示：DBMS_SQLTUNE是Oracle 10g中提供的一个自动调优的工具包，它可以对单条语句进行调优，也可以对一组SQL集进行调优，我们在后面章节会做详细介绍。
参数描述：
❑SQLSET_NAME：SQL集的名称。每个SQL集都有一个单独的名称（可以是创建时用户指定的，也可以是系统自动生成的），我们需要指定从哪个SQL集中读取和显示语句的执行计划，该参数没有默认值，必须指定；
❑SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从USER/DBA/ALL_SQLSET_PLANS.SQL_ID获得，该参数必须指定非空值，没有默认值；
❑PLAN_HASH_VALUE：执行计划的哈希值。如果未指定或为NULL，则会显示语句的所有执行计划；
❑FORMAT：格式化控制字符串。与DISPLAY的FORMAT选项相同；
❑SQLSET_OWNER：SQL集的所有者，默认为当前用户名。
示例见代码清单1-5。
代码清单1-5 显示执行计划（DISPLAY_SQLSET函数）
1.2.2.5 DISPLAY_SQL_PLAN_BASELINE
DISPLAY_SQL_PLAN_BASELINE函数显示存储在数据字典当中SQL执行计划基线的计划。
提示：SQL执行计划管理是Oracle 11g中提供的一个新特性，用于管理SQL语句的一组执行计划（执行计划基线，Plan Baseline），保证语句运行性能稳定性。
参数描述：
❑SQL_HANDLE：执行计划基线所属SQL的句柄名称，它由Oracle在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines查询，默认为NULL；
❑PLAN_NAME：执行计划基线中某个执行计划的名称，它由Oracle在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines查询，默认为NULL；
❑FORMAT：格式化控制字符串。DISPLAY_SQLSET函数的格式化选项与DISPLAY的选项相同。
当SQL_HANDLE和PLAN_NAME都为空时，显示所有基线数据中的全部执行计划。
示例见代码清单1-6。
代码清单1-6 显示执行计划（DISPLAY_SQL_PLAN_BASELINE函数）1.2.3 AUTOTRACE
AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*PLus会自动收集执行过的语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。
要使用AUTOTRACE，需要先做以下准备，用DBA用户创建角色PLUSTRCE，并将该角色赋予用户：
HELLODBA.COM＞conn sys/sys as sysdba
Connected.
HELLODBA.COM＞@?/SQLPLUS/ADMIN/PLUSTRCE.SQL
HELLODBA.COM＞grant plustrace to demo；
Grant succeeded.
在执行语句之前，在SQL*Plus中打开AUTOTRACE。可以在打开AUTOTRACE时选择不同选项，以控制输出的内容。选项如下所示：
❑SET AUTOTRACE ON：打开AUTOTRACE，并输出所有内容，包括语句本身的查询结果、执行计划，以及性能统计数据。
❑SET AUTOTRACE ON EXPLAIN：打开AUTOTRACE，并输出语句本身的查询结果和执行计划，不输出性能统计数据。
❑SET AUTOTRACE ON STATISTICS：打开AUTOTRACE，并输出语句本身的查询结果和性能统计数据，不输出执行计划。
❑SET AUTOTRACE TRACE：打开AUTOTRACE，并输出执行计划和性能统计数据，不输出语句本身的查询结果。
❑SET AUTOTRACE TRACE EXPLAIN：打开AUTOTRACE，并输出执行计划，不输出语句本身的查询结果和性能统计数据。
❑SET AUTOTRACE TRACE STATISTICS：打开AUTOTRACE，并输出性能统计数据，不输出语句本身的查询结果和执行计划。
❑SET AUTOTRACE OFF：关闭AUTOTRACE。
一个完整的AUTOTRACE报告输出包括三个部分：第一部分为SQL本身的执行结果；第二部分为SQL的执行计划；第三部分为SQL实际执行的性能统计数据。由于执行计划和执行的性能数据都是进行SQL调优时的重要参考信息，因此AUTOTRACE是进行SQL语句性能调优的一个非常实用的辅助方法。
提示：当打开AUTOTRACE后，在执行语句之前，Oracle会调用EXPLAIN PLAN命令对语句进行解析；在执行完成后，从PLAN_TABLE中查询和显示执行计划。因此，由于受到共享游标、绑定变量窥视等设置的影响，这一执行计划可能会与实际执行计划不同。1.2.4 其他方法
除了上述方法外，我们还可以通过其他一些途径获取到语句的执行计划。但在这些方法所产生的数据里，执行计划通常仅是辅助我们解决问题的一个部分，而非重点。
1.2.4.1 SQL_TRACE（或者10046跟踪事件）
SQL_TRACE跟踪的内容由三个部分组成：执行语句时造成的等待事件（Waits）、执行语句时产生的性能统计数据，以及语句的执行计划和绑定变量信息。这里仅介绍执行计划相关部分。
在会话或者系统中启动SQL跟踪后，会话结束或者关闭SQL跟踪之前，会话（或系统）中所有运行的语句的性能统计数据都会记录到UDUMP目录（user_dump_dest参数指定）下一个跟踪文件中（未指定标识字符串tracefile_identifier的情况下，文件名格式为＜ORACLE_SID＞_ORA_＜PID＞.trc），从跟踪文件中，我们可以找到语句的执行计划。示例见代码清单1-7。
代码清单1-7 SQL_TRACE
1.2.4.2 OPTIMIZER_TRACE（或者10053跟踪事件）
OPTIMIZER_TRACE可以跟踪优化器生成语句执行计划的整个过程，并且，在11g中还可以通过设置事件来指定仅跟踪一个或多个组件的信息。同样，其跟踪内容都会写入UDMP目录下的一个跟踪文件中，文件的命名方式和SQL_TRACE产生的跟踪文件的命名方式相同。示例见代码清单1-8。
代码清单1-8 OPTIMIZER_TRACE第2章解读执行计划
我们掌握了获取和显示执行计划的方法后，就可以开始了解如何来读懂一个执行计划。从我们前面的示例中可以发现，一个执行计划实际上是一个树状关系的结构图。在这个树状结构图中，每一个节点代表了一个操作（相应的，它们在PLAN_TABLE、V$SQL_PLAN等执行计划表、视图中都有一条记录），每个支节点（或者说父节点）下都有一个或多个子操作；除根节点外，每个节点都仅隶属于一个支节点。同一层操作按照它们的ID顺序执行。操作由顶向下调用，即父操作调用子操作；数据由底向上返回，即子节点获取到的数据在处理完成后返回给父节点。
提示：请执行脚本02_01_SQL_Explain＜版本＞.sql创建函数SQL_Explain，注意区分版本。
从前一章的示例中可以看到，执行计划包含了相当丰富的信息。通过执行计划，我们不仅可以知道SQL引擎将以什么样的访问顺序访问对象、获取语句的输出结果，而且还可以获得优化器估算出的代价结果、运行时间以及其他一些额外信息。在本章中，将为读者详细解释执行计划中的内容。阅读完本章以后，读者再拿到任何一个语句的执行计划，都能读懂该执行计划的含义，并能通过它传达给我们的信息来了解SQL执行该语句的过程及性能。
在本章中，我们将会了解到以下内容：
❑执行计划中存在哪些基本数据及其具体含义；
❑执行计划操作与Oracle内部函数之间的关系；
❑执行计划中所有列的具体含义；
❑执行计划每个数据源操作的具体含义及其示例；
❑与执行计划相关的其他信息含义。2.1 执行计划的基本数据
我们用代码清单2-1中的查询计划为例，解释计划访问中基本数据的含义。
代码清单2-1 执行计划查询
这里利用DBMS_XPLAN显示查询计划，它以表格样式输出查询计划。该表有多个列，其中ID是计划中每个操作的唯一序列号，Operation是每个操作的名称和方式，Name是操作的对象。实际上，该表还有其他一些列代表了优化、统计等信息，我们将在下一节解释这些列的含义。
我们再看每行记录的数据。如果留意看，就不难发现每行数据中的Operation都带有长短不一的前导空格，使之看起来成为一个树状结构。这个结构也就是我们之前提到的操作之间的调用关系。下面分析每一行：
❑首先看第一条，ID为0，操作为SELECT STATEMENT。这一行实际上只表示这条语句的类型是一条SELECT语句，而非一个真正的操作。因此在一些执行计划的显示当中，没有显示ID为0的操作。
❑ID为1的操作是NESTED LOOPS，表明它需要对两个数据集以嵌套循环的方式进行数据关联。而这两个数据集则是由其两个子操作2和4分别从表T_USERS和T_OBJECTS上读取得来，也就是说，操作1按顺序调用操作2和4，获取它们返回的数据进行关联。而要实现嵌套循环，就需要两个循环体。其中，操作2就是第一个循环体，也就是外循环；操作4就是第二个循环体，即内循环。
❑ID为2的操作是TABLE ACCESS BY INDEX ROWID,Name是T_USERS，表明它是通过索引上的ROWID来访问表T_USERS以获取数据。而索引上的ROWID则需要通过其子操作3来获取；
❑ID为3的操作是INDEX UNIQUE SCAN,Name是T_USERS_PK，表明它是对索引T_USERS_PK进行唯一键值的访问以获取其父操作所需要的ROWID。从之前的DDL语句我们知道，T_USERS_PK是表T_USERS的主键，也是一个唯一索引。而对唯一索引的唯一键值的访问，需要有一个数值的输入作为访问条件。在它的ID列，我们可以留意到*符号，表示这个操作有相关的谓词条件（访问条件或者过滤条件）。而我们这里也特地显示了谓词条件。在下面谓词信息输出部分，可以找到一条信息3-access（"U"."USER_ID"=TO_NUMBER（：B）），表明这是操作ID为3的谓词条件，其中access表示它是访问条件，内容是通过某个数值定位USER_ID键值。
提示：访问条件和过滤条件都属于谓词条件，但它们对操作的作用大不相同。访问条件可以帮助操作从物理对象上定位到符合条件的数据，然后再读取数据；而过滤条件是操作已经从物理存储上读取到了数据，然后将不符合条件的数据过滤掉。它们对语句的性能影响很大，了解了它们之间的差别，就有助于我们对语句进行进一步调优。
❑ID为4的操作是TABLE ACCESS FULL,Name是T_OBJECTS，表明它是对表T_OBJECTS进行全表扫描。全表扫描即读取表的物理段（Segment）的高水位线（High Water Mark,HWM）以下的所有数据块。同样，它的ID也有*符号，从谓词信息部分可以找到关联的谓词条件4-filter（"O"."OBJECT_NAME"LIKE：A AND"U"."USERNAME"="O"."OWNER"）。filter表明它是一个过滤条件，即读取了表T_OBJECTS的所有数据，再过滤掉不符合条件（"O"."OBJECT_NAME"LIKE：A AND"U"."USERNAME"="O"."OWNER"）的数据。
通过执行计划，我们就可以清楚地了解一条语句是通过什么样的方式读取物理对象的数据，如何对数据进行处理（过滤、排序等），最终获取到符合条件的数据。再结合执行计划中的其他数据，我们就可以进一步定位语句的性能瓶颈在哪里，从而为我们实施优化奠定基础。2.2 内部函数与操作
实际上，在Oracle内部，执行计划的每一个数据源（Row Source）操作都与一个内部函数（qer＜*＞）相对应，而操作对象、谓词条件都是这些函数的参数。这些函数之间可以相互调用，也正是这些函数的调用关系，映射成为执行计划的树状关系。换句话说，一个执行计划告诉Oracle的内部引擎如何调用这些函数，以及传给函数的参数值。
例如，在上述例子中，NESTED LOOPS获取数据的内部函数是qerjoFetch；TABLE ACCESS BY INDEX ROWID的内部函数是qertbFetchByRowID；INDEX UNIQUE SCAN的内部函数是qerixFetchUniqueIndex；TABLE ACCESS FULL的内部函数是qertbFetch。
从这个意义上来说，我们可以将一个执行计划视为一段伪代码或者一段解释型语言的代码，SQL执行引擎则负责解释和执行该段代码。
基本上，在Oracle内部，所有“qer”前缀的函数都属于数据源操作模块的函数，而根据它们操作对象和方式不同，又可以分为多个不同的子模块。大部分数据源函数模块见表2-1。
但是，要注意的是，执行计划操作所映射的函数并非是完成SQL语句执行过程的全部函数。SQL引擎在执行SQL语句时，除了需要知道调用哪些函数进行数据源操作以外，还需要知道如何进行其他相关操作，例如私有内存的使用，以及所有这些操作的上下文（Context）数据。而所有这些信息都存储在SQL区（SQL Area）当中。在每个游标的内存当中，它们都存放在Heap6中。2.3 执行计划各个列的含义
在执行计划中，除了ID、Operation和Name之外，还有其他一些列。这些列的数据是根据需要从PLAN_TABLE、V$SQL_PLAN、V$SQL_PLAN_STATISTICS_ALL等表和视图中读取的。它们可以帮助我们进一步理解该执行计划（例如优化器对各个操作的估算数据、实际运行中各个操作的性能数据等）。以下就是各个列的描述。
❑Rows/E-Rows：优化器估算出当前操作返回给上一级操作的数据记录数，如果计划中同时输出收集到实际记录数，则会显示为E-Rows以和实际记录数区别，在优化器中，又称为数据集的势（Cardinality）；
❑Bytes/E-Bytes：优化器估算出当前操作返回给上一级操作的数据的字节数，如果计划中同时输出收集到实际字节数，则会显示为E-Bytes以和实际字节数区别；
❑TempSpc/E-Temp：优化器估算出完成当前操作（仅部分操作需要临时空间，如SORT、Hash Join）所需要的临时表空间的大小，如果计划中同时输出收集到实际临时空间大小，则会显示为E-Temp以和实际临时空间大小区别；
❑Cost（%CPU）：优化器估算出完成当前操作的代价（包含子操作的代价），它是IO代价和CPU代价总和。其中，IO代价是最基本的代价。而对于CPU代价，在默认情况下，优化器会将CPU代价计算在内，并且将CPU代价根据系统配置由特定的转换公式转换为IO代价。也可以通过优化器参数_optimizer_cost_model指定是否在代价模型中包括CPU代价。括号中数据即为CPU代价在总代价中的比例；
❑Time/E-Time：优化器估算出完成当前操作所需要的时间，这个时间是其子操作的累计时间，如果计划中同时输出收集到实际时间，则会显示为E-Time以和实际时间区别；
❑Pstart：分区裁剪（Partition Prunning）后，访问的起始分区，仅在含有分区表访问操作的执行计划中出现；
❑Pstop：分区裁剪（Partition Prunning）后，访问的结束分区，仅在含有分区表访问操作的执行计划中出现；
❑Inst：分布式查询中，远程对象所在的数据库实例名；
❑TQ：并行查询中的表队列（Table Queue），我们会在相关操作中进一步阐述该列数据；
❑IN-OUT：并行查询或分布式查询中数据传输方式；
❑PQ Distrib：并行查询中，并行服务进程之间的数据分发方式；
❑Starts：当前操作实际被启动的次数，如果输出格式中指定了LAST关键字，则为计划最后一次执行中当前操作实际被启动的次数，否则为所有被启动次数总和；
❑Rows：当前操作实际返回的记录数，如果输出格式中指定了LAST关键字，则为最后一次执行的记录数，否则为所有执行的记录数总和；
❑Time：执行当前操作的实际时间，如果输出格式中指定了LAST关键字，则为最后一次执行的时间，否则为所有执行的时间总和；
❑Buffers：当前操作中发生读内存的次数，如果输出格式中指定了LAST关键字，则为最后一次执行的读内存次数，否则为所有执行的读内存次数总和。内存读次数包括一致性读（Consistent Read,CR）和当前模式读（Current Get,CU）；
❑Reads：当前操作中发生读磁盘的次数，如果输出格式中指定了LAST关键字，则为最后一次执行的读磁盘次数，否则为所有执行的读磁盘次数总和；
❑Writes：当前操作中发生写磁盘的次数，如果输出格式中指定了LAST关键字，则为最后一次执行的写磁盘次数，否则为所有执行的写磁盘次数总和；
❑OMem：当前操作完成所有内存工作区（Work Area）操作所总共使用私有内存（PGA）中工作区的大小。需要使用内存工作区的操作为：哈希操作，如哈希分组（Hash Group）、哈希关联（Hash Join）和排序（Sort）操作，它们分别占有工作区中哈希区（Hash Area）和排序区（Sort Area）进行工作，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
❑1Mem：当工作区大小无法满足操作所需要的大小时，需要将部分数据写入临时磁盘空间中（如果仅需要写入一次就可以完成操作，就称为一次通过，One-Pass；否则为多次通过，Multi-Pass）。该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
❑Used-Mem：语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为（发生磁盘交换的次数，1次即为One-Pass，大于1次则为Multi-Pass，如果没有使用磁盘，则显示OPTIMAL）；
❑Used-Tmp：语句最后一次执行中，当前操作所使用的临时段的大小，无法一次在工作区完成操作的数据被临时写入该段；
❑O/1/M：语句所有的执行总共使用内存或磁盘完成操作的执行次数，分别是Optimal（优化的，仅适用内存完成操作）/One-Pass/Multi-Pass；
❑Max-Tmp：语句所有执行中，当前操作所使用的临时段的最大空间。2.4 执行计划各个操作的含义
通常我们所说的执行计划操作包含两个部分：操作与其选项。例如，哈希关联反关联（HASH JOIN ANTI）中，哈希关联（HASH JOIN）是一种操作，“反”关联（ANTI）则是其选项；该操作还可以与其他选项（如“半”关联，SEMI）配合形成不同的执行计划操作。
执行计划中的操作数量非常多。我们下面列出的操作是Oracle 10gR2中的绝大多数操作。Oracle的每个版本都会有一些新的特性出现，而其中一些新特性又会带来新的操作，或者抛弃一些旧操作。如果发现执行计划出现新操作，读者可以结合相关新特性的描述来理解该操作的含义。
实际上，在执行计划里出现的操作包含两个信息，一个是操作类型（在PLAN_TABLE、V$SQL_PLAN等表或视图中，字段名为OPERATION）；一个是操作的选项（在相关表和视图中，字段名为OPTIONS）。例如TABLE ACCESS BY INDEX ROWID，它的操作类型是TABLE ACCESS，即访问表，选项是BY INDEX ROWID，即通过索引中的ROWID来访问表。
提示：在11g中，可以通过固化表X$XPLTON和X$XPLTOO分别查询出所有操作类型和选项名称。一个操作可以有0到多个选项。但并不是所有选项都能用于所有的操作。
为了便于读者更好地理解这些操作，我们对这些操作类型进行了归类。但这个归类并非是一个绝对的划分，例如，某些操作可以划分到多个类别中（如MERGE JOIN PARTITION OUTER，既是一个数据关联操作，又是一个分区操作），但我们会按照操作的相关性将其划分在某个类别中。2.4.1 语句类型
这些操作代表了这条语句的类型，在执行计划中，它们出现在ID为0的操作中。我们还可以将该类型操作分为数据定义语句（DDL）类型和数据管理语句（DML）类型，如表2-2所示。
CREATE TABLE STATEMENT执行创建表的语句，是最为常用的，其示例如下：
试读结束[说明：试读内容隐藏了图片]