《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.5 执行计划中其他信息的含义-阿里云开发者社区

开发者社区> 华章出版社> 正文
登录阅读全文

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

简介: 本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第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的优化技术,包括查询转换的方法,代价估算中的统计数据方法,以及如何进行代价估算。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: