Oracle SQL处理

Oracle® Database   SQL Tuning Guide

Part II   Query Optimizer Fundamentals 3 SQL Processing 4 Query Optimizer Concepts 5 Query Transformations 3 SQL 处理 本章介绍数据库如何处理DDL 语句 ( 创建对象 ) 、DML 语句( 修改数据 ) 以及查询 语句( 检索数据 ) 。 3.1 关于 SQL 处理 SQL 处理是 SQL 语句的解析、优化、行源生成和执行。 下图描述了SQL 处理的一般阶段。根据语句的不同，数据库可能会省略其中一些阶段。 3.1.1   SQL 解析 SQL 处理的第一阶段是解析。 解析阶段包括将SQL 语句的各个部分分离为其他例程可以处理的数据结构。数据库根据应用程序的指示解析语句，这意味着只有应用程序而不是数据库本身可以减少解析的次数。 当应用程序发出SQL 语句时，应用程序对数据库进行解析调用，以准备执行语句。解析调用打开或创建一个游标，该游标是特定于会话的私有 SQL 区域的句柄，该区域包含已解析的 SQL 语句和其他处理信息。游标和私有 SQL 区域位于程序全局区域 (PGA) 中。 在解析调用期间，数据库执行检查，以确定在语句执行之前可以找到的错误。有些错误无法通过解析捕获。例如，只有在语句执行期间，数据库才能在数据转换中遇到死锁或错误。 3.1.1.1   语法检查 Oracle 数据库必须检查每个 SQL 语句的语法有效性。 如果语句违反了SQL 语法规则，则检查失败。例如，下面的语句失败，因为关键字 FROM 被拼写为 FORM: SQL> SELECT * FORM employees;  SELECT * FORM employees * ERROR at line 1: ORA-00923: FROM keyword not found where expected 3.1.1.2   语义 检查 语句的语义就是它的意义。语义检查确定语句是否有意义，例如，语句中的对象和列是否存在。 语法 检查通过了，语义检查不一定能通过， 如下面 示例查询的表不存在 : SQL> SELECT * FROM nonexistent_table; SELECT * FROM nonexistent_table * ERROR at line 1: ORA-00942: table or view does not exist 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 数据库不能重用现有代码，那么它必须构建应用程序代码的新可执行版本。此操作称为硬解析或库缓存丢失。 注： 数据库总是对DDL 语句 执行硬解析。 在硬解析过程中，数据库多次访问库缓存和数据字典缓存来检查数据字典。当数据库访问这些区域时，它使用一个称为锁存器的序列化设备来访问所需的对象，这样它们的定义就不会改变。锁存争用会增加语句执行时间并降低并发性。 •软解析 软解析是任何非硬解析的解析。如果提交的语句与共享池中的可重用SQL 语句相同，那么 Oracle 数据库将重用现有代码。这种代码重用也称为库缓存命中。 软解析在执行的工作量方面可能有所不同。例如，配置会话共享SQL 区域有时可以减少软解析中的锁存数量，使它们变得“更软”。 通常，软解析比硬解析更可取，因为数据库跳过了优化和行源生成步骤，直接执行。 下图是专用服务器体系结构中UPDATE 语句的共享池检查的简化表示。 如果检查确定共享池中的语句具有相同的 哈希 值，则数据库将执行语义和环境检查，以确定语句是否具有相同的含义。相同的语法是不够的。例如，假设两个不同的用户登录到数据库并发出以下SQL 语句 : CREATE TABLE my_table ( some_col INTEGER ); SELECT * FROM my_table; 这两个用户的SELECT 语句在语法上是相同的，但是两个独立的模式对象名为 my_table 。这种语义上的差异意味着第二条语句不能重用第一条语句的代码。 即使两个语句在语义上是相同的，环境差异也会强制进行硬解析。在此上下文中，优化器环境是会影响执行计划生成的会话设置的总和，例如工作区域大小或优化器设置( 例如，优化器模式 ) 。考虑以下由单个用户执行的一系列 SQL 语句 : ALTER   SESSION   SET  OPTIMIZER_MODE = ALL_ROWS ; ALTER   SYSTEM   FLUSH   SHARED_POOL ;  # optimizer environment 1 SELECT   *   FROM  sh.sales ; ALTER   SESSION   SET  OPTIMIZER_MODE = FIRST_ROWS ;  # optimizer environment 2 SELECT   *   FROM  sh.sales ; ALTER   SESSION   SET   SQL_TRACE = true ;  # optimizer environment 3 SELECT   *   FROM  sh.sales ; 在前面的示例中，相同的SELECT 语句在三个不同的优化器环境中执行。因此，数据库为这些语句创建了三个独立的共享 SQL 区域，并强制对每个语句进行硬解析。 3.1.2   SQL 优化 在优化过程中，Oracle 数据库必须对每个唯一的 DML 语句至少执行一次硬解析，并在此解析过程中执行优化。 数据库没有优化DDL 。唯一的例外是当 DDL 包含 DML 组件（如需要优化的子查询）时。 3.1.3   SQL 行源生成 行源生成器是从优化器接收最优执行计划并生成可由数据库的其余部分使用的迭代执行计划的软件。 迭代计划是一个二进制程序，当由SQL 引擎执行时，生成结果集。每一步都返回一个行集。下一步使用这个集合中的行，或者最后一步将这些行返回给发出 SQL 语句的应用程序。 行源是执行计划中的一个步骤返回的行集，以及可以迭代处理行的控制结构。行源可以是联接或分组操作的表、视图或结果。 行源生成器生成行源树，它是行源的集合。行源树显示以下信息: •语句引用的表的顺序 •语句中提到的每个表的访问方法 •语句中受连接操作影响的表的连接方法 •过滤、排序或聚合等数据操作 示例 3-1 此示例显示了启用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%' ; 3.1.4   SQL 执行 在执行期间，SQL 引擎执行行源生成器生成的树中的每个行源。此步骤是 DML 处理中惟一必需的步骤。 图3-3 是一个执行树，也称为解析树，它显示了示例 3-1 中计划中从一个步骤到另一个步骤的行源流。通常，执行中的步骤的顺序与计划中的顺序相反，因此您可以自底向上阅读计划。 执行计划中的每个步骤都有一个ID 号。图 3-3 中的数字对应于例 3-1 所示计划中的 Id 列。 p 执行计划 的Operation 列中的初始空格表示层次关系。例如，如果一个操作的名称前面有两个空格，那么这个操作就是前面有一个空格的操作的子操作。前跟一个空格的操作是 SELECT 语句本身的子语句。 在图3-3 中，树的每个节点都充当行源，这意味着示例 3-1 中的执行计划的每一步要么从数据库中检索行，要么接受来自一个或多个行源的行作为输入。 SQL 引擎执行每个行源，如下所示 : •黑盒指示的步骤从数据库中的对象物理检索数据。这些步骤是用于从数据库中检索数据的访问路径或技术。 - 步骤 6 使用全表扫描来检索所有来自部门表的行。 - 步骤 5 使用全表扫描来检索 jobs 表中的所有行。 - 步骤 4 按顺序扫描 emp_name_ix 索引，查找每个以字母 A 开头的键并检索相应的 rowid 。例如，与 Atkinson 对应的 rowid 是 AAAPzRAAFAAAABSAAe 。 - 步骤 3 从 employees 表中检索由步骤 4 返回其行 id 的行。例如，数据库使用 rowid AAAPzRAAFAAAABSAAe 来检索 Atkinson 的行。 •清除方框指示的步骤操作行源。 - 步骤 2 执行 哈希 连接，接受来自步骤3 和步骤 5 的行源，将来自步骤 5 的行源的每一行连接到步骤 3 中相应的行，并将结果行返回到步骤 1 。 例如，employee Atkinson 的行与作业名称 Stock Clerk 相关联。 - 步骤 1 执行另一个 哈希 连接，接受来自步骤2 和步骤 6 的行源，将来自步骤 6 源的每一行连接到步骤 2 中相应的行，并将结果返回给客户端。 例如，employee Atkinson 的行与名为 Shipping 的部门相关联。 在一些执行计划中，步骤是迭代的，而在另一些执行计划中，步骤是连续的。例3-1 中显示的散列连接是连续的。数据库根据连接顺序完整地完成这些步骤。数据库从 emp_name_ix 的索引范围扫描开始。使用从索引中检索的 rowid ，数据库读取 employees 表中匹配的行，然后扫描 jobs 表。从 jobs 表中检索行之后，数据库执行散列连接。 在执行期间，如果数据不在内存中，数据库将数据从磁盘读入内存。数据库还会取出确保数据完整性所需的所有锁和锁存器，并记录SQL 执行期间所做的任何更改。处理 SQL 语句的最后一个阶段是关闭游标。 3.2   Oracle 数据库如何处理 DML 大多数DML 语句都有一个查询组件。在查询中，游标的执行将查询的结果放入称为结果集的一组行中。 3.2.1   如何获取行集 结果集行可以一次取一行，也可以取组。 在获取阶段，数据库选择行，如果查询请求，则对行进行排序。每个连续的获取都将检索结果的另一行，直到最后一行被获取。 通常，在获取最后一行之前，数据库无法确定查询要检索的行数。Oracle 数据库在响应 fetch 调用时检索数据，因此数据库读取的行越多，它执行的工作就越多。对于某些查询，数据库会尽可能快地返回第一行，而对于其他查询，它会在返回第一行之前创建整个结果集。 3.2.2   读 一致性 通常，查询通过使用Oracle 数据库读一致性机制来检索数据，该机制确保查询读取的所有数据块在某个时间点上是一致的。 读取一致性使用撤消数据来显示数据的过去版本。例如，假设一个查询必须在一次全表扫描中读取100 个数据块。查询处理前 10 个块，而 DML 在不同的会话中修改第 75 块。当第一个会话到达第 75 块时，它实现更改并使用撤消数据来检索数据的旧的、未修改的版本，并在内存中构造第 75 块的非当前版本。 3.2.3   数据更改 必须更改数据的DML 语句使用读取一致性，以便在修改开始时仅检索与搜索条件匹配的数据。 然后，这些语句检索数据块，因为它们存在于当前状态，并进行所需的修改。数据库必须执行与数据修改相关的其他操作，例如生成重做和撤消数据。 3.3   Oracle 数据库如何处理 DDL Oracle 数据库处理 DDL 的方式与 DML 不同。 例如，创建表时，数据库不会优化CREATE   TABLE 语句。相反， Oracle 数据库解析 DDL 语句并执行命令。 数据库处理DDL 的方式不同，因为它是在数据字典中定义对象的一种方法。通常， Oracle 数据库必须解析并执行许多递归 SQL 语句才能执行 DDL 语句。假设您创建一个表，如下所示： CREATE   TABLE  mytable ( mycolumn INTEGER ); 通常，数据库会运行许多递归语句来执行前面的语句。递归SQL 将执行以下操作 : •在执行 CREATE TABLE 语句之前发出 COMMIT •验证用户特权足以创建表 •确定表应该驻留在哪个表空间中 •确保没有超出表空间配额 •确保模式中的对象没有相同的名称 •将定义表的行插入到数据字典中 •如果 DDL 语句成功，则发出 COMMIT; 如果没有成功，则发出 ROLLBACK。 欢迎关注我的微信公众号"IT小Chen" ，共同学习，共同成长！！！