优化器介绍如果想要理解数据库如何处理 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_MODEFIRST_ROWS; SELECT * FROM my_table; ALTER SESSION SET SQL_TRACETRUE; 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_IDD.DEPARTMENT_ID) 2 - access(E.JOB_IDJ.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数据。如何处理 DDLOracle 数据库对 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