cursor_sharing 的妙用：用 CURSOR_SHARING_EXACT 与 hint 稳住 Oracle 执行计划

1. 当字面量 SQL 把 shared pool 撑爆时我在想什么cursor_sharing这个参数说白了就是 Oracle 帮你做的一层“SQL 文本归一化”。你写where id 1、where id 2、where id 3如果cursor_sharingEXACT默认值Oracle 认为这是三条完全不同的 SQL每条都要走一次硬解析各自在 library cache 里占一个坑。OLTP 系统里如果开发没规范用绑定变量shared pool 很快就被这些“长得像但不一样”的 SQL 塞满parse count (hard)蹭蹭往上涨latch 争用、CPU 飙高全来了。cursor_sharingFORCE或SIMILAR就是让 Oracle 自动把字面量替换成:SYS_B_0这种系统绑定变量从而复用游标。听起来很美但坑也在这里一旦替换执行计划就锁死在第一次硬解析时 peek 到的那个绑定值上。如果id列数据分布极不均匀——比如id1只返回 3 行走索引id999返回 80 万行走全表扫描——那绑定变量反而会把某些查询拖进深渊。我试过在一个订单表上把cursor_sharing从EXACT改成FORCE硬解析次数当天降了 90%但第二天报表模块开始超时原因就是某个状态字段分布倾斜绑定变量让本该走索引的查询走了全表扫描。所以真正要解决的问题不是“开不开 cursor_sharing”而是在需要共享的地方共享在需要独立执行计划的地方用 hint 强制不共享。CURSOR_SHARING_EXACT这个 hint 就是干这个的它告诉优化器“这条 SQL 别给我做字面量替换我要自己的执行计划”。这篇内容适合谁正在被硬解析压得喘不过气的 Oracle DBA、写 PL/SQL 时纠结绑定变量用法的后端开发、以及想搞清楚cursor_sharing到底该不该动的运维同学。下面我会给出可复制的参数配置、hint 写法、执行计划对比验证步骤你在自己的测试库上就能复现。2. 前置准备TaoToken 与实验环境在动手改参数之前先把实验环境搭好。你需要一个能连 Oracle 的客户端SQLPlus、SQL Developer、DBeaver 都行。我习惯用 SQLPlus 做这类验证因为v$sqlarea、v$sql这些视图查起来直接。如果你本地没有现成的 Oracle 实例或者想在一个隔离环境里跑这些验证可以用 TaoToken 的模型对话能力先帮你梳理参数含义和排查思路。它的入口在这里模型对话https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentmodel_chat比如你可以直接问“cursor_sharingsimilar 和 force 在 19c 里行为差异”它会结合文档给你解释省得你去翻官方手册。但注意参数最终还是要落到真实库上验证模型给的是方向不是结论。实验用的表我建议自己建一张别在生产库上乱动。下面这段建表语句你可以直接复制-- 建一张分布倾斜的测试表 CREATE TABLE t_skew AS SELECT CASE WHEN ROWNUM 900000 THEN 1 -- 90% 的数据 id1 ELSE ROWNUM END AS id, ROWNUM AS val, RPAD(x, 100, x) AS pad_col FROM dual CONNECT BY ROWNUM 1000000; -- 建索引 CREATE INDEX idx_t_skew_id ON t_skew(id); -- 收集统计信息让优化器知道分布 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname USER, tabname T_SKEW, method_opt FOR ALL COLUMNS SIZE AUTO, cascade TRUE ); END; /这张表里id1有 90 万行其他 id 各 1 行。这种分布下where id1应该走全表扫描where id999999应该走索引。绑定变量如果 peek 到id1那所有查询都用全表扫描计划peek 到小值所有查询都用索引计划。这就是我们要观察的核心现象。TaoToken 的 API 接入文档在这里如果你想把一些查询日志或执行计划文本丢给模型做分析可以走 API接入文档https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentdocAPI Key 在控制台生成API Keyshttps://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentapi_keys3. 可复制配置cursor_sharing 参数与 hint 写法3.1 查看当前参数值先确认你库里的现状别上来就改SHOW PARAMETER cursor_sharing; -- 或者 SELECT name, value, isdefault FROM v$parameter WHERE name cursor_sharing;默认是EXACT。如果你看到FORCE或SIMILAR说明之前有人动过。SIMILAR在 12c 之后已经被标记为 deprecated官方建议用FORCE或EXACT所以下面我主要围绕EXACT和FORCE来讲。3.2 会话级切换参数改参数一定要先在会话级做别直接改系统级-- 切到 FORCE让 Oracle 自动做字面量替换 ALTER SESSION SET cursor_sharing FORCE; -- 切回 EXACT ALTER SESSION SET cursor_sharing EXACT;会话级改完只影响当前连接你断开重连就恢复。这样验证最安全。3.3 CURSOR_SHARING_EXACT hint 的写法这个 hint 的作用是即使当前会话cursor_sharingFORCE这条 SQL 也不做字面量替换。写法很简单放在SELECT后面SELECT /* CURSOR_SHARING_EXACT */ * FROM t_skew WHERE id 1;注意 hint 必须紧跟在SELECT关键字后面中间不能有换行或注释干扰。如果你写成select * /* CURSOR_SHARING_EXACT */ from ...Oracle 不认。在cursor_sharingFORCE下普通 SQL 会被改写成SELECT * FROM t_skew WHERE id :SYS_B_0而加了 hint 的 SQL 保持原样SELECT /* CURSOR_SHARING_EXACT */ * FROM t_skew WHERE id 1这两条在 library cache 里是两个不同的游标各自有独立的执行计划。3.4 配合绑定变量使用的场景有时候你既想用绑定变量又想让某条 SQL 不被自动替换比如 SQL 里已经有自己的绑定变量不想被 Oracle 再套一层也可以加这个 hintSELECT /* CURSOR_SHARING_EXACT */ * FROM t_skew WHERE id :my_id;这样 Oracle 不会去动你的:my_id执行计划按你传的值来 peek。3.5 参数与 hint 行为对照表场景cursor_sharinghint实际行为默认EXACT无字面量不替换每条 SQL 独立硬解析自动共享FORCE无字面量替换为 :SYS_B_n游标共享强制独立FORCECURSOR_SHARING_EXACT该条 SQL 不替换独立游标默认独立EXACTCURSOR_SHARING_EXACT无变化本来就不替换绑定变量FORCECURSOR_SHARING_EXACT保留用户绑定变量不额外替换这张表建议截图存下来排查的时候对着看。4. 验证请求与成功结果执行计划对比4.1 基线EXACT 下的两条 SQL先把会话切回 EXACT跑两条不同字面量的 SQL看执行计划ALTER SESSION SET cursor_sharing EXACT; -- 清一下共享池里相关的游标方便观察 -- 生产库慎用测试库随意 -- ALTER SYSTEM FLUSH SHARED_POOL; EXPLAIN PLAN FOR SELECT * FROM t_skew WHERE id 1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); EXPLAIN PLAN FOR SELECT * FROM t_skew WHERE id 999999; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);你会看到id1走TABLE ACCESS FULLid999999走INDEX RANGE SCAN。两条 SQL 在v$sqlarea里是两个不同的hash_value各自硬解析一次。4.2 FORCE 下的自动替换切到 FORCE再跑ALTER SESSION SET cursor_sharing FORCE; SELECT * FROM t_skew WHERE id 1; SELECT * FROM t_skew WHERE id 999999;然后查v$sqlareaSELECT sql_text, hash_value, executions FROM v$sqlarea WHERE sql_text LIKE SELECT * FROM t_skew WHERE id% ORDER BY first_load_time DESC;你会看到sql_text变成了SELECT * FROM t_skew WHERE id :SYS_B_0两条 SQL 共享同一个hash_valueexecutions累加。但执行计划只有一个——取决于第一次硬解析时 peek 到的值。如果第一次跑的是id1那id999999也会走全表扫描这就是问题所在。4.3 用 hint 强制独立现在关键步骤来了。在 FORCE 下给需要独立计划的 SQL 加 hintALTER SESSION SET cursor_sharing FORCE; -- 这条会被自动替换走共享游标 SELECT * FROM t_skew WHERE id 1; -- 这条加 hint不替换独立游标 SELECT /* CURSOR_SHARING_EXACT */ * FROM t_skew WHERE id 999999;再查v$sqlareaSELECT sql_text, hash_value, executions FROM v$sqlarea WHERE sql_text LIKE %t_skew% ORDER BY first_load_time DESC;你应该能看到两条记录一条是:SYS_B_0的共享游标一条是带 hint 的原始 SQL。分别对它们查执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( sql_id 你的sql_id, cursor_child_no 0 ));带 hint 的那条会走INDEX RANGE SCAN共享游标那条走TABLE ACCESS FULL。这就达到了“大部分 SQL 共享、个别 SQL 独立”的效果。4.4 用 parse count 验证硬解析变化光看执行计划还不够我们来看硬解析次数的变化。先记录基线SELECT name, value FROM v$sysstat WHERE name IN (parse count (total), parse count (hard));然后连续跑 100 条不同字面量的 SQL可以用 PL/SQL 循环BEGIN FOR i IN 1..100 LOOP EXECUTE IMMEDIATE SELECT * FROM t_skew WHERE id || i; END LOOP; END; /在 EXACT 下parse count (hard)会增加约 100。在 FORCE 下只会增加 1第一次硬解析后面全是 soft parse。这就是 cursor_sharing 带来的收益。4.5 成功结果的判断标准验证成功的标志有三个第一v$sqlarea里出现:SYS_B_n形式的 SQL 文本说明自动替换生效。第二带CURSOR_SHARING_EXACThint 的 SQL 在v$sqlarea里保持原始字面量说明 hint 生效。第三parse count (hard)的增长幅度符合预期共享的 SQL 只硬解析一次独立的 SQL 每次硬解析。如果这三点都满足说明你的配置是对的。5. 本篇常见错排查5.1 hint 不生效SQL 还是被替换了最常见的原因是 hint 位置写错了。必须是SELECT /* CURSOR_SHARING_EXACT */不能是SELECT * /* ... */。另外如果 SQL 里有多个 hint用逗号分隔别用空格SELECT /* CURSOR_SHARING_EXACT INDEX(t_skew idx_t_skew_id) */ * FROM t_skew WHERE id 999999;还有一种情况SQL 被视图或 PL/SQL 包装了hint 传不进去。这种要在最外层 SQL 上加。5.2 ORA-01006: bind variable not exists这个错误在cursor_sharingFORCE下很典型。原因是 Oracle 把 SQL 里的常量替换成了:SYS_B_n但某些客户端或驱动不认这些系统绑定变量执行时报“绑定变量不存在”。比如原文里那个FLOOR(SUM(T_TIME1)/60)的例子60被替换成:SYS_B_0结果报错。解决办法有两个一是给这条 SQL 加CURSOR_SHARING_EXACThint让它不被替换二是把常量逻辑封装成函数比如原文里的f_10_60这样 SQL 里就没有裸常量了。5.3 执行计划突然变差如果你在 FORCE 下发现某条 SQL 计划变差先查它的sql_id和child_numberSELECT sql_id, child_number, sql_text, plan_hash_value FROM v$sql WHERE sql_text LIKE %你的表名%;如果child_number有多个说明有多个子游标可能是绑定变量 peek 到了不同的值。这时候要么加 hint 强制独立要么用DBMS_STATS重新收集统计信息要么考虑 SQL Plan Baseline 来固定计划。5.4 SIMILAR 参数在 12c 之后的坑cursor_sharingSIMILAR在 12c 之后虽然还能设但官方已经不推荐了。它在某些版本上会触发 ORA-7445 内部错误原文里也提到了 Solaris 环境下的 bug。如果你现在还在用 SIMILAR建议尽快切到 FORCE 或 EXACT别在这个参数上冒险。5.5 改了参数但没生效检查三件事一是ALTER SESSION是否在当前会话执行二是是否有触发器或登录脚本覆盖了参数三是v$parameter里的isdefault是否为FALSE确认改的是会话级还是系统级。SELECT name, value, isdefault, isses_modifiable, issys_modifiable FROM v$parameter WHERE name cursor_sharing;isses_modifiable为TRUE才能会话级改issys_modifiable为IMMEDIATE才能系统级立即生效。6. 长期编码与 Agent 场景下的建议如果你是在做长期编码项目或者用 Agent 自动生成 SQLcursor_sharing的取舍会更复杂。Agent 生成的 SQL 往往字面量多、绑定变量少硬解析压力天然就大。这时候我的建议是默认保持 EXACT在应用层强制使用绑定变量而不是依赖数据库的自动替换。因为自动替换的执行计划不可控出了问题排查成本高。如果确实需要开 FORCE那就在关键 SQL 上加CURSOR_SHARING_EXACThint 做白名单。哪些 SQL 需要独立计划你心里要有数——通常是那些 where 条件列分布倾斜、或者涉及范围扫描的查询。对于长期跑的 Coding Plan 类任务可以把这些 hint 规则写进代码模板里让生成的 SQL 自动带上。TaoToken 的 Coding Plan 入口在这里适合需要持续生成和优化 SQL 的场景Coding Planhttps://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcoding_plan最后给一个实用技巧定期查v$sqlarea里executions高但parse_calls也高的 SQL这些就是硬解析大户。对它们逐个分析该加绑定的加绑定该加 hint 的加 hint。别指望一个参数解决所有问题cursor_sharing只是工具用对了才叫妙用。