# 🧠 Oracle 19C V$SQLAREA_PLAN_HASH 视图详解
1. 视图概述与作用
V$SQLAREA_PLAN_HASH 是 Oracle 19C 中一个重要的动态性能视图，它提供了按 SQL_ID 和执行计划哈希值（PLAN_HASH_VALUE）聚合的 SQL 执行统计信息。这个视图是性能调优的重要工具，特别适用于分析同一SQL语句不同执行计划的性能差异。
核心作用：
- 执行计划分析：识别同一SQL语句的不同执行计划及其性能特征
- 计划稳定性监控：监控SQL语句执行计划的变化和稳定性
- 性能对比：比较不同执行计划对同一SQL语句的性能影响
- SQL调优验证：验证SQL优化后执行计划的变化和性能改善
- AWR数据访问：提供对AWR历史SQL数据的访问接口
2. 字段含义详解
下表详细说明了 V$SQLAREA_PLAN_HASH 视图中的各个字段：
| 字段名 (Column Name) | 数据类型 (Datatype) | 描述 (Description) |
|---|---|---|
| SQL_ID | VARCHAR2(13) | SQL语句的唯一标识符。基于SQL文本的哈希值。 |
| PLAN_HASH_VALUE | NUMBER | 执行计划的哈希值。唯一标识特定的执行计划。 |
| DBID | NUMBER | 数据库标识符。 |
| INSTANCE_NUMBER | NUMBER | 实例编号（在RAC环境中）。 |
| SNAP_ID | NUMBER | AWR快照ID。标识统计信息所属的快照。 |
| EXECUTIONS_DELTA | NUMBER | 快照期间SQL语句的执行次数变化量。 |
| DISK_READS_DELTA | NUMBER | 快照期间物理读次数的变化量。 |
| BUFFER_GETS_DELTA | NUMBER | 快照期间逻辑读次数的变化量。 |
| ROWS_PROCESSED_DELTA | NUMBER | 快照期间处理行数的变化量。 |
| CPU_TIME_DELTA | NUMBER | 快照期间CPU时间的变化量（微秒）。 |
| ELAPSED_TIME_DELTA | NUMBER | 快照期间总执行时间的变化量（微秒）。 |
| IOWAIT_DELTA | NUMBER | 快照期间I/O等待时间的变化量。 |
| CLWAIT_DELTA | NUMBER | 快照期间集群等待时间的变化量。 |
| APWAIT_DELTA | NUMBER | 快照期间应用等待时间的变化量。 |
| CCWAIT_DELTA | NUMBER | 快照期间并发等待时间的变化量。 |
| PARSE_CALLS_DELTA | NUMBER | 快照期间解析调用次数的变化量。 |
| INVALIDATIONS_DELTA | NUMBER | 快照期间游标失效次数的变化量。 |
| LOADED_VERSIONS_DELTA | NUMBER | 快照期间加载版本数的变化量。 |
| VERSION_COUNT_DELTA | NUMBER | 快照期间版本计数的变化量。 |
| SHARABLE_MEM_DELTA | NUMBER | 快照期间共享内存的变化量。 |
| TOTAL_SQL_AREA | NUMBER | SQL区域的总大小。 |
| SQL_PROFILE | VARCHAR2(64) | SQL配置文件的名称（如果存在）。 |
| SQL_PATCH | VARCHAR2(64) | SQL补丁的名称（如果存在）。 |
| SQL_BASELINE | VARCHAR2(64) | SQL基线的名称（如果存在）。 |
| OPTIMIZER_COST | NUMBER | 优化器估算的执行成本。 |
| OPTIMIZER_MODE | VARCHAR2(32) | 优化器模式。 |
| OPTIMIZER_ENV_HASH_VALUE | NUMBER | 优化器环境哈希值。 |
| PARSING_SCHEMA_NAME | VARCHAR2(128) | 解析该SQL语句的schema名称。 |
| MODULE | VARCHAR2(64) | 执行SQL的应用程序模块名。 |
| ACTION | VARCHAR2(64) | 执行SQL的应用程序动作名。 |
| CON_ID | NUMBER | 容器ID。在多租户环境中标识所属容器。 |
3. 使用场景
V$SQLAREA_PLAN_HASH 在以下场景中非常重要：
- 执行计划稳定性分析：监控同一SQL语句是否产生不同的执行计划
- 性能回归分析：当SQL性能下降时，检查执行计划是否发生变化
- AWR数据分析：分析历史时间段内SQL语句的执行计划变化
- SQL优化验证：验证优化措施（如索引添加、统计信息收集）是否改变了执行计划
- 多计划性能对比：比较同一SQL不同执行计划的性能特征
- 基线合规性检查：检查SQL是否使用正确的执行计划基线
4. 底层原理与相关知识点
4.1 执行计划哈希值原理
PLAN_HASH_VALUE 是执行计划的唯一标识符，基于执行计划的以下特征计算：
- 表访问顺序和连接方法
- 索引使用情况
- 排序和聚合操作
- 并行执行设置
- 优化器环境设置
相同的执行计划会产生相同的PLAN_HASH_VALUE，即使SQL文本稍有不同。
4.2 AWR快照机制
V$SQLAREA_PLAN_HASH 的数据来源于AWR（自动工作负载仓库）快照：
- 快照定期捕获性能数据（默认每小时一次）
- Delta值表示两个快照之间的变化量
- 数据持久化存储在SYSAUX表空间中
4.3 执行计划稳定性因素
影响执行计划稳定性的因素包括：
- 统计信息变化
- 绑定变量窥视
- 优化器参数变更
- 系统负载变化
- 数据库对象变更
5. 相关视图
| 视图名称 | 主要用途描述 |
|---|---|
| DBA_HIST_SQLSTAT | AWR历史SQL统计信息 |
| V$SQL | 当前共享池中的SQL详细信息 |
| V$SQLAREA | SQL语句的汇总统计信息 |
| DBA_HIST_SQL_PLAN | AWR历史执行计划信息 |
| V$SQL_PLAN | 当前共享池中的执行计划 |
| DBA_HIST_SQLTEXT | AWR历史SQL文本 |
6. 基表信息
V$SQLAREA_PLAN_HASH 基于底层的 AWR表构建，主要是：
- WRH$_SQLSTAT：AWR SQL统计信息表
- WRH$_SQL_PLAN：AWR执行计划表
- WRM$_SNAPSHOT：AWR快照元数据表
这些表存储在SYSAUX表空间中，由MMON后台进程维护。
7. 常用查询 SQL
7.1 同一SQL的不同执行计划性能对比
SELECT
sql_id,
plan_hash_value,
sum(executions_delta) as executions,
sum(buffer_gets_delta) as buffer_gets,
sum(disk_reads_delta) as disk_reads,
sum(cpu_time_delta)/1000000 as cpu_sec,
sum(elapsed_time_delta)/1000000 as elapsed_sec,
round(sum(buffer_gets_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))) as avg_gets_per_exec,
round(sum(cpu_time_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))/1000) as avg_cpu_ms_per_exec
FROM
v$sqlarea_plan_hash
WHERE
sql_id = '&sql_id'
AND executions_delta > 0
GROUP BY
sql_id, plan_hash_value
ORDER BY
executions DESC;
7.2 执行计划稳定性分析
SELECT
sql_id,
count(distinct plan_hash_value) as plan_count,
sum(executions_delta) as total_executions,
min(snap_id) as first_snap,
max(snap_id) as last_snap,
round(sum(elapsed_time_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))/1000, 2) as avg_elapsed_ms
FROM
v$sqlarea_plan_hash
WHERE
executions_delta > 100
GROUP BY
sql_id
HAVING
count(distinct plan_hash_value) > 1
ORDER BY
plan_count DESC;
7.3 执行计划性能回归分析
SELECT
sql_id,
plan_hash_value,
to_char(min(begin_interval_time), 'YYYY-MM-DD HH24:MI') as period_start,
to_char(max(end_interval_time), 'YYYY-MM-DD HH24:MI') as period_end,
sum(executions_delta) as executions,
round(sum(elapsed_time_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))/1000, 2) as avg_elapsed_ms,
round(sum(buffer_gets_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))) as avg_gets
FROM
v$sqlarea_plan_hash sph
JOIN
dba_hist_snapshot sn ON sph.snap_id = sn.snap_id AND sph.dbid = sn.dbid
WHERE
sql_id = '&sql_id'
AND executions_delta > 0
GROUP BY
sql_id, plan_hash_value
ORDER BY
period_start;
7.4 高资源消耗执行计划查询
SELECT
sql_id,
plan_hash_value,
sum(executions_delta) as executions,
sum(buffer_gets_delta) as total_buffer_gets,
sum(disk_reads_delta) as total_disk_reads,
sum(cpu_time_delta)/1000000 as total_cpu_sec,
round(sum(buffer_gets_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))) as avg_gets,
round(sum(cpu_time_delta) / decode(sum(executions_delta),0,1,sum(executions_delta))/1000) as avg_cpu_ms
FROM
v$sqlarea_plan_hash
WHERE
executions_delta > 1000
GROUP BY
sql_id, plan_hash_value
HAVING
sum(buffer_gets_delta) / decode(sum(executions_delta),0,1,sum(executions_delta)) > 10000
ORDER BY
avg_gets DESC;
8. 最佳实践与注意事项
- 定期监控：建立定期的执行计划稳定性监控机制
- 性能基线：为关键SQL语句建立性能基线，包括执行计划和性能指标
- 变更管理：任何可能影响执行计划的变更（如统计信息收集、参数调整）都应记录和验证
- AWR保留策略：确保AWR数据有足够的保留期以供历史分析
- SQL计划管理：使用SQL计划基线稳定关键SQL的执行计划
- 多维度分析：结合其他视图（如DBA_HIST_SQL_PLAN）进行深度分析
- 趋势分析：关注执行计划和性能指标的历史趋势，而不仅仅是当前状态
通过正确使用 V$SQLAREA_PLAN_HASH 视图，DBA可以深入了解SQL语句的执行计划行为，及时发现和解决执行计划不稳定导致的性能问题，确保数据库的性能和稳定性。
欢迎关注我的公众号《IT小Chen》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
