面试宝典:介绍下Oracle数据库动态性能视图 V$SQLSTATS_PLAN_HASH

原创于 2025-09-03 12:06:42 发布 · 661 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

Oracle 19C V$SQLSTATS_PLAN_HASH 动态性能视图全面详解

1. 视图概述与核心作用

V$SQLSTATS_PLAN_HASH 是 Oracle 19C 中一个专门用于按执行计划哈希值聚合 SQL 统计信息的动态性能视图。它提供了基于执行计划哈希值分组的 SQL 性能统计，是 SQL 性能调优和执行计划分析的重要工具。

核心作用：

按执行计划哈希值聚合 SQL 性能统计信息
识别同一 SQL 语句不同执行计划的性能差异
分析执行计划变化对性能的影响
支持执行计划稳定性和性能分析
提供执行计划级别的性能监控

2. 主要使用场景

执行计划分析：分析同一 SQL 语句不同执行计划的性能特征
执行计划稳定性监控：监控执行计划是否发生意外变化
性能问题诊断：诊断因执行计划变化导致的性能问题
SQL 调优验证：验证执行计划变更对性能的影响
资源消耗分析：分析不同执行计划的资源消耗模式
A/B 测试比较：比较不同执行计划的性能差异

3. 字段详解

以下是 V$SQLSTATS_PLAN_HASH 视图的主要字段及其详细说明：

字段名	数据类型	含义说明	重要程度
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符	高
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值	高
EXECUTIONS	NUMBER	该执行计划的执行次数	高
FETCHES	NUMBER	获取次数	中
END_OF_FETCH_COUNT	NUMBER	完成获取的次数	中
SORTS	NUMBER	排序次数	中
DISK_READS	NUMBER	磁盘读取次数	高
DIRECT_WRITES	NUMBER	直接写入次数	中
BUFFER_GETS	NUMBER	缓冲区获取次数	高
APPLICATION_WAIT_TIME	NUMBER	应用等待时间	高
CONCURRENCY_WAIT_TIME	NUMBER	并发等待时间	高
CLUSTER_WAIT_TIME	NUMBER	集群等待时间	高
USER_IO_WAIT_TIME	NUMBER	用户 I/O 等待时间	高
PLSQL_EXEC_TIME	NUMBER	PL/SQL 执行时间	中
JAVA_EXEC_TIME	NUMBER	Java 执行时间	中
ROWS_PROCESSED	NUMBER	处理的行数	高
CPU_TIME	NUMBER	CPU 时间（微秒）	高
ELAPSED_TIME	NUMBER	总耗时（微秒）	高
AVG_HARD_PARSE_TIME	NUMBER	平均硬解析时间	中
LAST_ACTIVE_TIME	DATE	最后活动时间	高
CON_ID	NUMBER	容器 ID（多租户环境）	中

4. 相关视图与基表

基表：

V$SQLSTATS_PLAN_HASH 基于内存中的 X$ 表实现，主要是：

X$KGLCURSOR_CHILD_STATS：子游标统计信息的内部表
X$KGLCURSOR：游标信息的内部表
X$KGLTABLE：对象句柄表
X$SQL_PLAN_STATISTICS：执行计划统计信息的内部表

这些 X$ 表是 Oracle 内部数据结构的外部化表示，存储在 SGA 的共享池中。

5. 底层原理与内部机制

数据收集机制：

执行计划哈希值生成：Oracle 为每个执行计划生成唯一的 PLAN_HASH_VALUE
统计信息聚合：SQL 执行统计信息按 SQL_ID 和 PLAN_HASH_VALUE 进行聚合
内存存储：统计信息存储在 SGA 的共享池中的内部数据结构中
实时更新：当 SQL 语句执行时，相关统计信息会实时更新

执行计划哈希值原理：

哈希算法：Oracle 使用特定的哈希算法基于执行计划的结构生成哈希值
唯一性：不同的执行计划会有不同的 PLAN_HASH_VALUE
稳定性：相同的执行计划在不同时间或环境下会有相同的 PLAN_HASH_VALUE
比较基础：PLAN_HASH_VALUE 用于快速比较执行计划是否相同

性能优势：

聚合查询：提供按执行计划分组的聚合查询能力
快速比较：支持快速比较不同执行计划的性能差异
降低开销：相比查询多个子游标，聚合查询开销更低
历史分析：支持执行计划级别的历史性能分析

6. 常用查询SQL

查询1：查找同一SQL的不同执行计划及其性能

SELECT sql_id, plan_hash_value, executions,
       elapsed_time/1000000 total_elapsed_secs,
       cpu_time/1000000 total_cpu_secs,
       buffer_gets, disk_reads,
       rows_processed/executions avg_rows_per_exec
FROM v$sqlstats_plan_hash
WHERE sql_id = '&sql_id'
ORDER BY elapsed_time DESC;

查询2：识别性能最差的执行计划

SELECT sql_id, plan_hash_value,
       elapsed_time/executions/1000000 avg_elapsed_secs,
       cpu_time/executions/1000000 avg_cpu_secs,
       buffer_gets/executions avg_buffer_gets,
       disk_reads/executions avg_disk_reads,
       executions
FROM v$sqlstats_plan_hash
WHERE executions > 10
  AND elapsed_time > 0
ORDER BY avg_elapsed_secs DESC
FETCH FIRST 10 ROWS ONLY;

查询3：分析执行计划的资源消耗模式

SELECT sql_id, plan_hash_value,
       ROUND(buffer_gets/NULLIF(rows_processed,0), 2) buffer_per_row,
       ROUND(cpu_time/NULLIF(rows_processed,0)/1000, 2) cpu_per_row,
       ROUND(elapsed_time/NULLIF(rows_processed,0)/1000, 2) elapsed_per_row,
       rows_processed
FROM v$sqlstats_plan_hash
WHERE rows_processed > 1000
ORDER BY buffer_per_row DESC;

查询4：监控执行计划的变化情况

SELECT sql_id, COUNT(DISTINCT plan_hash_value) plan_count,
       SUM(executions) total_executions,
       MIN(last_active_time) first_seen,
       MAX(last_active_time) last_seen
FROM v$sqlstats_plan_hash
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC;

查询5：比较不同执行计划的效率差异

SELECT sql_id, plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000, 2) total_elapsed_secs,
       ROUND(elapsed_time/executions/1000, 2) avg_elapsed_ms,
       ROUND(buffer_gets/executions) avg_buffer_gets,
       ROUND(disk_reads/executions) avg_disk_reads
FROM v$sqlstats_plan_hash
WHERE sql_id = '&sql_id'
  AND executions > 5
ORDER BY avg_elapsed_ms DESC;

查询6：查找高I/O消耗的执行计划

SELECT sql_id, plan_hash_value,
       disk_reads,
       disk_reads/executions avg_disk_reads,
       buffer_gets,
       buffer_gets/executions avg_buffer_gets,
       executions
FROM v$sqlstats_plan_hash
WHERE disk_reads > 10000
ORDER BY avg_disk_reads DESC;

查询7：多租户环境下的执行计划分析

SELECT con_id, sql_id, plan_hash_value,
       SUM(executions) total_executions,
       SUM(elapsed_time)/1000000 total_elapsed_secs,
       SUM(buffer_gets) total_buffer_gets
FROM gv$sqlstats_plan_hash
WHERE con_id = &container_id
GROUP BY con_id, sql_id, plan_hash_value
HAVING SUM(executions) > 100
ORDER BY total_elapsed_secs DESC;