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

Oracle 19c V$ALL_SQL_MONITOR 动态性能视图详解

核心作用

V$ALL_SQL_MONITOR 是 Oracle 数据库实时 SQL 监控的核心视图，主要用于：

1. 实时性能监控：提供正在执行 SQL 语句的实时性能数据
2. 跨实例追踪：在 RAC 环境中监控所有实例的 SQL 执行
3. 执行计划分析：展示 SQL 执行计划的实时进展
4. 资源消耗追踪：监控 CPU、I/O 和并行执行资源使用
5. 长时间操作诊断：识别和优化长时间运行的 SQL 语句

关键特性

• 实时监控：每秒刷新执行进度数据
• RAC感知：显示集群所有实例的 SQL 执行情况
• 详细统计：提供行级处理进度和资源消耗
• 计划对比：比较实际执行与优化器估算的差异
• 自动捕获：监控长时间运行（>5秒）或并行执行的 SQL

---

字段详解（Oracle 19c）

基础标识字段

字段名	数据类型	描述
KEY	VARCHAR2(64)	SQL 监控条目的唯一标识符
SQL_ID	VARCHAR2(13)	SQL 语句的唯一 ID
SQL_EXEC_ID	NUMBER	SQL 执行实例的唯一标识
SQL_EXEC_START	TIMESTAMP	SQL 执行的开始时间
INST_ID	NUMBER	RAC 实例 ID（仅 GV$SQL_MONITOR 有效）

执行状态字段

字段名	数据类型	描述
STATUS	VARCHAR2(19)	执行状态： EXECUTING DONE DONE (ERROR) QUEUED
SQL_PLAN_HASH_VALUE	NUMBER	执行计划的哈希值
SQL_TEXT	VARCHAR2(1000)	SQL 语句的前 1000 字符
SQL_CHILD_ADDRESS	RAW(8)	子游标地址

性能统计字段

字段名	数据类型	描述
ELAPSED_TIME	NUMBER	已用时间（微秒）
CPU_TIME	NUMBER	消耗的 CPU 时间（微秒）
QUEUING_TIME	NUMBER	排队等待时间（微秒）
FETCHES	NUMBER	Fetch 操作次数
BUFFER_GETS	NUMBER	逻辑读次数
DISK_READS	NUMBER	物理读次数
DIRECT_WRITES	NUMBER	直接写次数

并行执行字段

字段名	数据类型	描述
PX_SERVERS_REQUESTED	NUMBER	请求的并行服务器数量
PX_SERVERS_ALLOCATED	NUMBER	实际分配的并行服务器数量
IO_INTERCONNECT_BYTES	NUMBER	RAC 互连传输的字节数
PHYSICAL_READ_REQUESTS	NUMBER	物理读请求次数

执行计划字段

字段名	数据类型	描述
PLAN_OPERATION	VARCHAR2(30)	当前执行的操作（如：TABLE ACCESS）
PLAN_OPTIONS	VARCHAR2(30)	操作选项（如：FULL, BY INDEX ROWID）
PLAN_LINE_ID	NUMBER	执行计划中的行 ID
PLAN_OBJECT_NAME	VARCHAR2(128)	操作对象名称
OUTPUT_ROWS	NUMBER	实际输出的行数
STARTS	NUMBER	操作启动次数

---

基表与底层原理

底层结构：X$SQL_MONITOR（SQL 监控内部表）

数据来源：

1. SQL 执行引擎的实时统计
2. 并行执行协调器
3. 资源管理器统计
4. 优化器执行计划跟踪

工作原理：

1. 当 SQL 满足监控条件时启动跟踪
2. 执行引擎实时更新性能指标
3. 数据写入 SGA 的 SQL 监控区域
4. V$SQL_MONITOR 提供这些数据的可读视图
5. GV$SQL_MONITOR 提供 RAC 全局视图

监控触发条件：

-- 自动监控的SQL类型
SELECT * FROM v$sql_monitor WHERE
  sql_exec_start > SYSDATE - 1/24 AND (
    elapsed_time > 5000000 OR          -- 执行时间>5秒
    px_servers_requested > 0 OR         -- 并行执行
    sql_text LIKE '/*+ MONITOR */%'     -- 手动提示
  );

---

核心使用场景

1. 实时 SQL 监控

SELECT sql_id, sql_text, elapsed_time/1000000 secs
FROM v$sql_monitor 
WHERE status = 'EXECUTING';

2. 并行执行分析

SELECT sql_id, px_servers_requested, px_servers_allocated
FROM gv$sql_monitor
WHERE px_servers_requested > 0;

3. 执行计划进展

SELECT plan_operation, output_rows, starts
FROM v$sql_monitor
WHERE sql_id = 'g8x9j5s2a1b3c';

4. 资源消耗诊断

SELECT sql_id, buffer_gets, disk_reads, cpu_time/1000000 cpu_sec
FROM v$sql_monitor
ORDER BY cpu_time DESC;

5. 长时间操作识别

SELECT sql_id, sql_text, elapsed_time/1000000 secs
FROM v$sql_monitor
WHERE elapsed_time > 30000000; -- >30秒

---

常用查询 SQL 示例

1. 活动 SQL 监控概览

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

2. 并行执行效率分析

SELECT 
  sql_id,
  px_servers_requested AS req,
  px_servers_allocated AS alloc,
  ROUND(io_interconnect_bytes/1048576, 2) interconnect_mb,
  ROUND(elapsed_time/1000000, 2) elapsed_sec,
  ROUND((px_servers_requested - px_servers_allocated) / 
        GREATEST(px_servers_requested, 1) * 100, 2) AS parallel_deficit_pct
FROM gv$sql_monitor
WHERE px_servers_requested > 0;

3. 执行计划进展监控

SELECT 
  sql_id,
  plan_line_id,
  plan_operation || ' ' || plan_options AS operation,
  output_rows,
  starts,
  ROUND(elapsed_time/1000000, 2) op_elapsed_sec
FROM v$sql_monitor
WHERE sql_id = '&sql_id'
ORDER BY plan_line_id;

4. 资源消耗 TOP SQL

SELECT 
  sql_id,
  sql_text,
  ROUND(elapsed_time/1000000, 2) elapsed_sec,
  ROUND(cpu_time/1000000, 2) cpu_sec,
  buffer_gets,
  disk_reads,
  ROUND(buffer_gets/NULLIF(output_rows,0)) AS gets_per_row
FROM v$sql_monitor
WHERE output_rows > 0
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

5. RAC 全局 SQL 监控

SELECT 
  inst_id,
  sql_id,
  COUNT(*) AS active_execs,
  SUM(elapsed_time)/1000000 AS total_elapsed_sec,
  SUM(cpu_time)/1000000 AS total_cpu_sec
FROM gv$sql_monitor
WHERE status = 'EXECUTING'
GROUP BY inst_id, sql_id
ORDER BY total_elapsed_sec DESC;

6. 执行计划与实际对比

SELECT 
  m.plan_line_id,
  m.plan_operation,
  p.operation || ' ' || p.options AS planned_operation,
  m.output_rows AS actual_rows,
  p.cardinality AS estimated_rows,
  ROUND(m.output_rows / NULLIF(p.cardinality,0), 2) AS estimate_ratio
FROM v$sql_monitor m
JOIN v$sql_plan p 
  ON m.sql_id = p.sql_id AND m.plan_line_id = p.id
WHERE m.sql_id = 'g8x9j5s2a1b3c'
  AND m.plan_line_id IS NOT NULL;

---

SQL 监控操作示例

1. 手动监控 SQL

SELECT /*+ MONITOR */ * 
FROM large_table 
WHERE condition = 'value';

2. 生成监控报告

-- 文本报告
SET LONG 1000000
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'g8x9j5s2a1b3c',
  type => 'TEXT') 
FROM DUAL;

-- HTML报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'g8x9j5s2a1b3c',
  type => 'ACTIVE',
  report_level => 'ALL') 
FROM DUAL;

3. 监控历史 SQL

SELECT * 
FROM DBA_HIST_REPORTS 
WHERE component_name = 'sqlmonitor';

---

高级分析技巧

1. 实时执行计划可视化

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => 'g8x9j5s2a1b3c',
  type => 'HTML') 
FROM DUAL;

2. 监控绑定变量

SELECT name, value_string
FROM v$sql_monitor_binds
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND sql_exec_id = 16777216;

3. 并行执行细节

SELECT 
  qcinst_id AS coord_inst,
  qcsid AS coord_sid,
  server_group,
  server_set,
  server#,
  ROUND(elapsed_time/1000000, 2) AS elapsed_sec
FROM v$sql_monitor_sql_plan
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND plan_line_id = 0; -- 并行协调器

4. 监控 I/O 统计

SELECT 
  plan_line_id,
  plan_operation,
  physical_read_requests,
  physical_read_bytes,
  physical_write_requests,
  physical_write_bytes
FROM v$sql_monitor_io
WHERE sql_id = 'g8x9j5s2a1b3c';

---

性能优化实践

1. 识别低效并行执行

SELECT 
  sql_id,
  ROUND(elapsed_time/1000000) elapsed_sec,
  px_servers_allocated AS px,
  ROUND(io_interconnect_bytes/1048576) interconnect_mb,
  ROUND(io_interconnect_bytes / NULLIF(elapsed_time,0) * 1000000) AS mb_per_sec
FROM v$sql_monitor
WHERE px_servers_allocated > 0
  AND io_interconnect_bytes > 104857600 -- >100MB
ORDER BY interconnect_mb DESC;

2. 检测执行计划偏差

SELECT 
  sql_id,
  plan_line_id,
  plan_operation,
  output_rows AS actual,
  (SELECT p.cardinality 
   FROM v$sql_plan p 
   WHERE p.sql_id = m.sql_id 
     AND p.id = m.plan_line_id) AS estimated,
  ROUND(output_rows / NULLIF((
    SELECT p.cardinality 
    FROM v$sql_plan p 
    WHERE p.sql_id = m.sql_id 
      AND p.id = m.plan_line_id),0), 2) AS ratio
FROM v$sql_monitor m
WHERE output_rows > 0
  AND ROWNUM < 11;

3. 资源瓶颈分析

SELECT 
  sql_id,
  ROUND(cpu_time/1000000, 2) AS cpu_sec,
  ROUND(queuing_time/1000000, 2) AS queue_sec,
  ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
  ROUND((elapsed_time - cpu_time - queuing_time)/1000000, 2) AS other_wait_sec
FROM v$sql_monitor
WHERE elapsed_time > 5000000 -- >5秒
ORDER BY other_wait_sec DESC;

---

重要注意事项

1. 权限要求：

   • SELECT_CATALOG_ROLE
   • ADVISOR 权限
   • SELECT ANY DICTIONARY

2. 数据保留：

   • 内存：SQL 执行期间
   • 历史：AWR 快照保留期（默认 8 天）

   -- 查看历史监控
   SELECT * FROM dba_hist_sql_monitor;
   

3. 监控开销：

   • 增加约 1-3% CPU 开销
   • 每个监控会话约 200KB 内存
   • 建议仅监控关键 SQL

4. 配置参数：

   -- 控制SQL监控
   ALTER SYSTEM SET "_sqlmon_max_plan" = 100;      -- 最大监控计划数
   ALTER SYSTEM SET "_sqlmon_threshold" = 1;       -- 监控阈值(秒)
   

5. 与相关视图对比：

   特性	V$SQL_MONITOR	V$SQL	V$SESSION_LONGOPS
   实时性	实时	执行后	实时
   详细程度	非常高	中等	低
   执行计划进展	支持	不支持	部分支持
   并行执行细节	详细	有限	有限

6. RAC 环境：

   • 使用 GV$SQL_MONITOR 查看所有实例
   • 全局资源消耗在 IO_INTERCONNECT_BYTES 字段
   • 并行执行协调器可能在不同实例

最佳实践

-- 创建监控策略
BEGIN
  DBMS_SQL_MONITOR.BEGIN_OPERATION(
    operation_name => 'MONTHLY_REPORT',
    forced_tracking => 'YES');
END;
/

-- 执行需监控的SQL
EXEC monthly_report_proc;

-- 结束监控
BEGIN
  DBMS_SQL_MONITOR.END_OPERATION(
    operation_name => 'MONTHLY_REPORT');
END;
/

-- 查询监控结果
SELECT * 
FROM v$sql_monitor
WHERE sql_text LIKE '%MONTHLY_REPORT%';

通过 V$ALL_SQL_MONITOR，DBA 可以深入洞察 SQL 执行的实时性能特征，识别资源瓶颈，验证执行计划效率，并优化关键业务查询的性能。结合 SQL 监控报告和 AWR 数据，可构建全面的 SQL 性能管理体系。

欢迎关注我的公众号《IT小Chen》