Oracle 内存调优（SGA / PGA）

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

---

1. 概述

Oracle 内存主要分[1]：

• SGA：共享内存
• PGA：私有内存

---

2. SGA 组件

2.1 Buffer Cache

-- 查看
SELECT 
  name, 
  bytes/1024/1024 AS mb
FROM v$sgainfo WHERE name = 'Buffer Cache Size';

-- 命中率
SELECT 
  1 - (physical_reads / (consistent_gets + db_block_gets)) AS hit_ratio
FROM v$buffer_pool_statistics;

2.2 Shared Pool

-- 查看
SELECT bytes/1024/1024 AS mb FROM v$sgainfo WHERE name = 'Shared Pool Size';

-- 库缓存命中率
SELECT 
  SUM(gets - gethits) / SUM(gets) AS miss_rate
FROM v$librarycache;

2.3 Redo Log Buffer

SELECT bytes/1024/1024 AS mb FROM v$sgainfo WHERE name = 'Redo Buffers';

2.4 Large Pool / Java Pool / Streams Pool

SELECT * FROM v$sgainfo;

---

3. SGA 配置

3.1 AMM（自动内存管理）

ALTER SYSTEM SET memory_target = 8G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 16G SCOPE=SPFILE;

3.2 ASMM（自动共享内存管理）

ALTER SYSTEM SET sga_target = 6G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size = 8G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

3.3 手动

ALTER SYSTEM SET db_cache_size = 4G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size = 1G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

---

4. Buffer Cache 调优

4.1 命中率

SELECT 
  1 - SUM(decode(name, 'physical reads', value, 0)) / 
      SUM(decode(name, 'db block gets from cache', value, 0) +
          decode(name, 'consistent gets from cache', value, 0)) AS hit_ratio
FROM v$sysstat
WHERE name IN ('physical reads', 'db block gets from cache', 'consistent gets from cache');
-- 期望 > 95%

4.2 多缓冲池

-- KEEP / RECYCLE / DEFAULT
ALTER SYSTEM SET db_keep_cache_size = 500M SCOPE=SPFILE;
ALTER SYSTEM SET db_recycle_cache_size = 200M SCOPE=SPFILE;

-- 分配
ALTER TABLE small_lookup STORAGE (BUFFER_POOL KEEP);
ALTER TABLE big_history STORAGE (BUFFER_POOL RECYCLE);

4.3 调整大小

-- Buffer Cache Advisory
SELECT 
  size_for_estimate AS mb,
  estd_physical_read_factor AS factor,
  estd_physical_reads AS reads
FROM v$db_cache_advice;
-- factor < 1: 增加有益

---

5. Shared Pool 调优

5.1 库缓存

SELECT 
  namespace,
  gethitratio,
  pinhitratio
FROM v$librarycache;
-- 期望 > 95%

5.2 数据字典缓存

SELECT 
  SUM(gets) AS gets,
  SUM(getmisses) AS misses,
  SUM(getmisses) / SUM(gets) AS miss_ratio
FROM v$rowcache;
-- 期望 < 10%

5.3 共享池大小

-- Shared Pool Advisory
SELECT 
  shared_pool_size_for_estimate AS mb,
  estd_lc_size,
  estd_lc_time_saved
FROM v$shared_pool_advice;

5.4 减少硬解析

-- 1. 绑定变量
-- 2. CURSOR_SHARING
ALTER SYSTEM SET cursor_sharing = FORCE;  -- 谨慎

---

6. Redo Log Buffer 调优

6.1 等待

SELECT event, time_waited 
FROM v$system_event 
WHERE event IN ('log buffer space', 'log file sync');

6.2 调整

ALTER SYSTEM SET log_buffer = 64M SCOPE=SPFILE;
-- 重启生效

---

7. PGA 调优

7.1 查看

SELECT 
  name, 
  value/1024/1024 AS mb
FROM v$pgastat
WHERE name IN ('total PGA allocated', 'maximum PGA allocated', 'over allocation count');

7.2 命中率

SELECT 
  name, 
  value
FROM v$pgastat
WHERE name IN ('cache hit percentage', 'extra bytes read/written');
-- 期望 > 99%

7.3 PGA Advisory

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

7.4 工作区

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;

---

8. 内存建议

8.1 查看建议

SELECT * FROM v$memory_target_advice;
SELECT * FROM v$sga_target_advice;
SELECT * FROM v$pga_target_advice;

8.2 调整

• 增大：减少 I/O
• 减小：避免过度分配
• 平衡：SGA vs PGA

---

9. HugePages

9.1 概述

• 减少页表
• 提升 TLB 命中率
• 大内存推荐

9.2 配置

# Linux
# /etc/sysctl.conf
vm.nr_hugepages = 1024

# sysctl -p

****** Oracle 参数
ALTER SYSTEM SET use_large_pages = ONLY SCOPE=SPFILE;

9.3 查看

grep Huge /proc/meminfo

---

10. 内存诊断

10.1 查看 SGA

SELECT * FROM v$sga;
SELECT * FROM v$sgastat;

10.2 查看进程内存

SELECT 
  pid, 
  serial#,
  program,
  PGA_USED_MEM / 1024 / 1024 AS pga_mb
FROM v$process;

10.3 内存错误

SELECT * FROM v$sysstat WHERE name LIKE '%memory%';

---

11. 常见坑与排错

11.1 ORA-04031: 共享池内存不足

-- 1. 增大 Shared Pool
-- 2. 优化 SQL（绑定变量）
-- 3. FLUSH SHARED POOL（临时）
ALTER SYSTEM FLUSH SHARED_POOL;

11.2 ORA-04036: PGA 内存不足

-- 增大 PGA_AGGREGATE_TARGET
ALTER SYSTEM SET pga_aggregate_target = 4G;

11.3 Buffer Cache 命中率低

-- 1. 增大 Buffer Cache
-- 2. 优化 SQL
-- 3. 检查全表扫描

11.4 内存碎片

-- 查看大对象
SELECT * FROM v$db_object_cache WHERE sharable_mem > 10000000;

---

12. 最佳实践

1. AMM 简化：自动
2. ASMM 精细：控制
3. HugePages：大内存
4. 绑定变量：减少硬解析
5. KEEP 池：小热表
6. RECYCLE 池：大冷表
7. 监控命中率：性能
8. Advisory 指导：调整
9. 平衡 SGA/PGA：综合
10. 定期检查：持续

---

13. 参考资料

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