🎉 2.6 OraclePGA信息检查

在 Oracle 11g 中，PGA（Program Global Area）主要用于存放排序、哈希连接、位图合并等操作的内存区域。11g 引入了​自动 PGA 管理​（通过 pga_aggregate_target 参数控制），可以动态调整工作区大小。下面是一套完整的 PGA 信息检查方法。

1. 查看 PGA 参数配置

SHOW PARAMETER pga_aggregate_target

-- 或查询 v$parameter
SELECT name, value, isdefault
FROM v$parameter
WHERE name = 'pga_aggregate_target';

• ​pga_aggregate_target​：目标 PGA 总大小（单位：字节）。如果值为 0，表示手动 PGA 管理。
• ​workarea_size_policy​​：应为 AUTO​（自动）或 MANUAL​。11g 默认 AUTO。

2. PGA 总体使用情况（v$pgastat）

SELECT * FROM v$pgastat;

重点关注以下指标：

3. PGA 自动调优建议（v$pga_target_advice）

SELECT
  pga_target_for_estimate / 1024 / 1024 AS pga_target_mb,
  pga_target_factor,
  estd_pga_cache_hit_percentage,
  estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_factor;

• pga_target_factor=1​ 对应当前 pga_aggregate_target 大小。
• 观察 estd_pga_cache_hit_percentage​ 是否接近 100%，以及 estd_overalloc_count 是否 > 0。
• 如果当前大小下 estd_overalloc_count​ 不为 0，或命中率偏低，建议增大 pga_aggregate_target。

4. PGA 命中率（缓存命中率）

PGA 命中率主要反映排序和哈希操作在内存中完成的比例，直接从 v$pgastat 获取：

SELECT name, value
FROM v$pgastat
WHERE name = 'cache hit percentage';

通常应保持在 ​90% 以上，低于此值表示大量排序/哈希操作使用了临时段（磁盘），需要增加 PGA 或优化 SQL。

也可以从 v$sysstat 计算临时段 I/O 比例：

SELECT
  ROUND(100 * (1 - (physical_reads.value / (logical_reads.value + physical_reads.value))), 2) AS pga_hit_ratio
FROM
  (SELECT VALUE FROM v$sysstat WHERE name = 'physical reads') physical_reads,
  (SELECT VALUE FROM v$sysstat WHERE name = 'session logical reads') logical_reads;

但该公式包含所有 I/O，不够精确，推荐直接使用 v$pgastat 中的命中率。

5. 当前活跃工作区（排序/哈希/位图）情况

SELECT
  operation_type,
  actual_mem_used/1024/1024 AS actual_mem_mb,
  max_mem_used/1024/1024 AS max_mem_mb,
  tempseg_size/1024/1024 AS tempseg_mb,
  sql_id,
  sql_exec_id
FROM v$sql_workarea_active
ORDER BY actual_mem_used DESC;

• operation_type​：SORT​（排序）、HASH-JOIN​（哈希连接）、BITMAP（位图）等。
• actual_mem_used：当前使用的 PGA 内存。
• tempseg_size：如果内存不足，会使用临时段，该值 >0 表示已使用磁盘。

6. PGA 工作区历史分布（v$sql_workarea_histogram）

SELECT
  low_optimal_size/1024/1024 AS low_opt_mb,
  high_optimal_size/1024/1024 AS high_opt_mb,
  optimal_executions,
  onepass_executions,
  multipass_executions
FROM v$sql_workarea_histogram
ORDER BY low_optimal_size;

• optimal_executions：完全在内存中完成的操作次数（最佳）。
• onepass_executions：需要一次磁盘传递的操作。
• multipass_executions：需要多次磁盘传递的操作（应极力避免）。

若 multipass_executions 比例高，说明 PGA 严重不足或 SQL 设计糟糕。

7. 各进程 PGA 内存使用分布

SELECT
  pid,
  spid,
  pga_used_mem/1024/1024 AS used_mb,
  pga_alloc_mem/1024/1024 AS alloc_mb,
  pga_max_mem/1024/1024 AS max_mb
FROM v$process
WHERE pga_used_mem > 0
ORDER BY pga_used_mem DESC
FETCH FIRST 10 ROWS ONLY;   -- 若11g不支持FETCH，用ROWNUM

11g 中需使用 ROWNUM：

SELECT * FROM (
  SELECT
    pid, spid,
    pga_used_mem/1024/1024 AS used_mb,
    pga_alloc_mem/1024/1024 AS alloc_mb,
    pga_max_mem/1024/1024 AS max_mb
  FROM v$process
  WHERE pga_used_mem > 0
  ORDER BY pga_used_mem DESC
)
WHERE ROWNUM <= 10;

8. 检查 PGA 内存是否不足的典型信号

• ​v$pgastat​​ 中 ​over allocation count​​ > 0：PGA 已被过度分配，可能引起性能问题。
• ​v$sql_workarea_histogram​​ 中 ​multipass_executions​​ 比例高：大量操作需要多次磁盘 I/O。
• ​v$process​​ 中 ​pga_max_mem​​ 接近 ​pga_alloc_mem​​ 且远超 ​pga_aggregate_target​​ 设定：说明某些进程占用过高，可能需要限制或优化。
• AWR 报告中“PGA Memory Advisory”部分：会给出建议。

9. 一键检查 PGA 状态脚本（11g 兼容）

SET LINES 200 PAGES 100
COL metric FORMAT A40
COL value FORMAT A20

PROMPT === PGA Parameters ===
SELECT name, value FROM v$parameter WHERE name IN ('pga_aggregate_target','workarea_size_policy');

PROMPT === PGA Statistics (v$pgastat) ===
SELECT name, value FROM v$pgastat WHERE name IN (
  'aggregate PGA target parameter',
  'aggregate PGA auto target',
  'total PGA allocated',
  'total PGA used',
  'maximum PGA allocated',
  'cache hit percentage',
  'over allocation count'
);

PROMPT === PGA Target Advice ===
SELECT pga_target_for_estimate/1024/1024 AS pga_target_mb,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_factor;

PROMPT === Workarea Histogram (last 3 low ranges) ===
SELECT low_optimal_size/1024/1024 AS low_opt_mb,
       high_optimal_size/1024/1024 AS high_opt_mb,
       optimal_executions,
       onepass_executions,
       multipass_executions
FROM v$sql_workarea_histogram
WHERE ROWNUM <= 3
ORDER BY low_optimal_size DESC;

PROMPT === Top 10 PGA Consumers ===
SELECT * FROM (
  SELECT pid, spid, pga_used_mem/1024/1024 AS used_mb,
         pga_alloc_mem/1024/1024 AS alloc_mb,
         pga_max_mem/1024/1024 AS max_mb
  FROM v$process
  WHERE pga_used_mem > 0
  ORDER BY pga_used_mem DESC
) WHERE ROWNUM <= 10;

10. PGA 调优建议

• 优先使用自动管理​：设置 pga_aggregate_target​ 和 workarea_size_policy=AUTO（11g 默认）。
• 大小估算​：通常将 PGA 设为物理内存的 20%_{30%（OLTP）或 50%}70%（OLAP）。
• 监控临时表空间​：如果 PGA 不足，排序/哈希会使用临时表空间，导致 I/O 上升。通过 v$sort_usage 查看临时段使用情况。
• SQL 优化：减少排序和哈希连接操作，或通过索引优化避免大量内存消耗。
• 内存泄露​：部分应用可能未释放会话 PGA，导致 over allocation count 增加，可考虑定期回收资源。

通过以上检查，可以全面掌握 11g 数据库的 PGA 运行状态，及时发现潜在瓶颈。