Oracle PGA 与排序优化

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

1. 概述

PGA（Program Global Area） 是会话私有内存[1]：

组成：

私有 SQL 区
排序区
会话内存
游标状态

2. PGA 管理

2.1 自动 PGA

ALTER SYSTEM SET pga_aggregate_target = 4G;
ALTER SYSTEM SET pga_aggregate_limit = 8G;  -- 12c+

2.2 手动

ALTER SESSION SET sort_area_size = 1048576;
ALTER SESSION SET sort_area_retained_size = 524288;
ALTER SESSION SET workarea_size_policy = MANUAL;

2.3 查看

SHOW PARAMETER pga
SHOW PARAMETER workarea

SELECT 
  name, 
  value / 1024 / 1024 AS mb
FROM v$pgastat;

3. 排序

3.1 排序来源

ORDER BY
GROUP BY
DISTINCT
UNION/INTERSECT/MINUS
SORT MERGE JOIN
CREATE INDEX
ANALYZE

3.2 内存 vs 磁盘

内存排序：sorts (memory)
磁盘排序：sorts (disk)

3.3 查看

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

3.4 命中率

SELECT 
  SUM(decode(name, 'sorts (memory)', value, 0)) AS mem_sorts,
  SUM(decode(name, 'sorts (disk)', value, 0)) AS disk_sorts,
  ROUND(
    SUM(decode(name, 'sorts (memory)', value, 0)) /
    NULLIF(SUM(decode(name, 'sorts (memory)', value, 'sorts (disk)', value, 0)), 0) * 100,
    2
  ) AS hit_pct
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

4. 工作区

4.1 类型

Optimal：完全内存
One-Pass：内存+一次磁盘
Multi-Pass：多次磁盘（慢）

4.2 查看

SELECT 
  low_optimal_size / 1024 AS low_kb,
  high_optimal_size / 1024 AS high_kb,
  total_executions,
  total_optimal_executions AS optimal,
  total_onepass_executions AS onepass,
  total_multipass_executions AS multipass
FROM v$sql_workarea_histogram
WHERE total_executions > 0;

4.3 PGA Advisory

SELECT 
  pga_target_for_estimate / 1024 / 1024 AS mb,
  estd_pga_cache_hit_percentage AS hit_pct,
  estd_overalloc_count
FROM v$pga_target_advice;

5. 排序优化

5.1 增大 PGA

ALTER SYSTEM SET pga_aggregate_target = 8G;

5.2 索引排序

-- 索引已排序
CREATE INDEX idx_emp_sal ON employees(salary);

SELECT * FROM employees WHERE dept_id = 10 ORDER BY salary;
-- 索引已排序，无需额外排序

5.3 避免不必要排序

-- 1. 索引覆盖
-- 2. 减少 ORDER BY
-- 3. UNION ALL 替代 UNION

5.4 并行排序

SELECT /*+ PARALLEL(4) */ * FROM big_table ORDER BY col;

6. 临时表空间

6.1 查看

SELECT 
  file_name,
  bytes / 1024 / 1024 AS mb,
  autoextensible
FROM dba_temp_files;

6.2 大小

-- 足够大
CREATE TEMPORARY TABLESPACE temp 
  TEMPFILE '/u01/oradata/orcl/temp01.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G;

6.3 多临时文件

ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/orcl/temp02.dbf' SIZE 10G;

7. 排序监控

7.1 活跃排序

SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.program,
  w.operation,
  w.policy,
  w.estimated_optimal_size / 1024 AS est_kb,
  w.last_memory_used / 1024 AS used_kb
FROM v$session s, v$sql_workarea_active w
WHERE s.sid = w.sid;

7.2 临时段使用

SELECT 
  username,
  session_addr,
  sql_id,
  blocks * 8 / 1024 AS mb
FROM v$sort_usage
ORDER BY blocks DESC;

7.3 临时表空间使用

SELECT 
  tablespace_name,
  SUM(bytes_used) / 1024 / 1024 AS used_mb,
  SUM(bytes_free) / 1024 / 1024 AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

8. 大排序场景

8.1 CREATE INDEX

-- 增大 PGA
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 1073741824;  -- 1G

CREATE INDEX idx_big ON big_table(col);

ALTER SESSION SET workarea_size_policy = AUTO;

8.2 大表 GROUP BY

-- 并行
SELECT /*+ PARALLEL(8) */ 
  dept_id, COUNT(*), SUM(salary)
FROM big_table
GROUP BY dept_id;

8.3 大表 JOIN

-- Hash Join
SELECT /*+ USE_HASH(a b) PARALLEL(8) */ 
  a.id, b.name
FROM big_table_a a, big_table_b b
WHERE a.id = b.id;

9. 常见坑与排错

9.1 大量磁盘排序

-- 1. 增大 PGA
ALTER SYSTEM SET pga_aggregate_target = 8G;

-- 2. 优化 SQL
-- 3. 加索引

9.2 临时表空间满

-- 1. 增大
ALTER TABLESPACE temp ADD TEMPFILE '...' SIZE 5G;

-- 2. 查找大用户
SELECT * FROM v$sort_usage ORDER BY blocks DESC;

9.3 ORA-04036: PGA 不足

-- 增大 PGA_AGGREGATE_LIMIT
ALTER SYSTEM SET pga_aggregate_limit = 16G;

10. 最佳实践

自动 PGA 管理：默认
PGA 25% 物理内存：合理
PGA_AGGREGATE_LIMIT：12c+ 防失控
临时表空间足够：避免满
索引排序：减少排序
UNION ALL：避免排序
并行大排序：性能
监控命中率：> 99%
定期 OPTIMIZE：临时段
PGA Advisory：指导

11. 参考资料

[1] Oracle Database Performance Tuning Guide 19c, “PGA Memory” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/memory.html