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

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

核心作用

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

1. 实时执行监控：提供 SQL 执行计划的实时运行时统计
2. 跨实例追踪：在 RAC 环境中监控所有实例的 SQL 执行进度
3. 瓶颈识别：识别执行计划中的性能瓶颈步骤
4. 资源消耗分析：监控 CPU、I/O 和并行资源使用
5. 执行计划验证：比较优化器估算与实际执行差异

关键特性

• 实时刷新：每秒更新执行统计
• 全局视图：RAC 环境中聚合所有实例数据
• 详细步骤统计：提供执行计划每个操作的实际运行时指标
• 并行执行洞察：详细展示并行服务器执行情况
• 历史保留：执行完成后数据保留至监控窗口关闭

---

字段详解 (Oracle 19c)

基础标识字段

字段名	数据类型	描述
INST_ID	NUMBER	RAC 实例 ID
KEY	VARCHAR2(64)	SQL 监控条目的唯一标识符
SQL_ID	VARCHAR2(13)	SQL 语句的唯一 ID
SQL_EXEC_ID	NUMBER	SQL 执行实例的唯一标识
SQL_EXEC_START	TIMESTAMP	SQL 执行的开始时间
SQL_PLAN_HASH_VALUE	NUMBER	执行计划的哈希值
SQL_CHILD_ADDRESS	RAW(8)	子游标地址
STATUS	VARCHAR2(19)	执行状态：EXECUTING, DONE, DONE (ERROR), QUEUED

执行计划步骤字段

字段名	数据类型	描述
PLAN_LINE_ID	NUMBER	执行计划中的行 ID
PLAN_OPERATION	VARCHAR2(30)	执行计划操作 (如：TABLE ACCESS)
PLAN_OPTIONS	VARCHAR2(30)	操作选项 (如：FULL)
PLAN_OBJECT_NAME	VARCHAR2(128)	操作对象名称
PLAN_OBJECT_TYPE	VARCHAR2(20)	对象类型 (如：TABLE)
PLAN_DEPTH	NUMBER	计划步骤的深度
PLAN_PARENT_ID	NUMBER	父步骤 ID

运行时统计字段

字段名	数据类型	描述
OUTPUT_ROWS	NUMBER	实际输出的行数
STARTS	NUMBER	操作启动次数
LAST_REFRESH_TIME	TIMESTAMP	最后一次刷新统计的时间
REFRESH_COUNT	NUMBER	刷新次数
PROCESS_NAME	VARCHAR2(64)	执行该步骤的进程名称 (如：并行从属进程)
ELAPSED_TIME	NUMBER	该步骤消耗的总时间 (微秒)
CPU_TIME	NUMBER	该步骤消耗的 CPU 时间 (微秒)
QUEUING_TIME	NUMBER	排队等待时间 (微秒)
BUFFER_GETS	NUMBER	逻辑读次数
DISK_READS	NUMBER	物理读次数
DIRECT_WRITES	NUMBER	直接写次数
IO_INTERCONNECT_BYTES	NUMBER	RAC 互连传输的字节数 (仅 RAC)

并行执行字段

字段名	数据类型	描述
PX_SERVER#	NUMBER	并行服务器编号
PX_SERVER_GROUP	NUMBER	并行服务器组 ID
PX_SERVER_SET	NUMBER	并行服务器集 ID
PX_QCINST_ID	NUMBER	并行查询协调器实例 ID (仅 RAC)
PX_QCSID	NUMBER	并行查询协调器会话 ID
PX_SERVER_NAME	VARCHAR2(64)	并行服务器名称

---

基表与底层原理

底层结构：X$SQL_PLAN_MONITOR (SQL 监控内部表)

数据来源：

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

工作原理：

1. 当 SQL 满足监控条件时启动跟踪
2. 执行引擎实时更新性能指标
3. 数据写入 SGA 的 SQL 监控区域
4. 并行执行时，从属进程统计发送给协调器
5. V$SQL_PLAN_MONITOR 提供单实例视图
6. GV$SQL_PLAN_MONITOR 提供 RAC 全局视图

监控触发条件：

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

---

核心使用场景

1. 实时执行监控

SELECT plan_line_id, plan_operation, output_rows, elapsed_time/1000000 secs
FROM gv$sql_plan_monitor 
WHERE sql_id = 'g8x9j5s2a1b3c'
AND status = 'EXECUTING';

2. 并行执行分析

SELECT px_server#, process_name, SUM(output_rows) total_rows
FROM gv$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
GROUP BY px_server#, process_name;

3. 瓶颈步骤识别

SELECT plan_line_id, plan_operation, elapsed_time/1000000 secs
FROM v$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

4. 资源消耗分析

SELECT plan_operation, 
       SUM(buffer_gets) logical_reads,
       SUM(disk_reads) physical_reads
FROM gv$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
GROUP BY plan_operation;

5. 估算与实际对比

SELECT m.plan_line_id, m.output_rows actual,
       p.cardinality estimated
FROM v$sql_plan_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';

---

常用查询 SQL 示例

1. 活动 SQL 监控概览

SELECT 
  inst_id,
  sql_id,
  sql_exec_start,
  ROUND(SUM(elapsed_time)/1000000, 2) total_sec,
  COUNT(*) AS plan_lines,
  status
FROM gv$sql_plan_monitor
WHERE status = 'EXECUTING'
GROUP BY inst_id, sql_id, sql_exec_start, status
ORDER BY sql_exec_start;

2. 执行计划步骤详情

SELECT 
  plan_line_id,
  plan_operation || ' ' || plan_options AS operation,
  plan_object_name,
  output_rows,
  starts,
  ROUND(elapsed_time/1000000, 2) elapsed_sec,
  ROUND(cpu_time/1000000, 2) cpu_sec,
  buffer_gets,
  disk_reads
FROM gv$sql_plan_monitor
WHERE sql_id = '&sql_id'
  AND sql_exec_id = &exec_id
ORDER BY plan_line_id;

3. 并行执行负载分析

SELECT 
  inst_id,
  px_server#,
  COUNT(*) AS steps,
  SUM(output_rows) AS total_rows,
  ROUND(SUM(elapsed_time)/1000000, 2) total_sec
FROM gv$sql_plan_monitor
WHERE sql_id = '&sql_id'
  AND px_server# IS NOT NULL
GROUP BY inst_id, px_server#
ORDER BY inst_id, px_server#;

4. 步骤资源消耗 TOP

SELECT 
  plan_operation,
  plan_object_name,
  ROUND(SUM(elapsed_time)/1000000, 2) total_sec,
  SUM(buffer_gets) logical_reads,
  SUM(disk_reads) physical_reads
FROM gv$sql_plan_monitor
WHERE sql_exec_start > SYSDATE - 1/24  -- 最近1小时
GROUP BY plan_operation, plan_object_name
ORDER BY total_sec DESC
FETCH FIRST 10 ROWS ONLY;

5. RAC 全局执行分析

SELECT 
  inst_id,
  COUNT(DISTINCT sql_id) AS active_sql,
  SUM(output_rows) AS total_rows,
  ROUND(SUM(elapsed_time)/1000000, 2) total_sec
FROM gv$sql_plan_monitor
WHERE status = 'EXECUTING'
GROUP BY inst_id
ORDER BY inst_id;

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_plan_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 */ 
       product_id, SUM(quantity_sold) 
FROM sales 
GROUP BY product_id;

2. 生成监控报告

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

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

3. 监控绑定变量

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

4. 历史监控查询

SELECT * 
FROM dba_hist_sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND sql_exec_start > SYSDATE - 7;

---

高级分析技巧

1. 并行执行均衡性检查

SELECT 
  inst_id,
  px_server#,
  ROUND(SUM(elapsed_time)/1000000, 2) total_sec,
  SUM(output_rows) total_rows,
  ROUND(SUM(elapsed_time)/NULLIF(SUM(output_rows),0) micros_per_row
FROM gv$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND px_server# IS NOT NULL
GROUP BY inst_id, px_server#
ORDER BY total_sec DESC;

2. 执行计划树形展示

SELECT 
  LPAD(' ', plan_depth*2) || plan_operation || ' ' || plan_options AS operation_tree,
  plan_object_name,
  output_rows,
  ROUND(elapsed_time/1000000, 2) elapsed_sec
FROM v$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND sql_exec_id = 16777216
CONNECT BY PRIOR plan_line_id = plan_parent_id
START WITH plan_parent_id IS NULL;

3. I/O 性能分析

SELECT 
  plan_operation,
  plan_object_name,
  SUM(disk_reads) AS physical_reads,
  SUM(buffer_gets) AS logical_reads,
  ROUND(SUM(buffer_gets)/NULLIF(SUM(disk_reads),0), 2) AS buffer_ratio
FROM gv$sql_plan_monitor
WHERE disk_reads > 0
GROUP BY plan_operation, plan_object_name
ORDER BY physical_reads DESC;

4. 步骤执行频率分析

SELECT 
  plan_operation,
  AVG(starts) AS avg_starts,
  MAX(starts) AS max_starts,
  MIN(starts) AS min_starts
FROM gv$sql_plan_monitor
WHERE plan_operation IN ('NESTED LOOPS', 'HASH JOIN')
GROUP BY plan_operation;

---

性能优化实践

1. 检测并行执行倾斜

SELECT 
  inst_id,
  px_server#,
  ROUND(100 * RATIO_TO_REPORT(SUM(elapsed_time)) OVER () ,2) AS time_pct
FROM gv$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
  AND px_server# IS NOT NULL
GROUP BY inst_id, px_server#
HAVING MAX(ROUND(100 * RATIO_TO_REPORT(SUM(elapsed_time)) OVER () ,2)) > 30; -- >30%

2. 识别高开销步骤

SELECT 
  plan_line_id,
  plan_operation,
  plan_object_name,
  ROUND(elapsed_time/1000000, 2) elapsed_sec,
  ROUND(100 * elapsed_time / NULLIF(SUM(elapsed_time) OVER (), 0), 2) pct_total
FROM v$sql_plan_monitor
WHERE sql_id = 'g8x9j5s2a1b3c'
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

3. 优化器估算偏差分析

SELECT 
  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) * 100, 2) AS actual_pct
FROM v$sql_plan_monitor m
WHERE output_rows > 1000
  AND ROWNUM < 11;

---

重要注意事项

1. 许可要求：

   • 需要 Oracle Tuning Pack 许可
   • 检查许可状态：SELECT * FROM DBA_FEATURE_USAGE_STATISTICS WHERE FEATURE_INFO LIKE '%SQL Monitor%';

2. 数据保留：

   • 内存：SQL 执行期间 + 短暂保留
   • 历史：AWR 快照保留期 (默认 8 天)

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

3. 监控开销：

   • 增加 1-3% CPU 开销
   • 每个监控会话约 200KB 内存
   • 使用 /*+ MONITOR */ 提示时开销可控

4. 配置参数：

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

5. 与相关视图对比：

   特性	V$SQL_PLAN_MONITOR	V$SQL_PLAN_STATISTICS	V$SQL_MONITOR
   粒度	计划步骤级	计划步骤级	SQL 语句级
   实时性	实时	执行后	实时
   并行细节	详细	有限	中等
   历史数据	AWR 存储	无	AWR 存储

6. 最佳实践：

   -- 创建监控策略
   BEGIN
     DBMS_SQL_MONITOR.BEGIN_OPERATION(
       operation_name => 'BATCH_PROCESS',
       forced_tracking => 'YES');
   END;
   /
   
   -- 执行批处理
   EXEC batch_processing;
   
   -- 结束监控
   BEGIN
     DBMS_SQL_MONITOR.END_OPERATION(
       operation_name => 'BATCH_PROCESS');
   END;
   /
   
   -- 分析监控数据
   SELECT * 
   FROM v$sql_plan_monitor
   WHERE sql_text LIKE '%BATCH_PROCESS%';
   

RAC 环境特殊考虑

1. 使用 GV$SQL_PLAN_MONITOR 而非 V$SQL_PLAN_MONITOR
2. 注意 INST_ID 字段区分实例
3. 全局资源消耗关注 IO_INTERCONNECT_BYTES
4. 并行查询协调器可能在不同实例
5. 集群间通信增加查询开销

通过 V$ALL_SQL_PLAN_MONITOR，DBA 可以深入分析 SQL 执行计划的实时性能特征，识别资源瓶颈，优化关键查询，并在 RAC 环境中确保执行效率。结合 SQL 监控报告和 AWR 数据，可构建全面的 SQL 性能优化体系。

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