SQL 语句执行过程与硬软解析详解
SQL 语句执行过程与硬软解析详解
适用版本：Oracle Database 19c / 23ai 阅读基础：了解 SGA / PGA / Shared Pool 内存结构 文档版本：v1.0 / 2026-07
目录
- 1. 概述：为什么理解 SQL 执行过程是调优基础
- 2. SQL 执行的五大阶段
- 3. 解析阶段（Parse）详解
- 4. 优化阶段（Optimize）详解
- 5. 行源生成器（Row Source Generator）
- 6. 执行阶段（Execute）与获取阶段（Fetch）
- 7. 共享游标与子游标
- 8. 绑定变量与游标共享
- 9. 绑定变量窥探与自适应游标共享
- 10. 监控与诊断
- 11. 常见坑与最佳实践
- 12. 参考资料
1. 概述：为什么理解 SQL 执行过程是调优基础
Oracle 中一条 SQL 语句从用户提交到返回结果，会经历五大阶段：解析（Parse）、优化（Optimize）、行源生成（Row Source Generation）、执行（Execute）、获取（Fetch）[1][3]。
理解这一流程对调优至关重要，因为：
- 硬解析是最昂贵的操作：消耗大量 CPU 和 Shared Pool 内存
- 优化器决策决定执行效率：差的执行计划可能让 SQL 慢 1000 倍
- 应用代码影响极大：未用绑定变量、写法不当都会引发性能问题
- 诊断必备：知道每个阶段对应的 trace 与视图，才能快速定位问题
2. SQL 执行的五大阶段
┌────────────────────────────────────────────────────────────┐
│ SQL Statement Execution │
├────────────────────────────────────────────────────────────┤
│ 1. Parse（解析） │
│ ├─ 语法检查 Syntax Check │
│ ├─ 语义检查 Semantic Check │
│ └─ 共享池检查 Shared Pool Check → 软/硬解析 │
├────────────────────────────────────────────────────────────┤
│ 2. Optimize（优化）— 仅硬解析时执行 │
│ ├─ 查询转换器 Query Transformer │
│ ├─ 估算器 Estimator（统计信息 → 成本） │
│ └─ 计划生成器 Plan Generator（多计划择优） │
├────────────────────────────────────────────────────────────┤
│ 3. Row Source Generation（行源生成） │
│ └─ 把执行计划转为可执行的行源迭代器 │
├────────────────────────────────────────────────────────────┤
│ 4. Execute（执行） │
│ ├─ DML：实际修改数据 │
│ └─ SELECT：打开 cursor，准备获取 │
├────────────────────────────────────────────────────────────┤
│ 5. Fetch（获取）— 仅 SELECT │
│ └─ 按需返回结果集 │
└────────────────────────────────────────────────────────────┘关键区别：DDL 永远是硬解析，DML 才有软解析可能。
3. 解析阶段（Parse）详解
3.1 语法检查 Syntax Check
检查 SQL 文本是否符合语法规范[1][3]。
-- 错误示例（FROM 拼成 FORM）
SQL> SELECT * FORM emp;
ERROR at line 1:
ORA-00923: FROM keyword not found where expected3.2 语义检查 Semantic Check
检查 SQL 引用的对象是否存在、用户是否有权限、数据类型是否匹配[1]。
-- 错误示例（表不存在）
SQL> SELECT * FROM nonexistent_table;
ERROR at line 1:
ORA-00942: table or view does not exist
-- 错误示例（权限不足）
SQL> SELECT * FROM sys.dba_users;
ERROR at line 1:
ORA-01031: insufficient privileges3.3 共享池检查 Shared Pool Check
Oracle 计算 SQL 文本的 hash 值，在 Library Cache 中查找是否已存在相同 cursor[1][3]。
SQL 共享的条件（必须全部满足）：
| 条件 | 说明 |
|---|---|
| 文本完全一致 | 包括大小写、空格、换行 |
| 对象 schema 相同 | 引用对象的 owner 必须一致 |
| 绑定变量类型相近 | 名称和类型一致 |
| 优化器模式相同 | ALL_ROWS / FIRST_ROWS 等 |
| NLS 环境一致 | 字符集、日期格式、排序规则 |
| 对象定义未变更 | 表结构、索引未发生 DDL |
SQL 文本 Hash 计算：
SQL Text → Hash Function → Hash Value → 在 Library Cache 查找
│
┌───────────────────┴───────────────────┐
↓ ↓
命中 未命中
│ │
软解析 硬解析
│ │
复用执行计划 重新生成计划3.4 硬解析 vs 软解析 vs 软软解析
硬解析（Hard Parse）
触发条件：SQL 文本 hash 值在 Library Cache 中不存在。
内部流程：
- 在 Library Cache 中分配新的 cursor 内存（需获取
library cache latch） - 在 Shared Pool 中分配内存（需获取
shared pool latch） - 执行完整的 Parse → Optimize → Row Source Generation
- 把执行计划存入 Library Cache
开销：
- CPU：大量用于优化器计算
- Latch：library cache latch + shared pool latch
- Memory：分配新的 cursor 内存
- 等待：可能导致
library cache latch争用
软解析（Soft Parse）
触发条件：SQL 文本 hash 值在 Library Cache 中存在（父游标命中），但仍需检查权限、对象定义等。
内部流程：
- 计算 SQL hash → 命中 Library Cache 中的父游标
- 检查会话环境（NLS、权限、优化器模式）
- 找到匹配的子游标 → 复用执行计划
- 跳过 Optimize 阶段
- 仍需在 PGA 中创建 Private SQL Area
开销：远低于硬解析，但仍有 library cache latch 开销。
软软解析（Soft Soft Parse）
触发条件：会话已持有打开的 cursor，直接复用，无需重新查找 Library Cache。
实现：
// JDBC 应用层缓存 PreparedStatement
PreparedStatement ps = connection.prepareStatement("SELECT * FROM emp WHERE empno = ?");
// 多次执行同一 PreparedStatement，每次复用 cursor
ps.setInt(1, 7900); ps.executeQuery();
ps.setInt(1, 7901); ps.executeQuery();开销：最低，几乎为零。
三种解析对比
| 维度 | 硬解析 | 软解析 | 软软解析 |
|---|---|---|---|
| Library Cache 查找 | 是 | 是 | 否（cursor 已打开） |
| 优化器计算 | 是 | 否 | 否 |
| Latch 获取 | 多 | 少 | 几乎无 |
| CPU 开销 | 高 | 中 | 低 |
| 内存分配 | 新建 cursor | 复用 cursor | 复用 cursor |
| 目标 | 完全避免 | 减少 | 软软解析 |
生产目标：硬解析率 < 1%，软解析率 < 10%，软软解析率 > 90%。
-- 查看解析统计
SELECT name, value
FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)', 'parse count (failures)');
-- 计算硬解析率
SELECT
'硬解析率' AS metric,
ROUND(SUM(DECODE(name, 'parse count (hard)', value, 0)) /
SUM(DECODE(name, 'parse count (total)', value, 0)) * 100, 2) AS "PCT"
FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)');4. 优化阶段（Optimize）详解
优化器（Optimizer）是 SQL 执行的核心组件，输入是解析后的 SQL，输出是执行计划[1][4]。
4.1 查询转换器 Query Transformer
决定是否重写用户的 SQL 以生成更好的执行计划[1]。
常见转换：
| 转换 | 含义 |
|---|---|
| View Merging | 把视图展开为基表查询 |
| Subquery Unnesting | 子查询反嵌套，转为 JOIN |
| Predicate Pushing | 谓词推入到视图内部 |
| Query Rewrite with MV | 用物化视图重写 |
| OR Expansion | OR 条件展开为 UNION ALL |
| Join Elimination | 删除冗余 JOIN |
| DISTINCT Elimination | 移除冗余 DISTINCT |
示例：
-- 原始 SQL
SELECT * FROM (
SELECT empno, ename FROM emp WHERE deptno = 10
) WHERE ename LIKE 'S%';
-- 视图合并后
SELECT empno, ename FROM emp
WHERE deptno = 10 AND ename LIKE 'S%';
-- 谓词推入：WHERE 条件推入到子查询4.2 估算器 Estimator
使用统计信息估算每个操作的选择率（Selectivity）、基数（Cardinality）和成本（Cost）[4]。
三大估算维度：
Cardinality（基数）= Selectivity（选择率） × Num_Rows（总行数）
例：
emp 表 100 万行
WHERE deptno = 10（deptno 有 10 个不同值，无直方图）
Selectivity = 1/10 = 10%
Cardinality = 100 万 × 10% = 10 万统计信息来源[4]：
| 类型 | 包含字段 | 来源 |
|---|---|---|
| 表统计 | NUM_ROWS, BLOCKS, AVG_ROW_LEN | DBMS_STATS.GATHER_TABLE_STATS |
| 列统计 | NUM_DISTINCT, DENSITY, LOW_VALUE, HIGH_VALUE | DBMS_STATS.GATHER_TABLE_STATS |
| 直方图 | Frequency / Height Balanced / Top-Frequency | DBMS_STATS（METHOD_OPT） |
| 索引统计 | BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR | DBMS_STATS（CASCADE=TRUE） |
| 系统统计 | CPUSPEEDNW, IOTFRSPEED, IOSEEKTIM, MBRC | DBMS_STATS.GATHER_SYSTEM_STATS |
成本计算公式（简化版）：
Cost = IO Cost + CPU Cost
IO Cost = (单块读次数 × sreadtim + 多块读次数 × mreadtim) / sreadtim
CPU Cost = (CPU 周期数 / CPUSPEED) / (sreadtim × 1000)4.3 计划生成器 Plan Generator
生成多种可能的执行计划，由 Estimator 估算成本，选择成本最低的[1]。
优化器模式：
| 模式 | 目标 |
|---|---|
ALL_ROWS（默认） | 最小化总成本（吞吐量优先） |
FIRST_ROWS_n | 最小化返回前 n 行的成本（响应时间优先） |
FIRST_ROWS | 旧版兼容 |
-- 查看当前模式
SHOW PARAMETER optimizer_mode
-- 修改模式
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;坑 1：FIRST_ROWS 模式可能让 CBO 选择索引扫描而非全表扫描，即使全表扫描总成本更低。适合 OLTP，不适合批量。
5. 行源生成器（Row Source Generator）
接收优化器生成的执行计划，转换为可执行的行源（Row Source）[1]。
行源：可迭代的数据结构，每次返回一行（或一批行）。
执行计划：
HASH JOIN
TABLE ACCESS FULL emp
TABLE ACCESS FULL dept
转换为：
HashJoinRowSource
├─ FullScanRowSource(emp)
└─ FullScanRowSource(dept)执行引擎按迭代方式调用每个 RowSource 的 next() 方法获取数据。
6. 执行阶段（Execute）与获取阶段（Fetch）
6.1 Execute（执行）
对于 DML：实际执行数据修改，生成 redo / undo。
对于 SELECT：仅打开 cursor，准备数据访问，实际数据读取在 Fetch 阶段。
6.2 Fetch（获取）
按需读取结果集行，通常分批（默认每次 100 行，可配置）。
-- JDBC 设置 fetch size
PreparedStatement ps = conn.prepareStatement("SELECT * FROM large_table");
ps.setFetchSize(1000); -- 每次从数据库获取 1000 行坑 2：fetch size 过小会导致网络往返频繁，影响性能。OLTP 通常 100-500，批处理 1000-10000。
7. 共享游标与子游标
7.1 父游标与子游标
父游标（Parent Cursor）：
- 通过 SQL 文本 hash 值定位
- 存储 SQL 文本本身和 hash value
- 同一 SQL 文本只有一个父游标
子游标（Child Cursor）：
- 父游标下可以有多个子游标
- 每个子游标对应一个具体的执行计划
- 不同环境（schema、优化器模式、对象版本）会产生不同子游标
SQL Text: "SELECT * FROM emp WHERE empno = :1"
↓ Hash
Parent Cursor (hash_value=12345)
├─ Child Cursor 0 (SCOTT 用户, ALL_ROWS, 执行计划 A)
├─ Child Cursor 1 (HR 用户, ALL_ROWS, 执行计划 B) -- 不同 schema
└─ Child Cursor 2 (SCOTT 用户, FIRST_ROWS, 执行计划 C) -- 不同模式7.2 查看游标
-- 查看 SQL 与父游标
SELECT sql_id, hash_value, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT * FROM emp%';
-- 查看子游标
SELECT sql_id, child_number, plan_hash_value, executions, optimizer_mode
FROM v$sql
WHERE sql_id = '&sql_id';
-- 查看子游标为何无法共享
SELECT child_number, reason
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id';V$SQL_SHARED_CURSOR 中常见 reason：
| Reason | 含义 |
|---|---|
AUTH_CHECK_MISMATCH | 用户权限不同 |
OPTIMIZER_MISMATCH | 优化器模式不同 |
LANGUAGE_MISMATCH | NLS 环境不同 |
TRANSLATION_MISMATCH | 对象 base schema 不同 |
BIND_MISMATCH | 绑定变量类型不同 |
STATS_ROW_MISMATCH | 统计信息变化 |
INCOMP_RBO_MISMATCH | RBO/CBO 模式不同 |
8. 绑定变量与游标共享
8.1 字面值 SQL 的问题
-- 每条都是独立的 cursor
SELECT * FROM emp WHERE empno = 7900;
SELECT * FROM emp WHERE empno = 7901;
SELECT * FROM emp WHERE empno = 7902;
-- 1 万条不同 empno → 1 万个硬解析问题：
- Library Cache 充满相似 cursor
- 每条都触发硬解析，CPU 飙高
- shared pool latch 争用严重
8.2 绑定变量解决方案
-- 使用绑定变量
SELECT * FROM emp WHERE empno = :1;
-- 1 万次不同 empno → 1 个 cursor，1 次硬解析8.3 强制游标共享（cursor_sharing）
当无法改代码时，可强制字面值替换为系统绑定变量：
-- FORCE 模式（替换所有字面值）
ALTER SYSTEM SET cursor_sharing = FORCE;
-- EXACT 模式（默认，不替换）
ALTER SYSTEM SET cursor_sharing = EXACT;
-- SIMILAR 模式（11g 前，按选择性决定）
ALTER SYSTEM SET cursor_sharing = SIMILAR;坑 3：CURSOR_SHARING = FORCE 会破坏绑定变量窥探，可能导致差的执行计划。仅作为临时方案，根本解决还是改代码用绑定变量。
9. 绑定变量窥探与自适应游标共享
9.1 绑定变量窥探（Bind Peeking）
11g 引入，优化器在硬解析时”偷看”绑定变量的实际值，用于生成更精确的执行计划[1]。
问题：
-- 假设 emp 表 status 列：
-- 90% 数据 status='ACTIVE'，10% 其他
-- 有索引 idx_status
-- 第一次执行（窥探到 status='INACTIVE'）
SELECT * FROM emp WHERE status = :1;
-- CBO 选择索引扫描（INACTIVE 选择性好）
-- 第二次执行（status='ACTIVE'）
-- 由于是软解析，复用第一次的执行计划
-- 但 ACTIVE 选择性差，索引扫描反而慢！9.2 自适应游标共享（ACS, Adaptive Cursor Sharing）
11g 引入，解决绑定变量窥探的问题[1]。
机制：
- 第一次执行时正常窥探，生成执行计划 A
- 后续执行监控实际返回行数
- 如果发现选择性差异大，生成新的子游标（执行计划 B）
- 后续根据绑定变量值选择对应的子游标
SELECT * FROM emp WHERE status = :1
父游标
├─ Child 0（status='INACTIVE' 时使用，索引扫描）
├─ Child 1（status='ACTIVE' 时使用，全表扫描）
└─ Child 2（其他值时使用）查看 ACS 状态：
SELECT sql_id, child_number, bind_set_hash_value, executions, buffer_gets
FROM v$sql_cs_statistics
WHERE sql_id = '&sql_id';
SELECT sql_id, child_number, predicate_range
FROM v$sql_cs_selectivity
WHERE sql_id = '&sql_id';9.3 何时禁用 ACS
-- ACS 在某些场景可能产生过多子游标（cursor explosion）
-- 通过 _optim_peek_user_binds 隐藏参数控制
ALTER SESSION SET "_optim_peek_user_binds" = FALSE;坑 4：禁用绑定变量窥探会让 CBO 完全依赖默认选择率（1/NUM_DISTINCT），对倾斜数据列可能产生差的执行计划。
10. 监控与诊断
10.1 解析统计
-- 系统级解析统计
SELECT name, value
FROM v$sysstat
WHERE name LIKE 'parse%';
-- 会话级
SELECT name, value
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name LIKE 'parse%'
AND s.sid = &sid;10.2 查看硬解析 SQL
-- 找出执行次数少但版本多的 SQL（典型未用绑定变量）
SELECT sql_text, COUNT(*) AS versions, SUM(executions) AS total_execs
FROM v$sql
GROUP BY sql_text
HAVING COUNT(*) > 10
ORDER BY versions DESC
FETCH FIRST 20 ROWS ONLY;10.3 Library Cache 命中率
SELECT namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY');目标：
gethitratio> 95%（SQL AREA）pinhitratio> 99%
10.4 查看 cursor 无法共享的原因
SELECT sql_id, child_number,
LISTAGG(reason, ', ') WITHIN GROUP (ORDER BY reason) AS reasons
FROM (
SELECT sql_id, child_number,
CASE WHEN auth_check_mismatch = 'Y' THEN 'AUTH_CHECK' END AS reason
FROM v$sql_shared_cursor
-- 实际有 60+ 个 reason 列
)
GROUP BY sql_id, child_number;10.5 SQL Trace（10046 事件）
-- 启用本会话 SQL Trace，level 12 含绑定变量和等待
ALTER SESSION SET EVENTS '10053 trace name context forever, level 12';
-- 执行 SQL
SELECT * FROM emp WHERE empno = 7900;
-- 关闭
ALTER SESSION SET EVENTS '10046 trace name context off';
-- 查找 trace 文件
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';10046 Level 含义：
| Level | 内容 |
|---|---|
| 1 | 标准 SQL Trace（执行、解析、fetch 统计） |
| 4 | Level 1 + 绑定变量值 |
| 8 | Level 1 + 等待事件 |
| 12 | Level 1 + 4 + 8（推荐） |
10.6 10053 事件（优化器跟踪）
-- 启用 10053 跟踪
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
-- 执行 SQL（必须是硬解析才会被跟踪）
SELECT * FROM emp WHERE empno = 7900;
ALTER SESSION SET EVENTS '10053 trace name context off';10053 trace 内容[2]：
- 优化器参数
- SQL 文本和重写
- 基本统计信息（表、索引、列）
- 各访问路径的成本计算
- 各连接方式的成本计算
- 最终选择的执行计划
适用场景：分析”为什么 CBO 选了错的执行计划”[2]。
11. 常见坑与最佳实践
坑 1：未用绑定变量导致 Library Cache 撑爆
现象：v$librarycache 的 SQL AREA gethitratio < 90%，shared pool latch 争用严重，CPU 高。
诊断：
SELECT COUNT(DISTINCT sql_text) AS distinct_sqls, COUNT(*) AS total_cursors
FROM v$sql;
-- 如果 ratio 远小于 1，说明有大量相似 SQL解决：
- 改代码用绑定变量（首选）
- 临时方案：
ALTER SYSTEM SET cursor_sharing = FORCE - 使用 session_cached_cursors 增加软软解析
坑 2：cursor_sharing = FORCE 引发 ACS 失效
现象：开启 FORCE 后，倾斜列查询性能不稳定。
解决：
- 评估是否真的需要 FORCE
- 关键倾斜列的 SQL 不要受影响（用 hint 或 outline）
坑 3：DDL 频繁导致 cursor 失效
现象：每次 ALTER TABLE 后所有相关 SQL 都重新硬解析。
解决：
- 生产环境避免高峰期 DDL
- 大量 DDL 用 DBMS_LOCK 锁住再批量执行
坑 4：fetch size 过小
现象：JDBC 默认 fetch size = 10，每次往返 10 行，导致网络成为瓶颈。
解决：
// JDBC 设置较大 fetch size
((OraclePreparedStatement)ps).setFetchSize(500);
((OraclePreparedStatement)ps).setRowPrefetch(500);坑 5：会话打开过多 cursor
现象：ORA-01000: maximum open cursors exceeded
解决：
-- 查看 open_cursors 限制
SHOW PARAMETER open_cursors
-- 增大限制
ALTER SYSTEM SET open_cursors = 1000;
-- 查看会话实际打开的 cursor
SELECT sid, COUNT(*) AS open_cursors
FROM v$open_cursor
GROUP BY sid
ORDER BY open_cursors DESC;根本方案：应用层确保 PreparedStatement 关闭。
最佳实践总结
- 强制使用绑定变量：所有 OLTP 应用必用
- *避免 SELECT ：减少解析和传输开销
- 使用 session_cached_cursors：增加软软解析率
- 定期收集统计信息：CBO 决策依赖准确统计
- 倾斜列建直方图：让 CBO 知道数据分布
- 避免高峰期 DDL：减少 cursor 失效
- 应用层缓存 PreparedStatement：减少解析次数
- JDBC fetch size 适当调大：500-1000
- 监控硬解析率：< 1% 为健康
- 必要时用 SPM 固化执行计划：避免 CBO 选错
12. 参考资料
[1] Oracle Database 19c 概念文档，第 7 章 SQL Processing： https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/sql-processing.html
[2] Oracle Database 19c Performance Tuning Guide，Optimizer Trace： https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/
[3] naisiing，《一条 SQL 语句的执行流程（含优化器详解）》： https://blog.csdn.net/naisiing/article/details/141862998
[4] 墨天轮，《Oracle 调优之 Trace 方法及相关工具总结 02》： https://www.modb.pro/db/1777223226882625536
[5] 墨天轮，《使用 Oracle 10053 事件学习 Oracle 优化器怎样使用统计信息》： https://www.modb.pro/db/617111
[6] Oracle Database 19c SQL Tuning Guide： https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/
[7] AskTOM，Bind Variable Peeking 与 ACS： https://asktom.oracle.com
相关文章