Oracle内存全面分析(6)：PGA内存结构与配置实战

1. 从一次凌晨告警说起PGA 到底吃掉了多少内存很多 DBA 对 SGA 的调优已经形成肌肉记忆但一碰到 PGA 就有点发怵。原因很简单SGA 是启动时就分配好的共享内存大小基本可控而 PGA 是每个服务进程私有的内存区随会话数、SQL 复杂度、排序和 Hash 操作动态涨落稍不注意就会把物理内存吃穿。我遇到过最典型的一次是某套 OLTP 库在业务高峰期出现ORA-04030: out of process memory when trying to allocate同时操作系统层面swap飙升。查下来不是 SGA 的问题而是 PGA 在自动管理模式下被大量并发排序和 Hash Join 撑爆了。PGAProgram Global Area程序全局区是 Oracle 为每个服务进程创建的非共享内存区一个进程对应一个 PGA只有拥有它的进程才能读写因此内部结构不需要 Latch 保护。它主要包含私有 SQL 区、游标与 SQL 区、会话内存以及排序、Hash Join、Bitmap 操作使用的 SQL 工作区。这篇是 Oracle 内存分析系列的第 6 篇聚焦 PGA 的结构与配置实战。我会从 PGA 组成讲起结合 OLTP 与批处理两类场景给出可复制的参数配置骨架和验证 SQL帮你快速定位 PGA 使用异常并完成调优验证。适合已经会看 AWR、想进一步把 PGA 管明白的 DBA 和运维同学。2. 先把 PGA 的结构和自动管理机制理清楚2.1 PGA 由哪几块组成PGA 分为固定 PGA 和可变 PGAPGA 堆两部分。固定 PGA 大小固定存放原子变量、小数据结构和指向可变区的指针可变 PGA 是一个受管理的内存堆主要包含三块内容私有 SQL 区保存绑定变量值和运行时内存结构每个执行 SQL 的会话都有一份。它又分永久区绑定变量信息游标关闭时释放和运行区执行结束即释放查询类要等所有行 fetch 完或查询取消才释放。游标与 SQL 区由用户进程管理能分配多少私有 SQL 区受OPEN_CURSORS控制默认 50。游标不关永久区就一直占着内存。会话内存保存登录信息等会话变量。专有服务器模式下它是私有的共享服务器模式下它放在 SGA 里共享。真正吃内存的大头是 SQL 工作区服务于排序ORDER BY、GROUP BY、ROLLUP、窗口函数、Hash Join、Bitmap merge、Bitmap create 这几类操作。工作区越大操作越快但内存消耗也越高工作区不够数据就得到临时表空间落盘响应时间明显拉长。2.2 自动管理模式怎么工作9i 之后引入PGA_AGGREGATE_TARGET把所有*_AREA_SIZE参数统一接管。WORKAREA_SIZE_POLICY决定策略默认AUTO即由PGA_AGGREGATE_TARGET管理 PGA设为MANUAL才回到手工调SORT_AREA_SIZE那一套。注意自动管理只管工作区固定 PGA 那部分不受影响。设置PGA_AGGREGATE_TARGET后每个进程的 PGA 还受额外限制串行操作时单进程可用 PGA 为MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)隐含参数_pga_max_size默认 200M并行操作时并行语句可用 PGA 为PGA_AGGREGATE_TARGET * 30% / DOP。10g 之后专有服务器和共享服务器模式下自动管理都生效。2.3 专有服务与共享服务的差异内存区专有服务共享服务会话内存私有PGA共享SGA永久区PGASGASELECT 运行区PGAPGADML/DDL 运行区PGAPGA这张表很关键共享服务器模式下会话内存和永久区跑到了 SGA所以 PGA 压力会小一些但 SGA 要相应留足。判断 PGA 异常前先确认实例用的是哪种服务模式。3. 可复制的 PGA 参数配置骨架3.1 先算目标值Oracle 给过一个经验公式Metalink Note 223730.1OLTP 系统PGA_AGGREGATE_TARGET (物理内存 * 80%) * 20%DSS 系统PGA_AGGREGATE_TARGET (物理内存 * 80%) * 50%。比如 8G 物理内存的 OLTP 库推荐值约(8 * 80%) * 20% 1.28G。注意这只是起点不是终点。真实值要结合V$PGA_TARGET_ADVICE的建议和实际cache hit percentage来定。3.2 配置骨架下面这套配置适合大多数专有服务器模式的 OLTP 库你可以按实际内存调整-- 查看当前设置 SHOW PARAMETER pga_aggregate_target; SHOW PARAMETER workarea_size_policy; -- 设置 PGA 自动管理OLTP 场景物理内存 8G ALTER SYSTEM SET workarea_size_policy AUTO SCOPE BOTH; ALTER SYSTEM SET pga_aggregate_target 1280M SCOPE BOTH; -- 确认生效 SHOW PARAMETER pga_aggregate_target;批处理/DSS 场景可以把目标值调大并适当放开并行-- DSS 场景物理内存 32G目标值约 12G ALTER SYSTEM SET pga_aggregate_target 12G SCOPE BOTH; ALTER SYSTEM SET workarea_size_policy AUTO SCOPE BOTH; -- 并行相关按需调整 SHOW PARAMETER parallel_degree_policy;OPEN_CURSORS也建议一并检查游标开太多会持续占用私有 SQL 区SHOW PARAMETER open_cursors; -- 应用确实需要大量游标时再调大默认 50 对多数场景够用 ALTER SYSTEM SET open_cursors 300 SCOPE BOTH;提示_pga_max_size是隐含参数默认 200M不建议随意修改。它限制单进程 PGA 上限改大了可能让单个进程吃掉过多内存。4. 验证请求与成功结果用视图把 PGA 看透4.1 看整体使用情况V$PGASTAT是 PGA 诊断的第一站累加数据从实例启动开始统计SELECT name, value, units FROM v$pgastat ORDER BY name;重点看几个指标aggregate PGA target parameter是当前目标值aggregate PGA auto target是自动模式下可用于工作区的内存如果它相对目标值太小说明大量 PGA 被 PL/SQL、Java 等组件占用global memory bound是单个工作区可用上限若降到 1M 以下就该考虑加大目标值total PGA allocated是当前实际分配总量短期超过目标值属正常cache hit percentage若为 100%说明所有工作区都拿到了最佳内存低于 100% 说明有操作在落盘。4.2 看建议器给出的目标值V$PGA_TARGET_ADVICE会模拟不同目标值下的性能表现前提是STATISTICS_LEVEL不是BASICSELECT pga_target_for_estimate / 1024 / 1024 AS target_mb, pga_target_factor, estd_pga_cache_hit_percentage, estd_overalloc_count FROM v$pga_target_advice ORDER BY pga_target_for_estimate;挑选estd_pga_cache_hit_percentage接近 100% 且estd_overalloc_count为 0 的最小目标值就是性价比最高的配置。4.3 定位具体是哪条 SQL 在吃内存V$SQL_WORKAREA显示游标使用的工作区信息可以 joinV$SQL找到语句SELECT s.sql_text, w.operation_type, w.policy, w.estimated_optimal_size / 1024 / 1024 AS est_optimal_mb, w.last_memory_used / 1024 / 1024 AS last_used_mb, w.last_execution, w.total_executions, w.optimal_executions, w.onepass_executions, w.multipasses_executions FROM v$sql_workarea w JOIN v$sql s ON s.hash_value w.hash_value AND s.child_number w.child_number WHERE w.policy AUTO ORDER BY w.last_memory_used DESC FETCH FIRST 20 ROWS ONLY;last_execution为OPTIMAL说明内存够用出现ONE PASS或MULTI-PASS就说明工作区不足数据在落盘。multipasses_executions大于 0 的语句是重点优化对象。4.4 看当前活动工作区V$SQL_WORKAREA_ACTIVE提供瞬时信息能抓到正在超额分配或落盘的工作区SELECT sid, operation_type, policy, work_area_size / 1024 / 1024 AS work_area_mb, expected_size / 1024 / 1024 AS expected_mb, actual_mem_used / 1024 / 1024 AS actual_mb, max_mem_used / 1024 / 1024 AS max_mem_mb, number_passes, tempseg_size / 1024 / 1024 AS tempseg_mb FROM v$sql_workarea_active ORDER BY actual_mem_used DESC;当actual_mem_used明显大于expected_size说明内存被超额分配number_passes大于 0 说明发生了落盘。4.5 看进程级 PGA 占用V$PROCESS能直接看到每个进程的 PGA 使用SELECT spid, program, pga_used_mem / 1024 / 1024 AS used_mb, pga_allocated_mem / 1024 / 1024 AS alloc_mb, pga_max_mem / 1024 / 1024 AS max_mb FROM v$process ORDER BY pga_max_mem DESC FETCH FIRST 20 ROWS ONLY;pga_max_mem排在前面的进程就是历史上吃 PGA 最狠的结合program能判断是哪个应用或后台进程。4.6 看排序落盘比例V$SYSSTAT里sorts (memory)和sorts (disk)的比值能快速判断排序是否健康SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk), sorts (rows));sorts (disk)占比过高说明排序区普遍不够要么加大PGA_AGGREGATE_TARGET要么优化 SQL 减少排序量。5. 本篇常见错排查5.1 ORA-04030 进程内存不足报错ORA-04030: out of process memory when trying to allocate通常是 PGA 总量或单进程上限被打满。排查顺序先查V$PGASTAT的total PGA allocated是否远超目标值再查V$SQL_WORKAREA_ACTIVE看是否有工作区在疯狂超额分配最后查V$PROCESS定位具体进程。处理手段是适度加大PGA_AGGREGATE_TARGET同时优化那些MULTI-PASS的 SQL。5.2 cache hit percentage 长期偏低如果V$PGASTAT里cache hit percentage长期低于 90%说明大量工作区在落盘。先用V$PGA_TARGET_ADVICE确认加大目标值能否改善再检查是不是有超大排序或 Hash Join 语句。有时候问题不在 PGA 大小而在 SQL 本身写得让优化器选了糟糕的执行计划。5.3 目标值设了但没生效WORKAREA_SIZE_POLICY如果是MANUALPGA_AGGREGATE_TARGET就不起作用。用SHOW PARAMETER workarea_size_policy确认必要时改成AUTO。另外 9i 在 OpenVMS 上不支持自动管理10g 才支持老环境要注意。5.4 共享服务器模式下 PGA 视图对不上共享服务器模式下会话内存和永久区在 SGAPGA 视图反映的只是工作区那部分。如果按专有服务器的经验去套会误判 PGA 偏小。先确认服务模式再解读视图。5.5 并行查询把 PGA 吃爆并行操作时可用 PGA 为PGA_AGGREGATE_TARGET * 30% / DOPDOP 越高单个并行语句能用的内存越少反而更容易落盘。如果并行语句多要么降低 DOP要么加大目标值别盲目开高并行。6. 把 PGA 管明白从看懂视图开始PGA 调优的核心不是背参数而是建立“看视图—找异常—调参数—再验证”的闭环。日常巡检我习惯先跑一遍V$PGASTAT看cache hit percentage和over allocation count再用V$SQL_WORKAREA捞出MULTI-PASS的语句最后用V$PGA_TARGET_ADVICE确认目标值是否合理。这套流程跑顺了PGA 异常基本都能在告警之前发现。如果你在验证模型或排查接入问题时需要快速对比不同模型的输出可以到 TaoToken 模型对话 直接试需要长期跑编码或 Agent 任务Coding Plan 更合适接入配置和密钥管理在 API Keys 和 接入文档 里都有现成示例API 入口是https://taotoken.net/api。