DM8 优化器参数学习：从作用机制到调优验证

1. 优化器参数究竟改变了什么

DM 查询优化器采用基于代价的方法。它需要根据查询条件、统计信息以及可用访问路径等信息，估计不同方案的成本，再生成执行计划。优化器面对的选择可以是：某个条件先过滤还是后过滤，两张表采用哪一种连接方式，相关子查询如何处理，以及为了寻找计划需要探测多少种排列。

参数可能改变其中某个转换规则、候选算法或搜索限制。它通常不会改变业务数据，也不会自动补齐缺失索引或修复 SQL 的逻辑错误。调参之前，要先看清楚原计划慢在哪一步。

例如，大量外表记录逐条驱动内表索引查询，可能产生较多重复访问。此时可以检验“索引连接是否适合当前数据范围”；但如果查询只筛选出极少量外表记录，同一种索引连接又可能很合适。开关名称中包含“优化”，并不意味着打开后所有查询都会更快。

2. 五类常用参数怎么用

下面这些参数都可以在会话级动态调整。表中的默认值来自手册。

类别	参数	手册默认值	主要作用
优化器模式	`OPTIMIZER_MODE`	1	选择老优化器模式或新优化器模式
计划搜索	`OPTIMIZER_MAX_PERM`	7200	限制计划探测过程中的最大排列数
连接方式	`ENABLE_INDEX_JOIN`	1	是否允许索引连接
连接方式	`ENABLE_HASH_JOIN`	1	是否允许哈希连接
连接方式	`ENABLE_MERGE_JOIN`	1	是否允许归并连接
子查询转换	`ENABLE_RQ_TO_NONREF_SPL`	1	控制相关查询表达式的转换处理
绑定变量	`ENHANCE_BIND_PEEKING`	0	是否使用自适应绑定变量窥探

2.1 OPTIMIZER_MODE：改变优化器模式

0：老优化器模式，该模式采用后序遍历算法对关系树计算最优计划；

1：新优化器模式，该模式采用先序遍历算法对关系树计算最优计划，同时扩展了 NEST LOOP JOIN 等功能。

模式还可能影响其他规则是否生效。例如，手册说明 ADAPTIVE_NPLN_FLAG 所控制的自适应计划机制仅在 OPTIMIZER_MODE=1 时生效。

2.2 OPTIMIZER_MAX_PERM：平衡计划搜索时间与搜索范围

该参数控制计划探测过程中的最大排列数，手册取值范围为 1～4294967294。超过限制时，会减少相应排列，缩短探测计划的时间。

如果复杂查询花了很长时间才生成计划，可以关注这个参数。缩小搜索范围可能减少生成计划的时间，但也可能漏掉更合适的方案；增大限制则可能花更多时间搜索，最终执行却未必更快。

2.3 连接方式开关

ENABLE_INDEX_JOIN、ENABLE_HASH_JOIN 和 ENABLE_MERGE_JOIN 的取值均为：0 不允许，1 允许。

其中，索引连接利用连接条件在被连接一侧进行索引定位；哈希连接通过构建与探测哈希结构完成匹配；归并连接利用连接键的有序性进行匹配。不同方式的实际成本还与输入规模、排序需求、可用索引、内存和数据分布有关。

将 ENABLE_HASH_JOIN 设为 1，只是允许优化器考虑哈希连接，不等于最终计划必须选择它。同样，ENABLE_INDEX_JOIN=0 限制的是索引连接，不能解释成禁止整条 SQL 的所有索引扫描。

PHC_MODE_ENFORCE该参数能够约束连接方式，手册说明其部分取值会联动设置上述连接开关的内存值。例如取值 6 时，允许索引连接与哈希连接，并将归并连接开关置为 0。

2.4 ENABLE_RQ_TO_NONREF_SPL：观察相关子查询的重复工作

这个参数控制相关子查询的处理方式。它可以将平坦化处理改为逐行处理，具体取值如下。

值	手册说明
0	不启用该项优化
1	对查询项中出现的相关子查询表达式进行优化处理
2	对查询项和 WHERE 表达式中出现的相关子查询表达式进行优化处理
4	相关查询采用 SPL 方式去相关性后，可以作为单表过滤条件

例如，查询客户及其工单数量：

SELECT c.CUSTOMER_ID,
       (SELECT COUNT(*)
          FROM TICKET t
         WHERE t.CUSTOMER_ID = c.CUSTOMER_ID) AS TICKET_COUNT
FROM CUSTOMER c
WHERE c.CUSTOMER_ID BETWEEN 1 AND 100;

当外层只有少量客户，逐个客户定位工单可能合适；当外层扩大到大量客户，重复定位的成本可能增加。可以分别用少量客户和大量客户测试，再比较参数 0、1 对计划和耗时的影响。

同时检查 ENABLE_CHOOSE_BY_ESTIMATE_FLAG：它会根据估算行数决定是否进行对应的去相关性优化，部分取值还涉及连接结果的动态采样。只改变一个参数，却不记录这些关联条件，可能无法解释计划为什么没有变化。

2.5 ENHANCE_BIND_PEEKING：关注不同绑定值的数据分布

DM8 手册定义 0 为不使用自适应绑定变量窥探，1 为使用。

例如同一个状态列中，成功记录很多、失败记录很少。查询一个少见状态与查询一个常见状态，可能适合不同的访问路径。

3. 修改前先记录现场值与支持范围

先用下面的查询查看版本、参数值和 Hint 支持情况。

SELECT * FROM V$VERSION;

SELECT *
FROM V$DM_INI
WHERE PARA_NAME IN (
    'OPTIMIZER_MODE', 'OPTIMIZER_MAX_PERM',
    'ENABLE_INDEX_JOIN', 'ENABLE_HASH_JOIN', 'ENABLE_MERGE_JOIN',
    'PHC_MODE_ENFORCE', 'ENABLE_RQ_TO_NONREF_SPL',
    'ENABLE_CHOOSE_BY_ESTIMATE_FLAG', 'ENHANCE_BIND_PEEKING'
);

SELECT SF_GET_SESSION_PARA_VALUE('ENABLE_INDEX_JOIN') AS SESSION_INDEX_JOIN,
       SF_GET_SESSION_PARA_VALUE('PHC_MODE_ENFORCE') AS SESSION_PHC_MODE;

SELECT PARA_NAME, HINT_TYPE
FROM V$HINT_INI_INFO
WHERE PARA_NAME IN (
    'OPTIMIZER_MODE', 'OPTIMIZER_MAX_PERM',
    'ENABLE_INDEX_JOIN', 'ENABLE_HASH_JOIN', 'ENABLE_MERGE_JOIN',
    'ENABLE_RQ_TO_NONREF_SPL', 'ENHANCE_BIND_PEEKING'
);

V$DM_INI 用于检查实例暴露的参数信息；需要确认当前连接的有效会话值时，应使用 SF_GET_SESSION_PARA_VALUE。参数存在、参数允许会话修改、参数支持 Hint，是三个不同的检查项。

参数 Hint 分为分析阶段的 OPT 与运行阶段的 EXEC，并说明 EXEC 类型应放在最外层 SELECT。对没有在现场支持列表中确认的参数，不应直接套写 Hint。

4. 用 ENABLE_INDEX_JOIN 做一次对照实验

4.1 准备数据

下面用两张小表演示怎样对比参数。

CREATE TABLE BLOG_OPT_C_0920 (
    CUSTOMER_ID INT PRIMARY KEY,
    REGION_ID INT NOT NULL
);

CREATE TABLE BLOG_OPT_T_0920 (
    TICKET_ID INT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    AMOUNT DECIMAL(12,2) NOT NULL
);

CREATE INDEX IDX_BLOG_OPT_T_C_0920
ON BLOG_OPT_T_0920(CUSTOMER_ID);

INSERT INTO BLOG_OPT_C_0920 VALUES (1, 10);
INSERT INTO BLOG_OPT_C_0920 VALUES (2, 10);
INSERT INTO BLOG_OPT_C_0920 VALUES (3, 20);

INSERT INTO BLOG_OPT_T_0920 VALUES (101, 1, 100);
INSERT INTO BLOG_OPT_T_0920 VALUES (102, 1, 200);
INSERT INTO BLOG_OPT_T_0920 VALUES (201, 2, 300);
INSERT INTO BLOG_OPT_T_0920 VALUES (301, 3, 400);
COMMIT;

客户表有 3 行，工单表有 4 行。REGION_ID=10 对应客户 1、2：客户 1 有两张工单，金额为 100、200；客户 2 有一张工单，金额为 300。因此，按这组数据计算，连接后应有 3 条工单记录，分组后得到两行，两个客户的金额合计都是 300。

4.2 原 SQL：先找客户，再按客户编号找工单

SELECT c.CUSTOMER_ID, SUM(t.AMOUNT) AS TOTAL_AMOUNT
FROM BLOG_OPT_C_0920 c
JOIN BLOG_OPT_T_0920 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.REGION_ID = 10
GROUP BY c.CUSTOMER_ID
ORDER BY c.CUSTOMER_ID;

原 SQL 的执行计划如下：

1   #NSET2: [1, 1, 42]
2     #PRJT2: [1, 1, 42]; exp_num(2), is_atom(FALSE); INFO_BITS(0)
3       #SAGR2: [1, 1, 42]; grp_num(1), sfun_num(1), opt_num(0), distinct_flag[0]; slave_empty(0) keys(C.CUSTOMER_ID)
4         #NEST LOOP INDEX JOIN2: [1, 3, 42]
5           #SLCT2: [1, 1, 8]; C.REGION_ID = 10, slct_pushdown(0)
6             #BLKUP2: [1, 3, 8]; INDEX33555521(BLOG_OPT_C_0920); use_clu_addr(0)
7               #SSCN: [1, 3, 8]; INDEX33555521(BLOG_OPT_C_0920); btr_scan(1); is_global(0)
8           #BLKUP2: [1, 3, 4]; IDX_BLOG_OPT_T_C_0920(BLOG_OPT_T_0920); use_clu_addr(0)
9             #SSEK2: [1, 3, 4]; scan_type(ASC), IDX_BLOG_OPT_T_C_0920(BLOG_OPT_T_0920), is_global(0), scan_range[C.CUSTOMER_ID,C.CUSTOMER_ID]

阅读这份计划，可以从底层的数据来源向上看；遇到连接节点时，分别看它的两个分支。第 4 行 NEST LOOP INDEX JOIN2 是关键：左侧提供客户，右侧根据当前客户编号查工单。

先看左侧，第 7 行到第 5 行。第 7 行 SSCN 扫描客户表的二级索引 INDEX33555521，第 6 行 BLKUP2 再回表取数，第 5 行 SLCT2 判断 C.REGION_ID=10。

这里出现了索引扫描，但没有按 REGION_ID=10 直接定位的范围条件。按本例建表语句，REGION_ID 没有单独建索引，所以这条路径是先扫描、回表，再筛出符合区域条件的客户。

再看右侧，第 9 行到第 8 第 9 行 SSEK2 使用工单表的 IDX_BLOG_OPT_T_C_0920 索引，关键是：

scan_range[C.CUSTOMER_ID,C.CUSTOMER_ID]

它表示查找范围由当前客户编号决定。处理客户 1 时，在索引中定位 CUSTOMER_ID=1 的工单；处理客户 2 时，再定位 CUSTOMER_ID=2 的工单。

第 8 行 BLKUP2 表示工单侧还需要回表。本例二级索引只显式包含 CUSTOMER_ID，查询却要计算 SUM(t.AMOUNT)，因此还要取得工单金额。

最后看第 3 行。SAGR2是流分组，keys(C.CUSTOMER_ID) 表示按客户编号分组，sfun_num(1) 对应一个聚合函数，这里就是 SUM(t.AMOUNT)。它利用输入已有的分组顺序，把同一个客户的工单连续汇总。

这份计划没有单独的 SORT3。结合索引连接与 SAGR2，可以看出优化器利用了下层提供的顺序，完成分组后也满足 ORDER BY c.CUSTOMER_ID，所以没有另加一次排序。第 2 行 PRJT2 输出客户编号和汇总金额，第 1 行 NSET2 返回结果。

4.3 ENABLE_INDEX_JOIN(1)：允许索引连接，计划保持原样

SELECT /*+ ENABLE_INDEX_JOIN(1) */
       c.CUSTOMER_ID, SUM(t.AMOUNT) AS TOTAL_AMOUNT
FROM BLOG_OPT_C_0920 c
JOIN BLOG_OPT_T_0920 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.REGION_ID = 10
GROUP BY c.CUSTOMER_ID
ORDER BY c.CUSTOMER_ID;

对应计划如下：

1   #NSET2: [1, 1, 42]
2     #PRJT2: [1, 1, 42]; exp_num(2), is_atom(FALSE); INFO_BITS(0)
3       #SAGR2: [1, 1, 42]; grp_num(1), sfun_num(1), opt_num(0), distinct_flag[0]; slave_empty(0) keys(C.CUSTOMER_ID)
4         #NEST LOOP INDEX JOIN2: [1, 3, 42]
5           #SLCT2: [1, 1, 8]; C.REGION_ID = 10, slct_pushdown(0)
6             #BLKUP2: [1, 3, 8]; INDEX33555521(BLOG_OPT_C_0920); use_clu_addr(0)
7               #SSCN: [1, 3, 8]; INDEX33555521(BLOG_OPT_C_0920); btr_scan(1); is_global(0)
8           #BLKUP2: [1, 3, 4]; IDX_BLOG_OPT_T_C_0920(BLOG_OPT_T_0920); use_clu_addr(0)
9             #SSEK2: [1, 3, 4]; scan_type(ASC), IDX_BLOG_OPT_T_C_0920(BLOG_OPT_T_0920), is_global(0), scan_range[C.CUSTOMER_ID,C.CUSTOMER_ID]

这份计划与原 SQL 一致：仍然从客户表取得数据并过滤区域，再用客户编号到工单索引中定位、回表，最后由 SAGR2 汇总。

4.4 ENABLE_INDEX_JOIN(0)：改用哈希连接，并增加排序

SELECT /*+ ENABLE_INDEX_JOIN(0) */
       c.CUSTOMER_ID, SUM(t.AMOUNT) AS TOTAL_AMOUNT
FROM BLOG_OPT_C_0920 c
JOIN BLOG_OPT_T_0920 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.REGION_ID = 10
GROUP BY c.CUSTOMER_ID
ORDER BY c.CUSTOMER_ID;

对应计划如下：

1   #NSET2: [2, 1, 42]
2     #PRJT2: [2, 1, 42]; exp_num(2), is_atom(FALSE); INFO_BITS(0)
3       #SORT3: [2, 1, 42]; key_num(1), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(0KB), DISK_USED(0KB)
4         #HAGR2: [1, 1, 42]; grp_num(1), sfun_num(1), MEM_USED(0KB), DISK_USED(0KB), distinct_flag[0]; slave_empty(0) keys(C.CUSTOMER_ID)
5           #HASH2 INNER JOIN: [1, 1, 42]; LKEY_UNIQUE KEY_NUM(1), MEM_USED(0KB), DISK_USED(0KB) KEY(C.CUSTOMER_ID=T.CUSTOMER_ID) KEY_NULL_EQU(0)
6             #SLCT2: [1, 1, 8]; C.REGION_ID = 10, slct_pushdown(1)
7               #CSCN2: [1, 3, 8]; INDEX33555520(BLOG_OPT_C_0920); btr_scan(1); need_slct(1) prejudge_iescn(0)
8             #CSCN2: [1, 4, 34]; INDEX33555522(BLOG_OPT_T_0920); btr_scan(1); need_slct(0) prejudge_iescn(0)

第 7、8 行改为 CSCN2。客户表和工单表都采用聚集索引扫描。客户侧第 6 行保留 C.REGION_ID=10 条件，同时出现 slct_pushdown(1)，下层扫描也有 need_slct(1)，表明计划标记了过滤下推处理。

工单侧第 8 行直接扫描表数据，不再出现以 C.CUSTOMER_ID 为范围的 SSEK2，也不再有独立的 BLKUP2。与原计划相比，工单的读取方式从“按每个客户编号定位并回表”，变成“扫描工单数据后参与哈希匹配”。CSCN2 的名称中虽然有索引，它表示的是聚集索引扫描，并非前面那种通过二级索引逐客户查找的路径。

第 5 行变为 HASH2 INNER JOIN。过滤后的客户数据和工单数据按 C.CUSTOMER_ID=T.CUSTOMER_ID 做哈希内连接。KEY_NUM(1) 表示一个等值连接条件；LKEY_UNIQUE 表示左侧客户编号唯一，与客户表主键相符。

第 4 行变为 HAGR2。哈希连接的输出不能按客户编号有序来使用，因此这份计划采用哈希分组计算 SUM，不再使用原来的流分组。

第 3 行新增 SORT3。哈希分组不保证最终结果按客户编号排列，所以还需要排序来满足 ORDER BY c.CUSTOMER_ID。这里的排序位于分组之后，处理的是分组结果。

因此，设为 0 后的完整变化是：工单侧不再逐客户走二级索引定位，连接改为哈希连接，分组改为哈希分组，再对分组结果排序。

5. 怎样判断一次调参有没有价值

检查项目	记录什么	重点看什么
结果正确性	结果集合、重复次数、NULL 和聚合值	不能只比较总行数
计划变化	访问路径、连接顺序、连接算法、估算行数	原来开销大的步骤是否得到改善
运行开销	实际耗时、逻辑读或其他可用访问统计、内存及临时空间	主要耗时和资源消耗是否下降
输入覆盖	不同范围、不同绑定值、倾斜值	是否存在严重退化场景
稳定性	多轮结果及代表性并发负载	收益是否可重复，尾部延迟是否可接受

一轮执行更快而计划相同，通常不足以把收益归因于参数。计划改变但实际更慢，也不能因为 COST 更小就认定优化成功。只有结果正确、开销确实下降，而且常用业务场景没有明显变慢，才值得继续考虑。

如果明显存在估算偏差，应回到统计信息和数据分布检查；如果主要等待来自锁、存储或应用取数，调整优化器参数也未必是直接的解决方法。