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

原创于 2025-09-04 12:29:41 发布 · 727 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
好的，我将为您全面、深入地解析 Oracle 19C 数据库中的 V$SQL_MONITOR 动态性能视图。这是 Oracle 数据库实时 SQL 性能监控的核心视图，是诊断长时间运行或高资源消耗 SQL 语句的终极工具。

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

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

V$SQL_MONITOR 动态性能视图是 Oracle 数据库实时 SQL 监控功能的门户。它提供了正在执行或刚刚执行完毕的 SQL 语句的近实时（每秒刷新一次）性能数据。

该功能旨在自动监控那些消耗大量系统资源的 SQL 语句（默认情况下，并行执行的语句或单次执行消耗超过 5 秒 CPU 或 I/O 时间的语句会被自动监控），而无需预先启用跟踪（如 SQL Trace）。

该视图的核心作用在于：

实时性能诊断：无需等待语句执行完成，即可查看其当前的执行进度、资源消耗（CPU、I/O）、并行执行分布等关键指标。
历史分析：对于已完成的语句，它提供了详细的执行历史记录，用于事后分析。
执行计划洞察：它与 V$SQL_PLAN_MONITOR 结合，可以展示监控到的SQL语句的实际执行计划，包括每一步的实际行数（Rows）、实际耗时（Time）等，这与仅基于估算的普通执行计划有天壤之别。
简化调优：提供了一个集成视图，将SQL执行的关键指标集中展示，极大简化了复杂SQL的性能问题诊断过程。

2️⃣ 字段详细含义

V$SQL_MONITOR 包含大量字段，涵盖了SQL监控的各个方面。以下是其关键字段的详细解释：

字段名称	数据类型	含义说明
KEY	NUMBER	监控条目的唯一标识符。用于内部管理。
STATUS	VARCHAR2(10)	SQL 监控的状态。 - `EXECUTING`：正在执行。 - `DONE`：执行完成。 - `QUEUED`：执行已被排队（如受资源管理器限制）。 - `OTHER`：其他状态。
SQL_ID	VARCHAR2(13)	被监控 SQL 语句的 SQL_ID。这是与 `V$SQL`、`V$SQLAREA` 等视图关联的关键字段。
SQL_EXEC_ID	NUMBER	SQL 执行标识符。唯一标识一次具体的SQL执行。同一个 `SQL_ID` 可能被执行多次，每次都有唯一的 `SQL_EXEC_ID`。
SQL_EXEC_START	DATE	该次 SQL 执行开始的准确时间。对于长时间运行的语句，此字段非常重要。
SQL_TEXT	VARCHAR2(1000)	被监控的 SQL 语句的文本（前1000个字符）。
SQL_PLAN_HASH_VALUE	NUMBER	实际执行计划的哈希值。
USERNAME	VARCHAR2(128)	执行该 SQL 的数据库用户名。
SID	NUMBER	正在执行或刚刚执行该语句的会话标识符 (Session ID)。可与 `V$SESSION` 关联。
SERIAL#	NUMBER	会话的序列号。与 `SID` 共同唯一标识一个会话。
PX_MAXDOP	NUMBER	并行执行的最大并行度 (Degree Of Parallelism)。
ELAPSED_TIME	NUMBER	该次执行到目前为止已消耗的总时间（微秒）。对于正在执行的语句，此值会不断增加。
CPU_TIME	NUMBER	该次执行到目前为止已消耗的 CPU 时间（微秒）。
QUEUING_TIME	NUMBER	该语句在队列中等待的时间（微秒）（例如，受资源管理器限制时）。
FETCHES	NUMBER	执行的 Fetch 操作次数。
BUFFER_GETS	NUMBER	累计的逻辑读（一致性读）次数。
DISK_READS	NUMBER	累计的物理读次数。
DIRECT_WRITES	NUMBER	累计的直接路径写次数。
IO_INTERCONNECT_BYTES	NUMBER	通过存储网络传输的总 I/O 字节数。这是衡量真实 I/O 量的关键指标。
PHYSICAL_READ_REQUESTS	NUMBER	物理读请求次数。
PHYSICAL_READ_BYTES	NUMBER	物理读的总字节数。
PHYSICAL_WRITE_REQUESTS	NUMBER	物理写请求次数。
PHYSICAL_WRITE_BYTES	NUMBER	物理写的总字节数。
CON_ID	NUMBER	容器 ID。在多租户环境（CDB）中，标识该行信息所属的容器。

3️⃣ 相关视图与基表

3.1 核心相关视图

V$SQL_PLAN_MONITOR：这是最重要的相关视图。它为 V$SQL_MONITOR 中的每一次 SQL 执行提供了详细的、每步的监控信息。它就像是 V$SQL_PLAN 的监控版本，但包含了每一步的实际行数、实际耗时等运行时数据。两者通过 (SQL_ID, SQL_EXEC_ID) 关联。
```
SELECT plan_line_id, plan_operation, plan_options, output_rows, physical_read_bytes
FROM v$sql_plan_monitor
WHERE sql_id = '&sql_id' AND sql_exec_id = &sql_exec_id
ORDER BY plan_line_id;
```
V$ACTIVE_SESSION_HISTORY (ASH)：V$SQL_MONITOR 的数据会整合到 ASH 中。ASH 提供了更细粒度的采样数据（每秒采样一次），而 V$SQL_MONITOR 提供的是该SQL执行的汇总视图。
V$SQL / V$SQLAREA：提供 SQL 语句的总体执行统计信息。通过 SQL_ID 关联，可以获取该SQL的历史执行情况，与当前监控的执行进行对比。
DBA_HIST_REPORTS：V$SQL_MONITOR 中的信息可以通过 DBMS_SQLTUNE.REPORT_SQL_MONITOR 函数生成超文本（HTML）或文本（TEXT）的主动报告。这些报告可能会被保存到 AWR 历史数据中，并通过此视图查询。

3.2 底层基表与原理

V$SQL_MONITOR 的数据并非直接来源于传统的磁盘基表。其底层机制更为高级：

内存中的实时监控：当一条 SQL 语句满足监控条件时，Oracle 会在内存中（SGA 的一个专门区域）为其创建一个监控条目。该条目是一个轻量级的结构，用于持续收集执行统计信息。
Circular Buffer：监控数据存储在一个循环缓冲区（Circular Buffer） 中。这意味着监控数据量是有限的，旧的、已完成的监控记录会被新的记录覆盖。监控数据的保留时间取决于系统活动和内存大小。
直接访问内存结构：V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 视图本质上是这些内存中实时监控数据结构的直接投影。这也是它们能够提供近实时数据的原因。
AWR 集成：虽然详细的监控数据存在于内存中，但SQL 监控的元数据和高层摘要信息会被快照并保存到 AWR 中（表如 WRM$_SQL_MONITOR）。这使得通过 DBA_HIST_SQL_MONITOR 等视图进行历史查询成为可能。但是，详细的执行计划步骤数据（即 V$SQL_PLAN_MONITOR 的内容）通常不会持久化到 AWR。

因此，可以说 V$SQL_MONITOR 没有传统的“基表”，它的数据直接来源于 SGA 中的实时监控数据结构。

4️⃣ 底层原理与机制

4.1 监控触发机制

SQL 监控不是对所有 SQL 都开启的，它由以下条件自动触发（满足任一即可）：

并行执行：任何并行执行的 SQL 语句会自动被监控。
单次执行消耗超过 5 秒：任何单次执行消耗的 CPU 时间 + 等待 I/O 时间超过 5 秒的语句会被监控。
使用 /*+ MONITOR */ Hint：在 SQL 语句中强制使用 MONITOR Hint 可以显式指示优化器监控该语句，即使它不满足上述条件。
设置 STATISTICS_LEVEL = ALL 或 TYPICAL：此参数必须为 TYPICAL（默认）或 ALL 才能启用自动 SQL 监控功能。

4.2 数据收集与刷新机制

轻量级收集：监控被设计为低开销。它不会记录每一个等待事件或每一次函数调用，而是定期汇总和更新关键指标。
近实时刷新：监控统计数据大约每秒刷新一次。这为DBA提供了“近乎实时”的视图，同时又避免了因过于频繁的更新而带来的性能开销。
执行计划跟踪：监控不仅收集整体统计信息，还跟踪执行计划中每个操作（如 HASH JOIN， TABLE ACCESS FULL）的实际行为，包括每个操作实际处理的行数（OUTPUT_ROWS）、实际耗时、实际物理/逻辑 I/O。这是其最强大的功能之一。

4.3 与 Active Session History (ASH) 的集成

V$SQL_MONITOR 和 ASH 协同工作，提供了不同粒度的性能视图：

V$SQL_MONITOR：提供一次特定SQL执行的汇总视图。“What is the total CPU and I/O for this run?”
V$ACTIVE_SESSION_HISTORY：提供整个系统在每秒的采样视图。“What was the system doing at this exact second?”

ASH 数据可以向下钻取（Drill Down）到具体的 SQL 执行，而 V$SQL_MONITOR 则可以与 ASH 关联，查看该 SQL 执行在运行过程中，在每个时间点上的详细会话等待信息。

5️⃣ 常用查询 SQL

以下是一些用于监控和分析 SQL 性能的实用查询。

查看当前正在被监控的、正在执行的 SQL 语句（实时监控）

SELECT sql_id, sql_exec_id, sql_exec_start, sql_text, 
       username, sid, serial#,
       ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
       ROUND(cpu_time/1000000, 2) AS cpu_sec,
       buffer_gets, disk_reads,
       io_interconnect_bytes,
       physical_read_requests, physical_read_bytes
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY sql_exec_start;

查找最近完成的高资源消耗的 SQL 语句

SELECT sql_id, sql_exec_id, sql_exec_start, 
       ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
       ROUND(cpu_time/1000000, 2) AS cpu_sec,
       buffer_gets, disk_reads,
       io_interconnect_bytes,
       sql_text
FROM v$sql_monitor
WHERE status = 'DONE'
ORDER BY elapsed_time DESC;

获取一次特定 SQL 执行的详细执行计划监控信息

SELECT 
    p.plan_line_id, 
    p.plan_operation, 
    p.plan_options, 
    p.object_name,
    p.output_rows, -- 实际输出行数
    ROUND(p.elapsed_time/1000000, 2) AS step_elapsed_sec,
    p.physical_read_bytes,
    p.physical_write_bytes
FROM v$sql_plan_monitor p
WHERE p.sql_id = '&sql_id'       -- 替换为实际的SQL_ID
  AND p.sql_exec_id = &sql_exec_id -- 替换为实际的SQL_EXEC_ID
ORDER BY p.plan_line_id;

生成 SQL Monitor 主动报告（最强大的功能）

-- 获取HTML格式的报告，可在浏览器中查看，信息最直观全面
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    sql_id => '&sql_id',
    sql_exec_id => &sql_exec_id,
    type => 'HTML',
    report_level => 'ALL'
) AS report
FROM dual;

-- 获取TEXT格式的报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    sql_id => '&sql_id',
    type => 'TEXT'
) AS report
FROM dual;

6️⃣ 使用场景与总结

6.1 典型使用场景

即时性能诊断：当收到系统运行缓慢的报警时，立即查询 V$SQL_MONITOR，快速定位当前正在消耗大量资源的 SQL 语句。
优化长时间运行的批处理作业：针对夜间运行的报表或批处理任务，使用 /*+ MONITOR */ Hint 或等待其自动触发，事后分析其详细执行报告，找到优化点。
验证优化效果：对一条SQL进行优化（如添加索引、修改SQL）后，比较优化前后两次执行的 SQL_MONITOR 报告，直观地确认 ELAPSED_TIME、BUFFER_GETS、PHYSICAL_READ_BYTES 等指标是否下降。
理解并行执行性能：通过监控报告，清晰查看并行执行计划的各个部分（PX SEND/RECEIVE）的时间分布和行数分布，判断是否存在并行执行倾斜（Parallel Execution Skew）等问题。

6.2 重要总结与最佳实践

V$SQL_MONITOR 是 Oracle 提供的功能最强大的实时 SQL 诊断工具，它将执行计划、资源消耗、并行执行等信息整合在一个统一的视图中。
DBMS_SQLTUNE.REPORT_SQL_MONITOR 函数是其灵魂。生成的 HTML 报告提供了图形化的、交互式的分析界面，远比解析原始的视图字段更高效。
监控是自动且低开销的，无需DBA干预。但对于重要的、已知的批处理作业，主动添加 /*+ MONITOR */ Hint 是推荐的做法。
监控数据主要存在于内存中。要保留重要的监控历史记录，应及时生成 HTML 报告并保存，或查询 DBA_HIST_SQL_MONITOR。
结合 V$SQL_PLAN_MONITOR 查看执行计划中每一步的实际行数，是识别优化器估算错误（Cardinality Misestimate）的最佳方法。