DB / Oracle / 性能诊断

Oracle PGA 自动管理详解

2026.09.06

Oracle PGA 自动管理详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

PGA 自动管理优化会话内存[1]：

详细见：Oracle PGA 与排序优化、Oracle 内存调优 SGA PGA。

2. PGA 组件

2.1 私有 SQL Area

- 绑定变量
- 排序区
- 会话信息

2.2 SQL Work Area

- 排序
- Hash Join
- 位图
- 聚合

2.3 其他

- 会话内存
- 堆栈
- UGA（共享服务器）

3. 自动管理

3.1 启用

ALTER SYSTEM SET pga_aggregate_target = 4G;
ALTER SYSTEM SET pga_aggregate_limit = 8G;
ALTER SYSTEM SET workarea_size_policy = AUTO;  -- 默认

3.2 优势

- 自动调整
- 多会话共享
- 优化

3.3 限制

- pga_aggregate_limit：硬限制
- 单会话限制

4. Work Area

4.1 大小

- 自动调整
- 基于 PGA 目标
- Work Area 大小

4.2 模式

- OPTIMAL：内存完成
- ONEPASS：一次磁盘
- MULTIPASS：多次磁盘

4.3 监控

SELECT operation_type, policy, optimal_executions, onepass_executions, multipass_executions
FROM v$sql_workarea_active;

5. 查看

5.1 视图

SELECT * FROM v$pgastat;
SELECT * FROM v$process_memory;
SELECT * FROM v$process_memory_detail;

5.2 统计

SELECT name, value, unit 
FROM v$pgastat
WHERE name IN (
  'aggregate PGA target parameter',
  'aggregate PGA auto target',
  'global memory bound',
  'total PGA allocated',
  'total PGA used',
  'over allocation count'
);

6. 建议

6.1 PGA 建议

SELECT pga_target_for_estimate, pga_target_factor, 
       bytes_processed, estd_extra_bytes_rw, estd_pga_cache_hit_percentage
FROM v$pga_target_advice;

6.2 解读

- estd_pga_cache_hit_percentage：命中率
- estd_extra_bytes_rw：磁盘读写
- 选择最佳

7. Work Area Advice

SELECT workarea_size, workarea_size_factor, 
       estimated_optimal_executions, estimated_onepass_executions
FROM v$sql_workarea;

8. 排序

8.1 排序区

- sort_area_size（手动）
- 自动管理
- PGA

8.2 磁盘

SELECT name, value FROM v$sysstat 
WHERE name LIKE '%sort%';

8.3 优化

- PGA 充足
- 索引
- 避免

9. Hash Join

9.1 Work Area

- hash_area_size（手动）
- 自动
- PGA

9.2 监控

SELECT operation, options, object_name, 
       optimal_executions, onepass_executions, multipass_executions
FROM v$sql_plan p, v$sql_workarea w
WHERE p.id = w.operation_id;

10. 应用场景

10.1 OLTP

- PGA 小
- 简单查询
- 自动

10.2 OLAP

- PGA 大
- 排序
- Hash Join

10.3 批处理

- 大 Work Area
- 并行
- PGA

11. 监控

11.1 使用

SELECT pid, serial#, program, 
       pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process
ORDER BY pga_alloc_mem DESC;

11.2 会话

SELECT sid, serial#, program, 
       pga_used_mem, pga_alloc_mem
FROM v$session
ORDER BY pga_alloc_mem DESC;

11.3 超限

SELECT * FROM v$pgastat WHERE name = 'over allocation count';

12. 性能

12.1 命中率

- 95%+ OPTIMAL
- ONEPASS 可接受
- 避免 MULTIPASS

12.2 调整

- pga_aggregate_target
- pga_aggregate_limit
- 监控

13. 常见问题

13.1 ORA-04036

- PGA 超限
- pga_aggregate_limit
- 优化

13.2 MULTIPASS

- PGA 不足
- 增加
- 优化 SQL

13.3 性能

- 排序慢
- Hash Join 慢
- PGA

14. 最佳实践

  1. AUTO：自动管理
  2. target + limit：配置
  3. 命中率：监控
  4. advice：建议
  5. Work Area：OPTIMAL
  6. 排序：避免
  7. Hash Join：PGA
  8. 监控：会话
  9. 文档：配置
  10. 演练：定期

15. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “PGA Memory Management” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/