查询优化器基础知识—SQL语句处理过程

如何调优 Oracle SQL系列文章第三篇：SQL处理过程。

第一篇：SQL调优系列文章之—SQL调优简介

第二篇：SQL调优系列文章之—SQL性能方法论

要进行Oracle SQL调优，您必须了解查询优化器。优化器是内置软件，用于确定语句访问数据的最有效方法。

3 SQL处理过程

本章介绍数据库如何处理DDL语句并创建对象，DML如何修改数据以及查询数据。

3.1 关于SQL处理过程

下图描绘了SQL处理过程的几个阶段。根据声明，数据库可能会省略其中一些阶段。

图3-1 SQL处理过程的几个阶段

3.1.1 SQL解析

SQL处理的第一个阶段是解析。

解析阶段涉及将SQL语句的各个部分分离为其他进程可以处理的数据结构。数据库在应用程序的指示下解析语句，这意味着只有应用程序才可以减少解析的数量。

当应用程序发出SQL语句时，应用程序会对数据库进行解析调用以准备要执行的语句。解析调用将打开或创建一个游标，该游标是特定于会话的私有SQL区域的句柄，该区域包含已解析的SQL语句和其他处理信息。游标和私有SQL区域位于程序全局区域（PGA）中。

在解析调用期间，数据库执行检查以便识别在语句执行之前可以找到的错误。有些错误解析时无法捕获。例如，数据库只能在语句执行期间遇到数据转换中的死锁或错误。

3.1.1.1 语法检查

Oracle数据库必须检查每个 SQL 语句的语法有效性。

不合常规格式的 SQL 语句无法通过检查。例如，以下语句，因为关键字 FROM 拼写错误为 FORM：

3.1.1.2 语义检查

语义检查确定语句是否有意义，例如，语句中的对象和列是否存在。

语法正确的语句可能无法进行语义检查，如以下不存在的表的查询示例所示：

3.1.1.3 共享池检查

在解析期间，数据库执行共享池检查以确定它是否可以跳过资源密集的语句处理步骤。

为此，数据库使用散列算法为每个SQL语句生成散列值。 语句哈希值是V$SQL.SQL_ID 中显示的 SQL ID。 此哈希值在 Oracle 数据库版本中是确定性的，因此单个实例或不同实例中的相同语句具有相同的 SQL ID。

当用户提交SQL语句时，数据库将搜索共享 SQL 区域以查看现有的已解析语句是否具有相同的哈希值。SQL语句的哈希值与以下值不同：

• 语句的内存地址 Oracle 数据库使用 SQL ID 在查找表中执行键值读取。这样，数据库就可以获得语句的可能内存地址。
• 该语句的执行计划的哈希值 SQL 语句可以在共享池中具有多个计划。通常，每个计划都有不同的哈希值。如果相同的 SQL ID 具有多个计划哈希值，则数据库就会知道此 SQL ID 存在多个计划。

解析操作属于以下类别，具体取决于提交的语句类型和散列检查的结果：

• 硬解析

如果Oracle数据库无法重用现有代码，那么它必须构建应用程序代码的新可执行版本。 此操作称为硬解析或库高速缓存未命中。

在硬解析期间，数据库多次访问库高速缓存和数据字典高速缓存以检查数据字典。 当数据库访问这些区域时，它会在所需对象上使用称为锁存器的序列化设备，以便它们的定义不会更改。 Latch争用会增加语句执行时间并降低并发性。

• 软解析

软解析是任何不是硬解析的解析。 如果提交的语句与共享池中的可重用SQL语句相同，则Oracle Database将重用现有代码。 这种代码重用也称为库缓存命中。

软解析可以在他们执行的工作量上有所不同。 例如，配置会话共享SQL区域有时可以减少软解析中的锁存量，使其“更软”。

通常，软解析比硬解析更可取，因为数据库会跳过优化和行源生成步骤，直接执行。

下图是专用服务器体系结构中 UPDATE 语句的共享池检查的简化表示。

图3-2共享池检查

如果检查确定共享池中的语句具有相同的哈希值，则数据库将执行语义和环境检查以确定语句是否具有相同的含义。只有相同的语法是不够的。例如，假设两个不同的用户登录到数据库并发出以下SQL语句：

两个用户的 SELECT 语句在语法上是相同的，但是 my_table 属于两个单独的模式对象名。这种语义差异意味着第二个语句不能重用第一个语句的代码。

即使两个语句在语义上相同，环境差异也会导致难以解析。 在这种情况下，优化环境的会话设置可以影响执行计划生成，如工作区大小或优化程序的设置（例如：优化器模式）。 考虑由单个用户执行的以下一系列 SQL 语句：

在前面的示例中，相同的 SELECT 语句在三个不同的优化器环境中执行。因此，数据库为这些语句创建了三个单独的共享 SQL 区域，并强制对每个语句进行硬解析。

3.1.2 SQL优化

在优化期间，Oracle 数据库必须至少对每个唯一的 DML 语句执行一次硬解析，并在此解析期间执行优化。

数据库不优化 DDL。 唯一的例外是 DDL 包含 DML 组件，例如需要优化的子查询。

3.1.3 SQL行源生成

行源生成器是从优化器接收最佳执行计划并生成可由数据库的其余部分使用的迭代执行计划的软件。

迭代计划是一个二进制程序，当由 SQL 引擎执行时，会生成结果集。该计划采取步骤组合的形式。每个步骤都返回一个行集。下一步要么使用此集合中的行，要么最后一步将行返回给发出 SQL 语句的应用程序。

行源是执行计划中的步骤返回的行集，以及可以迭代处理行的控制结构。行源可以是表，视图或连接或分组操作的结果。

行源生成器生成行源树，它是行源的集合。 行源树显示以下信息：

• 语句引用的表的排序
• 语句中提到的每个表的访问方法
• 对语句中的连接操作影响的表的连接方法
• 数据操作，例如过滤，排序或聚合

示例3-1执行计划

此示例显示启用 AUTOTRACE 时 SELECT 语句的执行计划。该语句查询姓氏字母以 A 开头的所有员工的姓氏，职位和部门名称。此语句的执行计划是行源生成器的输出。

3.1.4 SQL执行

在执行期间，SQL引擎执行行源生成器生成的树中的每个行源。 此步骤是 DML 处理中唯一必需的步骤。

图3-3是一个执行树，也称为解析树，它显示了示例3-1中计划中从一个步骤到另一个步骤的行源流。通常，执行步骤的顺序与计划中的顺序相反，因此您从下往上阅读计划。

执行计划中的每个步骤都有一个 ID 号。 图3-3中的数字对应于例3-1中所示计划中的 Id 列。 计划的 “Operation” 列中的初始空间表示层次关系。 例如，如果 Operation 名称前面有两个空格，则此 Operation 是前面有一个空格的 Operation 的子操作。 以一个空格开头的 Operation 是 SELECT 语句本身的子项。

图3-3行源树

在图3-3中，树的每个节点都充当行源，这意味着示例3-1中的执行计划的每个步骤都从数据库中检索行，或者从一个或多个行源接受行作为输入。 SQL引擎执行每个行源，如下所示：

• 黑框指示的步骤从数据库中的对象物理地检索数据。这些步骤是访问路径或从数据库检索数据的技术。
  • 步骤6 使用全表扫描从 departments 表中检索所有行。
  • 步骤5 使用全表扫描从 jobs 表中检索所有行。
  • 步骤4 按顺序扫描 emp_name_ix 索引，查找以字母A开头并检索相应 rowid 的每个键。 例如，对应于 Atkinson 的 rowid 是 AAAPzRAAFAAAABSAAe。
  • 步骤3 从 employees 表中检索步骤4返回 rowid 的行。例如，数据库使用 rowid AAAPzRAAFAAAABSAAe 来检索 Atkinson 的行。
• 透明背景框指示的步骤在行源上操作。
  • 步骤2 执行散列连接，接受来自步骤3和5的行源，将步骤5行源中的每一行连接到步骤3中的相应行，并将结果行返回到步骤1。 例如，员工Atkinson的行与作业名称Stock Clerk相关联。
  • 步骤1 执行另一个散列连接，接受来自步骤2和6的行源，将步骤6源中的每一行连接到步骤2中的相应行，并将结果返回给客户端。

在一些执行计划中，步骤是迭代的，而在其他执行计划中是顺序，例3-1中显示的散列连接是顺序的。数据库根据连接顺序完成整个步骤。数据库以 emp_name_ix 的索引范围扫描开始。使用它从索引中检索的 rowid，数据库将读取 employees 表中的匹配行，然后扫描 jobs 表。 在从 jobs 表中检索行之后，数据库将执行散列连接。

在执行期间，如果数据不在内存中，则数据库将数据从磁盘读入内存。数据库还会获取确保数据完整性所需的任何锁和锁存器，并记录在 SQL 执行期间所做的任何更改。处理 SQL 语句的最后阶段是关闭游标。

3.2 Oracle 数据库如何处理 DML

大多数 DML 语句都有一个查询组件。在查询中，执行游标会将查询结果放入一组称为结果集的行中。

3.2.1 如何获取行集

结果集行可以一次提取一行，也可以按组提取。

在 fetch 阶段，数据库选择行，如果查询请求，则对行进行排序。 每次连续提取都会检索结果的另一行，直到获取最后一行。

通常，在获取最后一行之前，数据库无法确定查询要检索的行数。Oracle 数据库检索数据以响应 fetch 调用，因此数据库读取的行越多，它执行的工作就越多。 对于某些查询，数据库会尽快返回第一行，而对于其他查询，它会在返回第一行之前创建整个结果集。

3.2.2 读取一致性

通常，查询使用 Oracle 数据库读取一致性机制检索数据，该机制可确保查询读取的所有数据块与单个时间点保持一致。

读取一致性使用 undo 数据来显示过去的数据版本。例如，假设查询必须在全表扫描中读取100个数据块。查询处理前10个块，而不同会话中的 DML 修改块75.当第一个会话到达块75时，它将使用 undo 数据来检索旧的未修改版本的数据并构造非当前版本的块75。

3.2.3 数据变更

必须更改数据的 DML 语句使用读取一致性来仅检索修改开始时与搜索条件匹配的数据。

之后，这些语句将检索存在于当前状态的数据块并进行必要的修改。数据库必须执行与修改数据相关的其他操作，例如生成 redo 和 undo 数据。

3.3 Oracle 数据库如何处理 DDL

Oracle数据库以不同于 DML 的方式处理 DDL。

例如，在创建表时，数据库不会优化 CREATE TABLE 语句。 相反，Oracle 数据库会解析 DDL 语句并执行该命令。

数据库以不同方式处理 DDL，因为它是在数据字典中定义对象的一种方法。通常，Oracle 数据库必须解析并执行许多递归 SQL 语句才能执行 DDL 语句。假设您创建一个表，如下所示：

通常，数据库将运行许多递归语句来执行前面的语句。递归 SQL 将执行以下操作：

• 在执行 CREATE TABLE 语句之前发出 COMMIT
• 验证用户权限是否足以创建表
• 确定表应该驻留在哪个表空间中
• 确保未超出表空间配额
• 确保架构中没有对象具有相同的名称
• 将定义表的行插入到数据字典中
• 如果 DDL 语句成功则发出 COMMIT，否则发出 ROLLBACK