Oracle 内存优化实战

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

1. 概述

Oracle 内存优化实战[1]：

主要区域：

2. SGA 优化

2.1 Buffer Cache

-- 大小
ALTER SYSTEM SET db_cache_size = 8G;

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

2.2 Shared Pool

-- 大小
ALTER SYSTEM SET shared_pool_size = 2G;

-- Library Cache 命中率
SELECT SUM(gets - getmisses) / NULLIF(SUM(gets), 0) AS lib_hit
FROM v$librarycache;
-- 目标 > 99%

-- Dictionary Cache
SELECT SUM(gets - getmisses) / NULLIF(SUM(gets), 0) AS dict_hit
FROM v$rowcache;

2.3 Redo Log Buffer

-- 大小
ALTER SYSTEM SET log_buffer = 67108864 SCOPE=SPFILE;  -- 64M

-- 等待
SELECT event, total_waits FROM v$system_event 
WHERE event IN ('log file sync', 'log buffer space');

2.4 Large Pool

-- RMAN/并行
ALTER SYSTEM SET large_pool_size = 512M;

2.5 Java Pool / Streams Pool

ALTER SYSTEM SET java_pool_size = 256M;
ALTER SYSTEM SET streams_pool_size = 256M;

3. PGA 优化

3.1 自动 PGA

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

3.2 排序

-- 命中率
SELECT 
  SUM(decode(name, 'sorts (memory)', value, 0)) AS mem_sorts,
  SUM(decode(name, 'sorts (disk)', value, 0)) AS disk_sorts
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
-- mem > 99% 磁盘

3.3 工作区

SELECT 
  low_optimal_size / 1024 AS low_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;

详细见：Oracle PGA 与排序优化。

4. AMM vs ASMM

4.1 AMM

ALTER SYSTEM SET memory_target = 24G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 32G SCOPE=SPFILE;
-- SGA + PGA 自动

4.2 ASMM

ALTER SYSTEM SET sga_target = 16G;
ALTER SYSTEM SET pga_aggregate_target = 8G;
-- SGA 内部自动，PGA 独立

4.3 选择

AMM：简单
ASMM：精细
推荐 ASMM + HugePages

5. HugePages

5.1 配置

# 计算
# SGA / 2M = HugePages 数量

# /etc/sysctl.conf
vm.nr_hugepages = 8192

# /etc/security/limits.conf
oracle soft memlock unlimited
oracle hard memlock unlimited

5.2 Oracle 启用

ALTER SYSTEM SET use_large_pages = ONLY SCOPE=SPFILE;

5.3 验证

grep Huge /proc/meminfo

详细见：Oracle 内存调优 SGA/PGA。

6. 内存命中率综合

6.1 Buffer Cache

SELECT 
  name, 
  value
FROM v$sysstat 
WHERE name IN (
  'db block gets from cache',
  'consistent gets from cache',
  'physical reads cache'
);

6.2 Library Cache

SELECT 
  namespace, 
  gets, 
  gethits, 
  gethitratio
FROM v$librarycache;

6.3 Buffer Pool

SELECT 
  name, 
  block_size, 
  buffers, 
  target_size
FROM v$buffer_pool;

7. 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;

8. SGA Advisory

SELECT 
  sga_size, 
  sga_size_factor, 
  estd_db_time, 
  estd_physical_reads
FROM v$sga_target_advice;

9. 内存优化策略

9.1 增大 SGA

Buffer Cache 命中率 < 95%
物理读多
大表

9.2 增大 Shared Pool

解析高
大量 SQL
Library Cache 低

9.3 增大 PGA

磁盘排序多
大 JOIN
大 ORDER BY

9.4 优化 SQL

减少逻辑读
减少排序
绑定变量

10. 监控

10.1 v$sga

SELECT * FROM v$sga;
SELECT * FROM v$sgainfo;

10.2 v$pgastat

SELECT * FROM v$pgastat;

10.3 v$process_memory

SELECT 
  pid, 
  serial#, 
  category, 
  allocated, 
  max_allocated
FROM v$process_memory
ORDER BY allocated DESC
FETCH FIRST 10 ROWS ONLY;

11. 常见坑与排错

11.1 ORA-04031

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

11.2 ORA-04036

-- PGA 不足
ALTER SYSTEM SET pga_aggregate_limit = 16G;

11.3 ORA-00821

-- SGA 不足
-- 1. 检查物理内存
-- 2. 调整 sga_target

11.4 ORA-27102

-- 共享内存段
-- 1. /dev/shm 大小
-- 2. kernel.shmmax

12. 最佳实践

ASMM + HugePages：生产推荐
SGA 50-70% 内存：合理
PGA 20-30%：排序
Buffer > 95%：OLTP
Library > 99%：减少解析
绑定变量：减少硬解析
HugePages：大内存
PGA Advisory：指导
SGA Advisory：指导
监控内存：避免问题

13. 参考资料

[1] Oracle Database Memory Management Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/memory-management.html