Oracle 19c 性能参数排查：ACS 未开启引发的执行计划突变案例分析

先说背景实在没想通这个默认打开的参数为什么关闭了。从来没去考虑过会关闭。而且大部分都是打开的。实在不巧遇到这个数据库是关闭的一、问题背景前几天在客户现场排查一个性能问题发现Oracle 19c 数据库中 ACSAdaptive 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 WHERE status :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 WHERE name 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) ORDER BY 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 的所有游标 ALTER SYSTEM FLUSH SHARED_POOL; -- 方法1清空整个共享池影响大 这种方案一般来说是不用的。 -- 所以采用下面的 SELECT address, hash_value FROM v$sqlarea WHERE sql_id your_sql_id; -- 然后逐个清除这个方案是正确的因为清除了次优计划的游标下次执行时触发硬解析如果此时绑定变量值是典型的值就会生成正确的执行计划由于 ACS 关闭这个正确计划会被固化下来但治本的方案是-- 开启 ACS需要重启 ALTER SYSTEM SET _optimizer_adaptive_cursor_sharing TRUE SCOPESPFILE; ALTER SYSTEM SET _optimizer_extended_cursor_sharing UDO SCOPESPFILE; ALTER SYSTEM SET _optimizer_extended_cursor_sharing_rel NONE SCOPESPFILE; -- 然后重启数据库三、Oracle 11g-19c 性能相关参数清单基于这次排查经验我整理了Oracle 11g 到 19c 所有与性能相关的参数包括默认值和查询方式。3.1 自适应游标共享ACS相关参数名默认值作用影响查询方式_optimizer_adaptive_cursor_sharingTRUE (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_sharingUDO (11gR2)控制扩展游标共享行为与 ACS 配合使用同上替换参数名_optimizer_extended_cursor_sharing_relNONE (11gR2)关联查询的扩展游标共享影响复杂查询稳定性同上替换参数名注意这三个参数都是隐藏参数修改需要重启实例。3.2 自适应优化器特性参数名默认值作用影响查询方式_optimizer_adaptive_featuresTRUE (12cR1)启用自适应优化器运行时调整执行策略隐藏参数查询optimizer_adaptive_plansTRUE (12cR2)启用自适应执行计划改善复杂查询增加 CPU 开销SHOW PARAMETER optimizer_adaptive_plansoptimizer_adaptive_statisticsFALSE (12cR2)启用自适应统计信息动态收集统计信息SHOW PARAMETER optimizer_adaptive_statistics3.3 基数反馈Cardinality Feedback参数名默认值作用影响查询方式_optimizer_use_feedbackTRUE (11gR2)启用基数反馈自动纠正错误基数估算隐藏参数查询_optimizer_cardinality_feedbackTRUE (12c)基数反馈详细行为与上一参数配合隐藏参数查询3.4 动态采样参数名默认值作用影响查询方式optimizer_dynamic_sampling2 (11g)动态采样级别 (0-11)改善统计信息缺失表的查询SHOW PARAMETER optimizer_dynamic_sampling3.5 自动内存管理参数名默认值作用影响查询方式memory_target0 (需手动设)启用 AMM简化内存管理但可能不稳定SHOW PARAMETER memory_targetmemory_max_target0AMM 最大限制限制最大内存使用SHOW PARAMETER memory_max_targetsga_target0 (需手动设)启用 ASMM自动调整 SGA 组件SHOW PARAMETER sga_targetpga_aggregate_target10M 或 SGA 20%PGA 总大小影响排序、哈希操作SHOW PARAMETER pga_aggregate_target3.6 并行执行参数名默认值作用影响查询方式parallel_degree_policyMANUAL (11gR2)并行度策略AUTO/ADAPTIVE 自动决定并行度SHOW PARAMETER parallel_degree_policyparallel_degree_limitCPU (11gR2)并行度最大值防止过度并行SHOW PARAMETER parallel_degree_limitparallel_min_time_threshold10 (秒)并行最小时间阈值避免短查询并行SHOW PARAMETER parallel_min_time_threshold3.7 结果集缓存参数名默认值作用影响查询方式result_cache_modeMANUAL (11g)结果缓存模式AUTO 自动决定是否缓存SHOW PARAMETER result_cache_moderesult_cache_max_size0 或 sga*0.25%缓存最大大小限制内存使用SHOW PARAMETER result_cache_max_sizeresult_cache_max_result5 (%)单结果最大占比防止单个结果占满缓存SHOW PARAMETER result_cache_max_result3.8 SQL 计划管理SPM参数名默认值作用影响查询方式optimizer_use_sql_plan_baselinesTRUE (11g)启用 SQL 计划基线防止执行计划退化SHOW PARAMETER optimizer_use_sql_plan_baselinesoptimizer_capture_sql_plan_baselinesFALSE自动捕获基线自动维护计划稳定性SHOW PARAMETER optimizer_capture_sql_plan_baselines3.9 统计信息相关参数名默认值作用影响查询方式optimizer_use_pending_statisticsFALSE (11g)使用待定统计信息验证统计信息后再发布SHOW PARAMETER optimizer_use_pending_statistics_optimizer_gather_stats_on_loadTRUE (11g)加载时自动收集统计保持统计信息新鲜隐藏参数查询3.10 其他重要性能参数参数名默认值作用影响查询方式_optimizer_cost_based_transformationTRUE基于成本的查询转换改善复杂查询优化隐藏参数查询_optimizer_null_aware_antijoinTRUE (11g)NULL 感知反连接改善 NOT IN 性能隐藏参数查询_optimizer_use_histogramsTRUE使用直方图改善数据倾斜列估算隐藏参数查询_optimizer_system_stats_usageTRUE使用系统统计信息基于硬件成本的优化隐藏参数查询四、快速检查脚本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 检查所有优化器相关参数SELECT name, value, description FROM v$parameter WHERE name LIKE optimizer% ORDER BY name;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% ORDER BY 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 建议开启ALTER SYSTEM SET optimizer_adaptive_plans TRUE SCOPEBOTH;SQL 计划管理建议开启基线保护ALTER SYSTEM SET optimizer_use_sql_plan_baselines TRUE SCOPEBOTH;5.2 问题排查流程当遇到执行计划突变问题时检查 ACS 状态- 是否意外关闭检查游标状态- 是否有 invalidations检查统计信息- 最近是否更新检查共享池- 是否有空间压力清除游标测试-ALTER SYSTEM FLUSH SHARED_POOL谨慎使用5.3 预防措施定期监控设置定时任务检查关键参数变更管理任何参数修改都应在测试环境验证基线保护对核心 SQL 使用 SPM 基线统计信息避免在业务高峰期自动收集统计信息六、总结这次排查揭示了一个重要问题Oracle 19c 虽然默认开启 ACS但在某些情况下安装时候特意去关闭。自己想当然觉得这个是默认的就没去关注。也许我也没分析对有人如果有更好的分析可以告诉我。