内存颠簸的真相：深度解析Oracle内存抖动诊断与SGA/PGA频繁换页的解决方案


🌺The Begin🌺点点关注，收藏不迷路🌺

一、开篇惊魂：数据库突然“抽风”的真相

某个周一下午，业务部门反馈系统时快时慢，每5~10分钟出现一次明显的卡顿，持续30秒左右，然后恢复正常。登录数据库检查：CPU不高、磁盘I/O不高、没有锁阻塞，但AWR报告显示大量的内存相关等待事件。

这就是典型的Oracle内存抖动（Memory Thrashing）——SGA和PGA之间的内存频繁调整，导致性能剧烈波动。

内存抖动就像一个人不停地在几个箱子之间倒腾东西——时间全花在“倒腾”上，而不是“做事”上。今天这篇文章，带你从症状识别、根因分析到彻底解决，完整掌握Oracle内存抖动的诊断与修复方法。

二、什么是内存抖动？全景症状图

先通过一张图看清内存抖动的完整因果链：

是

否

物理内存不足

AMM启用?

MMAN频繁调整SGA/PGA比例

SGA内部组件频繁调整

操作系统Swap换页

SGA Resize操作频繁

共享池与Buffer Cache互相争抢

频繁的Granule迁移

磁盘I/O异常升高

内存Latch争用

Library Cache Latch等待

Buffer Cache Chains闩等待

数据库响应时间剧烈波动

用户感知：系统时快时慢

关键链条：

  • 红色节点： 根因（物理内存不足）和最终表现（性能波动）。
  • 橘色节点： 中间放大环节（AMM/ASMM的自动调整机制）。
  • 内存抖动很少是Oracle自身的Bug，通常是物理内存配置不足或内存参数设置不合理导致的。

三、内存抖动的五大典型症状

1. 症状一：SGA Resize操作频繁

现象：
v$sga_resize_ops视图中每分钟有多次SGA组件的大小调整记录。

诊断SQL：

-- 查看最近100次SGA大小调整操作
SELECT 
    component,
    oper_type,
    oper_mode,
    initial_size/1024/1024 AS initial_mb,
    final_size/1024/1024 AS final_mb,
    start_time
FROM v$sga_resize_ops
ORDER BY start_time DESC
FETCH FIRST 100 ROWS ONLY;

判断标准：

  • 每小时调整 < 10次：正常。
  • 每小时调整 10~60次：需要关注。
  • 每小时调整 > 60次：严重内存抖动。

2. 症状二：操作系统Swap使用率异常

现象：
Oracle的SGA或PGA被操作系统交换到磁盘Swap分区，导致访问内存时触发物理I/O。

诊断命令（Linux）：

# 查看Swap使用情况
free -h

# 查看哪些进程使用了Swap
cat /proc/meminfo | grep -i swap

# 查看Oracle进程的Swap使用情况
grep VmSwap /proc/$(pgrep -f pmon)/status

# 实时监控Swap使用
vmstat 1 10

判断标准：

  • Swap使用 < 100MB：正常。
  • Swap使用 100MB~1GB：需要关注。
  • Swap使用 > 1GB：严重问题，必须解决。

3. 症状三：Library Cache和Dictionary Cache命中率波动

现象：
共享池命中率时高时低，频繁出现硬解析。

诊断SQL：

-- 查看Library Cache命中率
SELECT 
    namespace,
    gets,
    gethits,
    ROUND(gethits/DECODE(gets, 0, 1, gets)*100, 2) AS hit_ratio,
    pins,
    pinhits,
    ROUND(pinhits/DECODE(pins, 0, 1, pins)*100, 2) AS pin_hit_ratio
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

4. 症状四：Buffer Cache命中率异常下降

现象：
Buffer Cache命中率突然下降，物理读增加。

诊断SQL：

-- 查看Buffer Pool命中率
SELECT 
    name,
    block_size,
    physical_reads,
    db_block_gets,
    consistent_gets,
    ROUND(1 - (physical_reads) / 
          DECODE((db_block_gets + consistent_gets), 0, 1, 
                 (db_block_gets + consistent_gets)), 4) * 100 AS hit_ratio
FROM v$buffer_pool_statistics;

5. 症状五：PGA过度分配（Over Allocation）

现象：
PGA的实际使用量超过PGA_AGGREGATE_TARGET的设置值。

诊断SQL：

-- 查看PGA过度分配次数
SELECT name, value
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'aggregate PGA auto target',
    'total PGA used',
    'total PGA allocated',
    'over allocation count'
);

判断标准：

  • over allocation count > 0：PGA不足，发生了过度分配。
  • 过度分配意味着Oracle被迫突破PGA_AGGREGATE_TARGET的限制。

四、内存抖动的五大根因分析

1. 根因一：物理内存不足（最常见）

问题描述：
服务器物理内存不够，Oracle的SGA+PGA总需求超过物理内存，触发操作系统Swap。

诊断方法：

# 查看服务器总内存
free -h

# 查看Oracle进程的内存使用
ps aux | grep oracle | awk '{sum+=$6} END {print sum/1024/1024 " GB"}'

# 对比Oracle的内存参数
# SGA_TARGET + PGA_AGGREGATE_TARGET 应 < 物理内存的80%

检查清单：

物理内存：64GB
OS预留：   8GB
Oracle可用：56GB
SGA_TARGET：40GB
PGA_AGGREGATE_TARGET：20GB
SGA + PGA = 60GB > 56GB ❌ 内存不足！

2. 根因二：AMM启用但MEMORY_TARGET设置过大

问题描述：
AMM自动调整SGA和PGA比例，但总内存设置过大，导致Oracle频繁在SGA和PGA之间“拆东墙补西墙”。

诊断方法：

-- 查看AMM的调整历史
SELECT 
    TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
    component,
    oper_type,
    initial_size/1024/1024 AS initial_mb,
    final_size/1024/1024 AS final_mb
FROM v$memory_resize_ops
ORDER BY start_time DESC
FETCH FIRST 50 ROWS ONLY;

典型问题模式：

时间线：
12:00 - SGA缩小到30GB，PGA扩大到20GB（OLAP报表开始）
12:05 - SGA扩大到40GB，PGA缩小到10GB（报表结束，OLTP恢复）
12:10 - SGA又缩小到30GB，PGA扩大到20GB（另一个报表）
...
每小时反复调整多次 → 内存抖动

3. 根因三：SGA组件下限设置不当

问题描述：
ASMM管理下，某些组件的下限设置过高，导致其他组件即使有需求也无法获得内存。

诊断方法：

-- 查看SGA组件的手动设置下限
SELECT 
    component,
    current_size/1024/1024 AS current_mb,
    min_size/1024/1024 AS min_mb,
    max_size/1024/1024 AS max_mb,
    user_specified_size/1024/1024 AS user_specified_mb
FROM v$sga_dynamic_components
WHERE user_specified_size > 0;

问题示例：

SHARED_POOL_SIZE手动设为20GB
DB_CACHE_SIZE手动设为30GB
SGA_TARGET = 60GB

Shared Pool实际只需要10GB，但下限设了20GB
→ 浪费10GB，Buffer Cache无法获得更多内存
→ Buffer Cache命中率下降，物理读增加

4. 根因四：操作系统内存管理参数不合理

问题描述：
Linux的vm.swappiness参数设置过高，导致操作系统过度使用Swap。

诊断与修复：

# 查看当前swappiness值
cat /proc/sys/vm/swappiness

# 对于数据库服务器，建议设置为10或更低
echo "vm.swappiness=10" >> /etc/sysctl.conf
sysctl -p

# 查看当前设置
sysctl vm.swappiness

swappiness取值说明：

值 含义 适用场景
0 几乎不使用Swap 内存充足的数据库服务器
10 尽量不使用Swap Oracle数据库推荐值
60 默认值 桌面系统
100 积极使用Swap 不适用于数据库

5. 根因五：AMM与Huge Pages冲突

问题描述：
AMM使用/dev/shm共享内存文件系统，而Huge Pages需要在启动时预先锁定内存，两者机制冲突导致内存管理异常。

症状识别：

# 查看Huge Pages使用情况
cat /proc/meminfo | grep -i huge

# 如果看到HugePages_Total > 0，但HugePages_Free > 0
# 且数据库使用AMM（MEMORY_TARGET > 0）
# 说明存在冲突

解决方案：

-- 禁用AMM，启用ASMM
ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=40G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=20G SCOPE=SPFILE;
-- 重启生效

五、内存抖动的完整诊断流程

诊断步骤一：快速扫描（5分钟）

-- 1. 检查内存管理模式
SELECT name, value, isdefault
FROM v$parameter
WHERE name IN ('memory_target', 'sga_target', 'pga_aggregate_target');

-- 2. 检查SGA调整频率
SELECT COUNT(*) AS resize_count, 
       MIN(start_time) AS first_time,
       MAX(start_time) AS last_time
FROM v$sga_resize_ops
WHERE start_time > SYSDATE - 1;

-- 3. 检查PGA过度分配
SELECT name, value
FROM v$pgastat
WHERE name IN ('over allocation count', 'total PGA allocated', 'aggregate PGA target parameter');

诊断步骤二：深入分析（15分钟）

-- 4. 分析AWR报告中的内存等待事件
-- 查看以下等待事件的次数：
-- - latch: shared pool
-- - latch: library cache
-- - buffer busy waits
-- - log buffer space
-- - library cache: mutex X

-- 5. 查看Buffer Cache和Shared Pool的命中率趋势
SELECT 
    TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS time,
    name,
    value
FROM v$sysmetric_history
WHERE name IN (
    'Buffer Cache Hit Ratio',
    'Library Cache Hit Ratio',
    'Memory Sorts Ratio'
)
AND begin_time > SYSDATE - 1
ORDER BY begin_time, name;

诊断步骤三：操作系统层面分析（10分钟）

# 6. 查看内存使用趋势
sar -r 1 10

# 7. 查看Swap使用趋势
sar -S 1 10

# 8. 查看Oracle进程的内存使用
ps aux | grep oracle | sort -k4 -r | head -10

# 9. 查看共享内存使用
ipcs -m | grep oracle

六、内存抖动的解决方案

方案一：增加物理内存（根本解决）

判断标准：
如果SGA + PGA 超过物理内存的80%，增加物理内存是最根本的解决方案。

计算公式：

所需物理内存 = (SGA_TARGET + PGA_AGGREGATE_TARGET) / 0.7
需要额外留30%给操作系统和其他进程

方案二：从AMM切换到ASMM（立竿见影）

操作步骤：

-- 1. 检查当前配置
SHOW PARAMETER memory_target;
SHOW PARAMETER sga_target;

-- 2. 计算合适的SGA和PGA大小
-- 假设总内存64GB，给Oracle 56GB
-- OLTP系统：SGA 45GB, PGA 11GB

-- 3. 切换配置
ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=45G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=45G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=11G SCOPE=SPFILE;

-- 4. 重启数据库
SHUTDOWN IMMEDIATE;
STARTUP;

方案三：优化SGA内部组件大小

调整策略：

-- 1. 取消不必要的手动下限设置
ALTER SYSTEM RESET SHARED_POOL_SIZE SCOPE=BOTH;
ALTER SYSTEM RESET DB_CACHE_SIZE SCOPE=BOTH;

-- 2. 只对关键组件设置合理下限
ALTER SYSTEM SET SHARED_POOL_SIZE=10G SCOPE=BOTH;
ALTER SYSTEM SET DB_CACHE_SIZE=25G SCOPE=BOTH;

-- 3. 观察一周后，根据实际使用情况微调
SELECT component, 
       current_size/1024/1024 AS current_mb,
       min_size/1024/1024 AS min_mb
FROM v$sga_dynamic_components;

方案四：操作系统层面优化

1. 降低Swappiness：

echo "vm.swappiness=10" >> /etc/sysctl.conf
sysctl -p

2. 启用Huge Pages（Linux）：

# 计算所需Huge Pages数量
# 假设SGA=45GB，Huge Page大小=2MB
# 所需页数 = 45GB / 2MB = 23040，预留一些

echo "vm.nr_hugepages=24000" >> /etc/sysctl.conf
sysctl -p

# 在Oracle中配置使用Huge Pages
ALTER SYSTEM SET USE_LARGE_PAGES=ONLY SCOPE=SPFILE;

3. 锁定SGA在物理内存中：

-- 设置LOCK_SGA参数（需要操作系统权限）
ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
-- 重启生效

方案五：SQL优化减少内存需求

优化方向：

  1. 减少全表扫描： 全表扫描会污染Buffer Cache，触发内存调整。
  2. 使用绑定变量： 减少硬解析，降低Shared Pool压力。
  3. 优化排序操作： 减少ORDER BY、DISTINCT的不必要使用。
  4. 拆分大查询： 将大排序、大哈希连接拆分为多个小操作。

七、内存抖动监控与预警机制

建立自动监控脚本

-- 创建内存抖动监控表
CREATE TABLE dba_ora.memory_thrashing_monitor (
    sample_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    sga_resize_count_per_hour NUMBER,
    pga_over_alloc_count NUMBER,
    swap_usage_mb NUMBER,
    status VARCHAR2(20)
);

-- 每小时采集一次数据
INSERT INTO dba_ora.memory_thrashing_monitor
SELECT 
    SYSTIMESTAMP,
    (SELECT COUNT(*) FROM v$sga_resize_ops WHERE start_time > SYSDATE - 1/24),
    (SELECT value FROM v$pgastat WHERE name = 'over allocation count'),
    0, -- 需要从OS采集
    CASE 
        WHEN (SELECT COUNT(*) FROM v$sga_resize_ops WHERE start_time > SYSDATE - 1/24) > 60 
        THEN 'WARNING'
        ELSE 'NORMAL'
    END
FROM dual;

告警规则：

  • SGA每小时调整 < 10次：正常。
  • SGA每小时调整 10~60次：发送通知。
  • SGA每小时调整 > 60次：立即告警。

八、总结：记住这个“搬家”类比就够了

【内存抖动 = 频繁搬家，搬家的时间比住的时间还长】

  • SGA内部抖动： 在一个房子里频繁调整房间的用途。 今天把书房改成卧室，明天又把卧室改回书房，每次调整都要搬家具，累死累活。
  • SGA与PGA之间抖动： 在两栋房子之间来回搬运物品。 AMM每天把内存从SGA搬到PGA，又从PGA搬回SGA，搬运过程占用大量时间。
  • 根本解决方案： 换一个大房子（增加物理内存），或者确定每个房间的用途后不再改变（用ASMM替代AMM）。

内存抖动是Oracle性能问题中最隐蔽的一种——它不像CPU高或I/O高那样直观，但破坏力同样巨大。掌握本文的诊断方法，下次遇到“时快时慢”的问题时，你就能第一时间定位是否内存抖动，并快速解决。

你在日常运维中是否遇到过内存抖动的问题？是通过什么方法定位到根因的？欢迎在评论区分享你的诊断经验。

在这里插入图片描述


🌺The End🌺点点关注，收藏不迷路🌺

# oracle # 数据库
Logo
openEuler 社区

openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构

更多推荐

cover

爆破 Windows UAC 冲突！MT4 下载后无法运行、找不到 EA/指标的底层机制与“数据文件夹”全修复指南

avatar openEuler 社区
cover

MySQL（进阶）万字吃透 MySQL SQL 调优：索引原理 + Explain 执行计划 + 全场景 SQL 优化 + 索引失效避坑实战

avatar openEuler 社区

为什么LeRobot还使用欧拉角

为什么LeRobot还使用欧拉角

avatar openEuler 社区