内存颠簸的真相深度解析Oracle内存抖动诊断与SGA/PGA频繁换页的解决方案一、开篇惊魂数据库突然“抽风”的真相二、什么是内存抖动全景症状图三、内存抖动的五大典型症状1.症状一SGA Resize操作频繁2.症状二操作系统Swap使用率异常3.症状三Library Cache和Dictionary Cache命中率波动4.症状四Buffer Cache命中率异常下降5.症状五PGA过度分配Over Allocation四、内存抖动的五大根因分析1.根因一物理内存不足最常见2.根因二AMM启用但MEMORY_TARGET设置过大3.根因三SGA组件下限设置不当4.根因四操作系统内存管理参数不合理5.根因五AMM与Huge Pages冲突五、内存抖动的完整诊断流程诊断步骤一快速扫描5分钟诊断步骤二深入分析15分钟诊断步骤三操作系统层面分析10分钟六、内存抖动的解决方案方案一增加物理内存根本解决方案二从AMM切换到ASMM立竿见影方案三优化SGA内部组件大小方案四操作系统层面优化方案五SQL优化减少内存需求七、内存抖动监控与预警机制建立自动监控脚本八、总结记住这个“搬家”类比就够了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大小调整操作SELECTcomponent,oper_type,oper_mode,initial_size/1024/1024ASinitial_mb,final_size/1024/1024ASfinal_mb,start_timeFROMv$sga_resize_opsORDERBYstart_timeDESCFETCHFIRST100ROWSONLY;判断标准每小时调整 10次正常。每小时调整 10~60次需要关注。每小时调整 60次严重内存抖动。2.症状二操作系统Swap使用率异常现象Oracle的SGA或PGA被操作系统交换到磁盘Swap分区导致访问内存时触发物理I/O。诊断命令Linux# 查看Swap使用情况free-h# 查看哪些进程使用了Swapcat/proc/meminfo|grep-iswap# 查看Oracle进程的Swap使用情况grepVmSwap /proc/$(pgrep-fpmon)/status# 实时监控Swap使用vmstat110判断标准Swap使用 100MB正常。Swap使用 100MB~1GB需要关注。Swap使用 1GB严重问题必须解决。3.症状三Library Cache和Dictionary Cache命中率波动现象共享池命中率时高时低频繁出现硬解析。诊断SQL-- 查看Library Cache命中率SELECTnamespace,gets,gethits,ROUND(gethits/DECODE(gets,0,1,gets)*100,2)AShit_ratio,pins,pinhits,ROUND(pinhits/DECODE(pins,0,1,pins)*100,2)ASpin_hit_ratioFROMv$librarycacheWHEREnamespaceIN(SQL AREA,TABLE/PROCEDURE,BODY,TRIGGER);4.症状四Buffer Cache命中率异常下降现象Buffer Cache命中率突然下降物理读增加。诊断SQL-- 查看Buffer Pool命中率SELECTname,block_size,physical_reads,db_block_gets,consistent_gets,ROUND(1-(physical_reads)/DECODE((db_block_getsconsistent_gets),0,1,(db_block_getsconsistent_gets)),4)*100AShit_ratioFROMv$buffer_pool_statistics;5.症状五PGA过度分配Over Allocation现象PGA的实际使用量超过PGA_AGGREGATE_TARGET的设置值。诊断SQL-- 查看PGA过度分配次数SELECTname,valueFROMv$pgastatWHEREnameIN(aggregate PGA target parameter,aggregate PGA auto target,total PGA used,total PGA allocated,over allocation count);判断标准over allocation count 0PGA不足发生了过度分配。过度分配意味着Oracle被迫突破PGA_AGGREGATE_TARGET的限制。四、内存抖动的五大根因分析1.根因一物理内存不足最常见问题描述服务器物理内存不够Oracle的SGAPGA总需求超过物理内存触发操作系统Swap。诊断方法# 查看服务器总内存free-h# 查看Oracle进程的内存使用psaux|greporacle|awk{sum$6} END {print sum/1024/1024 GB}# 对比Oracle的内存参数# SGA_TARGET PGA_AGGREGATE_TARGET 应 物理内存的80%检查清单物理内存64GB OS预留 8GB Oracle可用56GB SGA_TARGET40GB PGA_AGGREGATE_TARGET20GB SGA PGA 60GB 56GB ❌ 内存不足2.根因二AMM启用但MEMORY_TARGET设置过大问题描述AMM自动调整SGA和PGA比例但总内存设置过大导致Oracle频繁在SGA和PGA之间“拆东墙补西墙”。诊断方法-- 查看AMM的调整历史SELECTTO_CHAR(start_time,YYYY-MM-DD HH24:MI:SS)ASstart_time,component,oper_type,initial_size/1024/1024ASinitial_mb,final_size/1024/1024ASfinal_mbFROMv$memory_resize_opsORDERBYstart_timeDESCFETCHFIRST50ROWSONLY;典型问题模式时间线 12:00 - SGA缩小到30GBPGA扩大到20GBOLAP报表开始 12:05 - SGA扩大到40GBPGA缩小到10GB报表结束OLTP恢复 12:10 - SGA又缩小到30GBPGA扩大到20GB另一个报表 ... 每小时反复调整多次 → 内存抖动3.根因三SGA组件下限设置不当问题描述ASMM管理下某些组件的下限设置过高导致其他组件即使有需求也无法获得内存。诊断方法-- 查看SGA组件的手动设置下限SELECTcomponent,current_size/1024/1024AScurrent_mb,min_size/1024/1024ASmin_mb,max_size/1024/1024ASmax_mb,user_specified_size/1024/1024ASuser_specified_mbFROMv$sga_dynamic_componentsWHEREuser_specified_size0;问题示例SHARED_POOL_SIZE手动设为20GB DB_CACHE_SIZE手动设为30GB SGA_TARGET 60GB Shared Pool实际只需要10GB但下限设了20GB → 浪费10GBBuffer Cache无法获得更多内存 → Buffer Cache命中率下降物理读增加4.根因四操作系统内存管理参数不合理问题描述Linux的vm.swappiness参数设置过高导致操作系统过度使用Swap。诊断与修复# 查看当前swappiness值cat/proc/sys/vm/swappiness# 对于数据库服务器建议设置为10或更低echovm.swappiness10/etc/sysctl.confsysctl-p# 查看当前设置sysctlvm.swappinessswappiness取值说明值含义适用场景0几乎不使用Swap内存充足的数据库服务器10尽量不使用SwapOracle数据库推荐值60默认值桌面系统100积极使用Swap不适用于数据库5.根因五AMM与Huge Pages冲突问题描述AMM使用/dev/shm共享内存文件系统而Huge Pages需要在启动时预先锁定内存两者机制冲突导致内存管理异常。症状识别# 查看Huge Pages使用情况cat/proc/meminfo|grep-ihuge# 如果看到HugePages_Total 0但HugePages_Free 0# 且数据库使用AMMMEMORY_TARGET 0# 说明存在冲突解决方案-- 禁用AMM启用ASMMALTERSYSTEMSETMEMORY_TARGET0SCOPESPFILE;ALTERSYSTEMSETSGA_TARGET40G SCOPESPFILE;ALTERSYSTEMSETPGA_AGGREGATE_TARGET20G SCOPESPFILE;-- 重启生效五、内存抖动的完整诊断流程诊断步骤一快速扫描5分钟-- 1. 检查内存管理模式SELECTname,value,isdefaultFROMv$parameterWHEREnameIN(memory_target,sga_target,pga_aggregate_target);-- 2. 检查SGA调整频率SELECTCOUNT(*)ASresize_count,MIN(start_time)ASfirst_time,MAX(start_time)ASlast_timeFROMv$sga_resize_opsWHEREstart_timeSYSDATE-1;-- 3. 检查PGA过度分配SELECTname,valueFROMv$pgastatWHEREnameIN(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的命中率趋势SELECTTO_CHAR(begin_time,YYYY-MM-DD HH24:MI)AStime,name,valueFROMv$sysmetric_historyWHEREnameIN(Buffer Cache Hit Ratio,Library Cache Hit Ratio,Memory Sorts Ratio)ANDbegin_timeSYSDATE-1ORDERBYbegin_time,name;诊断步骤三操作系统层面分析10分钟# 6. 查看内存使用趋势sar-r110# 7. 查看Swap使用趋势sar-S110# 8. 查看Oracle进程的内存使用psaux|greporacle|sort-k4-r|head-10# 9. 查看共享内存使用ipcs-m|greporacle六、内存抖动的解决方案方案一增加物理内存根本解决判断标准如果SGA PGA 超过物理内存的80%增加物理内存是最根本的解决方案。计算公式所需物理内存 (SGA_TARGET PGA_AGGREGATE_TARGET) / 0.7 需要额外留30%给操作系统和其他进程方案二从AMM切换到ASMM立竿见影操作步骤-- 1. 检查当前配置SHOWPARAMETER memory_target;SHOWPARAMETER sga_target;-- 2. 计算合适的SGA和PGA大小-- 假设总内存64GB给Oracle 56GB-- OLTP系统SGA 45GB, PGA 11GB-- 3. 切换配置ALTERSYSTEMSETMEMORY_TARGET0SCOPESPFILE;ALTERSYSTEMSETSGA_TARGET45G SCOPESPFILE;ALTERSYSTEMSETSGA_MAX_SIZE45G SCOPESPFILE;ALTERSYSTEMSETPGA_AGGREGATE_TARGET11G SCOPESPFILE;-- 4. 重启数据库SHUTDOWNIMMEDIATE;STARTUP;方案三优化SGA内部组件大小调整策略-- 1. 取消不必要的手动下限设置ALTERSYSTEM RESET SHARED_POOL_SIZE SCOPEBOTH;ALTERSYSTEM RESET DB_CACHE_SIZE SCOPEBOTH;-- 2. 只对关键组件设置合理下限ALTERSYSTEMSETSHARED_POOL_SIZE10G SCOPEBOTH;ALTERSYSTEMSETDB_CACHE_SIZE25G SCOPEBOTH;-- 3. 观察一周后根据实际使用情况微调SELECTcomponent,current_size/1024/1024AScurrent_mb,min_size/1024/1024ASmin_mbFROMv$sga_dynamic_components;方案四操作系统层面优化1. 降低Swappinessechovm.swappiness10/etc/sysctl.confsysctl-p2. 启用Huge PagesLinux# 计算所需Huge Pages数量# 假设SGA45GBHuge Page大小2MB# 所需页数 45GB / 2MB 23040预留一些echovm.nr_hugepages24000/etc/sysctl.confsysctl-p# 在Oracle中配置使用Huge PagesALTER SYSTEM SETUSE_LARGE_PAGESONLYSCOPESPFILE;3. 锁定SGA在物理内存中-- 设置LOCK_SGA参数需要操作系统权限ALTERSYSTEMSETLOCK_SGATRUESCOPESPFILE;-- 重启生效方案五SQL优化减少内存需求优化方向减少全表扫描全表扫描会污染Buffer Cache触发内存调整。使用绑定变量减少硬解析降低Shared Pool压力。优化排序操作减少ORDER BY、DISTINCT的不必要使用。拆分大查询将大排序、大哈希连接拆分为多个小操作。七、内存抖动监控与预警机制建立自动监控脚本-- 创建内存抖动监控表CREATETABLEdba_ora.memory_thrashing_monitor(sample_timeTIMESTAMPDEFAULTSYSTIMESTAMP,sga_resize_count_per_hour NUMBER,pga_over_alloc_count NUMBER,swap_usage_mb NUMBER,statusVARCHAR2(20));-- 每小时采集一次数据INSERTINTOdba_ora.memory_thrashing_monitorSELECTSYSTIMESTAMP,(SELECTCOUNT(*)FROMv$sga_resize_opsWHEREstart_timeSYSDATE-1/24),(SELECTvalueFROMv$pgastatWHEREnameover allocation count),0,-- 需要从OS采集CASEWHEN(SELECTCOUNT(*)FROMv$sga_resize_opsWHEREstart_timeSYSDATE-1/24)60THENWARNINGELSENORMALENDFROMdual;告警规则SGA每小时调整 10次正常。SGA每小时调整 10~60次发送通知。SGA每小时调整 60次立即告警。八、总结记住这个“搬家”类比就够了【内存抖动 频繁搬家搬家的时间比住的时间还长】SGA内部抖动在一个房子里频繁调整房间的用途。今天把书房改成卧室明天又把卧室改回书房每次调整都要搬家具累死累活。SGA与PGA之间抖动在两栋房子之间来回搬运物品。AMM每天把内存从SGA搬到PGA又从PGA搬回SGA搬运过程占用大量时间。根本解决方案换一个大房子增加物理内存或者确定每个房间的用途后不再改变用ASMM替代AMM。内存抖动是Oracle性能问题中最隐蔽的一种——它不像CPU高或I/O高那样直观但破坏力同样巨大。掌握本文的诊断方法下次遇到“时快时慢”的问题时你就能第一时间定位是否内存抖动并快速解决。你在日常运维中是否遇到过内存抖动的问题是通过什么方法定位到根因的欢迎在评论区分享你的诊断经验。The End点点关注收藏不迷路⬆ ⬆ 顶部 ⬆ ⬆