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

原创于 2025-09-04 12:31:05 发布 · 680 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
好的，我将为您全面、深入地解析 Oracle 19C 数据库中的 V$SQL_MONITOR_SESSTAT 动态性能视图。这个视图是 SQL 监控功能体系中的重要组成部分，它提供了被监控 SQL 语句在执行过程中，其相关会话（Session）的详细统计信息变更情况。

🔍 Oracle 19C V$SQL_MONITOR_SESSTAT 动态性能视图详解

1️⃣ 视图概述与核心作用

V$SQL_MONITOR_SESSTAT 动态性能视图记录了被 SQL 监控功能跟踪的 SQL 语句在执行期间，其相关会话的统计信息（Session Statistics）的变更情况。

这些统计信息与 V$SESSTAT 视图中的统计项相同（例如：session logical reads, physical reads, redo size 等），但 V$SQL_MONITOR_SESSTAT 的关键在于它将这些统计信息与特定的 SQL 执行（SQL_EXEC_ID）关联起来，并且记录了该次 SQL 执行所导致的统计信息增量。

该视图的核心作用在于：

精细化资源归属：精确地将系统级的统计信息增长（如逻辑读、物理读）归因到某一次具体的 SQL 执行上，即使该会话同时还在运行其他语句。
并行执行分析：对于并行查询，此视图会为每个并行服务器进程（PX Server）记录各自的统计信息增量。这对于诊断并行执行倾斜（Parallel Execution Skew）问题至关重要。
资源消耗分析：深入理解一条 SQL 语句在运行过程中，在哪些类型的资源上（CPU、I/O、Redo等）产生了多少消耗。
历史性能问题诊断：结合 AWR 历史数据，可以回溯分析过去某次 SQL 执行的确切资源消耗。

2️⃣ 字段详细含义

V$SQL_MONITOR_SESSTAT 视图的字段可以划分为三大类：标识字段、统计元数据字段和统计值字段。

字段名称	数据类型	含义说明
KEY	NUMBER	监控条目的唯一标识符。与 `V$SQL_MONITOR.KEY` 关联，用于内部联接。
SQL_ID	VARCHAR2(13)	被监控的 SQL 语句的 SQL_ID。这是与 `V$SQL_MONITOR` 等视图关联的关键字段。
SQL_EXEC_ID	NUMBER	SQL 执行标识符。唯一标识一次具体的SQL执行。与 `V$SQL_MONITOR.SQL_EXEC_ID` 对应。
SQL_EXEC_START	DATE	该次 SQL 执行开始的准确时间。与 `V$SQL_MONITOR.SQL_EXEC_START` 对应，是关联父视图的重要字段。
SESSION_ID	NUMBER	产生该统计信息的会话的 SID。对于并行查询，此字段表示一个并行服务器进程的 SID。可与 `V$SESSION` 关联。
STATISTIC#	NUMBER	统计信息编号。这是一个关键字段，它与 `V$STATNAME.STATISTIC#` 相对应，用于标识统计信息的类型（例如，逻辑读的编号是 15）。
STATISTIC_NAME	VARCHAR2(64)	统计信息的名称（例如：`session logical reads`, `physical reads`, `redo size`）。由 `STATISTIC#` 派生而来，便于阅读。
VALUE	NUMBER	该次 SQL 执行所导致的统计信息的总增量。这是最核心的字段。它表示从 `SQL_EXEC_START` 到执行结束（或当前时刻），该统计项的增长值。
CON_ID	NUMBER	容器 ID。在多租户环境（CDB）中，标识该行信息所属的容器。值为 `0` 表示该行数据属于 CDB$ROOT（根容器）。

重要说明：VALUE 字段表示的是增量，而不是当前总值。例如，如果会话在执行该SQL前的 session logical reads 是 10000，执行后是 15000，那么此处的 VALUE 就是 5000。

3️⃣ 相关视图与基表

3.1 核心相关视图

V$SQL_MONITOR：这是最直接的父视图。V$SQL_MONITOR_SESSTAT 提供的是某次被监控SQL执行的详细统计信息分解。两者通过 (SQL_ID, SQL_EXEC_ID, SQL_EXEC_START) 或 KEY 字段进行关联。

SELECT sm.sql_id, sm.sql_text, sms.statistic_name, sms.value
FROM v$sql_monitor sm
JOIN v$sql_monitor_sesstat sms ON sm.key = sms.key
WHERE sm.status = 'DONE'
AND sms.statistic_name = 'physical reads'
ORDER BY sms.value DESC;

V$STATNAME：此视图是所有统计信息的字典表。它提供了 STATISTIC# 到 STATISTIC_NAME 的映射关系，以及统计信息的分类。用于理解 V$SQL_MONITOR_SESSTAT 中记录的数字代表什么含义。
```
-- 查看所有可用的会话统计信息
SELECT statistic#, name FROM v$statname ORDER BY statistic#;
```
V$SESSTAT：提供所有当前会话的统计信息瞬时快照。V$SQL_MONITOR_SESSTAT 可以看作是 V$SESSTAT 的“SQL执行级别增量”版本。两者通过 SESSION_ID 和 STATISTIC# 关联。
V$SQL_PLAN_MONITOR：提供SQL执行计划中每个操作（如HASH JOIN, TABLE ACCESS）的监控数据。可以与 V$SQL_MONITOR_SESSTAT 结合分析，判断资源主要消耗在执行计划的哪一步。
DBA_HIST_SQL_MONITOR 与 DBA_HIST_SQL_MONITOR_SESSTAT：AWR 历史快照中的对应视图，用于对过去的 SQL 监控记录进行历史回溯分析。

3.2 底层基表与原理

与 V$SQL_MONITOR 类似，V$SQL_MONITOR_SESSTAT 也是一个动态性能视图，其数据直接来源于 SGA 中为 SQL 监控功能分配的实时内存数据结构。

内存中的统计信息跟踪：当一条 SQL 语句被监控时（满足并行、长运行等条件），Oracle 不仅会为其创建 V$SQL_MONITOR 条目，还会为与该SQL执行相关的每个会话（包括并行服务器进程）初始化一个统计信息跟踪结构。
基准值记录：在 SQL 执行开始时刻（SQL_EXEC_START），Oracle 会记录下相关会话在 V$SESSTAT 中各项统计信息的当前值作为基准值。
增量计算：在 SQL 执行过程中或执行结束时，Oracle 会再次读取 V$SESSTAT 中的当前值，与之前记录的基准值相减，得到该次 SQL 执行导致的统计信息增量，并将其存入监控数据结构。
视图投影：V$SQL_MONITOR_SESSTAT 视图就是这些内存中计算好的增量数据的对外接口。

因此，V$SQL_MONITOR_SESSTAT 没有传统的磁盘基表，它是内存中 SQL 监控基础架构的又一重要组成部分。

4️⃣ 底层原理与机制

4.1 统计信息增量的计算与归属

这是该视图最核心的原理。其设计目标是解决一个经典问题：“如何在一个长时间运行的会话中，准确知道其中某一条SQL语句的资源消耗？”

传统方法的局限：通常，我们会通过比较语句执行前后 V$SESSTAT 的差值来估算。但如果会话在执行该SQL的同时还在执行其他操作，或者SQL本身是并行执行，这种方法就会变得非常不准确且复杂。
SQL Monitor 的解决方案：Oracle SQL Monitor 基础设施在内部自动化了这个过程。它在 SQL 执行的起始点和结束点自动为相关会话打上快照，并计算差值。这个差值就是 VALUE 字段的值，它精确地归属于这次 SQL 执行。

4.2 并行执行与统计信息聚合

对于并行查询，该视图的工作机制更加精彩：

** per-PX Server 统计**：查询协调器（QC）进程和每个并行服务器（PX Server）进程都会在 V$SQL_MONITOR_SESSTAT 中拥有自己独立的记录行。
诊断执行倾斜：通过按 SESSION_ID 和 STATISTIC_NAME 分组查询，可以轻松发现是否存在执行倾斜。例如，如果大部分 physical reads 都集中在一两个 PX Server 上，而其他的很少，这就表明数据分布或任务分配不均匀。
汇总视图：V$SQL_MONITOR 视图中的 BUFFER_GETS, DISK_READS 等字段，本质上就是 V$SQL_MONITOR_SESSTAT 中所有相关会话（QC + PX Servers）的对应统计信息 VALUE 的总和。

4.3 统计信息的类型

V$SQL_MONITOR_SESSTAT 捕获的统计信息类型与 V$SESSTAT 完全一致，主要包括以下几大类：

I/O 相关：session logical reads（逻辑读）, physical reads（物理读）, physical writes（物理写）, physical read bytes, physical write bytes。
Redo 相关：redo size（重做日志大小）。
CPU 相关：虽然不直接记录CPU时间，但高的 session logical reads 通常意味着高的CPU消耗。
其他：parse count, execute count, sorts, 等等。

5️⃣ 常用查询 SQL

以下是一些用于分析 SQL 资源消耗的实用查询。

查看某次特定 SQL 执行消耗最多的资源类型（Top Resource Consumers）

SELECT statistic_name, SUM(value) total_value
FROM v$sql_monitor_sesstat
WHERE sql_id = '&sql_id'
  AND sql_exec_id = &sql_exec_id
GROUP BY statistic_name
ORDER BY total_value DESC;

分析并行查询的执行倾斜（Parallel Execution Skew）

-- 检查各个PX进程的逻辑读分布是否均匀
SELECT session_id, statistic_name, value
FROM v$sql_monitor_sesstat
WHERE sql_id = '&sql_id'
  AND sql_exec_id = &sql_exec_id
  AND statistic_name = 'session logical reads' -- 也可换成 'physical reads' 等
ORDER BY value DESC;

关联 V$SESSION 获取更多会话信息

SELECT sms.session_id, s.username, s.program, sms.statistic_name, sms.value
FROM v$sql_monitor_sesstat sms
JOIN v$session s ON sms.session_id = s.sid
WHERE sms.sql_id = '&sql_id'
  AND sms.sql_exec_id = &sql_exec_id
  AND sms.statistic_name IN ('session logical reads', 'physical reads')
ORDER BY sms.statistic_name, sms.value DESC;

比较两次 SQL 执行的资源消耗差异

-- 假设有两次执行：sql_exec_id 100 和 101
SELECT 
    stat.statistic_name,
    exec100.value AS value_exec_100,
    exec101.value AS value_exec_101,
    exec101.value - exec100.value AS diff
FROM 
    (SELECT statistic_name, value FROM v$sql_monitor_sesstat 
     WHERE sql_id='&sql_id' AND sql_exec_id=100) exec100
FULL OUTER JOIN
    (SELECT statistic_name, value FROM v$sql_monitor_sesstat 
     WHERE sql_id='&sql_id' AND sql_exec_id=101) exec101
ON exec100.statistic_name = exec101.statistic_name
ORDER BY ABS(COALESCE(exec101.value,0) - COALESCE(exec100.value,0)) DESC;

6️⃣ 使用场景与总结

6.1 典型使用场景

精准的资源成本分析：当需要向业务部门报告某个报表或作业的确切资源成本（特别是CPU和I/O）时，使用此视图可以得到精确到单次执行的数据，而不是模糊的会话级估算。
深度性能诊断：当 V$SQL_MONITOR 显示某条SQL效率低下时，使用此视图深入分析资源具体消耗在哪些方面（是逻辑读过高还是物理读爆炸？是否产生了大量Redo？）。
并行查询调优：识别并解决并行执行倾斜问题。如果发现资源集中在一两个PX进程上，可能需要对表进行重分区（使用更适合连接键的分区策略）或使用DYNAMIC_SAMPLING帮助优化器生成更好的并行计划。
优化验证：在对SQL进行优化（如添加索引、修改SQL写法）后，对比优化前后两次执行的统计信息增量，量化优化成果（例如，物理读下降了80%）。

6.2 重要总结与最佳实践

V$SQL_MONITOR_SESSTAT 是 SQL Monitor 功能的“资源消耗计量器”，它提供了 V$SQL_MONITOR 中汇总数据的详细分解。
其核心字段 VALUE 表示的是增量，这使其数据非常精确和纯净，只反映一次执行的影响。
它是诊断并行执行倾斜问题的利器，通过对比不同 SESSION_ID（PX Servers）的同一统计项值，可以一目了然地发现问题。
使用此视图时，必须与 V$SQL_MONITOR 和 V$STATNAME 视图关联查询，才能获得完整、可读的信息。
数据存在于内存中，对于重要的执行记录，应及时查询或生成报告进行保存。