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

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

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

一、问题背景

前几天在客户现场排查一个性能问题，发现 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 退回到传统的 绑定变量窥探模式：

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

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）相关

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

3.2 自适应优化器特性

3.3 基数反馈（Cardinality Feedback）

3.4 动态采样

3.5 自动内存管理

3.6 并行执行

3.7 结果集缓存

3.8 SQL 计划管理（SPM）

3.9 统计信息相关

3.10 其他重要性能参数

四、快速检查脚本

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 生产环境参数配置建议

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

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

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

   ALTERSYSTEMSET optimizer_adaptive_plans = TRUESCOPE=BOTH;
   

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

   ALTERSYSTEMSET optimizer_use_sql_plan_baselines = TRUESCOPE=BOTH;
   

5.2 问题排查流程

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

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

5.3 预防措施

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

六、总结

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