为什么在PL/SQL存储过程中使用动态游标会降低性能以及如何优化？-SQL-PHP中文网

动态游标性能崩盘主因是运行时拼接sql导致硬解析频繁、执行计划无法复用，叠加未索引过滤、函数滥用及统计信息缺失；应控制使用边界，改用批量获取、显式释放、绑定变量优化及替代方案。

为什么在pl/sql存储过程中使用动态游标会降低性能以及如何优化？

动态游标本身不慢，慢的是它常被用在不该用的地方——比如嵌套查询、未索引字段过滤、或与函数调用耦合时，执行计划无法复用，每次 FETCH 都触发重解析。

动态游标（REF CURSOR）的查询语句在运行时才拼接，数据库无法在编译期生成稳定执行计划。一旦变量传入导致谓词变化（如 WHERE status = :p_status），优化器可能为每种值生成不同计划，甚至因绑定变量窥探失效而选错索引。

每次 OPEN ref_cursor FOR v_sql USING ... 都是一次全新硬解析，尤其当 v_sql 含拼接条件时，v_sql 字符串不同 = 完全不同的 SQL，共享池无法复用
若动态 SQL 中含函数（如 TO_CHAR(created_date, 'YYYYMM')），且该列无函数索引，就会强制全表扫描 —— 而且是每次 FETCH 前都扫一遍
Oracle 19c 对统计信息更敏感，若目标表未重收集（DBMS_STATS.GATHER_TABLE_STATS），动态游标更易误判选择性，跳过本该用的索引

常见错误是在 FETCH 循环内对每一行再查一次函数结果，例如：SELECT get_user_level(user_id) FROM dual。这会让 O(N) 变成 O(N×M)，且函数反复执行无法缓存。

把函数调用提到游标查询里：改写为 OPEN cur FOR 'SELECT u.*, get_user_level(u.user_id) lvl FROM users u WHERE ...'
对高频调用的函数建函数索引：CREATE INDEX idx_user_lvl ON users (get_user_level(user_id))，前提是函数声明为 DETERMINISTIC
避免在 WHERE 子句中对列用函数：WHERE UPPER(name) = :p_name → 改为 WHERE name = UPPER(:p_name) 并在 name 列建普通索引

关键不是禁用动态游标，而是控制它的使用边界和资源生命周期。

用 BULK COLLECT INTO 代替逐行 FETCH：一次取 100–500 行到集合，减少上下文切换；但注意内存，别 BULK COLLECT 百万行
显式关闭并释放：CLOSE cur; DEALLOCATE cur; 缺一不可，否则游标句柄长期占 PGA，并发高时直接 OOM
限制动态 SQL 复杂度：禁止在循环内拼接新 SQL；所有条件尽量收口到一个 v_where 字符串里，避免多层 EXECUTE IMMEDIATE
加 /*+ GATHER_PLAN_STATISTICS */ 提示，在开发环境跑一次，用 DBMS_XPLAN.DISPLAY_CURSOR 看真实执行计划是否走索引、有无 FULL TABLE SCAN

真正难的不是写动态游标，而是识别出“这里根本不需要动态”。

只是根据参数开关几个字段？用 SELECT ... CASE WHEN p_show_email = 1 THEN email END email，不用拼 SQL
只是过滤条件变多？用 WHERE (:p_dept IS NULL OR dept_id = :p_dept) AND (:p_status IS NULL OR status = :p_status)，配合绑定变量和函数索引
要分页？别用游标 + ROWNUM 模拟，直接用 OFFSET ... FETCH NEXT（12c+）或 ROW_NUMBER() OVER() CTE
要跨库或调外部？游标真没法绕，但至少把数据先 INSERT INTO GLOBAL TEMP TABLE，再从临时表驱动逻辑，避免反复连远端

动态游标最危险的时刻，是开发者觉得“反正只是临时查一下”，却忘了它每次打开都在消耗硬解析、锁资源、占 PGA —— 这些开销在单次测试里不明显，压测时立刻暴露。真正该盯的不是语法，而是 V$SQL 里那条动态 SQL 的 EXECUTIONS 和 PARSE_CALLS 是否严重失衡。