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. 最佳实践

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

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