DB / Oracle / ACE 精选

Oracle AskTOM 绑定变量深度解析

2026.09.06

Oracle AskTOM 绑定变量深度解析

来源：AskTOM (asktom.oracle.com) 适用版本：Oracle Database 全版本 文档版本：v1.0 / 2026-07-22

1. 概述

绑定变量是 Tom Kyte 在 AskTOM 反复强调的核心主题[1]。

详细见：Oracle 绑定变量窥探详解。

2. 什么是绑定变量

2.1 字面量 SQL

SELECT * FROM emp WHERE id = 1;
SELECT * FROM emp WHERE id = 2;
SELECT * FROM emp WHERE id = 3;

2.2 绑定变量 SQL

SELECT * FROM emp WHERE id = :1;

2.3 区别

- 字面量：每次都不同 → 硬解析
- 绑定变量：相同 → 软解析

3. 解析机制

3.1 硬解析

1. 语法检查
2. 语义检查
3. 优化器生成执行计划
4. 编译
5. 缓存到 Library Cache

3.2 软解析

1. 计算 hash
2. 查找 Library Cache
3. 找到 → 复用
4. 执行

3.3 性能差距

- 硬解析：CPU 密集
- 软解析：轻量
- 差距：100-1000 倍

4. Tom Kyte 经典示例

4.1 未使用绑定变量

CREATE OR REPLACE PROCEDURE bad_example IS
  v_cnt NUMBER;
BEGIN
  FOR i IN 1..10000 LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM emp WHERE id=' || i 
      INTO v_cnt;
  END LOOP;
END;
/

4.2 使用绑定变量

CREATE OR REPLACE PROCEDURE good_example IS
  v_cnt NUMBER;
BEGIN
  FOR i IN 1..10000 LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM emp WHERE id=:1' 
      INTO v_cnt USING i;
  END LOOP;
END;
/

4.3 性能对比

- bad_example: 10+ 秒
- good_example: 0.5 秒
- 差距 20 倍

5. Library Cache 影响

5.1 未使用绑定变量

- Library Cache 充满
- 大量子游标
- 共享池压力
- ORA-04031 风险

5.2 使用绑定变量

- 一个父游标 + 子游标
- 共享池高效
- 性能稳定

5.3 查看解析

SELECT sql_text, executions, parse_calls 
FROM v$sql 
WHERE sql_text LIKE 'SELECT COUNT(*) FROM emp%' 
ORDER BY sql_text;

6. 何时使用绑定变量

6.1 OLTP

- 必须使用
- 高并发
- 性能关键

6.2 数据仓库

- 不一定
- 全表扫描多
- 字面量更好（CBO 信息更准）

6.3 报表

- 评估
- 长查询
- 字面量可接受

7. 绑定变量窥探

7.1 11g 之前

- 第一次窥探
- 后续复用
- 数据倾斜时问题

7.2 11g ACS

- Adaptive Cursor Sharing
- 自适应游标
- 多个执行计划

详细见：Oracle 自适应游标共享详解。

8. 强制绑定变量

8.1 CURSOR_SHARING

ALTER SYSTEM SET cursor_sharing=FORCE;

8.2 注意

- 临时方案
- 不如代码改
- 有副作用
- 监控

8.3 EXACT vs FORCE

- EXACT：默认，字面量
- FORCE：自动替换
- SIMILAR：11g 弃用

9. 监控

9.1 解析率

SELECT name, value 
FROM v$sysstat 
WHERE name IN ('parse count (hard)', 'parse count (total)');
-- hard/total < 5% 为佳

9.2 字面量 SQL

SELECT sql_text, executions 
FROM v$sql 
WHERE parse_calls > executions * 0.9 
  AND executions < 5
ORDER BY sql_text;

9.3 共享池

SELECT * FROM v$sgastat WHERE pool='shared pool' AND name='free memory';

10. 绑定变量捕获

10.1 查看绑定

SELECT sql_id, name, value_string 
FROM v$sql_bind_capture 
WHERE sql_id = 'abc123';

10.2 历史

SELECT * FROM dba_hist_sqlbind WHERE sql_id = 'abc123';

11. Java/JDBC

11.1 PreparedStatement

PreparedStatement ps = conn.prepareStatement(
  "SELECT * FROM emp WHERE id=?");
ps.setInt(1, 100);
ResultSet rs = ps.executeQuery();

11.2 错误

// 拼接字符串 → 字面量
String sql = "SELECT * FROM emp WHERE id=" + id;
Statement s = conn.createStatement();

12. Python

12.1 oracledb

import oracledb
cursor = conn.cursor()
cursor.execute("SELECT * FROM emp WHERE id=:1", (100,))

12.2 错误

# 拼接 → SQL 注入 + 字面量
cursor.execute(f"SELECT * FROM emp WHERE id={id}")

13. 常见问题

13.1 数据倾斜

- 绑定变量窥探
- 执行计划不准
- ACS

13.2 字面量更好场景

- 数据仓库
- 全表扫描决策
- 评估

13.3 ORA-04031

- 字面量过多
- 共享池耗尽
- 改绑定变量

14. Tom Kyte 名言

"Bind variables are not optional, they are mandatory"
"If you are not using bind variables, you are broken"

15. 最佳实践

  1. OLTP：必用
  2. 应用代码：PreparedStatement
  3. 监控：解析率
  4. CURSOR_SHARING：临时
  5. ACS：数据倾斜
  6. 测试：性能
  7. 代码审查：禁止拼接
  8. 文档：规范
  9. 培训：开发
  10. 优化：持续

16. 参考资料

[1] AskTOM, “Bind Variables”, https://asktom.oracle.com [2] Tom Kyte, “Expert Oracle Database Architecture”, Chapter 5