AWR中高共享内存消耗的动态采样查询问题排查

数据库出现性能问题，排查时发现存在带OPT_DYN_SAMP标记的动态采样查询。当前dynamic_sampling参数值为2，相关表的统计信息已更新，业务查询本身未包含动态采样hint，AWR报告中也找不到对应的hint。存在以下疑问：

1. 为何会自动生成这类动态采样查询？
2. 高共享内存计数是否会影响SGA内存？具体影响是什么？

查询示例：

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1), 0),
 NVL(SUM(C2), 0), NVL(SUM(C3), 0), COUNT(DISTINCT C4), 
NVL(SUM(CASE WHEN C4 IS NULL THEN 1 ELSE 0 END), 0),
 COUNT(DISTINCT C5), NVL(SUM(CASE WHEN C5 IS NULL THEN 1 ELSE 0 END), 0) 
 FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("table") FULL("table") NO_PARALLEL_INDEX("table") */

一、自动触发动态采样查询的原因 #

尽管dynamic_sampling=2的默认逻辑是仅对未收集统计信息的表触发动态采样，但以下场景仍会导致自动触发：

• 统计信息存在缺失或不准确：即便你认为统计信息已更新，仍可能存在分区表的分区统计信息缺失、全局统计信息过期，或者统计信息粒度不足以支撑复杂查询（如多表关联、复杂过滤条件下的选择性估算）。Oracle优化器判断现有统计信息无法生成最优执行计划时，会自动触发动态采样补充数据。
• 查询包含复杂表达式或函数：若查询中存在自定义函数、复杂CASE表达式、多列组合过滤等，现有统计信息无法准确估算这些条件的返回结果集大小，优化器会启动动态采样获取更精准的基数估算。
• 临时表或物化视图异常：查询涉及临时表（尤其是会话级临时表），或物化视图刷新状态异常时，优化器可能无法获取有效统计信息，进而触发动态采样。
• 隐含参数修改默认逻辑：部分隐含参数（如_optimizer_dynamic_sampling的子参数）可能修改了默认采样规则，即便dynamic_sampling=2，也可能在特定条件下触发采样。

二、高共享内存计数对SGA的影响 #

这里的“高共享内存计数”指共享池中解析过的SQL游标数量过高，主要影响如下：

• SGA内存占用膨胀：每个游标会占用共享池内存（包含SQL文本、执行计划、绑定变量信息等），大量未复用的游标会占满共享池，触发频繁的LRU（最近最少使用）清理，增加CPU开销。
• 硬解析频率上升：共享池内存不足时，新SQL无法找到可复用游标，会触发硬解析，消耗更多CPU和时间，还可能引发library cache pin或library cache lock等等待事件，拖慢数据库性能。
• 性能波动：频繁的内存清理和硬解析会导致数据库响应时间不稳定，高并发场景下可能出现间歇性性能卡顿。
• 极端情况引发OOM风险：若SGA配置内存无法容纳大量游标，极端情况下可能导致数据库进程内存不足，甚至引发实例崩溃。

内容的提问来源于stack exchange，提问作者Baalback