Oracle 性能监控视图大全

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 性能监控视图分类[1]：

类别	说明
V$SESSION	会话
V$SQL	SQL
V$SYSSTAT	系统统计
V$SESSION_WAIT	等待
V$LOCK	锁
V$LATCH	闩锁
V$SQLAREA	SQL 聚合

---

2. 会话监控

2.1 当前会话

SELECT 
  sid,
  serial#,
  username,
  status,
  program,
  machine,
  osuser,
  logon_time
FROM v$session
WHERE username IS NOT NULL;

2.2 活跃会话

SELECT 
  sid,
  serial#,
  username,
  sql_id,
  event,
  state,
  wait_class,
  seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
  AND username IS NOT NULL;

2.3 阻塞会话

SELECT 
  sid,
  serial#,
  username,
  blocking_session,
  event,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

---

3. SQL 监控

3.1 Top SQL

SELECT 
  sql_id,
  child_number,
  executions,
  elapsed_time / 1000000 AS elapsed_sec,
  cpu_time / 1000000 AS cpu_sec,
  buffer_gets,
  disk_reads,
  rows_processed,
  sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

3.2 SQL 详情

SELECT 
  sql_id,
  sql_text,
  executions,
  parse_calls,
  loads,
  invalidations
FROM v$sqlarea
WHERE sql_id = '&sql_id';

3.3 执行计划

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

---

4. SQL 监控（实时）

4.1 v$sql_monitor

SELECT 
  sql_id,
  status,
  elapsed_time / 1000000 AS elapsed_sec,
  cpu_time / 1000000 AS cpu_sec,
  buffer_gets,
  disk_reads
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY elapsed_time DESC;

4.2 报告

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

---

5. 等待事件

5.1 系统级

SELECT 
  event,
  total_waits,
  time_waited,
  average_wait,
  wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;

5.2 会话级

SELECT 
  s.sid,
  sw.event,
  sw.wait_class,
  sw.wait_time,
  sw.seconds_in_wait
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
  AND s.username IS NOT NULL
  AND sw.wait_class != 'Idle';

5.3 等待类

SELECT 
  wait_class,
  SUM(time_waited) AS total_time,
  COUNT(*) AS events
FROM v$system_event
WHERE wait_class != 'Idle'
GROUP BY wait_class
ORDER BY total_time DESC;

---

6. 系统统计

6.1 关键统计

SELECT name, value 
FROM v$sysstat
WHERE name IN (
  'parse count (total)',
  'parse count (hard)',
  'execute count',
  'user commits',
  'user rollbacks',
  'db block gets',
  'consistent gets',
  'physical reads',
  'physical writes',
  'redo size'
);

6.2 命中率

-- Buffer Cache 命中率
SELECT 
  1 - SUM(decode(name, 'physical reads', value, 0)) /
      SUM(decode(name, 'db block gets', value, 'consistent gets', value, 0)) AS buffer_hit
FROM v$sysstat
WHERE name IN ('physical reads', 'db block gets', 'consistent gets');

---

7. 锁监控

7.1 锁信息

SELECT 
  s.sid,
  s.username,
  l.type,
  l.lmode,
  l.request,
  l.id1,
  l.id2,
  l.block,
  o.object_name
FROM v$lock l, v$session s, dba_objects o
WHERE l.sid = s.sid
  AND l.id1 = o.object_id(+)
ORDER BY l.block DESC;

7.2 锁等待

SELECT * FROM v$session_blockers;
SELECT * FROM v$blocked_sequences;

---

8. 闩锁监控

8.1 Latch 统计

SELECT 
  name,
  gets,
  misses,
  spin_gets,
  sleeps,
  immediate_gets,
  immediate_misses
FROM v$latch
WHERE misses > 0
ORDER BY misses DESC;

8.2 Latch 详情

SELECT 
  name,
  child#,
  gets,
  misses,
  spin_gets
FROM v$latch_children
WHERE name = 'cache buffers chains'
ORDER BY misses DESC
FETCH FIRST 10 ROWS ONLY;

---

9. 文件 I/O

9.1 数据文件

SELECT 
  df.file_name,
  fs.phyrds AS reads,
  fs.phywrts AS writes,
  fs.readtim,
  fs.writetim,
  fs.avgreadtim,
  fs.avgwrtim
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id;

9.2 临时文件

SELECT 
  tf.file_name,
  fs.phyrds,
  fs.phywrts
FROM v$tempstat fs, dba_temp_files tf
WHERE fs.file# = tf.file_id;

---

10. 表空间监控

10.1 使用情况

SELECT 
  ts.tablespace_name,
  ts.size_mb,
  ts.used_mb,
  ts.free_mb,
  ROUND(ts.used_mb / ts.size_mb * 100, 2) AS pct_used
FROM (
  SELECT 
    df.tablespace_name,
    SUM(df.bytes) / 1024 / 1024 AS size_mb,
    SUM(df.bytes - fs.bytes) / 1024 / 1024 AS used_mb,
    SUM(fs.bytes) / 1024 / 1024 AS free_mb
  FROM dba_data_files df, dba_free_space fs
  WHERE df.file_id = fs.file_id(+)
  GROUP BY df.tablespace_name
) ts
ORDER BY pct_used DESC;

10.2 段大小

SELECT 
  owner,
  segment_name,
  segment_type,
  ROUND(bytes / 1024 / 1024, 2) AS mb
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

---

11. 内存监控

11.1 SGA

SELECT * FROM v$sgainfo;
SELECT pool, name, bytes FROM v$sgastat;

11.2 PGA

SELECT 
  name, 
  value / 1024 / 1024 AS mb
FROM v$pgastat;

11.3 进程内存

SELECT 
  spid,
  program,
  PGA_USED_MEM / 1024 / 1024 AS pga_mb,
  PGA_ALLOC_MEM / 1024 / 1024 AS alloc_mb,
  PGA_MAX_MEM / 1024 / 1024 AS max_mb
FROM v$process;

---

12. 长操作

12.1 v$session_longops

SELECT 
  sid,
  serial#,
  opname,
  sofar,
  totalwork,
  ROUND(sofar / totalwork * 100, 2) AS pct,
  time_remaining,
  message
FROM v$session_longops
WHERE sofar < totalwork;

---

13. 系统参数

SELECT 
  name, 
  value, 
  isdefault, 
  isses_modifiable, 
  issys_modifiable
FROM v$parameter
WHERE name LIKE '%&param%'
ORDER BY name;

---

14. 常用脚本

14.1 Top SQL by elapsed

SELECT * FROM (
  SELECT 
    sql_id,
    elapsed_time / 1000000 AS elapsed_sec,
    executions,
    ROUND(elapsed_time / executions / 1000000, 3) AS avg_sec,
    sql_text
  FROM v$sql
  WHERE executions > 0
  ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 10;

14.2 Top SQL by buffer gets

SELECT * FROM (
  SELECT 
    sql_id,
    buffer_gets,
    executions,
    ROUND(buffer_gets / executions, 2) AS gets_per_exec,
    sql_text
  FROM v$sql
  WHERE executions > 0
  ORDER BY buffer_gets DESC
)
WHERE ROWNUM <= 10;

14.3 死锁检测

SELECT 
  s.sid,
  s.username,
  s.program,
  l.type,
  l.lmode,
  l.request,
  l.block
FROM v$lock l, v$session s
WHERE l.sid = s.sid
  AND l.block = 1;

---

15. 最佳实践

1. 定期监控：性能基线
2. Top SQL 优化：性价比
3. 等待事件：瓶颈
4. 锁监控：并发
5. I/O 监控：存储
6. 内存监控：调优
7. AWR 历史对比：趋势
8. ASH 实时：定位
9. 长操作监控：进度
10. 告警阈值：自动化

---

16. 参考资料

[1] Oracle Database Reference 19c, “Dynamic Performance Views” https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/dynamic-performance-views.html