每次碰到Oracle调优内存参数都是最先被怀疑的对象。以前在维护某套业务系统后面统一叫它模拟项目X的时候一到业务高峰期DBA群里的第一句话往往是“是不是SGA太小了”然后大家就开始讨论要不要把SGA_TARGET往上提。这个思路不能说错但如果不把19c的内存结构、参数关系和调整边界先摸清楚很容易把内存调成“账面上很漂亮实际性能原地踏步”的状态。这篇文章我不打算堆概念而是沿着我自己平时处理问题的主线来写先看19c的内存到底分了哪几块再看MEMORY_TARGET、SGA_TARGET这一串参数谁听谁的接着用一套OLTP环境的调优过程把整个链路走一遍最后说说那些我踩过、也看别人反复踩的内存调优坑。适合读过一点Oracle基础、想系统提升调优能力的DBA和运维同学参考。1. 19c内存全景SGA、PGA与In-Memory这三块谁也不白吃内存1.1 SGA各组件就是一副“宿舍分配表”SGASystem Global Area系统全局区是实例启动时分配的一块共享内存所有后台进程和会话都从这里拿资源。如果你把数据库实例比作一栋宿舍楼SGA就是公共活动区里面的每个房间都有明确用途谁住的面积大、谁住的面积小直接影响整个楼的使用体验。在19c里SGA主要包含这几类“住户”共享池Shared Pool这是最容易被误伤的组件。它内部又分成Library CacheSQL文本、解析树、执行计划、存储过程定义、Dictionary Cache数据字典行缓存和Server Result Cache。OLTP系统里SQL反复硬解析、共享池命中率不稳定通常都和这里有关。数据库缓冲区缓存Database Buffer Cache数据块读进来之后先放这里下次再读就不用碰磁盘。默认缓冲池由DB_CACHE_SIZE控制另外还有Keep池和Recycle池以及非默认块大小的缓冲池。重做日志缓冲Redo Log Buffer会话产生redo记录后先写到这里再由LGWR进程刷入在线日志文件。它是个环形缓冲写满之前LGWR没来得及刷盘会话就会等“log buffer space”。大池Large PoolRMAN备份恢复、并行查询的消息缓冲、共享服务器Shared Server的会话内存都从这里分配。Java池Java Pool数据库里跑Java存储过程时使用。流池Streams PoolGoldenGate、XStream这类数据集成产品使用。此外还有一块固定的Fixed SGA存放实例相关的内部数据和进程间通信结构一般只有几MB不属于动态调整范围。很多人以为SGA越大越好其实是错的。SGA里的内存是“共享但不免费”的Buffer Cache再多如果SQL本身访问的数据量远超缓存容量命中率照样上不去Shared Pool再大如果SQL写得差导致不停硬解析问题也不会消失。1.2 PGA是会话的私人工具箱排序、哈希和位图都在这里发生PGAProgram Global Area程序全局区和SGA最大的区别是“私有”。每个服务进程都有自己的PGA不共享主要内容包括SQL工作区排序Sort、哈希连接Hash Join、位图合并Bitmap Merge等操作的内存区间会话内存会话变量、登录信息等私有SQL区绑定变量、游标状态等信息。联机交易系统里常见的“排序过多”“临时表空间暴涨”很多时候不是临时表空间不够而是PGA给单个工作区分配的内存不够导致排序落盘。19c里面PGA相关参数主要有两个。PGA_AGGREGATE_TARGET是PGA总目标值数据库在这个框架下自动管理各个进程的SQL工作区大小工作区可以增量扩展也可以相互借用但总规模要控制在目标附近。PGA_AGGREGATE_LIMIT则是硬上限当所有进程的PGA总和超过这个上限时数据库会主动终止PGA占用最大的会话防止整个实例把物理内存耗尽。注意它的默认值一般是PGA_AGGREGATE_TARGET的2倍左右但如果你物理内存本身紧张这个默认值也可能仍然偏高后面第4部分会专门讲。1.3 In-Memory列式存储19c里要不要单独留一间房从12c开始Oracle引入了In-Memory Column StoreIM列式存储在SGA中专门划出一块区域以列式格式缓存表和分区主要服务分析型查询。19c里通过INMEMORY_SIZE参数指定大小启用后还需要对对象做INMEMORY属性标记才会真正加载。这块区域不是自动管理的即使你设置了SGA_TARGET它也不会被数据库动态伸缩必须显式配置。纯OLTP业务如果分析查询很少我不建议给In-Memory分太大内存如果是混合负载可以先用V$INMEMORY_AREA观察实际使用率再决定要不要加大。2. 参数关系链MEMORY_TARGET、SGA_TARGET和各组件Target到底谁听谁的2.1 三种管理模式手动、ASMM、AMM在动手调参数之前先搞清楚你的数据库现在处于哪种内存管理模式。很多线上问题都是“以为自己开了自动管理实际某个参数写死了导致自动管理根本没接管”。三种模式的区分很简单管理模式关键参数谁来决定内存分配适用场景手动管理SHARED_POOL_SIZE、DB_CACHE_SIZE、LARGE_POOL_SIZE等全部显式设置管理员逐个指定组件大小极少数老环境不推荐新库使用自动共享内存管理ASMMSGA_TARGET大于0MEMORY_TARGET为0数据库在SGA总预算内自动调整共享池、缓冲区缓存等组件绝大多数单实例和RAC环境自动内存管理AMMMEMORY_TARGET大于0数据库在全局预算内自动划分SGA和PGA中小型环境、内存热调整要求高的场景19c中对ASMM的支持已经非常成熟这也是我个人推荐的生产环境默认选择。AMM在多实例RAC和需要大页HugePages的环境里有不少限制后面会细说。2.2 参数组合关系MEMORY_TARGET与SGA_TARGET不能糊涂这一串参数从大到小的关系经常把人绕晕我用一句口令概括上限看MAX目标看TARGET组件参数看下限。MEMORY_MAX_TARGET是整个实例内存SGAPGA的静态上限设置后不能在线改动要提前预留余量。MEMORY_TARGET是当前AMM的目标值可以动态调整但最大不能超过MEMORY_MAX_TARGET。当MEMORY_TARGET大于0时SGA_TARGET和PGA_AGGREGATE_TARGET如果设了就作为SGA和PGA的“下限基线”数据库会在总预算内自动调配。当MEMORY_TARGET为0、SGA_TARGET大于0时进入ASMM模式SGA内部组件默认缓冲池、共享池、大池、Java池等由数据库自动伸缩。如果显式设置了某个组件参数比如SHARED_POOL_SIZE512M在ASMM下它就是一个下限数据库只能把它调大不会调小到低于这个值。这里有个容易翻车的地方SGA_MAX_SIZE和MEMORY_MAX_TARGET都是静态参数。在手动管理模式下如果你把SGA_TARGET在线调大但没提前设置足够大的SGA_MAX_SIZE调整会直接报错。AMM模式下因为有MEMORY_MAX_TARGET兜底可动空间相对宽裕一些但前提是你当初给MAX留了余量。还有几个组件是“自动管理管不到”的它们是REDO_LOG_BUFFER、Keep池、Recycle池、非默认块大小缓存池以及In-Memory列式存储区域。这些要么显式设置要么保持默认ASMM不会替你做主。2.3 必备监控视角从V$SGAINFO、V$MEMORY_DYNAMIC_COMPONENTS读现场每次调内存之前我习惯先跑一遍这些视图确认当前实例的真实分配情况。SELECT name, bytes/1024/1024 AS mb, resizable FROM v$sgainfo;这个视图能看到当前SGA各块的“真实大小”注意要区分“当前值”和“配置值”。比如Database Buffers显示12GB不代表你已经把DB_CACHE_SIZE设成了12GB很可能只是自动管理的结果。接着看动态组件的历史和当前值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 ORDER BY component;这里user_specified_mb为0的组件说明没有显式设置参数完全交给自动管理非0的组件表示你写死了下限。再看内存调整历史SELECT component, oper_type, oper_mode, oper_status, initial_size/1024/1024 AS initial_mb, target_size/1024/1024 AS target_mb, start_time, end_time FROM v$sga_resize_ops ORDER BY start_time;如果看到大量SHRINK和EXPAND交替出现说明当前SGA确实在频繁自动调整此时更要关注系统整体的DB Time和等待事件是否有改善别被“动态调整”这个动作本身迷惑。3. 一套OLTP环境的真实调优复盘从等待事件到参数落地的全流程3.1 现场信息采集AWR先说话视图跟上下面的案例来自我整理过的一套典型OLTP环境为了便于描述我们叫它模拟项目X。配置大概是Linux x86_6464GB物理内存32个vCPU单实例Oracle 19.20未启用In-Memory。当时的参数是SGA_TARGET16GPGA_AGGREGATE_TARGET4G两个MAX参数都留了一定余量。业务高峰期的现象是应用侧接口响应变慢数据库侧没有锁等待风暴但系统层面能看到轻微的swap使用。我第一步不是改参数而是先出一份完整的AWR报告。重点看Top 5等待事件。模拟项目X的AWR里等待事件排名大致是这样等待事件累计等待次数占DB Time比例free buffer waits约240万27%log buffer space约108万14%buffer busy waits约63万8%db file sequential read约51万7%log file sync约37万5%free buffer waits排名第一很容易让人第一时间想“加Buffer Cache”。但经验告诉我这个等待出现的前提是服务进程在Buffer Cache里找不到空闲缓冲区它可能是缓存小的信号也可能是DBWR来不及刷盘、脏块写不出去的信号。所以不能只看等待名字就下结论。接着我补充查了两个指标SELECT name, value FROM v$sysstat WHERE name IN (redo buffer allocation retries, physical reads, session logical reads);结果redo buffer allocation retries数值明显偏高说明重做日志缓冲确实有“写满之后用户进程还在等”的情况这对应AWR里的log buffer space等待。再看系统整体物理读总量虽然绝对值不低但结合缓冲区命中率来看还有乐观的优化空间。3.2 靠Advisor而不是“经验之谈”决定调参方向决定动哪个参数之前我习惯先让Oracle自己的Advisor给个方向。19c里这几个视图很关键V$DB_CACHE_ADVICE模拟不同Buffer Cache大小下的物理读变化V$SHARED_POOL_ADVICE模拟不同共享池大小下的Library Cache时间节省V$PGA_TARGET_ADVICE模拟不同PGA目标下的额外读写与命中率V$SGA_ADVICE从SGA整体角度估算DB Time变化V$MEMORY_TARGET_ADVICEAMM模式下估算整实例内存目标。我当时先跑了一段Buffer Cache模拟SELECT size_for_estimate/1024/1024 AS cache_mb, size_factor, estd_physical_read_factor, estd_physical_reads FROM v$db_cache_advice WHERE name DEFAULT AND block_size (SELECT value FROM v$parameter WHERE name db_block_size) ORDER BY size_factor;输出里有一行很关键当SIZE_FACTOR在1.3左右也就是默认缓冲池约12GB时ESTD_PHYSICAL_READ_FACTOR从1.0降到了0.72左右再往上加到1.6倍时收益已经非常有限。这说明12GB附近是一个比较经济的点再继续加内存钱花得不值。再看共享池SELECT shared_pool_size_for_estimate/1024/1024 AS sp_mb, shared_pool_size_factor, estd_lc_size, estd_lc_memory_objects, estd_lc_time_saved_factor FROM v$shared_pool_advice ORDER BY shared_pool_size_factor;共享池从当前1GB左右提升到1.5GB时ESTD_LC_TIME_SAVED_FACTOR有明显提升到2GB之后曲线基本走平。所以我把共享池的下限锚定在1536M左右既不浪费内存也给了Library Cache流动的余地。PGA这边看V$PGA_TARGET_ADVICESELECT pga_target_for_estimate/1024/1024 AS pga_mb, pga_target_factor, estd_extra_bytes_rw, estd_pga_cachehit_percentage FROM v$pga_target_advice ORDER BY pga_target_factor;4GB提高到6GB时估算的额外读写字节数明显下降继续加大到8GB以后曲线平缓。再加上V$PGASTAT里的over allocation count不是0说明存在超配风险我决定把PGA_AGGREGATE_TARGET提到6GB并同步设置一个合理的PGA_AGGREGATE_LIMIT。3.3 动态调整、重启项与落地后的交叉验证方向确定之后真正执行的参数变更如下ALTER SYSTEM SET SGA_TARGET18G SCOPEBOTH; ALTER SYSTEM SET DB_CACHE_SIZE12G SCOPEBOTH; ALTER SYSTEM SET SHARED_POOL_SIZE1536M SCOPEBOTH; ALTER SYSTEM SET PGA_AGGREGATE_TARGET6G SCOPEBOTH; ALTER SYSTEM SET PGA_AGGREGATE_LIMIT12G SCOPEBOTH; ALTER SYSTEM SET REDO_LOG_BUFFER32M SCOPESPFILE;这里每一句都解释一下为什么这样写SGA_TARGET18G给Buffer Cache和Shared Pool的下限腾出空间。如果还停留在16G那么设置DB_CACHE_SIZE12G之后自动管理可调配的余量会非常窄容易触发频繁resize。DB_CACHE_SIZE12G在ASMM下这是一个下限数据库只会在此基础上向上自动扩展不会压缩到12G以下。SHARED_POOL_SIZE1536M同理作为共享池下限防止Library Cache被自动调得过小而引发硬解析。PGA_AGGREGATE_TARGET6G把PGA的总预算抬高PGA_AGGREGATE_LIMIT12G是硬顶防止某个异常会话把内存吃穿。REDO_LOG_BUFFER32M这个参数是静态的在线改不了只能写进SPFILE等重启生效。它和“redo buffer allocation retries”偏高的问题对应。改完参数不等于完事。我一般会等实例运行一段时间后再回头查实际分配SELECT component, current_size/1024/1024 AS current_mb, user_specified_size/1024/1024 AS user_specified_mb FROM v$sga_dynamic_components WHERE component IN (DEFAULT buffer cache, shared pool, large pool, KEEP buffer cache);正常情况下DEFAULT buffer cache的current_mb会略高于12G而不是停在12Gshared pool也会在1536M之上浮动。这就说明自动管理真的在往下接管而不是被你的下限钉死。最后等到下一个完整高峰窗口结束后用awrddrpt.sql生成调整前后的AWR对比报告。模拟项目X的最终结果是free buffer waits从240万次降到80万次以内log buffer space也明显收敛整体DB Time下降了接近两成。这里多说一句一定要跑完至少一个完整业务周期再下结论别拿高峰前1小时的快照对比这种对比没有统计意义。4. 内存调优最容易翻车的四个地方大页、超配、回退与验证盲区4.1 大页和AMM之间的取舍Linux默认内存页是4KBOracle SGA动辄十几GB的时候页表项会非常庞大CPU查找页表也会产生额外开销。所以生产环境普遍建议给SGA使用HugePages也就是2MB甚至1GB的大页。问题在于AMMMEMORY_TARGET大于0和HugePages并不兼容。原因是AMM在Linux上会借助/dev/shm来动态分配和释放共享内存而HugePages需要预先保留固定的物理内存页。一旦开了AMM大页基本就用不上反过来有些环境强行给/dev/shm分配超过物理内存的容量结果实例直接起不来。我自己在19c环境里的默认做法是除非是小型测试库否则优先关闭AMM把MEMORY_TARGET设为0走SGA_TARGETPGA_AGGREGATE_TARGET的ASMM方案然后给SGA配置HugePages。相关检查命令也不复杂grep -E HugePages_Total|HugePages_Free /proc/meminfo cat /sys/kernel/mm/transparent_hugepage/enabledHugePages_Free长期偏低说明大页不够用而Transparent HugePagesTHP如果处于always状态Oracle官方也建议关闭因为THP的碎片整理机制会导致偶发性能抖动。4.2 只看SGA不看PGA的超配问题很多同学调内存时眼里只有SGAPGA基本不管直到某天凌晨数据库日志里出现ORA-04036才反应过来。ORA-04036的含义是PGA总内存超过了PGA_AGGREGATE_LIMIT。这通常是以下几种情况叠加导致的并行查询/并行DML开得太多多个并行进程各自分配工作区排序量大的SQL在高并发下同时执行PGA_AGGREGATE_TARGET本身没设或设得太低而PGA_AGGREGATE_LIMIT又没有被合理调整。我见过一个案例PGA目标4GB但并行度8的报表SQL一跑20个会话同时进去每个工作区要2GB瞬间就把物理内存挤爆了。后来通过限制并行度、把PGA_AGGREGATE_LIMIT设成一个“物理内存能兜住且不影响其他进程”的值配合实时观察V$PGASTAT才把这个隐患压下去。日常监控可以这样看SELECT name, value, unit FROM v$pgastat WHERE name IN (aggregate PGA target parameter, aggregate PGA auto target, global memory bound, total PGA allocated, over allocation count);重点是over allocation count它统计的是PGA目标被突破的次数。只要这个值持续增长就要警惕PGA超配。4.3 静态参数不是改完就完SPFILE、PFILE与MAX类参数内存参数里“动态”和“静态”的边界很容易被忽略。SGA_TARGET、PGA_AGGREGATE_TARGET、DB_CACHE_SIZE、SHARED_POOL_SIZE都是动态参数在线改完立刻生效但MEMORY_MAX_TARGET、SGA_MAX_SIZE、REDO_LOG_BUFFER这些是静态参数只能通过SPFILE在重启后生效。更隐蔽的坑是启动文件。如果数据库是用PFILE启动的ALTER SYSTEM SET ... SCOPEBOTH或者SCOPESPFILE都会受到限制动态参数只能sCOPEMEMORY临时改重启后全部打回原形。正规生产环境应该用SPFILE启动改静态参数之前先备份SPFILECREATE PFILE FROM SPFILE;这个备份文件不大但关键时刻能救你一命。调SGA_TARGET之前我习惯先把SGA_MAX_SIZE或MEMORY_MAX_TARGET留够余量毕竟静态上限不是你想改就能在线改的等应用不让你重启的时候你会非常怀念那个提前写好的MAX值。4.4 验证盲区一轮调整后的AWR对比怎么做才可信调优不是“改完参数就收工”。我见过太多人改完一堆参数过了一周来问“为什么没效果”结果一问连调整前后的AWR对比都没做过全凭感觉。一个可复用的验证流程是确定基线时段选取一个完整的高峰窗口记录起始和结束快照ID调整参数并运行至少一个完整业务周期用awrddrpt.sql对比相同时间跨度的两个时段重点看Top等待事件、物理读次数、硬解析次数、DB Time、PGA Stat这几项如果网络、业务量本身波动很大最好多对比几天别被单日偶然数据带偏。我自己的习惯是给每一次变更写一页变更记录包含改了什么参数、为什么改、预期收益、实际结果。脑子的记忆不可靠几个月后再回来看这一页记录比任何监控截图都有用。最后再分享一个小技巧内存调优不要一次把所有参数都改到位。宁可分两三个窗口逐步调整每次跑完高峰看效果也不要在一次变更里同时动SGA_TARGET、PGA_AGGREGATE_TARGET、REDO_LOG_BUFFER和组件下限。变量太多的时候出了问题你根本不知道是谁的锅。这个习惯帮我挡掉了不少“调优事故”也希望你在模拟项目X这类环境里少走弯路。
的优化思路)
