Oracle 内存相关视图详解

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

---

1. 概述

Oracle 提供丰富的内存视图，用于监控和诊断 SGA/PGA/UGA[1]。

主要视图：

视图	作用
V$SGA	SGA 总体信息
V$SGAINFO	SGA 详细信息
V$SGASTAT	SGA 子组件统计
V$SGA_DYNAMIC_COMPONENTS	SGA 动态组件
V$SGA_DYNAMIC_FREE_MEMORY	SGA 可用空闲内存
V$SGA_RESIZE_OPS	SGA 调整历史
V$SGA_TARGET_ADVICE	SGA 大小建议
V$PGA	PGA 总体（旧版）
V$PGASTAT	PGA 详细统计
V$PGA_TARGET_ADVICE	PGA 大小建议
V$PROCESS	进程级 PGA 使用
V$MEMORY_TARGET_ADVICE	AMM 建议器
V$MEMORY_DYNAMIC_COMPONENTS	AMM 动态组件

---

2. SGA 视图详解

2.1 V$SGA

SELECT * FROM v$sga;
-- NAME              VALUE
-- ---------------- ----------
-- Fixed Size        8666128
-- Variable Size     1879048192
-- Database Buffers  4294967296
-- Redo Buffers      7708672

2.2 V$SGAINFO

SELECT name, bytes/1024/1024 AS mb, resizeable 
FROM v$sgainfo;
-- NAME                       MB  RES
-- -------------------------- --- ---
-- Fixed SGA Size             8   NO
-- Redo Buffers               7   NO
-- Buffer Cache Size          4096 YES
-- In-Memory Area Size        0   NO
-- Shared Pool Size           1792 YES
-- Large Pool Size            256 YES
-- Java Pool Size             128 YES
-- Streams Pool Size          256 YES
-- Shared IO Pool Size        256 YES
-- Data Transfer Cache Size   0   YES
-- Granule Size               16  NO
-- Maximum SGA Size           8192 NO
-- Startup overhead in Shared Pool 192 NO
-- Free SGA Memory Available  0   NO

2.3 V$SGASTAT

-- 各 SGA 子组件详细
SELECT 
  pool,
  name,
  bytes/1024/1024 AS mb
FROM v$sgastat
ORDER BY bytes DESC;

-- 查找 Shared Pool 大对象
SELECT name, bytes/1024/1024 AS mb
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

2.4 V$SGA_DYNAMIC_COMPONENTS

SELECT 
  component,
  current_size/1024/1024 AS cur_mb,
  min_size/1024/1024 AS min_mb,
  max_size/1024/1024 AS max_mb,
  user_specified_size/1024/1024 AS user_mb,
  oper_type,
  oper_mode,
  parameter
FROM v$sga_dynamic_components;

2.5 V$SGA_RESIZE_OPS

-- 最近的 SGA 调整操作
SELECT 
  component,
  oper_type,
  oper_mode,
  parameter,
  initial_size/1024/1024 AS initial_mb,
  target_size/1024/1024 AS target_mb,
  final_size/1024/1024 AS final_mb,
  status,
  to_char(time, 'YYYY-MM-DD HH24:MI:SS') AS op_time
FROM v$sga_resize_ops
ORDER BY time DESC
FETCH FIRST 20 ROWS ONLY;

2.6 V$SGA_TARGET_ADVICE

SELECT 
  sga_size AS size_factor,
  sga_size_factor,
  estd_db_time,
  estd_db_time_factor,
  estd_physical_reads
FROM v$sga_target_advice
ORDER BY sga_size;
-- 找到 estd_db_time 最小且 estd_physical_reads 较低的 SGA 大小

---

3. PGA 视图详解

3.1 V$PGASTAT

SELECT 
  name,
  CASE 
    WHEN unit = 'bytes' THEN ROUND(value/1024/1024, 2)
    ELSE value
  END AS value,
  unit
FROM v$pgastat;

关键指标：

指标	含义
total PGA allocated	当前分配的 PGA 总量
total PGA used	实际使用的 PGA
total PGA inuse	PGA 工作区使用量
total freeable PGA memory	可释放的 PGA
maximum PGA allocated	历史峰值
aggregate PGA target parameter	PGA_AGGREGATE_TARGET
aggregate PGA auto target	自动管理部分
global memory bound	单 Work Area 最大
over allocation count	超分配次数

3.2 V$PROCESS（PGA 信息）

SELECT 
  pid,
  spid,
  program,
  pga_used_mem/1024/1024 AS used_mb,
  pga_alloc_mem/1024/1024 AS alloc_mb,
  pga_freeable_mem/1024/1024 AS freeable_mb,
  pga_max_mem/1024/1024 AS max_mb
FROM v$process
ORDER BY pga_alloc_mem DESC;

3.3 V$PGA_TARGET_ADVICE

SELECT 
  round(pga_target_for_estimate/1024/1024) AS pga_mb,
  pga_target_factor,
  estd_pga_cache_hit_percentage AS hit_pct,
  estd_overalloc_count AS overalloc
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;
-- 选择 hit_pct > 95% 且 overalloc = 0 的最小 PGA

3.4 V$SQL_WORKAREA

-- 高内存消耗的 SQL
SELECT 
  sql_id,
  child_number,
  workarea_address,
  operation_type,
  policy,
  estimated_optimal_size/1024/1024 AS est_opt_mb,
  last_memory_used/1024/1024 AS last_mb,
  last_execution,
  optimal_executions,
  onepass_executions,
  multipasses_executions
FROM v$sql_workarea
WHERE last_memory_used > 100*1024*1024
ORDER BY last_memory_used DESC;

---

4. AMM 视图

4.1 V$MEMORY_TARGET_ADVICE

SELECT 
  memory_size AS size_factor,
  memory_size_factor,
  estd_db_time,
  estd_db_time_factor,
  estd_disk_reads
FROM v$memory_target_advice
ORDER BY memory_size;

4.2 V$MEMORY_DYNAMIC_COMPONENTS

SELECT 
  component,
  current_size/1024/1024 AS cur_mb,
  min_size/1024/1024 AS min_mb,
  max_size/1024/1024 AS max_mb,
  user_specified_size/1024/1024 AS user_mb,
  oper_type,
  parameter
FROM v$memory_dynamic_components;

---

5. UGA 视图

5.1 V$MYSTAT（当前会话统计）

-- 当前会话 UGA/PGA 使用
SELECT 
  s.name,
  m.value/1024/1024 AS mb
FROM v$mystat m
JOIN v$statname s ON m.statistic# = s.statistic#
WHERE s.name IN (
  'session pga memory',
  'session pga memory max',
  'session uga memory',
  'session uga memory max'
);

5.2 V$SESSTAT

-- 所有会话的内存使用
SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.program,
  st.value/1024/1024 AS pga_mb,
  st2.value/1024/1024 AS uga_mb
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid 
  AND st.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session pga memory')
JOIN v$sesstat st2 ON s.sid = st2.sid 
  AND st2.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session uga memory')
WHERE s.username IS NOT NULL
ORDER BY st.value DESC;

---

6. 综合查询示例

6.1 SGA 总览

SELECT 
  'SGA Total' AS component,
  SUM(value)/1024/1024 AS mb
FROM v$sga
UNION ALL
SELECT 
  'PGA Allocated',
  value/1024/1024
FROM v$pgastat
WHERE name = 'total PGA allocated'
UNION ALL
SELECT 
  'PGA Target',
  value/1024/1024
FROM v$pgastat
WHERE name = 'aggregate PGA target parameter';

6.2 Buffer Cache 命中率

SELECT 
  ROUND(
    1 - (phy.value - dir.value - lob.value) / 
        NULLIF(cons.value + db.value, 0), 
    4
  ) * 100 AS hit_pct
FROM 
  v$sysstat phy,
  v$sysstat dir,
  v$sysstat lob,
  v$sysstat cons,
  v$sysstat db
WHERE 
  phy.name = 'physical reads cache'
  AND dir.name = 'physical reads direct'
  AND lob.name = 'physical reads direct (lob)'
  AND cons.name = 'consistent gets'
  AND db.name = 'db block gets';

6.3 Library Cache 命中率

SELECT 
  ROUND(SUM(gethits)/NULLIF(SUM(gets),0)*100, 2) AS hit_pct
FROM v$librarycache;

6.4 排序溢出比例

SELECT 
  ROUND(
    SUM(CASE WHEN name = 'sorts (memory)' THEN value END) / 
    NULLIF(SUM(value), 0) * 100, 
    2
  ) AS mem_sort_pct
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

---

7. 自动监控脚本

-- 综合内存监控
SET LINESIZE 200
SET PAGESIZE 100

PROMPT === SGA 概况 ===
SELECT name, bytes/1024/1024 AS mb FROM v$sgainfo WHERE name IN ('Buffer Cache Size','Shared Pool Size','Large Pool Size','Java Pool Size','Streams Pool Size','Maximum SGA Size');

PROMPT === PGA 概况 ===
SELECT name, ROUND(value/1024/1024, 2) AS mb 
FROM v$pgastat 
WHERE name IN ('total PGA allocated','total PGA used','maximum PGA allocated','aggregate PGA target parameter');

PROMPT === Buffer Cache 命中率 ===
SELECT 
  ROUND(1 - (phy.value - dir.value - lob.value) / NULLIF(cons.value + db.value, 0), 4) * 100 AS hit_pct
FROM v$sysstat phy, v$sysstat dir, v$sysstat lob, v$sysstat cons, v$sysstat db
WHERE phy.name = 'physical reads cache' AND dir.name = 'physical reads direct' 
  AND lob.name = 'physical reads direct (lob)' AND cons.name = 'consistent gets' 
  AND db.name = 'db block gets';

PROMPT === Library Cache 命中率 ===
SELECT ROUND(SUM(gethits)/NULLIF(SUM(gets),0)*100, 2) AS hit_pct
FROM v$librarycache;

PROMPT === 排序内存比例 ===
SELECT 
  ROUND(SUM(CASE WHEN name = 'sorts (memory)' THEN value END) / NULLIF(SUM(value), 0) * 100, 2) AS mem_sort_pct
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

PROMPT === Top PGA 使用会话 ===
SELECT ROWNUM AS rank, program, pga_alloc_mem/1024/1024 AS mb
FROM (SELECT program, pga_alloc_mem FROM v$process ORDER BY pga_alloc_mem DESC)
WHERE ROWNUM <= 10;

---

8. 常见坑与排错

8.1 V$SGASTAT 中 pool 为空

现象：部分项 pool 字段为空。

澄清：Fixed SGA、Redo Buffers 等不属于任何 pool，pool 字段为空。

8.2 PGA 统计不准确

现象：v$pgastat 数值与 v$process.pga_alloc_mem 不一致。

澄清：v$pgastat 是聚合视图，与 v$process 有时间差。

8.3 命中率突然下降

排查：

-- 查看 SQL 执行情况
SELECT sql_id, executions, buffer_gets, disk_reads
FROM v$sql
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

-- 查看 AWR 报告
-- $ORACLE_HOME/rdbms/admin/awrrpt.sql

---

9. 最佳实践

1. 建立基线：正常情况下记录内存指标
2. 定期监控：每日采集关键指标
3. 关注命中率：Buffer Cache > 95%，Library Cache > 95%
4. 关注 PGA：sort(memory) 比例 > 95%
5. 使用 AWR：定期生成报告对比
6. 关注异常波动：突然变化需调查
7. 结合等待事件：内存视图 + 等待事件联合分析

---

10. 参考资料

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

[2] Oracle Database Administrator’s Guide 19c, “Monitoring Memory” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html