《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一2.5　执行计划中其他信息的含义

2.5　执行计划中其他信息的含义

通过DBMS_XPLAN输出执行计划，除了计划本身外，还可以获得一些其他信息帮助我们进一步分析执行计划及语句性能。
2.5.1　查询块和对象别名
在使用DBMS_XPLAN显示执行计划时，选择'ADVANCED'预定义格式作为参数或者加入'ALIAS'控制字符串，可以在输出中看到以下内容:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / O@SEL$1

其中，数字为对应的操作ID，SEL$1为查询块（Query Block）的名字，T@SEL$1和O@SEL$1为查询块中对象的别名（Alias）。
语句在被提交到Oracle后，解析器（Parser）会对SQL语句的语法、语义进行分析，并将查询中的视图展开、划分为小的查询块（Query Block）。这些查询块被传输给优化器后，其查询转换器（Query Transformer）会对它们进行进一步地查询转换，使优化器能生成效率更高的执行计划。

2.5.2　计划概要数据

在使用DBMS_XPLAN显示执行计划时，选择'ADVANCED'预定义格式作为参数或者加入'OUTLINE'控制字符串，可以在输出中看到以下内容：

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T_USERS"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OPT_PARAM('optimizer_index_cost_adj' 60)
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

这是由一组提示（HINT）组成的数据，即执行计划的概要（Outline）数据。计划概要可以确保语句解析出一个特定的执行计划，换句话说，它能确保语句执行计划的稳定性。在9i当中，引入了一个特性：存储概要（Stored Outline），作为一个保持语句执行计划稳定的手段，它可以在不中止相关程序、不修改源代码的情况下，影响优化器解析执行计划时的行为。
要注意的是，执行计划中的概要数据是在优化器选择了最终的执行计划后，根据该计划产生的，用于重现该执行计划的必要的概要数据。
提示：SQL“提示”（HINT）是内嵌在SQL语句中，由/+ Hint_Words /构成的一段注释。它不会改变语句的逻辑结果，但可以强制优化器在选择执行计划的过程中，使用特定值作为优化参数，或者选择某些特定的操作作为执行计划的一部分。
在11g中，执行计划管理器（SQL Plan Management）的引入，能使SQL语句获得更加稳定的性能，官方不再推荐使用存储概要。
DBMS_XPLAN中显示的概要数据对于我们来说具有相当重要的作用：
1）它可以在不对语句做OPTIMIZER_TRACE的情况下，让我们了解优化器生成该执行计划的基本环境；
2）利用概要数据，我们可以在其他环境中重现一条语句的执行计划，以帮助我们做问题分析（Troubleshooting）和语句调优。
提示：概要数据是由一组SQL提示构成，在11g中，Oracle提供了一个视图V$SQL_HINT，可用于查询各个版本可用的SQL提示。其中，字段VERSION_OUTLINE表示SQL提示是否可用于计划概要，并且是从哪个版本开始可以被用于计划概要。

2.5.3　绑定变量信息

对于使用绑定变量，并且在解析计划时启用了绑定变量窥视特性的语句，在使用DBMS_XPLAN显示执行计划时，选择'ADVANCED'预定义格式作为参数或者加入'PEEKED_BINDS'控制字符串，可以在输出中看到以下内容：

Peeked Binds (identified by position):
--------------------------------------
   1 - :A (VARCHAR2(30), CSID=871): 'S'

其中，数字1为关联的操作ID，:A为绑定变量名（括号中为变量数据类型，对于字符类型，还有其字符集的ID号），最后为解析计划时，该变量所窥视到的数值。
绑定变量（Bind Variable）是PLSQL的一个重要特性。我们在描述SQL的处理过程中提到：SQL被提交到Oracle后，会被哈希化，检查该语句是否已经存在于内存中，以决定是否进行硬解析。而语句的细微差别（如大小写、注释、空格等）都会导致产生不同的哈希值，引起硬解析。而硬解析是一个相当消耗CPU的过程。通常，在应用中，同一条语句在不同的会话中可能会使用不同的数值作为参数。
例如，一个系统登录模块，不同的用户登录时，会输入不同的用户名、密码作为参数，引发系统执行用于查询用户信息的语句。这样，任何一个用户都会导致这条语句得到一个不同的哈希值，从而导致对其进行硬解析。
而绑定变量使Oracle避免了此类重复的硬解析。使用绑定变量的语句进行解析时，变量并不会代入具体数据，而是以:VARIABLE的形式出现在语句中，在语句执行时，再将变量代入。
绑定变量的引入，可以帮助系统减少硬解析。但是，我们之前提到，CBO是对数据敏感的优化器，在使用绑定变量对语句进行执行计划选择时，如果不考虑实际数据的分布性，可能会导致不能获取到最优的执行计划。例如，某张表上有一个字段COL1，COL1上建有索引，但其数据分布非常不均衡：其99%数值为A，%1的数值为其他，如B、C...在对该表以字段COL1进行条件查询时，如果查询数值为A的数据记录，则使用全表扫描比使用索引访问效率更高（全表扫描是多数据块读，一次读入多个数据块；索引扫描一次读入单个数据块，并且需要访问索引和表两个对象）；在查询其他数据时，使用索引访问会使语句的性能更好。如果在解析语句时，没有考虑实际的参数值，就可能会导致优化器选择一个错误的执行计划。
为了解决这个问题，在Oracle 9i中引入了绑定变量窥视（Bind Variable Peeking）特性。即在解析含有绑定变量的语句时，会“窥视”其具体数值以获取最优的执行计划。
不过，这一特性并不完善。以上述例子为例，如果解析语句时，窥视到的数值为A，相应执行计划则为全表扫描。但如果该语句的其他执行参数为非A数值，那么该执行计划则会导致这些执行出现性能问题。事实上，在9i和10g的系统，绑定变量这一缺陷导致的性能问题屡见不鲜，而我们的解决手段通常是禁用绑定变量窥视特性（参数_optim_peek_user_binds控制），使用存储概要（Stored Outline）、SQL配置文件（SQL Profile）或提示等方法强制改变执行计划。在11g中，自适应游标共享（Adatpive Cursor Sharing）特性可以解决这一问题：它会比较绑定变量不同数值的执行计划的效率，相应的选择最优的执行计划。

2.5.4　分布式查询语句信息

分布式查询中，会涉及对远程数据库上对象的查询。该部分信息则是将执行计划中涉及远程对象查询的语句显示出来，语句是与执行计划中的操作相关联的。示例如下：

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
       ","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_CONSUME
       R_GROUP","EXTERNAL_NAME" FROM "T_USERS" "T_USERS" (accessing 'ORA10201' )

2.5.5　注释

注释（Note）部分显示了在输出执行计划时所探测到的问题以及相关建议。例如，以下注释内容告诉我们，该执行计划使用了RBO作为优化器，建议我们使用CBO：

Note
-----
   - rule based optimizer used (consider using cbo)

第二篇
SQL优化技术
我们现在已经知道，在对语句进行解析时，由优化器（Optimizer）生成和选择语句的执行计划。而优化器生成和选择执行计划的过程也就是对SQL的执行方式进行优化（Optimizing）的过程。
整个过程可分为三个步骤：查询转换、代价估算以及计划生成，由SQL引擎中的优化器组件完成，即由以下三个组件完成：查询转换器（Query Transformer）、代价估算器（Estimator）和计划生成器（Plan Generator）。其中，查询转换又称为逻辑优化，这一过程通过转换查询来消除一些代价高昂的操作；代价估算则称为物理优化，在逻辑优化的基础上，对各种可能的操作进行代价估算；而计划生成则根据代价估算结果选择最终代价最小的执行计划。
在Oracle中，有两种优化器：基于规则的优化器（Rule Based Optimizer，RBO）和基于代价的优化器（Cost Based Optimizer，CBO）。下面简单介绍一下。
1.基于规则的优化器（RBO）
尽管RBO还存在于Oracle的优化器当中，并且在某些特定环境中仍然起作用，但从10g开始，Oracle已经不再对其做技术支持，而是推荐用户尽量使用CBO。我们这里仅对RBO做简要介绍，以后再提到优化器，除非特别指示为RBO，否则都是指CBO。
RBO在选择执行计划时，按照系统的特定规则取优先级最高的访问路径。这些规则仅考虑对象结构，不考虑对象上面的数据。以下是RBO中访问路径的排序，优先级由高到低：
1）通过ROWID访问单条数据记录(Single Row by Rowid)；
2）通过簇关联访问单条数据记录(Single Row by Cluster Join)；
3）通过唯一键或者主键的哈希簇访问单条数据记录(Single Row by Hash Cluster Key with Unique or Primary Key)；
4）通过唯一键或者主键访问单条数据记录(Single Row by Unique or Primary Key)；
5）簇关联(Clustered Join)；
6）访问哈希簇键值(Hash Cluster Key)；
7）访问索引簇键值(Indexed Cluster Key)；
8）访问复合索引(Composite Index)；
9）访问单字段索引(Single-Column Indexes)；
10）闭包范围查询索引字段(Bounded Range Search on Indexed Columns)，即范围查询条件包括起始值和结束值，如BETWEEN AND、LIKE；
11）非闭包范围查询索引字段(Unbounded Range Search on Indexed Columns)，即范围查询条件仅包括起始值或结束值，如>[=]、<[=]；
12）排序合并关联(Sort Merge Join)；
13）索引字段上取最大值、最小值(MAX or MIN of Indexed Column)；
14）对索引字段排序(ORDER BY on Indexed Column)；
15）全表扫描(Full Table Scan)。
2.基于代价的优化器（CBO）
CBO在选择执行计划时，会枚举各种可能的访问路径、关联方法、关联顺序及其他可能的操作。由对象及系统的相关统计数据，按照特定公式计算各种操作的代价，并最终选择一个总代价最小的计划作为执行计划。
本篇详细介绍SQL的优化技术，包括查询转换的方法，代价估算中的统计数据方法，以及如何进行代价估算。