如何在Oracle中利用预编译变量绑定提升SQL执行速度？

PreparedStatement能显著提升执行速度，因其复用执行计划避免硬解析；字符串拼接导致每次硬解析，消耗CPU和共享池内存；正确使用需显式指定参数类型、及时关闭资源，并注意Oracle游标限制。

直接用 preparedstatement 就能显著提升执行速度——前提是 sql 结构不变、变量值可变，且数据库能复用执行计划。硬解析（hard parse）才是真正的性能杀手，绑定变量不是“锦上添花”，而是避免重复解析的必要手段。

为什么不用字符串拼接，而必须用 PreparedStatement

字符串拼接（如 "SELECT * FROM users WHERE id = " + userId）会让 Oracle 把每条语句当作全新 SQL 处理，哪怕只差一个数字，也会触发一次 hard parse。这不仅吃 CPU，还占共享池内存，高并发下容易引发 latch 竞争。

• PreparedStatement 提前编译 SQL 结构，只传参数值，Oracle 能识别为同一语句
• 首次执行走 hard parse，后续全走 soft parse（验证权限+绑定类型，开销极低）
• JDBC 驱动会自动把 ? 映射为 Oracle 的 :1、:2 等内部绑定名，无需手动写 :id

PreparedStatement 的正确写法和常见错例

写对才能生效。错在细节，比如参数类型不匹配、未关闭资源、或误用动态拼接。

• ✅ 正确：用 setInt(1, 1001)、setString(2, "active") 显式指定类型，让 Oracle 准确推导绑定变量数据类型
• ❌ 错误：用 setObject(1, 1001) 或 setString(1, String.valueOf(1001))，可能造成隐式转换，导致执行计划失效（例如索引失效）
• ❌ 错误：在循环里反复 connection.prepareStatement(sql) 却不 close()，会快速耗尽游标数（open_cursors）
• ⚠️ 注意：Oracle 对绑定变量个数有限制（默认 1000），超限会报 ORA-01000: maximum open cursors exceeded

PL/SQL 中怎么用绑定变量？别只靠 EXECUTE IMMEDIATE

PL/SQL 块内直接写 SQL（如 SELECT ... INTO ... FROM t WHERE id = v_id）天然就是绑定变量——变量 v_id 会被 Oracle 自动绑定，无需额外操作。

• ✅ 推荐：优先写静态 SQL（即非 EXECUTE IMMEDIATE），执行计划稳定、性能最优
• ⚠️ EXECUTE IMMEDIATE 必须显式绑定：EXECUTE IMMEDIATE 'SELECT name FROM emp WHERE dept_id = :d' INTO l_name USING v_dept; —— 漏掉 USING 或写成字符串拼接就退化为硬解析
• ❌ 不要这样：EXECUTE IMMEDIATE 'SELECT name FROM emp WHERE dept_id = ' || v_dept;，这是典型硬解析陷阱

执行计划真的复用了？怎么看？

不能凭感觉，得查 v$sql。关键看三列是否一致：相同 sql_id、child_number 递增但 executions 累加、parse_calls 增长缓慢。

• 查当前 SQL 是否被复用：SELECT sql_id, child_number, executions, parse_calls, loads FROM v$sql WHERE sql_text LIKE 'SELECT%WHERE id = %'
• 如果 executions 是 1000 但 parse_calls 是 1，说明复用成功；若两者接近，大概率没用好绑定变量
• 注意：绑定变量窥探（bind peeking）可能导致首次执行计划不适配后续值，但这属于优化器行为，不影响复用本身

真正难的是在复杂业务逻辑里坚持用绑定变量——比如分页查询拼 OFFSET、动态字段列表、或 where 条件开关。这些地方一旦退回到字符串拼接，前面所有努力就白做了。不是技术做不到，是开发习惯和代码审查能不能守住这条线。