《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的优化技术,包括查询转换的方法,代价估算中的统计数据方法,以及如何进行代价估算。

相关文章
|
15天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
40 0
|
3天前
|
SQL 存储 弹性计算
GaussDB SQL调优:建立合适的索引
GaussDB SQL调优:建立合适的索引
|
6天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
41 1
|
12天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之flink sql ROW_NUMBER()回退更新的机制,有相关文档介绍吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
27 1
|
15天前
|
SQL 缓存 关系型数据库
一次sql改写优化子查询的案例
在生产环境中,一个MySQL RDS实例遭遇了高CPU使用率问题,原因是执行了一条复杂的UPDATE SQL语句,该语句涉及一个无法缓存的子查询(UNCACHEABLE SUBQUERY),导致子查询需要针对每一行数据重复执行,极大地影响了性能。SQL语句的目标是更新一行数据,但执行时间长达30秒。优化方法是将子查询转换为内连接形式,优化后的语句执行时间降低到毫秒级别,显著减少了CPU消耗。通过示例数据和执行计划对比,展示了优化前后的时间差异和执行效率的提升。
|
15天前
|
存储 SQL 关系型数据库
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
|
15天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式,提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗,从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
240 3
|
15天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在DataWorks中使用ODPS SQL时遇到"该文件对应引擎实例已失效,请重新选择可用的引擎实例"的错误提示”,是什么导致的
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
42 0
|
15天前
|
存储 SQL 缓存
30个业务场景的SQL优化
这些优化策略和示例可以帮助改善 `SQL` 查询的性能和效率。在实践中,需要综合考虑数据库设计、`SQL` 编写、服务器配置等多方面因素,选择合适的优化方法,并进行充分的测试和验证。以上 30 个经验是 V 哥在实际经验中总结的内容,当然,业务场景不同,具体的优化策略也会不同,按实际情况处理,这不就是程序员要做的事情么。
|
15天前
|
SQL 存储 算法
clickhouse SQL优化
clickhouse 是 OLAP 数据库,但其具有独特的索引设计,所以如果拿 MySQL 或者其他 RDB 的优化经验来优化 clickhouse 可能得不到很好的效果,所以特此单独整理一篇文档,用于有 SQL 优化需求的同学,本人接触 clickhouse 时间也不长,难免有不足的地方,如果大家发现错误,还请不吝指正。