Oracle数据库优化器概述

Oracle数据库优化器概述

优化器介绍

如果想要理解数据库如何处理 SQL 语句，那么必须了解数据库中被称为优化器的部件（也称为查询优化器或基于成本的优化器）。

优化器最本质的目标是为一个 SQL 语句找到最高效的执行方式。（所有 SQL 语句都使用优化器来确定访问指定的数据的最有效手段）

使用优化器

要执行一个 DML 语句，数据库可能需要执行许多步骤。每一步或者是从数据库中物理地检索数据行，或者是在为发出语句的用户准备数据，等等。

在Oracle数据库中，一个DML语句的处理方式可能有很多种。例如，访问表或索引的顺序可能会不同。
数据库用来执行一条语句的步骤，很大程度上会影响该语句的运行速度有多快。
优化器生成执行计划来描述可能的执行方法。
优化器生成处理一个查询的几乎所有可能的方法，并给生成的执行计划中的每个步骤分配一个成本。具有最低成本的计划被选择为要执行的查询计划。

优化器通过考虑几个信息来源来确定哪种执行计划是最有效的，包括查询条件、可用的访问路径、为系统收集的统计信息、以及提示等。Oracle 数据库处理的任何语句，优化器将执行以下操作：
1、表达式和条件评估
2、检查完整性约束， 以了解数据和基于此元数据的优化的更多信息
3、语句转换
4、优化器目标选择
5、访问路径选择
6、连接顺序选择

注意：
可以获得一个 SQL 语句的执行计划，而不一定需要执行它。只有数据库实际上用于执行一个查询的执行计划，才称为查询计划。

优化器目标

可以通过设置优化器目标，并为优化程序收集有代表性的统计数据，来影响优化器的选择。
优化器目标设置有如下两种
总吞吐量：ALL_ROWS 提示指示优化器尽可能快地将所有结果数据返回给客户端应用程序。
初始响应时间：FIRST_ROWS 提示指示优化器尽可能快地获取第一行数据给客户端。

典型的交互式终端用户应用程序将受益于初始响应时间优化，
非交互式批处理模式应用程序将受益于总吞吐量的优化。

优化器组件

优化器包含三个主要组件，如图：

优化器的输入是一个已解析的查询 。优化器将执行以下操作：

1、优化器接收已解析的查询，经过查询转换器确定，更改查询的形式是否有助于优化器生成一个更好的执行计划
2、优化器接收已解析的查询，并基于可用的访问路径和提示，为SQL 语句生成一组潜在的计划。
3、优化器基于数据字典中的统计信息，估计每个计划的成本。成本是一个与特定用于执行该语句的计划所需的预期资源使用成正比的估计值。
4、优化器比较各个计划的成本，并选择具有最低成本的计划（也叫查询计划），然后传递给行源生成器。

查询转换器

查询转换器确定更改查询的形式是否有助于优化器生成一个更好的执行计划。
查询转换器的首要任务是对SQL进行等价重写。它会应用子查询展开、视图合并等超过30种转换技术，将一个复杂的SQL重写为结构更优、更易于优化的形式。
例如，将select中的子查询，如果优化器评估，可以转为表连接形式而不影响整体结果，且这样会使得执行代价更低，那么查询转换器就会将该语句进行透明的改写。

估算器

估算器确定一个给定的执行计划的总体成本。
表的统计信息越准确，才能更准确的估算执行计划的总体成本
估算器生成三种不同类型的测量值，以实现这一目标：

选择性

简单理解：WHERE条件能过滤掉多少比例的数据

基数

执行每一步操作后，预计会产生多少行数据。

成本

这项测量表示工作量或使用的资源。查询优化器使用磁盘 I/O、 CPU 使用率、和内存使用情况作为工作量。
综合以上信息，估算出执行计划的整体资源消耗。
统计信息越准确，评估出来的成本越准确。

计划生成器

基于前两者提供的转换后SQL和代价估算，穷举各种执行路径，如不同的访问路径（全表扫描、索引扫描）、连接方法（嵌套循环、哈希连接等）和连接顺序，最终选出代价最小的方案。

像 EXPLAIN PLAN 这样的诊断工具使能够查看优化器所选择的执行计划。EXPLAIN PLAN 会显示指定的 SQL 查询的查询计划，就好像它已经在当前会话中执行过一样。其他的诊断工具还有 Oracle 企业管理器和 SQL * Plus AUTOTRACE 命令。

访问路径

访问路径是从数据库检索数据的方式。例如，使用索引的查询与不使用索引的查询具有不同的访问路径。
通常，索引访问路径对于只检索表行中的一个小的子集是最佳的。而完全扫描则对访问表中的一大部分更有效。

从表中检索数据的访问路径主要如下几种：
全表扫描
这种类型的扫描从一个表读取所有行，并滤掉那些不符合选择条件的行。数据库顺序扫描段中的所有数据块，包括那些高水位标记以下的块，高水位标记用来分隔已使用和未使用的空间 。

Rowid 扫描
行的 rowid 指定包含行的数据文件和数据块，以及行在该块中的位置。数据库通过语句的 WHERE 子句或一个索引扫描，首先获取所选的行的 rowids，然后基于这些 rowid 查找每个选定的行。

索引扫描
扫描被 SQL 语句访问的索引列的值。如果该语句仅访问已被索引的列，则数据库直接从索引读取索引的列值。

簇扫描
簇扫描用来检索存储在一个索引表簇中的一个表中的数据，具有相同的簇键的所有行都存储在同一个数据块中 。Oracle 数据库首先通过扫描簇索引来获取所选行的rowid。然后基于此 rowid 查找相应行。

哈希扫描
哈希扫描用于查找哈希群集中的行，其中具有相同哈希值的所有行都存储在同一个数据块中 。Oracle 数据库首先通过将哈希函数应用于由该语句指定的簇键值，以获得哈希值。然后扫描包含具有此哈希值的行的数据块。

优化器对访问路径的选择，基于语句的所有可用的访问路径，和使用每个访问路径或其组合的估算成本。

优化器统计

优化器统计信息是描述有关数据库和数据库中的对象的详细信息的数据集合。
统计信息提供数据存储和分布的正确描述，以被优化器用来评估访问路径。
优化器统计信息包括：
表统计
包括行数、 块数、和平均行长等。
列统计
包括非重复值数目、空值数目、和数据的分布。
索引统计
包括叶块数目和索引层级等。
系统统计
包括 CPU 和 I/O 的性能及利用率。

Oracle 数据库自动收集所有数据库对象的优化器统计信息，并作为一项自动维护任务来维护这些统计信息。
还可以使用 DBMS_STATS 包手动收集统计信息。该 PL/SQL 包可以修改、 查看、 导出、 导入、和删除统计信息。
优化器统计信息被创建来用于查询优化的目的，并存储在数据字典中。这些统计数字不应与通过动态性能视图看到的性能统计信息相混淆。

优化器提示

提示是 SQL 语句中的注释，作为优化程序的一个指示。有时应用程序的设计者比优化器更了解一个特定的应用程序的数据的详细信息，他可以选择一个运行 SQL 语句的更有效方法。应用程序设计者可以在 SQL 语句中使用提示，来指定该语句应该如何运行。

优化器提示的语法： /* + 提示信息 */ ，具体参考下面案例

简单案例

例如，假设你的交互式应用程序运行一个查询，返回 50 行。此应用程序最初只读取查询的前 25 行来呈现给终端用户。你想使优化器生成一个计划，尽可能快地获取首批 25 条记录，以使用户不必被迫等待。可以使用一个提示来将此指令传递给优化器，
带有 FIRST_ROWS 提示的 SELECT 语句的执行计划
SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 26 | 182
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 26 | 182
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | |
------------------------------------------------------------------------
示例 中的执行计划显示，优化器选择 employees.department_id 列上的一个索引，来查找其部门 ID 超过 50 的雇员中的前 25 行。优化器使用从索引中检索到的 rowid， 从雇员表中检索相应记录，并将其返回给客户端。第一条记录的检索通常几乎是在瞬间即可完成的。

下面示例显示了相同的语句，但未使用优化器提示。
示例无提示 SELECT 语句的执行计划
SELECT employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50 | 350 |
|* 1 | VIEW | index$_join$_001 | 50 | 350 |
|* 2 | HASH JOIN | | | |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 50 | 350 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 50 | 350 |

上面示例中的执行计划，将两个索引联接以尽可能快的返回请求的记录。优化器并不像第一个示例中那样多次在表和索引间倒腾，而是在
EMP_DEPARTMENT_IX 索引上使用范围扫描，找出所有部门 ID 超过 50 的行，并将这些行放在一个哈希表中。然后优化器读取 EMP_EMP_ID_PK 索引。对该索引中的每一行，它探测一次该哈希表，以查找相应的部门 id。
在这种情况下，数据库不能在完成对 EMP_DEPARTMENT_IX 索引的范围扫描之前向客户端返回第一行。因此，此生成的计划将需要更长的时间返回第一条记录。与第一个示例中按索引 rowid 访问表的计划不同，该示例的计划使用多数据块 I/O，导致大量读取操作。这种读取使得整个结果集的最后一行会更快地返回。

SQL 处理的概述

这一部分说明了数据库处理创建对象的 DDL 语句、修改数据的 DML 语句、和检索数据的查询语句等的处理方式。

SQL 处理的阶段

下图显示了 SQL 处理的一般阶段： 解析、 优化、 产生行源、和执行。数据库可能会忽略某些步骤，这取决于具体的语句。

SQL 解析

如上图所示 ，SQL 处理的第一阶段是解析。
这一阶段涉及将 SQL 语句的各个片断分离到一个可由其他例程处理的数据结构。数据库会在应用程序的指示下，对一个语句进行解析，这意味着只有应用程序可以减少解析数目，而不是数据库本身。

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

在解析调用期间，数据库会执行以下检查：
1、语法检查
2、语义检查
3、共享池检查

前面的检查确定在语句执行之前可以发现的错误。一些错误不能通过解析来捕获。例如，数据库在数据转换过程中可能会遇到死锁或错误，但这仅在语句执行中才会发生。

语法检查

Oracle 数据库必须检查每个 SQL 语句的语法有效性。违反了标准格式的SQL 语法规则的语句无法通过检查。

例如，下面的语句会失败，因为 FROM关键字被错误地拼写为 FORM：
SQL> SELECT * FORM employees;
SELECT * FORM employees
 *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

语义检查

语句的语义即是它的含义。因此，语义检查确定一条语句是否是有意义的，例如，该语句中的对象和列是否存在。
语法正确的语句可能通不过语义检查。

如下例所示，查询一个不存在的表：
SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
 *
ERROR at line 1:
ORA-00942: table or view does not exist

共享池检查

在解析期间，数据库执行一个共享池检查，以确定是否可以跳过占用大量资源的语句处理步骤。
数据库使用一种哈希算法为每个 SQL 语句生成一个哈希值。语句的哈希值即是在 V$SQL.SQL_ID 中显示的 SQL ID。
当用户提交一个 SQL 语句时，数据库搜索共享 SQL 区，以查看是否已经有一个现成的已分析的语句具有相同的哈希值。
SQL 语句的哈希值有别于下列值：
该语句的内存地址：Oracle 数据库使用 SQL ID 在一个查找表中执行一个键值读取。通过这种方式，数据库获取该语句的所有可能的内存地址。
该语句的执行计划的哈希值：SQL 语句在共享池中可以有多个计划。每个计划都具有不同的哈希值。如果一个相同的 SQL ID 有多个计划哈希值，则数据库知道针对该 SQL ID 有多个计划存在。

基于所提交语句的类型和哈希检查的结果，解析操作分为以下类别：
硬解析
如果数据库不能重用现有代码，则它必须生成应用程序代码的一个新的可执行版本。此操作称为一个硬解析，或库缓存未命中。
数据库对DDL 始终执行硬分析。
在硬解析期间，数据库多次访问库缓存和数据字典缓存以检查数据字典。当数据库访问这些区域时，它在所需对象上使用一个叫做闩锁的串行化设备，以便它们的定义不会被更改 。闩锁争用会增加语句的执行时间，并降低并发性。

软解析
任何不是硬解析的解析都是软解析。如果提交的语句与在共享池中的某个可重用 SQL 语句相同，则数据库将重用该现有代码。
重用代码也称为库缓存命中。

软软解析
可以进一步减少软解析的工作量，如果会话的私有 SQL 区的句柄，已经存在该语句，可以更进一步减少软解析中的闩锁用量，这样的解析称为软软解析，。

一般地，软解析比硬解析更可取，因为数据库可以跳过优化和行源生成步骤，而直接进入到执行阶段。

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

如果检查到共享库中有一个语句具有相同的哈希值，则数据库执行语义和环境检查，以确定其含义是否相同。
相同的语法是不够的。

例如，假设两个不同用户登录到数据库并发出以下 SQL 语句：
CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;
两个用户的 SELECT 语句的语法相同，但这是两个独立的模式对象，名字都是 my_table。这种语义差异意味着第二个语句不能重用第一个语句的代码。

即使两个语句在语义上是相同的，某个环境差异也可能使其强制进行硬解析。在这种情况下，环境是可以影响执行计划生成的全部会话设置，如工作区大小或优化器设置等。

请考虑以下由单个用户执行的一系列 SQL 语句：
ALTER SYSTEM FLUSH SHARED_POOL;
SELECT * FROM my_table;
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
SELECT * FROM my_table;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT * FROM my_table;
在前面的示例中，相同的 SELECT 语句在三种不同的优化器环境中执行。因此，数据库为这些语句创建三个单独的共享 SQL 区域，并对每个语句强制进行硬解析。

SQL 优化

优化器最本质的目标是为一个 SQL 语句找到最高效的执行方式。查询优化是选择执行 SQL 语句的最有效手段的过程。数据库对查询的优化基于对正在访问的实际数据收集的统计信息。优化器使用行数、数据集大小、和其他因素，来生成各种可能的执行计划，并为每个计划分配一个成本数值。数据库会使用具有最低成本的计划。

数据库对每个唯一的 DML 语句必须至少执行一次硬解析，并在解析期间执行优化。
DDL 永远不会被优化，除非它包括需要优化的 DML 组件，如子查询。

SQL 行源生成

行源生成器是一种软件，它从优化器接收经过优化的执行计划，并生成一个称为查询计划的迭代计划，可供数据库的其余部分使用。
迭代计划是一个二进制程序，由 SQL 虚拟机执行，以生成结果集。

查询计划采用组合多个步骤的形式。
每一步返回一个行集。
该集合中的行可以在下一步被使用，或在最后一步返回给发出 SQL 语句的应用程序。
行源是执行计划中的某一步骤所返回的行集，且带有能够迭代该行集的控制结构。
行源可以是表、 视图、或联接操作或分组操作的结果。

行源生成器产生一个行源树，它是一个行源的集合。行源树显示以下信息：
1、由语句所引用的多个有次序的表
2、在语句中提及的每个表的访问方法
3、在语句中受联接操作影响的各个表的联接方法
4、进行的数据操作，如筛选、 排序、或聚合等

简单案例

示例显示一个 AUTOTRACE 处于启用状态的 SELECT 语句的执行计划。该语句选择其姓氏以 A 开头的所有雇员的姓氏、 职位名称、和部门名称。此语句的执行计划是行源生成器的输入
示例执行计划
SELECT e.last_name, j.job_title, d.department_name 
FROM hr.employees e, hr.departments d, hr.jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.last_name LIKE 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 975837011
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 189 | 7 (15)| 00:00:01 |
|* 2 |  HASH JOIN | | 3 | 141 | 5 (20)| 00:00:01 |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN | EMP_NAME_IX | 3 | | 1 (0)| 00:00:01 |
| 5 |    TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 6 |   TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 2 - access("E"."JOB_ID"="J"."JOB_ID")
 4 - access("E"."LAST_NAME" LIKE 'A%')
 filter("E"."LAST_NAME" LIKE 'A%')

SQL 执行

在执行期间， SQL 引擎执行行源生成器所产生的树中的每个行源。这一步是在 DML 处理中唯一的强制性步骤。
下图是一个执行树，也称为解析树，显示了行源从一步流向另一个步。通常，执行步骤的顺序与计划中顺序相反，所以你应该从底向上来阅读计划。
在 Operation 列中的初始空格表示层次结构关系。例如，如果一个操作的名称前面有两个空格，则此操作是前面有一个空格的操作的子操作。前面有一个空格的操作是 SELECT 语句本身的子操作。

在上图中，树的每个节点作为一个行源，这意味着每个步骤的执行计划要么从数据库中检索行，要么接受一个或多个行源中的行作为输入。
SQL 引擎这样执行每个行源，如下所示：
1、黑框所示的步骤物理地从数据库中检索对象的数据。这些步骤即是访问路径，或某种从数据库中检索数据的技术。
1）第 6 步使用全表扫描来从 departments 表中检索所有行。
2）第 5 步使用全表扫描来从 jobs 表中检索所有行。
3）第 4 步顺序扫描 emp_name_ix 索引，查找以字母 A 开头的每个键，并检索相应的 rowid 。例如，与 Atkinson 对应的 rowid 是
AAAPzRAAFAAAABSAAe.。
4）第 3 步，从 employees 表中检索由第 4 步所返回的 rowids所在的行。例如，数据库使用 rowid AAAPzRAAFAAAABSAAe 来检索 Atkinson 的行。

2、白框中所示的步骤操作行源。
1）第 2 步执行一个哈希联接，它从第 3 步和第 5 步中接受行源，将第 5 步行源中的每一行与第 3 步中的相应行连接，并将结果行返回给 第 1 步。例如，雇员 Atkinson 所在行与职位 Stock Clerk 相关联。
2）第 1 步执行另一个哈希连接，从第 2 步和第 6 步接受行源，将第 6 步行源中的每一行与第 2 步中的相应行连接，并将结果返回客户端。例如，雇员 Atkinson 所在行与名为 Shipping 的部门相关联。

在某些执行计划中的步骤是迭代的，而其他一些则是顺序的。"SQL行源生成所"部分展示的计划是迭代的，因为 SQL 引擎多次在索引、表、客户端重复这些步骤。

在执行过程中，如果数据不在内存中，数据库则从磁盘读取数据到内存。
为确保数据的完整性，数据库还取得任何必要的锁和闩锁，并为 SQL 执行过程中所做的任何更改记录日志。
处理 SQL 语句的最后一个阶段是关闭游标。

如何处理 DML

大多数 DML 语句都有一个查询组件。在一个查询中，游标执行后会将查询结果放入一个称为结果集的行集。
结果集中的行可以每次读取一行或一组。在读取阶段，数据库选择行，如果该查询要求排序，则将其排序。
每次后续读取从结果中检索下一行，直到最后一行已被读取。
通常，只有直到读取了最后一行，数据库才知道一个查询到底需要检索多少行数。Oracle 数据库检索数据来响应读取调用，因此数据库读取的行越多，则它执行的工作就越多。对于某些查询，数据库会尽可能快地返回第一行，而其它一些则是先创建整个结果集之后才返回第一行。

读一致性

通常，查询通过使用数据库读取一致性机制来检索数据。这一机制使用撤消数据来显示以前版本的数据，保证查询所读取的所有数据块都是单点时间一致的。

举一个读取一致性的例子，假设一个查询在一次全表扫描中必须读取 100 个数据块。该查询处理前面的 10 块，而在另一个会话中的 DML 修改了第75 块。当第一个会话读到第 75 块时，发现数据已更改，于是就使用撤消数据来检索旧的、 未经修改的版本，并在内存中构造一个第 75 块的非当前版本。

数据改变

必须更改数据的 DML 语句会使用读一致性机制，仅检索在修改开始时符合搜索条件的数据。此后，这些语句将以数据块当前存在的状态检索它们，并进行所需的修改，也就是"当前读机制。数据库还必须执行与数据修改相关的其他操作，例如生成重做（redo）数据和撤销（undo）数据。

如何处理 DDL

Oracle 数据库对 DDL 的处理不同于 DML。例如，在创建表时，数据库并不会优化 CREATE TABLE 语句。相反，数据库只是解析该 DDL 语句并执行该命令。

数据库以不同方式处理 DDL 的原因是，它是一种在数据字典中定义对象的方式。通常，为执行 DDL 命令，数据库必须解析和执行许多递归 SQL 语句。

假设创建一个表，如下所示：
CREATE TABLE mytable (mycolumn INTEGER);

通常，数据库将运行数十个递归语句来执行前面的语句。递归 SQL 会执行以下操作：
1、在执行 CREATE TABLE 语句之前，发出 一个 COMMIT 命令
2、验证用户权限足以创建表
3、确定表应位于的表空间
4、确保不超过表空间配额
5、确保在模式中没有具有相同的名称的对象
6、将定义表的行插入到数据字典（例如insert tab$ ,insert col$ 这些内部数据字典表）
7、如果 DDL 语句成功，发出 一个 COMMIT，或者如果未成功，发出一个 ROLLBACK