一、 PGA 的设计目标与核心思想
- 私有 (Private):
- 核心设计理念： PGA 是每个服务器进程（Server Process）专属的、非共享的内存区域。每个连接到数据库的会话（无论是专用服务器 Dedicated Server 还是共享服务器 Shared Server）都关联一个 PGA。
- 解决的问题： 存储与会话强相关且无需共享的信息，避免在共享内存（SGA）中引入不必要的同步开销（如 Latch）和复杂性。提供会话的独立工作空间。
- 会话上下文 (Session Context):
- 存储会话特有的状态信息和控制数据，如登录信息、会话变量（
NLS设置）、游标状态、堆栈空间等。
- 存储会话特有的状态信息和控制数据，如登录信息、会话变量（
- 工作区 (Work Area):
- 为 SQL 语句执行过程中需要大量内存的操作提供临时工作内存。这是 PGA 最重要的功能之一，直接影响执行效率。
- 主要操作类型：
- 排序 (
ORDER BY,GROUP BY,DISTINCT, 索引创建,UNION等) - 哈希连接 (
HASH JOIN) - 位图合并 (
BITMAP MERGE) - 位图创建 (
BITMAP CREATE) - 批量装载 (
WRITE BUFFERS用于直接路径插入等)
- 排序 (
- 解决的问题： 这些操作需要中间结果集。如果能在内存中完成，速度极快；如果内存不足被迫使用磁盘（临时表空间），性能会急剧下降。
- 数据缓冲 (Data Buffering - 可选):
- 在专用服务器模式 (Dedicated Server) 下，PGA 通常包含一个私有 SQL 区域 (Private SQL Area)，用于存储绑定变量值、查询执行状态信息、以及运行时内存。
- 在共享服务器模式 (Shared Server / MTS) 下，会话的用户全局区 (UGA) - 包含私有 SQL 区域和会话状态 - 通常位于 SGA 的大池 (Large Pool) 中（为了在调度进程间共享），而不是在 PGA 中。此时 PGA 主要服务于共享服务器进程本身的后台数据结构。
- 高效执行： 通过为每个进程提供独立的私有空间，避免了并发访问共享资源的争用，简化了内存管理，提高了特定操作的执行效率。
二、 PGA 的核心原理
- 内存独占性： PGA 内存由操作系统进程分配，仅对该进程可见和可访问。进程结束时，PGA 被操作系统回收。
- 工作区管理：
- 理想操作 (Optimal Execution): SQL 操作所需的内存完全在 PGA 工作区内分配。操作完全在内存中完成，性能最佳。
- 单遍操作 (One-pass Execution): 工作区内存不足容纳整个操作，需要将部分中间数据写入磁盘上的临时表空间，并进行一次额外的读写操作。性能显著下降。
- 多遍操作 (Multi-pass Execution): 工作区严重不足，需要多次读写磁盘上的临时数据。性能急剧恶化，应尽量避免。
- 算法目标： Oracle 优化器根据可用的 PGA 内存和操作的数据量估算，尽量使操作在
OPTIMAL或ONE-PASS模式下运行。
- 自动内存管理： 从 Oracle 9i 开始引入
PGA_AGGREGATE_TARGET参数，实现了 PGA 的自动管理 (Automatic PGA Memory Management - APMM)。- DBA 设置一个实例级的总 PGA 目标大小。
- Oracle 的
*_AREA_SIZE参数（如SORT_AREA_SIZE,HASH_AREA_SIZE）将被忽略（除非设置为最小值限制）。 - 后台进程 MMAN (Memory Manager) 或 MMON (Manageability Monitor) 负责监控所有工作区的内存使用。
- 基于一个复杂的内部算法，Oracle 在所有活动工作区之间动态分配 PGA 内存：
- 优先保证活跃的、对性能影响大的操作（如正在执行的排序/哈希连接）获得足够内存以达到
OPTIMAL模式。 - 对于非活跃或低优先级的工作区，可能会回收其内存。
- 目标是最大化
OPTIMAL执行的比例，最小化磁盘 I/O (写入TEMP表空间)。
- 优先保证活跃的、对性能影响大的操作（如正在执行的排序/哈希连接）获得足够内存以达到
- 优点： 极大简化管理，优化整体性能，适应变化的工作负载。
三、 PGA 的主要组件详解 (Dedicated Server 模式为主)
-
私有 SQL 区域 (Private SQL Area):
- 位置： 每个会话在其 PGA 中都有自己的私有 SQL 区域。
- 功能： 存储特定 SQL 语句的执行状态和运行时内存。
- 关键内容：
- 绑定变量值 (Bind Variables): 当前执行使用的具体值。
- 游标状态 (Cursor State): 游标是打开、解析、执行还是获取状态？当前行指针位置。
- 运行时内存 (Runtime Memory): 处理
WHERE子句中的复杂表达式、函数调用等所需的临时空间。 - 查询执行工作区 (Query Execution Work Areas): 指向实际执行排序、哈希连接等操作所需的工作区的指针（工作区本身是 PGA 的另一部分）。
- 生命周期： 通常与会话或游标的生命周期相关。当游标关闭或会话结束时释放。
SESSION_CACHED_CURSORS参数控制会话可以缓存的已关闭游标的私有 SQL 区域数量，以减少软软解析开销。 - 注意： 与 共享 SQL 区域 (Shared SQL Area) 区别：共享 SQL 区域位于 SGA 的库缓存中，存储的是 SQL 文本、解析树、执行计划等可共享的信息。私有 SQL 区域存储的是特定于会话的执行状态和变量值。
-
SQL 工作区 (SQL Work Areas):
- 功能： 这是 PGA 中最重要、最消耗资源的部分，专门用于执行内存密集型操作。
- 主要类型：
- 排序区 (Sort Area): 用于
ORDER BY,GROUP BY,DISTINCT, 索引创建, 集合操作 (UNION,INTERSECT,MINUS) 等排序操作。 - 哈希区 (Hash Area): 用于哈希连接 (
HASH JOIN) 和哈希聚合 (GROUP BY使用HASH GROUP BY时)。存储哈希表。 - 位图合并区 (Bitmap Merge Area): 用于合并多个位图索引扫描结果 (
BITMAP MERGE)。 - 位图创建区 (Bitmap Create Area): 用于创建位图索引时 (
BITMAP CREATE)。 - 批量装载区 (Write Buffers): 用于直接路径插入 (
INSERT /*+ APPEND */)、并行 DML、外部表访问等操作的缓冲区。
- 排序区 (Sort Area): 用于
- 管理： 如前所述，在 APMM (
PGA_AGGREGATE_TARGET) 下，由 Oracle 自动动态分配和管理大小。操作完成后，工作区内存被释放回 PGA 池。
-
会话内存 (Session Memory):
- 功能： 存储会话的固定信息和控制结构。
- 内容：
- 会话登录信息 (用户身份、权限)。
- 会话设置 (
NLS_LANGUAGE,NLS_TERRITORY,OPTIMIZER_MODE等)。 - 打开数据库链接的信息。
- 会话堆栈空间 (用于 PL/SQL 调用栈、递归 SQL 等)。
- 会话相关的控制块和状态信息。
-
游标区 (Cursor Area - 可视为私有 SQL 区域的一部分):
- 存储显式或隐式打开的游标的状态信息。
四、 PGA 的内存管理
-
管理模式：
- 手动管理 (Manual PGA Management - 9i 之前):
- 通过设置
*_AREA_SIZE参数（如SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE,CREATE_BITMAP_AREA_SIZE）为每个操作指定固定大小的最大工作区。 - 缺点：
- 难以设置：设置太小导致大量磁盘 I/O (
ONE-PASS/MULTI-PASS)，设置太大浪费内存且可能导致 ORA-4030 (PGA 内存不足) 或操作系统交换。 - 不灵活：无法根据系统负载动态调整。
- 不公平：一个消耗大量内存的操作可能独占资源，饿死其他操作。
- 难以设置：设置太小导致大量磁盘 I/O (
- 遗留参数： 在 APMM 下，这些参数通常被忽略，除非用作
_AREA_SIZE参数的最小值下限（如果设置）。
- 通过设置
- 自动 PGA 管理 (APMM - 9i 及以后):
- 设置
PGA_AGGREGATE_TARGET参数。这是实例级的总目标。 - Oracle 自动管理所有工作区的内存分配。
- 强烈推荐使用！ 是主流和最佳实践。
- 设置
- 自动内存管理 (AMM - 11g 及以后):
- 设置
MEMORY_TARGET参数。 - Oracle 自动在 SGA (
SGA_TARGET) 和 PGA (PGA_AGGREGATE_TARGET) 之间动态分配总内存。 - 在 PGA 内部，仍然使用 APMM 机制管理各个工作区。
- 简化整体内存管理。
- 设置
- 手动管理 (Manual PGA Management - 9i 之前):
-
PGA_AGGREGATE_TARGET的设置：- 初始估算： 一个常用的经验法则是：
- 对于 OLTP 系统：
PGA_AGGREGATE_TARGET= (总物理内存* 20%) /活跃并发进程数估算值 - 对于 DSS/数据仓库系统：
PGA_AGGREGATE_TARGET= (总物理内存* 50% - 70%) /活跃并发进程数估算值 - 注意：这仅是起点，需要根据实际监控调整。必须给操作系统和 SGA 留足内存。
- 对于 OLTP 系统：
- 动态调整：
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = nG [SCOPE=MEMORY|SPFILE|BOTH];可以在线调整（SCOPE=MEMORY立即生效但不持久；SCOPE=SPFILE/BOTH需重启生效或持久化）。 - 最大值限制： 单个工作区能获得的最大内存由隐含参数
_PGA_MAX_SIZE控制（通常默认为 200MB），即使PGA_AGGREGATE_TARGET设置很大。这是为了防止单个操作耗尽所有 PGA 资源。在极端 DSS 环境可能需要调整（谨慎！）。
- 初始估算： 一个常用的经验法则是：
-
共享服务器模式 (Shared Server / MTS) 的特殊性：
- 在共享服务器模式下，服务器进程 (Shared Server Processes) 是池化的，服务于多个用户会话。
- UGA 的位置：
- 会话的 UGA (User Global Area) - 包含私有 SQL 区域和会话内存 - 必须位于共享内存中，以便不同的共享服务器进程都能访问到它所服务的会话的上下文。
- 如果配置了
LARGE_POOL_SIZE，UGA 优先放在 SGA 的大池 (Large Pool) 中。 - 如果没有配置大池或大池空间不足，UGA 会放在 共享池 (Shared Pool) 中。
- PGA 的内容： 共享服务器进程的 PGA 相对较小，主要包含该进程自身的固定状态和控制信息，不再包含 UGA。
- 工作区： SQL 工作区（排序区、哈希区等）仍然位于共享服务器进程的 PGA 中。因为它们是进程在执行 SQL 时使用的临时内存，与会话的永久状态（UGA）无关，且不需要在不同进程间共享。
- 关键点： 在共享服务器模式下，
PGA_AGGREGATE_TARGET只统计所有共享服务器进程的 PGA 中的工作区内存，不包括 UGA（UGA 在 SGA 的大池或共享池中统计）。
五、 PGA 的监控与性能优化关键点
-
关键动态性能视图 (V$ Views):
V$PROCESS:PGA_USED_MEM: 该进程 PGA 当前已使用的内存量 (Bytes)。PGA_ALLOC_MEM: 该进程 PGA 当前已分配的内存总量 (Bytes - 包括已使用和空闲但尚未释放给操作系统的)。PGA_MAX_MEM: 该进程 PGA 曾经达到过的最大内存量 (Bytes)。
V$SESSTAT:- 结合
V$STATNAME，查看会话级的 PGA 相关统计：session pga memory: 当前会话使用的 PGA 内存。session pga memory max: 该会话曾经达到的最大 PGA 内存。- 工作区统计：
workarea memory allocated: 会话当前所有活动工作区分配的内存总和。workarea executions - optimal: 以OPTIMAL模式执行的操作次数。workarea executions - onepass: 以ONEPASS模式执行的操作次数。workarea executions - multipass: 以MULTIPASS模式执行的操作次数。重点关注！高值表示严重问题。sorts (memory): 完全在内存中完成的排序次数。sorts (disk): 需要写磁盘 (临时表空间) 的排序次数。重点关注！
- 结合
V$SYSSTAT:- 查看实例级的 PGA 和工作区统计：
total PGA allocated: 实例当前分配给所有进程的 PGA 内存总和。total PGA inuse: 实例当前所有进程 PGA 中实际使用的内存总和。total PGA used for auto workareas: 实例当前所有自动管理工作区使用的内存总和。workarea executions - optimal,- onepass,- multipass(实例级汇总)。sorts (memory),sorts (disk)(实例级汇总)。
- 查看实例级的 PGA 和工作区统计：
V$PGASTAT:- 核心视图！ 提供实例级 PGA 的关键摘要信息：
aggregate PGA target parameter:PGA_AGGREGATE_TARGET的当前值。aggregate PGA auto target: 可用于自动工作区的 PGA 内存量 (≈PGA_AGGREGATE_TARGET- 其他固定的 PGA 开销)。total PGA allocated: 当前分配给所有进程的 PGA 总和。total PGA inuse: 当前所有进程 PGA 中实际使用的内存总和。total freeable PGA memory: 可以被操作系统回收的已分配但未使用的 PGA 内存量。over allocation count: 重要指标！ 实例启动后，PGA 使用量超过PGA_AGGREGATE_TARGET的次数。理想为 0。非 0 表示目标设置过低或存在内存泄漏/失控进程。cache hit percentage: 关键性能指标！ 计算得出：100% * (bytes processed / (bytes processed + extra bytes read/written))。它反映了工作区执行的效率。bytes processed是操作处理的数据量，extra bytes read/written是ONEPASS/MULTIPASS模式产生的额外临时表空间 I/O 量。越高越好，一般应 >90%。maximum PGA allocated: 实例启动后达到的最大total PGA allocated。
- 核心视图！ 提供实例级 PGA 的关键摘要信息：
V$SQL_WORKAREA/V$SQL_WORKAREA_ACTIVE:- 查看当前或历史 SQL 语句的工作区使用情况：
SQL_ID,PLAN_HASH_VALUE: 标识 SQL。OPERATION_TYPE: 操作类型 (SORT, HASH-JOIN, BITMAP, etc.)。POLICY: 管理模式 (AUTO - APMM, MANUAL)。ESTIMATED_OPTIMAL_SIZE: 估算达到OPTIMAL模式所需内存 (Bytes)。ESTIMATED_ONEPASS_SIZE: 估算达到ONEPASS模式所需内存 (Bytes)。LAST_MEMORY_USED: 上次执行实际使用的最大内存 (Bytes)。LAST_EXECUTION: 上次执行模式 (OPTIMAL, ONEPASS, MULTIPASS)。LAST_DEGREE: 并行度。ACTIVE_TIME: (仅V$SQL_WORKAREA_ACTIVE) 工作区已激活的时间 (ms)。WORK_AREA_SIZE: (仅V$SQL_WORKAREA_ACTIVE) 当前分配的工作区大小 (Bytes)。EXPECTED_SIZE: (仅V$SQL_WORKAREA_ACTIVE) Oracle 认为该操作达到OPTIMAL所需大小 (Bytes)。ACTUAL_MEM_USED/MAX_MEM_USED/TEMPSEG_SIZE: (仅V$SQL_WORKAREA_ACTIVE) 内存和临时空间使用详情。
- 查看当前或历史 SQL 语句的工作区使用情况：
V$TEMPSEG_USAGE(关联DBA_TEMP_FILES):- 监控当前正在使用临时表空间的会话和 SQL。可以看到哪个会话/SQL 在写多少临时数据。是诊断
ONEPASS/MULTIPASS问题的直接证据。
- 监控当前正在使用临时表空间的会话和 SQL。可以看到哪个会话/SQL 在写多少临时数据。是诊断
-
优化方向：
- 设置合理的
PGA_AGGREGATE_TARGET:- 监控
V$PGASTAT的cache hit percentage和over allocation count。目标：cache hit percentage > 90%，over allocation count = 0。 - 使用
V$PGA_TARGET_ADVICE视图（基于历史工作负载统计）预测不同PGA_AGGREGATE_TARGET值对cache hit percentage和over allocation count的影响。这是最重要的调优工具。 - 根据建议逐步调整目标值。
- 监控
- 识别并优化高 PGA 消耗的 SQL:
- 使用
V$SQL,V$SQLAREA,V$SQLSTATS结合V$SQL_WORKAREA查找：- 执行次数多且
LAST_EXECUTION为ONEPASS/MULTIPASS的 SQL。 - 磁盘排序 (
sorts (disk)) 或哈希操作多的 SQL。 LAST_MEMORY_USED或ESTIMATED_OPTIMAL_SIZE很大的 SQL。
- 执行次数多且
- 优化手段：
- SQL 重写： 消除不必要的排序 (
DISTINCT,ORDER BY)、减少处理的数据量 (优化WHERE条件、使用更有效的连接方式)。 - 索引优化： 创建合适的索引避免全表扫描和后续的大排序/哈希。考虑索引覆盖查询。
- 调整连接方法： 如果哈希连接导致大量
MULTIPASS，尝试使用嵌套循环连接（如果驱动行集小）或归并排序连接（如果数据已排序）。可使用提示/*+ USE_NL */,/*+ USE_MERGE */引导优化器。 - 增加
_PGA_MAX_SIZE(谨慎！): 对于确实需要处理海量数据的 DSS 查询，如果估算的OPTIMAL大小远超默认 200MB 限制且系统资源充足，可考虑增大此隐含参数，使单个操作能获得更多内存。务必充分测试！
- SQL 重写： 消除不必要的排序 (
- 使用
- 优化临时表空间 I/O:
- 确保临时表空间使用临时文件 (Temp Files) 而非永久数据文件，并放在高性能磁盘（最好独立于数据文件和重做日志文件）上。
- 考虑使用
TEMPORARY TABLESPACE组（如果可用），利用多个临时文件分散 I/O。 - 监控
V$TEMPSEG_USAGE和V$SESSION_WAIT中与临时表空间相关的等待事件 (direct path read temp,direct path write temp,enq: TS - contention等)。
- 审慎使用共享服务器模式：
- 虽然共享服务器能减少进程数，但将 UGA 放入 SGA 会增加共享池/大池的压力和争用风险。
- 现代系统通常内存充足，专用服务器模式是更简单、更常见的选择，除非有明确的连接数限制需求。
- 如果使用共享服务器，务必配置足够大的
LARGE_POOL_SIZE以避免 UGA 侵占共享池。
- 监控失控进程：
- 定期检查
V$PROCESS的PGA_USED_MEM/PGA_ALLOC_MEM/PGA_MAX_MEM，识别异常消耗 PGA 的进程（如存在内存泄漏的 Bug）。 - 结合
V$SESSION和V$SQL定位具体会话和 SQL。
- 定期检查
- 设置合理的
六、 总结
PGA 是 Oracle 数据库中为每个服务器进程分配的私有内存区域，与共享的 SGA 相辅相成。
- 核心功能：
- 存储会话私有状态： 登录信息、会话设置、绑定变量值、游标状态 (私有 SQL 区域)。
- 提供 SQL 工作区： 为内存密集型操作（排序、哈希连接、位图操作、批量装载）提供关键的执行空间。工作区的管理 (
OPTIMAL,ONEPASS,MULTIPASS) 是 PGA 性能的核心。
- 管理核心：
PGA_AGGREGATE_TARGET(APMM): 现代 Oracle 数据库的标准配置。DBA 设定实例级 PGA 总目标，Oracle 自动在所有工作区间动态分配内存，优先保证OPTIMAL执行。- 避免使用过时的手动
*_AREA_SIZE参数管理。
- 共享服务器模式差异： UGA (会话状态) 位于 SGA (大池优先)，工作区仍在共享服务器进程的 PGA 中。
- 监控与优化关键：
V$PGASTAT:cache hit percentage(>90%)over allocation count(=0)V$PGA_TARGET_ADVICE指导目标设置。
V$SQL_WORKAREA/V$SQL_WORKAREA_ACTIVE:- 识别
ONEPASS/MULTIPASS操作的 SQL。 - 查看操作估算和实际内存需求。
- 识别
V$SYSSTAT/V$SESSTAT:sorts (disk),workarea executions - multipass。
V$TEMPSEG_USAGE:- 实时监控临时表空间使用。
- 优化手段：
- 设置合理的
PGA_AGGREGATE_TARGET(基于建议视图)。 - 识别并优化高消耗 SQL (消除不必要操作、优化连接、利用索引)。
- 优化临时表空间 I/O (高性能磁盘、独立存储、临时文件组)。
- 慎用共享服务器，确保大池充足 (如果使用)。
- 设置合理的
理解 PGA 的设计原理和管理机制，对于诊断和解决与会话内存、排序、哈希连接相关的性能瓶颈至关重要。结合 SGA 的知识，才能全面掌握 Oracle 数据库的内存管理体系，构建高性能、可扩展的数据库系统。
评论区