Oracle 11g 自适应游标共享(ACS)详解：从绑定变量窥探到执行计划共享的配置与验证

1. 生产库里的怪现象同一条 SQL 时快时慢如果你在生产库值班时遇到过这种场景同一条带绑定变量的 SQL早上跑 0.2 秒下午某个时段突然变成 8 秒执行计划从索引范围扫描变成了全表扫描而 SQL 文本一个字都没改——那大概率是绑定变量窥探Bind Peeking留下的坑。Oracle 11g 引入的自适应游标共享Adaptive Cursor Sharing简称 ACS就是专门解决这个矛盾的。它让同一条使用绑定变量的 SQL可以根据不同绑定值的选择性生成并复用多个子游标而不是像 10g 那样死守第一次窥探出来的计划。ACS 默认开启但很多人以为它自动生效就万事大吉实际上它有一堆前置条件不满足就直接退化成普通游标共享。这篇面向 DBA 和数据库开发聚焦生产库中绑定变量导致执行计划劣化的排查场景。我会把 ACS 的初始化参数配置、v$sql/v$sql_cs_statistics等视图的观察方法、强制硬解析与计划回退的对比测试步骤都写清楚目标是让你能独立判断 ACS 到底有没有生效以及共享失效时该往哪个方向查。适合谁看正在维护 Oracle 11g 生产库、被绑定变量 数据倾斜折磨过的 DBA以及写 PL/SQL 时习惯用绑定变量、但不确定执行计划是否稳定的开发者。2. 先搞懂 ACS 的三个核心状态在动手配置之前得先理解 ACS 把游标分成了三种状态这三个状态在v$sql里都有对应字段是判断 ACS 是否生效的关键。第一种是bind-sensitive对绑定敏感。当 SQL 使用绑定变量且优化器在硬解析时做了绑定窥探用直方图计算了谓词选择性并且绑定值的变化可能导致不同计划时游标就被标记为 bind-sensitive。对应v$sql.is_bind_sensitive Y。注意这只是敏感还没到能识别。第二种是bind-aware能识别绑定。当同一条 SQL 连续执行时如果行源基数row source cardinality出现显著变化游标会升级为 bind-aware。对应v$sql.is_bind_aware Y。到了这一步Oracle 才会真正为不同的绑定值选择性范围维护多个子游标。第三种是is_shareable。这个字段告诉你当前子游标是否还能被共享。如果它变成N说明这个子游标已经过时下次执行会触发硬解析。理解这三者的递进关系很重要bind-sensitive 是入场券bind-aware 是真正干活的状态而 is_shareable 决定复用性。很多ACS 没生效的案例其实是游标卡在 bind-sensitive 没升级到 bind-aware。ACS 还有几个硬性限制踩过才知道疼绑定变量个数不能超过 14 个超过就失效并行查询Parallel Query会禁用 ECS使用了/*NO_BIND_AWARE*/提示、Outline、递归查询都会让 ACS 不工作。另外cursor_sharing如果设成SIMILAR在 11g 里是不推荐的这个值最终会被废弃生产库应该用EXACT。3. TaoToken 前置把验证环境准备好排查 ACS 这种需要反复跑 SQL、对比执行计划、观察视图变化的活最怕的就是环境不统一、命令记不住、视图字段对不上。我习惯把常用的诊断 SQL 和参数配置整理成一套可复用的工具箱需要的时候直接调。TaoToken 在这里的定位是帮你把模型对话、编码辅助和 API 调用串起来。比如你在写一段复杂的v$sql_cs_selectivity查询时可以直接在模型对话里让它帮你补全字段含义或者把一段 PL/SQL 诊断脚本丢进去做解释。对于长期做数据库运维的人来说Coding Plan 更适合把这类重复性的诊断脚本沉淀下来。具体入口我列一下按需取用模型对话问字段含义、解释执行计划https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentmodel_chatCoding Plan沉淀诊断脚本、长期编码辅助https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcoding_plan控制台https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentconsoleAPI Keys接入自己的诊断工具https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentapi_keys接入文档https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentdocAPI 地址统一用 https://taotoken.net/api 注意这个不带 UTM 参数。如果你要写脚本自动拉取诊断结果走 API 就行。注意TaoToken 是辅助你写 SQL、解释视图、整理排查思路的工具不是替代 SQL*Plus 或 SQL Developer 的。真正的库操作还是在你的客户端里执行。4. 可复制的 ACS 参数配置与验证脚本这一节是重点所有命令都可以直接复制到 SQL*Plus 或 SQL Developer 里跑。先确认 ACS 相关参数再构造数据倾斜场景最后观察游标状态变化。4.1 确认 ACS 相关参数当前值ACS 涉及三个隐藏参数虽然官方手册不重点提但排查时必须知道它们的状态-- 查看 ACS 相关隐藏参数 SELECT a.ksppinm AS parameter_name, b.ksppstvl AS current_value, a.ksppdesc AS description FROM x$ksppi a, x$ksppcv b WHERE a.indx b.indx AND a.ksppinm IN (_optimizer_adaptive_cursor_sharing, _optimizer_extended_cursor_sharing, _optimizer_extended_cursor_sharing_rel) ORDER BY a.ksppinm;正常开启 ACS 时期望看到参数期望值说明_optimizer_adaptive_cursor_sharingTRUEACS 总开关_optimizer_extended_cursor_sharingSIMPLE 或 UDUAL扩展游标共享模式_optimizer_extended_cursor_sharing_relSIMPLE关系运算符的扩展共享同时确认cursor_sharing是EXACTSHOW PARAMETER cursor_sharing;如果看到SIMILAR建议改回EXACT因为 11g 里SIMILAR已被标记为不推荐会干扰 ACS 的正常判断。4.2 构造数据倾斜测试表ACS 只有在数据倾斜、绑定值选择性差异大时才会明显触发。建一张测试表-- 建表并制造严重数据倾斜99% 是值 11% 是值 0 CREATE TABLE t_acs_test ( id NUMBER, flag NUMBER, pad VARCHAR2(100) ); BEGIN FOR i IN 1..100000 LOOP INSERT INTO t_acs_test VALUES (i, 1, RPAD(x, 100, x)); END LOOP; FOR i IN 100001..101000 LOOP INSERT INTO t_acs_test VALUES (i, 0, RPAD(x, 100, x)); END LOOP; COMMIT; END; / -- 收集统计信息并生成直方图这是 ACS 生效的关键前提 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname USER, tabname T_ACS_TEST, method_opt FOR COLUMNS flag SIZE 254, cascade TRUE ); END; / -- 建索引让选择性差异能体现为不同计划 CREATE INDEX idx_acs_flag ON t_acs_test(flag);直方图很关键。如果flag列没有直方图优化器对选择性的估算就是均匀分布ACS 很难判断出不同绑定值需要不同计划。4.3 用绑定变量执行并观察游标状态先清空共享池里这条 SQL 的游标保证从干净状态开始-- 找到目标 SQL 的 sql_id先执行一次拿到 VAR b NUMBER; EXEC :b : 0; SELECT COUNT(*) FROM t_acs_test WHERE flag :b;拿到sql_id后查游标状态SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable, executions, plan_hash_value FROM v$sql WHERE sql_id target_sql_id ORDER BY child_number;第一次用flag 0选择性高走索引执行后通常看到is_bind_sensitive Yis_bind_aware N。这符合预期——游标已经敏感但还没升级。接着用flag 1选择性低应该走全表扫描执行几次EXEC :b : 1; SELECT COUNT(*) FROM t_acs_test WHERE flag :b; -- 重复执行 3~5 次让 ACS 有足够样本判断基数变化再查一次v$sql如果 ACS 正常工作你会看到is_bind_aware变成Y并且出现新的child_number不同子游标对应不同的plan_hash_value。4.4 用 v$sql_cs_statistics 看执行统计v$sql_cs_statistics汇总了 ACS 监控组件收集的原始执行统计是判断为什么没升级到 bind-aware的关键SELECT sql_id, child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time FROM v$sql_cs_statistics WHERE sql_id target_sql_id ORDER BY child_number, bind_set_hash_value;peeked YES表示这个子游标是用绑定窥探构建的。rows_processed和buffer_gets在不同绑定集之间的差异就是 ACS 判断是否需要新计划的依据。如果所有绑定集的rows_processed都差不多ACS 会认为一个计划够用不会升级到 bind-aware。4.5 用 v$sql_cs_selectivity 看选择性立方体这个视图暴露了每个子游标维护的选择性范围低值和高值SELECT sql_id, child_number, predicate, range_low, range_high FROM v$sql_cs_selectivity WHERE sql_id target_sql_id ORDER BY child_number, predicate;如果range_low和range_high覆盖了当前绑定值的选择性子游标就能被共享如果落在范围外就会触发硬解析生成新子游标。这就是 ACS 选择性立方体匹配的底层逻辑。4.6 用 v$sql_cs_histogram 看执行历史分布SELECT sql_id, child_number, bucket_id, count FROM v$sql_cs_histogram WHERE sql_id target_sql_id ORDER BY child_number, bucket_id;三个 bucket 分别对应不同的执行次数区间ACS 用它来决定是否启用扩展游标共享。如果某个 bucket 的 count 明显偏高说明该选择性区间的执行很频繁值得单独维护子游标。5. 强制硬解析与计划回退的对比测试光看状态还不够得做对比实验才能确认 ACS 真的在起作用。下面这套步骤可以帮你验证关闭 ACS 后计划是否回退。5.1 关闭 ACS 观察计划回退在会话级别关闭 ACS不影响其他会话ALTER SESSION SET _optimizer_extended_cursor_sharing_rel NONE; ALTER SESSION SET _optimizer_extended_cursor_sharing NONE; ALTER SESSION SET _optimizer_adaptive_cursor_sharing FALSE;然后清掉共享池里这条 SQL 的游标重新用flag 0执行一次再用flag 1执行多次-- 用 flag0 先窥探出索引计划 EXEC :b : 0; SELECT COUNT(*) FROM t_acs_test WHERE flag :b; -- 再用 flag1 执行观察是否复用同一个计划 EXEC :b : 1; SELECT COUNT(*) FROM t_acs_test WHERE flag :b;关闭 ACS 后你会发现v$sql里只有一个子游标is_bind_aware始终是Nflag 1的执行复用了flag 0窥探出来的索引计划——这就是 10g 时代的经典问题明明全表扫描更快却硬走索引。5.2 重新开启 ACS 对比恢复会话参数ALTER SESSION SET _optimizer_adaptive_cursor_sharing TRUE; ALTER SESSION SET _optimizer_extended_cursor_sharing SIMPLE; ALTER SESSION SET _optimizer_extended_cursor_sharing_rel SIMPLE;清游标后重复 5.1 的执行序列这次应该能看到is_bind_aware Y并且出现第二个子游标plan_hash_value与第一个不同。用DBMS_XPLAN分别看两个子游标的计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(target_sql_id, 0, ALLSTATS LAST)); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(target_sql_id, 1, ALLSTATS LAST));对比两个计划的Rows估算和实际A-Rows就能直观看到 ACS 为不同选择性选择了不同访问路径。5.3 强制硬解析的辅助手段有时候为了复现问题需要强制硬解析。除了ALTER SYSTEM FLUSH SHARED_POOL生产库慎用更精准的做法是-- 只清掉目标 SQL 的游标影响面小 BEGIN FOR r IN (SELECT sql_id, child_number FROM v$sql WHERE sql_id target_sql_id) LOOP DBMS_SHARED_POOL.PURGE(r.sql_id || , || r.child_number, C); END LOOP; END; /DBMS_SHARED_POOL.PURGE需要相应权限且只对当前缓存的游标有效。用它可以在不影响其他 SQL 的前提下反复做 ACS 的对比实验。6. 本篇常见错排查排查 ACS 时下面这几个坑我踩过不止一次列出来帮你省时间。游标一直是 bind-sensitive 不升级到 bind-aware。最常见的原因是数据分布不够倾斜或者直方图没收集。ACS 判断升级的依据是连续执行的行源基数显著变化如果flag列没有直方图优化器估算的选择性都一样自然不会升级。检查v$sql_cs_statistics里不同绑定集的rows_processed是否真的有差异。绑定变量超过 14 个导致 ACS 失效。这是硬限制没有绕过的办法。如果你的 SQL 绑定变量很多要么拆分 SQL要么接受 ACS 不生效、改用其他手段比如 SQL Plan Baseline来稳定计划。cursor_sharing被设成了SIMILAR。11g 里这个值会干扰 ACS而且已被标记为废弃。生产库统一用EXACT需要共享就用绑定变量不要靠SIMILAR做字面量替换。并行查询禁用了 ECS。如果 SQL 走了并行_optimizer_extended_cursor_sharing相关的扩展共享就不工作。检查执行计划里有没有PX相关操作有的话 ACS 的观察结果不可信。子游标数量爆炸导致 shared pool 压力。ACS 的代价就是可能生成大量子游标MOS 文档里明确建议从 10g 升级到 11g 时考虑增大shared_pool_size。如果发现某个sql_id的子游标数异常多查v$sql_shared_cursor定位原因必要时用 SQL Plan Baseline 固定计划减少 ACS 的自由发挥。用了/*NO_BIND_AWARE*/提示或 Outline。这两个都会直接禁用 ACS。排查时先确认 SQL 里没有这类提示也没有生效的 Outline。递归查询不触发 ACS。递归 SQL比如数据字典查询本身就不走 ACS别在递归 SQL 上验证 ACS 是否生效。排查顺序建议先看v$sql的is_bind_sensitive/is_bind_aware再看v$sql_cs_statistics的统计差异最后用v$sql_cs_selectivity确认选择性范围。三步走下来基本能定位到是没敏感、没升级还是升级了但共享失效。如果你在写诊断脚本时需要快速查某个视图的字段含义或者想把排查逻辑整理成可复用的 SQL 模板可以走模型对话入口问一下比翻文档快。长期做这类运维的Coding Plan 更适合把脚本沉淀成自己的工具库。API Keys 和接入文档在需要把诊断结果接到自有平台时用得上。