Oracle 19c ：ACS 未开启引发的执行计划案例分析

薛晓刚-

发布于 2026-07-22 19:16:54

770

先说背景，实在没想通这个默认打开的参数为什么关闭了。

从来没去考虑过会关闭。而且大部分都是打开的。实在不巧遇到这个数据库是关闭的

一、问题背景

前几天在客户现场排查一个性能问题，发现 Oracle 19c 数据库中 ACS（Adaptive Cursor Sharing，自适应游标共享）居然没有开启。

按照 Oracle 的官方文档，ACS 从 11gR2 开始默认是开启的，但客户的 19c 环境却意外关闭了。这导致了一个典型的性能问题：

现象：一条核心业务 SQL 在当天早上 8 点突然变慢，之后一直使用次优执行计划
影响：业务响应时间总之变慢了
排查：发现该 SQL 的执行计划发生了突变，且固化了错误的计划
解决：清除该 SQL 的所有游标（按 SQL_ID 清除），强制重新解析后恢复正常

这引发了一个思考：为什么 ACS 关闭后，SQL 会突然在某天早上"崩掉"？由于这是第一次遇到这么奇怪的，所以也是推论。可能推论的不对，可以纠正我的想法。

二、原理分析：ACS 关闭后的执行计划稳定性问题

2.1 ACS 的作用机制

自适应游标共享（ACS）是 Oracle 11g 引入的重要特性，用于解决 绑定变量窥探（Bind Peeking）带来的问题。

传统绑定变量窥探的问题：

-- 假设有一个查询
SELECT * FROM orders WHEREstatus = :bind1;

-- 如果第一次执行时 :bind1 = 'ACTIVE'（返回 1000 行）
-- 优化器会生成一个适合返回大量数据的执行计划（比如 FULL SCAN）

-- 后续如果 :bind1 = 'CLOSED'（只返回 10 行）
-- 但 Oracle 仍然使用之前的执行计划，不会重新优化

ACS 的解决方案：

监控绑定变量的实际值分布
根据绑定变量的选择性，生成多个子游标（child cursor）
不同绑定变量值可以选择不同的执行计划

2.2 ACS 关闭后的风险场景

当 ACS 关闭时，Oracle 退回到传统的 绑定变量窥探模式：

第一次硬解析：基于第一次执行的绑定变量值生成执行计划
后续执行：无论绑定变量值如何变化，都复用同一个执行计划
风险点：如果第一次硬解析时碰巧遇到"非典型"的绑定变量值，就会生成一个次优计划并长期固化

2.3 为什么会在"当天早上 8 点"突然出问题？

可能原因：游标被清除，触发了重新解析。因为我列出了一些可能的原因，然后排除了一些没有发生的情况，那么剩下的是可能性较高的

因为可能的原因包括：

（1）共享池空间压力

-- 查看共享池使用情况
SELECT * FROM v$sgastat WHEREname = 'free memory'AND pool = 'shared pool';

-- 查看游标失效情况
SELECT sql_id, child_number, executions, loads, invalidations
FROM v$sql
WHERE sql_id = 'your_sql_id';

共享池空间不足时，LRU 算法会淘汰旧的游标
如果淘汰了该 SQL 的游标，下次执行时会触发硬解析
这次硬解析如果碰巧遇到一个"非典型"的绑定变量值，就会生成次优计划

（2）统计信息自动收集

-- 查看统计信息收集历史
SELECT table_name, last_analyzed, num_rows
FROM dba_tables
WHERE table_name IN ('YOUR_TABLES')
ORDERBY last_analyzed DESC;

Oracle 默认在夜间（通常 22:00-06:00）自动收集统计信息
统计信息更新后，相关的游标会被标记为 INVALID
下次执行时触发硬解析，可能生成新的执行计划
因为这个表没有数量级的变化，变化程度也没有超过10%。所以没有被收集过。而且如果因为是这个原因的话，那早就出问题了。

（3）对象结构变更

索引重建、表分析、DDL 操作等都会导致游标失效
这个也问了相关的人，没有做过DDL和索引重建

（4）实例重启或共享池刷新

当然很明显这个数据库没有重启过。

-- 查看实例启动时间
SELECT startup_time FROM v$instance;

-- 查看是否有人手动刷新共享池
SELECT * FROM dba_audit_trail WHERE obj_name = 'FLUSH SHARED_POOL';

2.4 问题复现路径

基于以上分析，问题的完整路径应该是：

1. ACS 关闭（隐藏参数 _optimizer_adaptive_cursor_sharing = FALSE）
   ↓
2. SQL 游标因某种原因被清除（共享池压力/统计信息更新等）
   ↓
3. 早上 8 点业务高峰，该 SQL 第一次执行
   ↓
4. 硬解析时碰巧遇到一个"非典型"的绑定变量值
   ↓
5. 生成了一个次优的执行计划（比如全表扫描）
   ↓
6. 由于 ACS 关闭，后续所有执行都复用这个次优计划
   ↓
7. 性能急剧下降，且问题持续存在

2.5 解决方案验证

采用的方案是：

-- 清除该 SQL 的所有游标
ALTERSYSTEMFLUSHSHARED_POOL;  -- 方法1：清空整个共享池（影响大）
这种方案一般来说是不用的。
-- 所以采用下面的
SELECT address, hash_value FROM v$sqlarea WHERE sql_id = 'your_sql_id';
-- 然后逐个清除

这个方案是正确的，因为：

清除了次优计划的游标
下次执行时触发硬解析
如果此时绑定变量值是"典型"的值，就会生成正确的执行计划
由于 ACS 关闭，这个正确计划会被固化下来

但治本的方案是：

-- 开启 ACS（需要重启）
ALTERSYSTEMSET"_optimizer_adaptive_cursor_sharing" = TRUESCOPE=SPFILE;
ALTERSYSTEMSET"_optimizer_extended_cursor_sharing" = 'UDO'SCOPE=SPFILE;
ALTERSYSTEMSET"_optimizer_extended_cursor_sharing_rel" = 'NONE'SCOPE=SPFILE;
-- 然后重启数据库

三、Oracle 11g-19c 性能相关参数清单

基于这次排查经验，我整理了 Oracle 11g 到 19c 所有与性能相关的参数，包括默认值和查询方式。

3.1 自适应游标共享（ACS）相关

参数名	默认值	作用	影响	查询方式
_optimizer_adaptive_cursor_sharing	TRUE (11gR2+)	启用 ACS	避免绑定变量导致的次优计划	SELECT ksppinm, ksppstvl FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_optimizer_adaptive_cursor_sharing';
_optimizer_extended_cursor_sharing	UDO (11gR2+)	控制扩展游标共享行为	与 ACS 配合使用	同上，替换参数名
_optimizer_extended_cursor_sharing_rel	NONE (11gR2+)	关联查询的扩展游标共享	影响复杂查询稳定性	同上，替换参数名

注意：这三个参数都是隐藏参数，修改需要重启实例。

3.2 自适应优化器特性

参数名	默认值	作用	影响	查询方式
_optimizer_adaptive_features	TRUE (12cR1+)	启用自适应优化器	运行时调整执行策略	隐藏参数查询
optimizer_adaptive_plans	TRUE (12cR2+)	启用自适应执行计划	改善复杂查询，增加 CPU 开销	SHOW PARAMETER optimizer_adaptive_plans
optimizer_adaptive_statistics	FALSE (12cR2+)	启用自适应统计信息	动态收集统计信息	SHOW PARAMETER optimizer_adaptive_statistics

3.3 基数反馈（Cardinality Feedback）

参数名	默认值	作用	影响	查询方式
_optimizer_use_feedback	TRUE (11gR2+)	启用基数反馈	自动纠正错误基数估算	隐藏参数查询
_optimizer_cardinality_feedback	TRUE (12c+)	基数反馈详细行为	与上一参数配合	隐藏参数查询

3.4 动态采样

参数名	默认值	作用	影响	查询方式
optimizer_dynamic_sampling	2 (11g+)	动态采样级别 (0-11)	改善统计信息缺失表的查询	SHOW PARAMETER optimizer_dynamic_sampling

3.5 自动内存管理

参数名	默认值	作用	影响	查询方式
memory_target	0 (需手动设)	启用 AMM	简化内存管理，但可能不稳定	SHOW PARAMETER memory_target
memory_max_target	0	AMM 最大限制	限制最大内存使用	SHOW PARAMETER memory_max_target
sga_target	0 (需手动设)	启用 ASMM	自动调整 SGA 组件	SHOW PARAMETER sga_target
pga_aggregate_target	10M 或 SGA 20%	PGA 总大小	影响排序、哈希操作	SHOW PARAMETER pga_aggregate_target

3.6 并行执行

参数名	默认值	作用	影响	查询方式
parallel_degree_policy	MANUAL (11gR2+)	并行度策略	AUTO/ADAPTIVE 自动决定并行度	SHOW PARAMETER parallel_degree_policy
parallel_degree_limit	CPU (11gR2+)	并行度最大值	防止过度并行	SHOW PARAMETER parallel_degree_limit
parallel_min_time_threshold	10 (秒)	并行最小时间阈值	避免短查询并行	SHOW PARAMETER parallel_min_time_threshold

3.7 结果集缓存

参数名	默认值	作用	影响	查询方式
result_cache_mode	MANUAL (11g+)	结果缓存模式	AUTO 自动决定是否缓存	SHOW PARAMETER result_cache_mode
result_cache_max_size	0 或 sga*0.25%	缓存最大大小	限制内存使用	SHOW PARAMETER result_cache_max_size
result_cache_max_result	5 (%)	单结果最大占比	防止单个结果占满缓存	SHOW PARAMETER result_cache_max_result

3.8 SQL 计划管理（SPM）

参数名	默认值	作用	影响	查询方式
optimizer_use_sql_plan_baselines	TRUE (11g+)	启用 SQL 计划基线	防止执行计划退化	SHOW PARAMETER optimizer_use_sql_plan_baselines
optimizer_capture_sql_plan_baselines	FALSE	自动捕获基线	自动维护计划稳定性	SHOW PARAMETER optimizer_capture_sql_plan_baselines

3.9 统计信息相关

参数名	默认值	作用	影响	查询方式
optimizer_use_pending_statistics	FALSE (11g+)	使用待定统计信息	验证统计信息后再发布	SHOW PARAMETER optimizer_use_pending_statistics
_optimizer_gather_stats_on_load	TRUE (11g+)	加载时自动收集统计	保持统计信息新鲜	隐藏参数查询

3.10 其他重要性能参数

参数名	默认值	作用	影响	查询方式
_optimizer_cost_based_transformation	TRUE	基于成本的查询转换	改善复杂查询优化	隐藏参数查询
_optimizer_null_aware_antijoin	TRUE (11g+)	NULL 感知反连接	改善 NOT IN 性能	隐藏参数查询
_optimizer_use_histograms	TRUE	使用直方图	改善数据倾斜列估算	隐藏参数查询
_optimizer_system_stats_usage	TRUE	使用系统统计信息	基于硬件成本的优化	隐藏参数查询

四、快速检查脚本

4.1 检查 ACS 状态

SELECT ksppinm AS parameter_name,
       ksppstvl AS current_value,
       ksppdesc AS description
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx
AND ksppinm IN (
'_optimizer_adaptive_cursor_sharing',
'_optimizer_extended_cursor_sharing',
'_optimizer_extended_cursor_sharing_rel'
);

4.2 检查所有优化器相关参数

SELECTname, value, description
FROM v$parameter
WHEREnameLIKE'optimizer%'
ORDERBYname;

4.3 检查隐藏参数（谨慎使用）

-- 查询所有 _optimizer 开头的隐藏参数
SELECT ksppinm AS parameter_name,
       ksppstvl AS current_value,
       ksppdesc AS description
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx
AND ksppinm LIKE'_optimizer%'
ORDERBY ksppinm;

五、最佳实践建议

5.1 生产环境参数配置建议

ACS 相关参数：保持默认值（开启）

-- 验证 ACS 是否开启
SELECT ksppinm, ksppstvl 
FROM x$ksppi x, x$ksppcv y 
WHERE x.indx = y.indx 
AND ksppinm = '_optimizer_adaptive_cursor_sharing';

自适应优化器：12cR2+ 建议开启

ALTERSYSTEMSET optimizer_adaptive_plans = TRUESCOPE=BOTH;

SQL 计划管理：建议开启基线保护

ALTERSYSTEMSET optimizer_use_sql_plan_baselines = TRUESCOPE=BOTH;

5.2 问题排查流程

当遇到执行计划突变问题时：

检查 ACS 状态- 是否意外关闭？
检查游标状态- 是否有 invalidations？
检查统计信息- 最近是否更新？
检查共享池- 是否有空间压力？
清除游标测试- ALTER SYSTEM FLUSH SHARED_POOL（谨慎使用）

5.3 预防措施

定期监控：设置定时任务检查关键参数
变更管理：任何参数修改都应在测试环境验证
基线保护：对核心 SQL 使用 SPM 基线
统计信息：避免在业务高峰期自动收集统计信息

六、总结

这次排查揭示了一个重要问题：Oracle 19c 虽然默认开启 ACS，但在某些情况下安装时候特意去关闭。自己想当然觉得这个是默认的就没去关注。也许我也没分析对，有人如果有更好的分析可以告诉我。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-21，如有侵权请联系 cloudcommunity@tencent.com 删除

统计

本文分享自四海内皆兄弟微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度