查询优化原理 -- SQL处理

最新推荐文章于 2023-07-25 14:40:34 发布

cowboy90

最新推荐文章于 2023-07-25 14:40:34 发布

阅读量393

点赞数

分类专栏： ORACLE_SQL Tuning 文章标签： oracle 12C 执行计划 SQL调优

本文链接：https://blog.csdn.net/cowboy90/article/details/95490814

版权

ORACLE_SQL Tuning 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

参考文档：《Oracle® Database SQL Tuning Guide》

本章解释数据库如何处理DDL语句来创建对象、如何处理DML语句用于修改数据、如何处理查询语句用于检索数据。

1，SQL处理

SQL处理：是SQL语句的解析、优化、行源生成和执行。

下图描述了SQL处理的一般阶段。根据语句的不同，数据库可能会省略其中一些阶段。

1.1，SQL解析

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

解析阶段涉及将SQL语句的各个部分分离成其他例程可以处理的数据结构。当应用程序发出指令时，数据库解析语句，这意味着只有应用程序可以减少解析的次数，而数据库本身不行。

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

在解析调用过程中，数据库执行语句之前会检查语法错误。有些错误无法在解析过程中检测出来。例如，只有在语句执行期间才会遇到的死锁或数据转换错误。

1.1.1，语法检查

Oracle数据库必须检查每个SQL语句的语法有效性，比如查询语句中少了from关键字。如果检测到语法错误，则会报错提示该错误。

1.1.2，语义检查

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

1.1.3，共享池检查

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

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

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

1）语句的内存地址

Oracle数据库使用SQL ID在查找表中执行键控读取。这样，数据库就可以获得语句的可能内存地址。

2）语句执行计划的哈希值

SQL语句在共享池中可以有多个计划。通常，每个计划都有不同的哈希值。如果同一个SQL ID具有多个计划哈希值，则数据库知道此SQL ID存在多个计划。

根据提交的语句类型和哈希检查的结果，解析操作分为以下类别：

1）硬解析

如果Oracle数据库不能重用现有代码，那么它必须构建应用程序代码的新的可执行版本。此操作称为硬分析或库缓存未命中。在硬解析过程中，数据库多次访问库缓存和数据字典缓存来检查数据字典。当数据库访问这些区域时，为了使访问对象的定义不发生改变，它会在对象上使用latch这种序列化设备。latch争用会增加语句执行时间并降低并发性。

注：数据库对DDL只执行硬解析。

2）软解析

所有不是硬解析的解析就是软解析。如果提交的语句与共享池中可重用的SQL语句相同，则Oracle数据库将重用现有代码。这种代码重用也称为库缓存命中。软解析在执行的工作量上也会有所不同。例如，配置会话共享SQL区域有时可以减少软解析中的latch的使用量，使它们“更软”。一般来说，软解析比硬解析更可取，因为数据库会跳过优化和行源生成步骤，直接执行。

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

如果检查确定共享池中的语句具有相同的哈希值，则数据库将执行语义和环境检查，以确定这些语句是否具有相同的含义。只有相同的语法是不够的，例如，两个schema下创建了两个同名的表，两个用户登录后对各自的这个同名表执行了一条相同的查询SQL。两个用户的select语句在语法上是相同的，但由于schema不一样，这种语义差异意味着第二条语句不能重用第一条语句的代码。

两个用户都执行如下语句，语法相同，但语义不同：

create table stu(age number);
select * from stu;

即使两个语句在语义上相同，环境差异也可以导致强制硬解析。优化器环境是会话设置的总和，这些设置往往会影响执行计划生成，例如工作区大小或优化器设置（例如优化器模式）。

如下由单个用户执行的一系列SQL语句，相同的select语句在三个不同的优化器环境中执行。因此，数据库为这些语句创建三个单独的共享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;

1.2，SQL调优

在优化过程中，Oracle数据库必须对每个唯一的DML语句至少执行一次硬解析，并在此解析过程中进行优化。数据库不会优化DDL，唯一的例外是当DDL包含DML组件（如需要优化的子查询）时。

1.3，SQL行源生成

行源生成器是从优化器接收最佳执行计划并生成可供数据库其余部分使用迭代执行计划的软件。迭代计划是一个二进制程序，当由SQL引擎执行时，它生成结果集。该计划采取了步骤组合的形式。每个步骤返回一个行集。下一步要么使用这个集合中的行，要么最后一步将这些行返回给发出SQL语句的应用程序。

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

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

1）语句引用的表的顺序

2）语句中提到的每个表的访问方法

3）语句中受连接操作影响的表的连接方法

4）数据操作，如筛选、排序或聚合

如下例子显示了启用AUTOTRACE时SELECT语句的执行计划。该语句查看以字母A开头的员工的姓、职位名称和部门名称。此语句的执行计划是行源生成器的输出。

示例1.3
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%')

1.4，SQL执行

在执行期间，SQL引擎执行由行源生成器生成的树中的每个行源。此步骤是DML处理中唯一必需的步骤。图3-3是一个执行树，也称为解析树，它显示了示例1.3中的计划从一个步骤到另一个步骤的行源流。一般来说，执行步骤的顺序与计划的顺序相反，所以要从下往上阅读计划。

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

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

1）图3-3中黑框里的步骤是从数据库对象中物理的检索数据。这些步骤是访问路径，或者是检索数据的方法。

– 步骤6使用全表扫描从Departments表中检索所有行。
– 步骤5使用全表扫描从jobs表中检索所有行。
– 步骤4按顺序扫描emp_name_ix索引，查找以字母A开头的键并检索相应的rowid。例如，Atkinson这一行对应的rowid是AAAPzRAAFAAAABSAAe。
– 步骤3根据步骤4返回的rowid从Employees表中检索对应的行。例如，数据库使用rowid AAAPzRAAFAAAABSAAe检索到Atkinson这一行。

2) 图3-3中白框中的步骤是在行源上的操作

– 步骤2执行哈希联接，接受步骤3和5中的行源，将步骤5行源中的每一行连接到步骤3中相应的行，并将结果行返回到步骤1。例如，员工Atkinson这一行与JOB名称Stock Clerk相关联。
– 步骤1执行另一个哈希连接，接受步骤2和6中的行源，将步骤6行源中的每一行连接到步骤2中对应的行，并将结果返回给客户端。例如，员工Atkinson这一行与名为Shipping的部门关联。

在一些执行计划中，步骤是迭代的，而在另一些执行计划中是连续的。示例1.3中所示的hash join是连续的。数据库根据连接顺序完整地完成这些步骤。数据库从emp_name_ix的索引范围扫描开始。使用它从索引中检索的rowids，数据库读取Employees表中匹配的行，然后扫描jobs表。从jobs表中检索行后，数据库将执行哈希联接。

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

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

2.1，如何获取行集

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

在数据抓取阶段，数据库查询行，如果有排序请求，则对行进行排序。每次连续的数据抓取都会检索结果的其它一行，直到最后一行被获取为止。

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

2.2，一致性读

通常，查询通过使用Oracle数据库读取一致性机制来检索数据，该机制确保查询读取的所有数据块在同一时间点上是一致的。

一致性读使用undo数据来显示旧版本数据。例如，假设一个查询必须在全表扫描中读取100个数据块。当会话查询到第10个块时，另一个会话用DML语句修改了第75个块。当第一个会话到达第75号块时，它发现数据已经变更，然后使用undo数据检索修改前的旧数据版本，并在内存中构造第75块的旧版本数据。

2.3，数据修改

修改数据的DML语句，在用一致性读检索数据时，只检索那些匹配条件的数据。然后，这些语句检索当前状态下存在的数据块，并进行必要的修改。数据库必须执行与修改数据相关的其他操作，例如生成重做和撤消数据。

3，Oracle数据库如何处理DDL语句

DDL的处理和ＤML语句的处理有些不同。比如在创建表时，Oracle数据库会解析DDL语句并执行命令，但不会优化create table语句。另外，DDL是在数据字典中定义对象，数据库必须解析和执行许多递归SQL语句才能执行DDL语句。

CREATE TABLE mytable (mycolumn INTEGER);

用上面的DDL语句建表时，数据库会运行几十个递归语句来执行前面的语句。递归SQL将执行以下操作:

1)在执行create table语句之前执行commit
2)验证用户权限是否足有建表权限
3)确定表应放在哪个表空间中
4)确保没有超过表空间配额
5)确保schema中没有同名的对象
6)将定义表的行插入数据字典
7)如果DDL语句成功，则发出COMMIT；如果不成功，则发出ROLLBACK

cowboy90

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查询优化原理 -- SQL处理

参考文档：《Oracle® Database SQL Tuning Guide》本章解释数据库如何处理DDL语句来创建对象、如何处理DML语句用于修改数据、如何处理查询语句用于检索数据。1，SQL处理 SQL处理：是SQL语句的解析、优化、行源生成和执行。下图描述了SQL处理的一般阶段。根据语句的不同，数据库可能会省略其中一些阶段。...
复制链接

扫一扫