oracle 执行计划详解

夜光小兔纸

已于 2024-04-22 21:52:43 修改

阅读量5.3k

点赞数 35

分类专栏：数据库 Oracle 文章标签： oracle 数据库

于 2024-04-22 21:49:33 首次发布

本文链接：https://blog.youkuaiyun.com/Ruishine/article/details/138045863

版权

执行计划是指示 Oracle 如何获取和过滤数据、产生最终的结果集，是影响SQL 语句执行性能的关键因素。在深入了解执行计划之前，首先需要知道执行计划是在什么时候产生的，以及如何让 SQL 引擎为语句生成执行划。

先了解 SQL 语句的处理执行过程。当一条语句提交到 Oracle 后，SQL 引擎会分为三个步骤对其处理和执行：解析（Parse）、执行（Execute）和获取（Fetch），分别由 SQL 引擎的不同组件完成。

在这里插入图片描述

Oracle SQL 引擎的体系结构

一、SQL 编译器（SQL Compiler）

SQL 编译器由解析器（Parser）、查询优化器（Query Optimizer）和行源生成器（Row Source Generator）组成。

 解析器（Parser）：执行对 SQL 语句的语法、语义分析，将查询中的视图展开、划分为小的查询块。
 查询优化器（Query Optimizer）:Oracle 数据库中 SQL 优化器（SQLOptimizer）是 SQL 分析和执行的优化工具，为语句生成一组可能被使用的执行计划，估算出每个执行计划的代价，并调用计划生成器（Plan Generator）生成计划，比较计划的代价，最终选择一个代价最小的计划，优化器负责生成 SQL 的执行计划。它对优化器来说输入的是解析后的 SQL 语句，输出的是执行计划。
查询优化器由查询转换器（Query Transform）、代价估算器（Estimator）和计划生成器（Plan Generator）组成。
在这里插入图片描述

Oracle 两种优化器
 RBO（Rule-Based Optimizer）：基于规则的优化器
 CBO（Cost-Based Optimizer）：基于成本的优化器（11g 默认优化器）

从 Oracle 10g 开始，RBO 已经被弃用，但是我们依然可以通过 Hint 方式来使用它。
上述优化器实际上指的是基于代价的优化器（Cost Based Optimizer，CBO），CBO 也是当前采用的所有优化和调优技术的核心基础。

查询转换器（Query Transformer）:决定是否重写用户的查询（包括视图合并、子查询反嵌套），以生成更好的查询计划。

代价估算器（ Estimator ） : 使用统计数据来估算操作的选择率（Selectivity）、返回数据集（Cardinality）和代价，并最终估算出整个执行计划的代价。

计划生成器（Plan Generator）:计划生成器会考虑可能的访问路径（AccessPath）、关联方法和关联顺序，生成不同的执行计划，让查询优化器从这些计划中选择出代价最小的一个计划。

行源生成器（Row Source Generator）:从优化器接收到优化的执行计划后，为该计划生成行源（Row Source）。行源是一个可被迭代控制的结构体，它能以迭代方式处理一组数据行、并生成一组数据行。

SQL 执行引擎（SQL Execution Engine）:SQL 执行引擎依照语句的执行计划进行操作，产生查询结果。在每一个操作中，SQL 执行引擎会以迭代方式执行行源、生成数据行。

Oracle 引入一些新的优化技术时，例如，SPM、SPA 等，这些组件会与 SQL引擎的组件融合，提供更好的优化和调优方法。

CBO 优化模式
（1）FIRST_ROWS(n)
（2）ALL_ROWS （默认值）

查看参数：

SQL> show parameter optimizer_mode

在同一系统环境下，同一条 SQL 语句选用不同的优化模式，将可能令优化器生成不同的执行计划。

修改 CBO 优化模式：
（1）Sessions 级别：

SQL> alter session set optimizer_mode = all_rows;

（2）系统级别：修改 spfile 参数

SQL> alter system set OPTIMIZER_MODE = ALL_ROWS;

（3）语句级别：用 Hint（/*+ … */）来设定

SQL> Select /*+ first_rows(10) */ name from table;

二、SQL 语句的执行过程

用户发出待执行的 SQL 语句 -> 解析 -> 优化器（查询转换，RBO 或 CBO处理）-> 生成执行计划 -> 实际执行 -> 返回结果。
在这里插入图片描述
处理步骤包括:
1、语法检查(syntax check): 检查此 sql 的拼写是否语法。例如：

SELECT * FORM employees;

2、语义检查(semantic check): 访问对象是否存在及该用户是否具备相应的权限。例如：

SELECT * FROM nonexistent_table;

3、共享池检查(Shared Pool Check)，生成 SQL 语句的 Hash 值和 SQL_ID，按 SQL_ID 及其 Hash 值 Shared Pool 中查找匹配的相同 SQL 语句。

4、加载 SQL 代码至内存，生成内存共享数据（cursor），生成执行计划(execution plan)。

5、执行 SQL，返回结果(execute and return)。

SQL 解析类型:
Hard Parse (硬解析): 也称为"library cache miss"。进行语法检查、语义检查，加载 SQL 语句至 Shared pool 的 Library Cache 中，生成执行计划。

大小写、空格等差异都会令同一条语句被解析为不同的语句。由于须要加载到内存中，需要专门分配内存空间并进行内存管理，因此，硬解析需要占用 CPU、获取 library cache latch 和 shared pool latch 等资源，对于 SQL 的执行来说，硬解析是最消耗资源的。所以，应当尽量避免解析，力求实现 SQL 语句的一次解析，多次执行。

Soft Parse (软解析): 也称为"library cache hit"。在 SQL 语法和语义检查后，如果在 Shared Pool 中找到了与之完全相同的 SQL 语句，则无需执行内存加载，直接调用已有的执行计划并执行。

提高软解析的方法包括：增加 shared_pool_size，使用绑定变量优化 SQL语句，调整 cursor_sharing 参数等。

Softer Soft Parse(软软解析): 当设置了 session_cached_cursors 这个参数之后，Cursor 被直接 Cache 在当前 Session 的 UGA（User Global Area）中的，会话端重复执行相同的 SQL 时，先在 UGA 中查找，如果发现完全相同的Cursor，就直接到 Shared pool 中取结果，也就实现了 Softer Soft Parse。

SQL>alter system flush shared_pool;
SQL>create table test as select * from dba_objects where 1<>1;
--收集 test 表统计信息
SQL>exec dbms_stats.gather_table_stats('sys',&