导语

在你写下一条SQL的那一刻，Oracle优化器就已悄然启动了它的“军师”本能。它不仅是SQL的执行总导演，更是影响查询快慢、生死攸关的幕后推手。

这篇文章，带你深入解密优化器背后的三大“智囊”——查询转换器、估算器、计划生成器，看看一条SQL是如何被层层打磨、筛选、计算，最终走上“最优路径”的！

01查询转换器：把SQL“翻译”得更聪明

在 Oracle 数据库中，查询优化器是核心组件之一，负责选择最佳执行计划，从而确保 SQL 查询以最高效的方式执行。

Oracle 的查询优化器由三个主要组件组成：查询转换器 (Query Transformer)、估算器 (Estimator) 和 计划生成器 (Plan Generator)。

每个组件在生成查询执行计划时都起着重要作用，接下来将深入解析这些组件的作用、原理及其协作方式。

以下图表展示了这些组件。

一组查询块代表一个解析后的查询，它是优化器的输入。

下表描述了优化器操作：

表1-1 优化器操作

02

查询转换器

查询转换器是优化器的第一步，它的主要任务是对输入的 SQL 查询进行变换，以寻找更高效的执行路径。

具体来说，查询转换器会根据已知的优化规则对查询进行重写，确保查询能够使用更有效的操作。

工作原理

• 语义等效转换
• 查询转换器会判断是否有可能将原始 SQL 查询转换为语义等效但成本更低的查询。例如，Oracle 可能会将 OR 条件重写为 UNION ALL，从而优化查询的执行路径。
• 替代方案
• 当查询有多种执行方式时，查询转换器会生成多个可行的替代方案，并为每个方案计算预期的成本。最后，它会选择成本最低的方案。

示例

图1-2 查询变换器

假设原始 SQL 查询使用了 OR 条件：

SELECT *

FROM sales

WHERE promo_id = 33

OR prod_id = 136;

查询转换器可能将其重写为：

SELECT *

FROM sales

WHERE prod_id = 136

UNION ALL

SELECT *

FROM sales

WHERE promo_id = 33

AND LNNVL(prod_id = 136);

这种转换可能会降低查询的执行成本，特别是当 UNION ALL 可以避免额外的排序操作时。

03

估算器

估算器负责根据表的统计信息估算每个执行计划的成本。

这一步骤对于确保查询优化器选择最佳执行计划至关重要。

估算器通过计算选择性、基数和成本等度量，来为每个执行计划提供成本评估。

主要度量

• 选择性 (Selectivity)
• 选择性表示查询条件从表中筛选出的行的比例。选择性值接近0表示谓词过滤了大部分行，而接近1则表示过滤效果较弱。
• 基数 (Cardinality)
• 基数是执行计划中每个操作返回的行数。准确的基数估算对于优化器决定连接策略、排序方式等至关重要。
• 成本 (Cost)
• 成本是优化器用于评估执行计划的资源消耗度量，主要包括磁盘I/O、CPU 使用和内存使用等资源的消耗。

如下图所示，如果有统计数据，那么估计器会使用这些数据来计算各项指标。

这些统计数据提高了指标的准确程度。

图 1- 3 估计器

对于示例4 - 1中所示的查询，估计器使用选择性、估计基数（总共返回10行）和成本度量来得出其总成本估计值3：

---------------------------------------------------------------------------

|Id| Operation |Name |Rows|Bytes|Cost %CPU|Time|

---------------------------------------------------------------------------

| 0| SELECT STATEMENT | |10|250|3 (0)|00:00:01|

| 1| NESTED LOOPS | | | | | |

| 2| NESTED LOOPS | |10|250|3 (0)|00:00:01|

|*3| TABLE ACCESS FULL |DEPARTMENTS | 1| 7|2 (0)|00:00:01|

|*4| INDEX RANGE SCAN |EMP_DEPARTMENT_IX|10| |0 (0)|00:00:01|

| 5| TABLE ACCESS BY INDEX ROWID|EMPLOYEES |10|180|1 (0)|00:00:01|

---------------------------------------------------------------------------

1

选择性（Selectivity）

选择性表示行集中行的一个比例，对于等值查询的场景(非等值的计算公式相对复杂，后面再讨论)，公式如下：

Selectivity = 谓词条件过滤之后返回的结果集 / 未经谓词条件过滤之后返回的结果集

谓词从行集中筛选出特定数量的行。

因此，谓词的选择性表明有多少行通过谓词测试。选择性的范围是0.0到1.0。

选择性为0.0意味着没有从行集中选择任何行，而选择性为1.0则意味着选择了所有行。

当该值接近0.0时，谓词的选择性更强；当该值接近1.0时，谓词的选择性更弱（或更不具选择性）。

当目前列没有直方图且没有NULL值的情况下，用目标列做等值查询的可选择率：

Selectivity = 1 / NUM_DISTINCT

优化器根据统计信息是否可用来估计选择性：

1）统计数据不可用

根据 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数的值，优化器会使用动态统计信息或内部默认值。

数据库根据谓词类型使用不同的内部默认值。

例如，等值谓词（last_name = 'Smith'）的内部默认值低于范围谓词（last_name > 'Smith'）的内部默认值，因为预计等值谓词返回的行占比更小。

2）有可用的统计信息

当有可用的统计信息时，估计器会使用它们来估计选择性。

假设存在150个不同的员工姓氏。对于等值谓词last_name = 'Smith'，选择性是last_name不同值数量n的倒数，在此示例中为0.006，因为该查询选择的行包含150个不同值中的1个。

如果last_name列上存在直方图，那么估计器将使用直方图而非不同值的数量。

直方图记录了列中不同值的分布，因此它能得出更好的选择性估计，特别是对于存在数据倾斜的列。

示例1:有直方图计算sales.prod_id列的Selectivity

• 查询sales.prod_id的distinct数量及sales总数据量

方法1：适用于测试环境

select count(distinct PROD_ID),count(1) from sh.sales;



COUNT(DISTINCTPROD_ID) COUNT(1)

---------------------- ----------

72 918843



方法2：适用于生产环境

SQL> SELECT num_rows, sample_size

FROM dba_tables

WHERE owner='SH' AND table_name='SALES';



NUM_ROWS SAMPLE_SIZE

___________ ______________

918843 918843



SELECT column_name, num_distinct, histogram

FROM dba_tab_columns

WHERE owner='SH' AND table_name='SALES' AND column_name='PROD_ID';

COLUMN_NAME NUM_DISTINCT HISTOGRAM

______________ _______________ ____________

PROD_ID 72 FREQUENCY

• 计算选择性

select distinct prod_id from sh.sales where rownum<3;



PROD_ID

__________

13



SQL> select count(1) from sh.sales where prod_id=13;



COUNT(1)

___________

6002



SQL>

SQL> select 6002/918843;



6002/918843

_______________________________________________

0.006532127904331860829325575751243683632568

示例2：无直方图计算sales.prod_id列的Selectivity

• 删除列上的直方图

exec dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'SALES',estimate_percent => 100,cascade => true, method_opt=>'for columns prod_id size 1',no_invalidate => false);

• 查看直方图信息

SELECT column_name, num_distinct, histogram

FROM dba_tab_columns

WHERE owner='SH' AND table_name='SALES' AND column_name='PROD_ID';

COLUMN_NAME NUM_DISTINCT HISTOGRAM

______________ _______________ ____________

PROD_ID 72 NONE

• 计算选择性

Selectivity

select 1/72;



1/72

_____________________________________________

0.0138888888888888888888888888888888888889

这里补充一个计算sh.sales表中各列的统计信息的查询脚本

SELECT a.column_name,

b.num_rows,

a.num_distinct ,

ROUND(a.num_distinct / b.num_rows * 100, 2) selectivity,

a.histogram,

a.num_buckets

FROM dba_tab_col_statistics a, dba_tables b

WHERE a.owner = b.owner

AND a.table_name = b.table_name

AND a.owner = 'SH'

AND a.table_name = 'SALES';

2 基数(Cardinality)

基数是执行计划中每个操作返回的行数。

例如，如果优化器对全表扫描返回的行数估计为100，那么此操作的基数估计就是100。基数估计显示在执行计划的“Rows（行数）”列中。

优化器根据一组复杂的公式确定每个操作的基数，这些公式使用表级和列级统计信息或动态统计信息作为输入。

当单表查询中出现单个等值谓词且没有直方图时，优化器会使用最简单的公式之一。

在这种情况下，优化器假定为均匀分布，并通过将表中的总行数除以WHERE子句谓词中使用的列中的不同值的数量来计算查询的基数。

计算公式如下：

Computed Cardinality = Original Cardinality * Selectivity

下面的示例是无直方图的场景利用Selectivity估算Cardinality的场景，有直方图的场景在后面的章节再详细介绍。

示例1：用户sh对销售表进行如下查询：

SQL> select /*+full(sales)*/ * from sh.sales where prod_id=13;

6002 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1550251865

---------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12762 | 398K| 3550 (1)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL| | 12762 | 398K| 3550 (1)| 00:00:01 | 1 | 15 |

|* 2 | TABLE ACCESS FULL | SALES | 12762 | 398K| 3550 (1)| 00:00:01 | 1 | 15 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


2 - filter("PROD_ID"=13)

销售表包含918843 行数据。

当前的数据库统计信息表明，prod_id列中的不同值数量为72。

因此，selectivity=1/72,优化器使用公式918843*( 1/72)= 12761.7，将结果集的基数估计为12762。

基数估计必须尽可能准确，因为它们会影响执行计划的各个方面。

当优化器确定连接的成本时，基数很重要。

例如，在员工表和部门表的嵌套循环连接中，员工表中的行数决定了数据库必须查询部门表的频率。

基数对于确定排序成本也很重要。

3 成本

优化器成本模型考虑了预测查询将使用的机器资源。

成本是一种内部数值度量，代表对某个计划估计的资源使用情况。成本特定于优化器环境中的某个查询。

为了估计成本，优化器会考虑以下因素：

• 系统资源，包括估计的输入/输出、CPU 和内存
• 估计返回的行数（基数）
• 初始数据集的大小
• 数据的分布情况
• 访问结构

执行时间是成本的一个函数，但成本并不直接等同于时间。

例如，如果查询A的计划成本低于查询B的计划成本，那么可能会出现以下结果：

• A的执行速度比B快。
• A的执行速度比B慢。
• A的执行时间与B相同。

因此，不能将不同查询的成本相互比较。

此外，也不能比较使用不同优化器模式的语义等效查询的成本。

关于成本的计算规则，我们会在后面的章节中详细介绍。

04

计划生成器

计划生成器负责根据查询块和查询变换器提供的信息，生成多种可能的执行计划，并评估每个计划的成本。

优化器会通过评估各种访问路径、连接方法和连接顺序来选择成本最低的执行计划。

工作原理

• 评估多种连接方法
• 计划生成器会尝试不同的连接方法，例如嵌套循环连接 (Nested Loops)、排序合并连接 (Merge Join)、哈希连接 (Hash Join) 等。
• 考虑不同的访问路径
• 例如，优化器可能会选择使用全表扫描、索引扫描或索引范围扫描来访问表数据。
• 连接顺序
• 优化器会考虑不同表连接的顺序，因为不同的连接顺序会导致不同的执行计划成本。

以下图表展示了优化器为输入查询测试不同的计划。

图3-1 计划生成器

以下是优化器跟踪文件中的一段代码片段，展示了优化器执行的一些计算：

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: DEPARTMENTS[D]#0 EMPLOYEES[E]#1



***************

Now joining: EMPLOYEES[E]#1

***************

NL Join

Outer table: Card: 27.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 16

Access path analysis for EMPLOYEES

. . .

Best NL cost: 13.17

. . .

SM Join

SM cost: 6.08

resc: 6.08 resc_io: 4.00 resc_cpu: 2501688

resp: 6.08 resp_io: 4.00 resp_cpu: 2501688

. . .

SM Join (with index on outer)

Access Path: index(FullScan)

. . .

HA Join

HA cost: 4.57

resc: 4.57 resc_io: 4.00 resc_cpu: 678154

resp: 4.57 resp_io: 4.00 resp_cpu: 678154

Best:: JoinMethod: Hash

Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27

. . .



***********************

Join order[2]: EMPLOYEES[E]#1 DEPARTMENTS[D]#0

. . .



***************

Now joining: DEPARTMENTS[D]#0

***************

. . .

HA Join

HA cost: 4.58

resc: 4.58 resc_io: 4.00 resc_cpu: 690054

resp: 4.58 resp_io: 4.00 resp_cpu: 690054

Join order aborted: cost > best plan cost

***********************

跟踪文件显示，优化器首先尝试将“departments”表作为连接中的外表。

优化器计算三种不同连接方法的成本：嵌套循环连接（NL）、排序合并（SM）和哈希连接（HA）。

优化器选择哈希连接作为最有效的方法：

Best:: JoinMethod: Hash

Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27

补充10053跟踪事件

oradebug setmypid

alter session set events '10053 trace name context forever ,level 12';

SELECT e.employee_id, d.department_name

FROM hr.employees e

JOIN hr.departments d ON e.department_id = d.department_id;

alter session set events '10053 trace name context off';

oradebug close_trace

oradebug tracefile_name

然后，优化器尝试使用不同的连接顺序，将employees表作为外表。

这种连接顺序的成本比之前的连接顺序更高，因此被放弃。

优化器使用一个内部截止值，以减少在寻找成本最低的计划时尝试的计划数量。

该截止值基于当前最优计划的成本。

如果当前最优成本较高，那么优化器会探索其他替代计划，以找到成本更低的计划。

如果当前最优成本较低，那么优化器会迅速结束搜索，因为进一步降低成本的意义不大。

05

三大组件协作机制全图解

1）查询转换器 → 多个等价SQL变体

2）估算器 → 每个变体计算代价

3）计划生成器 → 从中选最优计划

它们就像是一支高效的三人小组：
重写、评估、决策，环环相扣。

而最终生成的“执行计划”，才是真正交给数据库执行的“落地方案”。

写在最后

优化器不懂业务，但比你更懂数据！

Oracle 优化器的设计，是“经验 + 统计 + 枚举”的典范。通过查询转换、成本估算、计划筛选的三步走，它让 SQL 在复杂的数据库结构中也能“跑得又稳又快”。

如果你是 DBA、开发、调优工程师——学会与优化器对话，将是你性能突破的关键！