SQL优化与BDTA_SIZE参数解析

概要说明

本文基于 500 万行数据量的测试场景，针对无索引关联查询的高逻辑读问题展开调优。重点阐述如何通过建立联合索引实现全表扫描向索引定位的转化，并从数据库执行引擎控制流的角度，深入剖析哈希连接（HASH JOIN）算子中出现的 ->256 实际输出行数现象及其对应的系统参数 BDTA_SIZE 运行机制。

1. 测试建表和查询语句优化问题

1.1 环境建表与测试 SQL

执行以下 SQL 创建测试表 T1 并生成 500 万条测试数据：

CREATE TABLE T1 AS 
SELECT LEVEL V1, DBMS_RANDOM.STRING('x', 20) V2 
FROM DUAL CONNECT BY LEVEL <= 5000000;

初始查询语句如下：

SELECT * 
FROM T1 A, T1 B 
WHERE A.V1 = B.V1 
  AND B.V2 = '1';

1.2 诊断工具开启

在 DIsql 会话中开启 AUTOTRACE 跟踪与会话级 SQL 执行监控，用于获取实际执行计划与节点处理行数：

-- 开启 SQL 执行计划与资源统计跟踪
SET AUTOTRACE TRACE;

-- 开启会话级 SQL 执行节点实际行数监控
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);

1.3 初始耗时与执行计划分析

在无索引状态下执行该查询，执行计划及运行统计如下：

未选定行

1   #NSET2: [1571, 374250->0, 104]
2     #PRJT2: [1571, 374250->0, 104]; exp_num(4), is_atom(FALSE)
3       #HASH2 INNER JOIN: [1571, 374250->0, 104];
          KEY_NUM(1), MEM_USED(0KB), DISK_USED(0KB)
          KEY(B.V1=A.V1) KEY_NULL_EQU(0)
4         #SLCT2: [625, 125000->0, 52]; B.V2 = '1'
5           #CSCN2: [625, 5000000->5000000, 52];
            INDEX33555799(T1); btr_scan(1)
6         #CSCN2: [577, 5000000->256, 52];
          INDEX33555799(T1); btr_scan(1)

Statistics
-----------------------------------------------------------------
        0           data pages changed
        0           undo pages changed
        30499       logical reads
        0           physical reads
        0           redo size
        276         bytes sent to client
        119         bytes received from client
        1           roundtrips to/from client
        0           sorts (memory)
        0           sorts (disk)
        0           rows processed
        0           io wait time(ms)
        385         exec time(ms)

已用时间: 385.882(毫秒). 执行号:1704.

执行特征说明：
当前执行计划走了全表扫描（CSCN2）加过滤（SLCT2）。由于没有索引支撑，数据库全表扫描了 5,000,000 行数据，产生 30,499 次逻辑读，耗时约 385 ms。虽然 B.V2='1' 过滤后实际输出 0 行导致最终结果为空，但全表扫描的开销已经产生。

2. 联合索引优化实战

2.1 优化思路与联合索引建立

分析该查询的访问条件：

过滤条件：B.V2='1' 等值过滤；
连接条件：B.V1 与 A.V1 进行关联。

建立联合索引 (V2, V1)：

CREATE INDEX IDX_T1_V2_V1 ON T1(V2, V1);

2.2 联合索引核心机制

最左匹配原则：
- 将等值过滤列 V2 作为前导列，使优化器能够通过索引树直接检索定位 V2='1' 的范围。
- 若建立为 (V1, V2) 索引，因缺乏 V1 的限制条件，无法进行高效的范围扫描。
避免回表（覆盖索引）：
- 将连接所需字段 V1 纳入索引第二列。
- 过滤侧直接从二级索引页面获取 V1 键值，无需通过 ROWID 回表读取主表数据页，消除回表 I/O。

2.3 优化后执行计划与性能对比

建立联合索引后重新执行查询，执行计划及统计信息如下：

未选定行

1   #NSET2: [963, 374250->0, 104]
2     #PRJT2: [963, 374250->0, 104]; exp_num(4), is_atom(FALSE)
3       #HASH2 INNER JOIN: [963, 374250->0, 104];
          KEY_NUM(1), MEM_USED(0KB), DISK_USED(0KB)
          KEY(B.V1=A.V1) KEY_NULL_EQU(0)
4         #SSEK2: [17, 125000->0, 52];
          scan_type(ASC), IDX_T1_V2_V1(T1), is_global(0),
          scan_range[('1',min),('1',max))
5         #SSCN: [577, 5000000->1000, 52];
          IDX_T1_V2_V1(T1); btr_scan(1); is_global(0)

Statistics
-----------------------------------------------------------------
        0           data pages changed
        0           undo pages changed
        93          logical reads
        0           physical reads
        0           redo size
        276         bytes sent to client
        119         bytes received from client
        1           roundtrips to/from client
        0           sorts (memory)
        0           sorts (disk)
        0           rows processed
        0           io wait time(ms)
        3           exec time(ms)

已用时间: 3.511(毫秒). 执行号:1707.

优化前后性能对比：

阶段	访问路径	逻辑读	服务端耗时	最终输出行数
优化前	`CSCN2` + `SLCT2`	30,499 次	385 ms	0
优化后	`SSEK2`	93 次	3 ms	0

全表扫描替换为二级索引定位（SSEK2），逻辑读下降 99.7%，执行耗时由 385 ms 降至 3 ms。

3. 关于执行计划的哈希连接左右算子

3.1 右侧算子的 256 行现象

执行计划第 6 行显示：

6   #CSCN2: [577, 5000000->256, 52]; INDEX33555799(T1); btr_scan(1)

其中 5000000->256 表示算子在本次执行中实际向上交付了 256 行。该数值与系统参数 BDTA_SIZE 有关（默认值为 256）。

3.2 按需驱动的执行控制流模型

数据库执行引擎采用自上而下的按需拉取（Volcano Iterator Model）控制流：

控制流：上层算子向下层发起取数请求；
数据流：下层算子处理后向上层传输数据。

哈希连接包含 Build 侧（左子树）与 Probe 侧（右子树）：

       #HASH2 INNER JOIN (算子3)
          /             \
    [Build 侧 (左)]   [Probe 侧 (右)]
     #SLCT2 (算子4)   #CSCN2 (算子6)
        |
     #CSCN2 (算子5)

3.3 右侧仅执行单批次的原因分析

Build 侧处理：算子 5 扫描 500 万行经 V2='1' 过滤，交付给 HASH JOIN 的实际行数为 0。
Probe 侧首次拉取：初始化时，算子 3 向右侧算子 6 发起取数请求。算子 6 批量扫描装满一个 BDTA 缓存批次（BDTA_SIZE 即 256 行）并向上交付。
逻辑短路终止：HASH JOIN 获取左侧 Build 数据发现为空（0 行）。因内连接中 0 ⋈ N = 0，引擎判定连接结果必定为空，终止向右侧算子发送后续批次的取数请求。

因此，右侧扫描算子仅执行了一次批处理，实际输出精准止于 256 行。

4. 关于 BDTA_SIZE

4.1 BDTA_SIZE 参数

BDTA_SIZE（Batch DaTA Size）为数据库内部控制批处理数据传输架构（Batch Data Transport Architecture）中单个数据批次最大容纳记录数的静态系统参数，系统默认值为 256，取值范围为 1~10000。

扫描算子终止机制：
- 扫描算子（如 CSCN、SSCN、CSEK、SSEK）在填充当前 BDTA 批次时，若装载记录数达到 BDTA_SIZE，或连续扫描数据页数达到 MAX_SCAN_PAGES，即结束本轮批次装载并向上层交付数据。
- 单轮交接结束不代表物理全表扫描终止，上层算子若继续发出 Fetch 请求，下层算子将继续产生后续批次，直至扫描完毕或上层主动终止。
执行计划数值含义：执行计划右侧箭头记录该算子在整条 SQL 执行周期内累计向上层交付的总行数。在本案例中，HASH JOIN 左侧 Build 输出为 0 行，因此，右侧仅被请求并装载了第 1 个 BDTA 批次（256 行），故累计输出精准记录为 ->256。

4.2 参数验证与调整

可通过动态性能视图 V$PARAMETER 查询当前参数属性：

SELECT NAME, TYPE, VALUE, SYS_VALUE, FILE_VALUE, DEFAULT_VALUE, ISDEFAULT
FROM V$PARAMETER
WHERE NAME IN ('BDTA_SIZE', 'MAX_SCAN_PAGES');

修改静态参数 BDTA_SIZE 为 99：

-- 修改配置文件 dm.ini（SCOPE=2 表示仅写入 INI 文件，需重启实例生效）
SP_SET_PARA_VALUE(2, 'BDTA_SIZE', 99);

重启数据库实例后再次执行目标 SQL，在右侧扫描能够装满一个批次的条件下，执行计划右侧算子的实际累计输出行数将更变为 ->99：

6   #CSCN2: [577, 5000000->99, 52]

单批次实际输出行数受 BDTA_SIZE、MAX_SCAN_PAGES、数据选择性与上层控制逻辑共同决定，验证的目标在于确认执行器批处理交接粒度符合预期设定。

4.3 参数值分析与建议

增大 BDTA_SIZE 的影响：
- 吞吐：单次传输记录数增多，摊薄算子进入、状态切换及函数调用的管理开销，提升大批量扫描与吞吐型 SQL 的处理效率。
- 资源开销：增加单个 BDTA 容器的内存占用；在高并发场景下可能放大系统总内存消耗。对于只需少量数据的查询，可能产生预读未消费数据的无效消耗。
减小 BDTA_SIZE 的影响：
- 响应收益：降低单批次内存开销，提高首批数据的响应速度。
- 吞吐代价：增加算子间的进入频次与数据封装开销，对大吞吐量扫描 SQL 的整体执行效率存在负面影响。
调优建议：BDTA_SIZE 属于运行期数据传输粒度控制参数，而非 CBO 优化器成本估算参数。修改该参数不会改变优化器的执行计划选型（如驱动 Nest-Loop 或切换索引）。不建议为了使执行计划显示特定数值而盲目调整。

4.4 相似 BDTA 参数辨析

相似 BDTA 参数辨析：
- 服务器执行参数 BDTA_SIZE：控制执行器内核中算子间数据交接的批记录数上限（本案例探讨的参数）。
- 快速装载参数 FLDR_ATTR_BDTA_SIZE：控制快速装载工具（FLDR）数据装载的批次大小，作用于装载客户端工具层。
- 结果集传输参数 RS_BDTA_FLAG / RS_BDTA_BUF_SIZE：控制服务器是否以 BDTA 格式向客户端网络驱动打包传输结果集，作用于网络通信传输层。
- 传输压缩参数 BDTA_PACKAGE_COMPRESS：控制网络传输中 BDTA 数据包的压缩状态。
官方参考文档依据：
- 《系统管理员手册》：BDTA_SIZE 静态参数定义（取值范围 1~10000）、MAX_SCAN_PAGES 与扫描算子批次终止条件、V$PARAMETER 参数字段说明。
- 《程序员手册》：BDTA（Batch DaTA）批量数据处理机制的概念阐述。
- 《SQL 语言使用手册》：系统过程 SP_SET_PARA_VALUE 的 SCOPE 参数取值说明。