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

原创于 2025-08-25 12:06:59 发布 · 743 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来对 Oracle 19C 中的 V$PROCESS_MEMORY 动态性能视图进行一次全面、深入的详解。这个视图是诊断和监控 PGA (Program Global Area) 内存使用明细 的核心工具。

1. 作用与概述

V$PROCESS_MEMORY 动态性能视图提供了每个 Oracle 进程（服务器进程/后台进程）内部详细的内存分配分类统计。它按照 Oracle 内部的内存管理类别，细化了 V$PROCESS 中的 PGA_USED_MEM, PGA_ALLOC_MEM 等聚合信息。

核心作用：深入揭示一个数据库进程的 PGA 内存具体被哪些工作负载所消耗（例如：SQL 执行、PL/SQL 运行、Java 等），从而帮助进行精准的内存故障诊断和性能优化。

2. 使用场景

诊断 PGA 内存泄漏 (Memory Leak)：发现某个进程的 PGA 内存持续增长且不释放时，通过此视图可以定位到是哪个内存类别（如 Other 或 PL/SQL）在异常增长。
分析内存消耗模式：理解特定操作（如大型排序、哈希连接、PL/SQL 数组处理）的内存消耗情况，确认其是否符合预期。
优化高内存消耗的 SQL 或 PL/SQL：识别出消耗大量 SQL 或 PL/SQL 内存的进程，进而找到对应的会话和 SQL 语句进行优化。
深入排查进程异常：当进程因内存问题（如 ORA-4030）失败时，此视图可以提供失败前的内存分配快照，辅助分析原因。

3. 字段含义详解

以下是 V$PROCESS_MEMORY 视图的主要字段及其含义的详细表格。

字段名	类型	含义详解
PID	NUMBER	Oracle 进程标识符：与 `V$PROCESS.PID` 对应，标识哪个数据库进程。
CATEGORY	VARCHAR2(23)	内存类别：这是本视图最关键的字段。它指明了分配的内存的用途。主要类别包括： - SQL：用于 SQL 执行的内存，如排序区 (sort area)、哈希区 (hash area)、位图合并等。 - PL/SQL：用于 PL/SQL 执行的内存，如存储过程中的变量、数组等。 - OLAP：用于 OLAP 分析工作。 - JAVA：用于 Java 虚拟机 (JVM) 和 Java 存储过程。 - Other：不属于以上类别的所有其他内存分配。这是一个需要重点关注的可能出现泄漏的类别。 - Freeable：已分配但当前未使用、可被释放的内存。
ALLOCATED	NUMBER	已分配内存 (字节)：此进程在此类别下当前已分配的内存总量。
USED	NUMBER	已使用内存 (字节)：此进程在此类别下当前已使用的内存大小。`USED` <= `ALLOCATED`。对于大多数类别，`USED` 是更有意义的指标，它反映了实际的数据占用空间。
MAX_ALLOCATED	NUMBER	历史最大分配值 (字节)：自进程启动以来，在此类别下达到的最大已分配内存峰值。这对于评估进程的历史内存需求非常有价值。

4. 相关视图与基表

V$PROCESS：最直接的相关视图。V$PROCESS_MEMORY.PID 与 V$PROCESS.PID 关联，可以获取进程的全局信息（如 SPID, PROGRAM）和总 PGA 信息。V$PROCESS.PGA_USED_MEM 应近似等于 V$PROCESS_MEMORY 中所有类别的 USED 之和。
V$SESSION：通过 V$PROCESS 桥接，可以将会话信息（SID, SERIAL#, USERNAME, SQL_ID）与详细的内存使用情况关联起来。
V$SQL_WORKAREA_ACTIVE：用于监控当前正在执行的 SQL 操作（如排序、哈希）的内存使用情况，其信息可以看作是 V$PROCESS_MEMORY 中 SQL 类别的一个子集和实时细化。
基表 (Underlying Table)：V$PROCESS_MEMORY 的数据来源于实例内存结构。其底层 X $表 * * 通常是 * * ‘ X$ KSMPS` (Kernel Service Memory Process Statistics) 或类似结构。这些 X$ 表是 Oracle 内部用于跟踪进程内存堆（heap）使用情况的结构。同样，强烈不建议直接查询。

5. 底层详细原理与相关知识点

PGA 与 UGA：
- PGA：进程全局区，是服务于单个服务器进程的私有内存区域。它是在操作系统进程启动时分配的。
- UGA：用户全局区，是存储会话特定信息（如会话变量、私有 SQL 区）的内存区域。
- 在专用服务器 (Dedicated Server) 模式下，UGA 位于 PGA 之中。因此，V$PROCESS_MEMORY 实际上包含了 UGA 的内存分配。
- 在共享服务器 (Shared Server) 模式下，UGA 位于 SGA 的 Large Pool 中，而 PGA 只包含进程本身的信息。此时 V$PROCESS_MEMORY 主要反映 PGA 的使用。
内存管理机制：
- Oracle 进程使用复杂的内存管理器来分配和管理 PGA 中的内存。这些管理器将内存组织成多个堆 (Heap)，每个堆用于不同的目的（如 session heap, sql heap, pl/sql heap）。
- V$PROCESS_MEMORY 的 CATEGORY 字段，本质上就是对这各种内存堆的分类聚合视图。例如，“SQL” 类别可能汇总了多个用于 SQL 执行的堆。
工作流程：
1. 一个进程需要内存来执行任务（例如，对查询结果进行排序）。
2. 进程的内存管理器从其 PGA 的相应堆中分配一块内存。
3. 分配成功后，V$PROCESS_MEMORY 中对应类别（如 SQL）的 ALLOCATED 和 USED 值会增加。
4. 当任务完成（排序结束），内存通常不会被立即释放回操作系统，而是标记为可重用（Freeable），或在进程池中保持分配状态以备下次使用。这就是为什么 ALLOCATED 可能远大于 USED。
5. 如果进程需要更多内存但 PGA 已无法满足，它会向操作系统申请更多内存。如果达到 PGA_AGGREGATE_LIMIT 限制，操作将会失败。
与参数的关系：
- PGA_AGGREGATE_TARGET：自动 PGA 内存管理的目标值。V$PROCESS_MEMORY 中所有进程的内存总和受到此目标的间接影响。
- PGA_AGGREGATE_LIMIT：PGA 总量的硬性限制，防止 PGA 内存失控。V$PROCESS_MEMORY 的总分配量绝对不可超过此值。

6. 常用查询 SQL

1. 按进程和类别查看明细内存使用（基础查询）
此查询将进程信息与明细内存使用关联起来。

SELECT p.spid, 
       p.program, 
       p.pid AS ora_pid,
       m.category,
       m.allocated,
       m.used,
       m.max_allocated
FROM v$process p, v$process_memory m
WHERE p.pid = m.pid
ORDER BY p.spid, m.category;

2. 查找当前消耗内存最多的进程和类别
用于快速定位内存消耗的热点。

SELECT p.spid,
       s.sid,
       s.username,
       s.sql_id,
       m.category,
       m.used / 1024 / 1024 AS used_mb,
       m.allocated / 1024 / 1024 AS allocated_mb
FROM v$process p
JOIN v$process_memory m ON p.pid = m.pid
LEFT JOIN v$session s ON p.addr = s.paddr
WHERE m.used > 0
ORDER BY m.used DESC;

3. 监控特定类别（如 Other）的内存使用
Other 类别是排查内存泄漏的重点。

SELECT p.spid,
       p.program,
       s.sid,
       s.username,
       m.allocated,
       m.used,
       m.max_allocated
FROM v$process p
JOIN v$process_memory m ON p.pid = m.pid
LEFT JOIN v$session s ON p.addr = s.paddr
WHERE m.category = 'Other'
  AND m.allocated > 100 * 1024 * 1024 -- 分配超过100MB
ORDER BY m.allocated DESC;

4. 对比已使用和已分配内存，查找内存效率低的进程
如果 ALLOCATED 远大于 USED，说明该进程可能持有大量空闲内存。

SELECT p.spid,
       p.program,
       m.category,
       m.allocated / 1024 / 1024 AS allocated_mb,
       m.used / 1024 / 1024 AS used_mb,
       ROUND((m.allocated - m.used) / GREATEST(m.allocated, 1) * 100, 2) AS waste_percent
FROM v$process p, v$process_memory m
WHERE p.pid = m.pid
  AND m.allocated > 10 * 1024 * 1024 -- 分配超过10MB
  AND (m.allocated - m.used) > 5 * 1024 * 1024 -- 空闲超过5MB
ORDER BY waste_percent DESC;

总结

V$PROCESS_MEMORY 视图是 Oracle DBA 进行 PGA 内存深度诊断 的显微镜。它将 V$PROCESS 中的聚合内存数字分解为具有明确业务语义的类别（SQL, PL/SQL, Other等），使得定位内存问题变得有迹可循。通过结合 V$PROCESS 和 V$SESSION，DBA 可以精准地将异常的内存消耗模式与具体的数据库会话、甚至正在执行的 SQL 语句关联起来，从而有效地解决因内存引起的性能问题、OOM错误和泄漏问题。

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