接手一套跑了好几年的Oracle库最让人头疼的往往不是SQL怎么优化反而是内存怎么分配。SGA调大一点PGA就得让路PGA给足了排序会话一多又撑不住。Oracle内存管理修改SGA与PGA这件事表面上看就是几条ALTER SYSTEM命令但真正到了生产环境要考虑的东西远不止参数本身。这篇文章把我这些年调整SGA/PGA的真实经验、踩过的坑、以及排查思路完整梳理一遍争取让刚入门的DBA也能照着操作少走弯路。先说结论Oracle 11g之后默认开启了自动内存管理AMM很多场景下数据库会自己决定SGA和PGA怎么分但自动管理不代表一劳永逸。实际运维中业务并发模型变了、物理内存扩容了、某些池子频繁报4031错误都需要手动介入调整。这篇文章会从原理讲起再给出一套可以落地的修改流程和排查方法适合需要接手生产库、或正在准备OCP考试的朋友参考。1. 先搞懂SGA和PGA到底“管”什么1.1 SGA里那些“池子”分别干什么共享池Shared Pool存SQL文本、执行计划、数据字典缓存是所有会话共享的内存区域。它的命中率直接影响硬解析次数硬解析一旦上去CPU和锁等待很快飙升。缓冲区缓存Buffer Cache负责缓存数据块减少物理读;重做日志缓冲区Redo Log Buffer暂存重做记录提交频繁的应用如果把它调小了日志写入进程会频繁等待。此外还有Large Pool用于RMAN备份、并行查询Java Pool承载Java存储过程Streams Pool在Goldengate场景下会用到。很多人只盯着SGA_TARGET这个总大小忽略了SGA内各组件的分配是否合理。10g后自动共享内存管理ASMM会按压力自动调整Buffer Cache和Shared Pool等动态组件但Redo Log Buffer、Fixed SGA这类固定大小区域不会自动变化。这意味着即使SGA总内存够大如果Shared Pool被其他组件挤占仍然可能触发ORA-04031。1.2 PGA的自动与手动管理逻辑PGAProgram Global Area不是共享的它是每个服务进程私有的内存区域主要用于排序、哈希连接、位图合并等操作。11g之前要手动设置SORT_AREA_SIZE、HASH_AREA_SIZE这种参数DBA需要非常清楚业务是什么类型OLTP偏小、OLAP偏大。11g之后只需设PGA_AGGREGATE_TARGETOracle会根据负载自动分配单个进程的工作区大小。自动管理有一个容易误解的点PGA_AGGREGATE_TARGET只是一个目标值不是硬限制。Oracle可以在整体内存充足的情况下超分配。如果设置了PGA_AGGREGATE_LIMIT那么它才是硬顶超过后Oracle会终止并回滚会话。12c之后这个参数默认取PGA_AGGREGATE_TARGET的2倍或者2GB的较大值但手动调整的时候一定要让LIMIT大于TARGET否则实例直接起不来。1.3 自动管理时代为什么还要手动改参数Oracle 11g之后有了MEMORY_TARGET参数数据库能同时管理SGA和PGA的总和理论上无需手动干预。但生产环境里不少DBA为了稳定会关掉AMM退回ASMM甚至手动管理。原因很直接自动管理在负载剧烈波动时可能频繁触发SGA大小调整引发性能抖动;还有一部分早期版本的Bug使得AMM与大页配置冲突。另一个常见场景是迁移上云或物理机扩容后DBA需要把内存直接顶上去如果还依赖自动调整很多池子的增长会很保守性能优势发挥不出来。手动改SGA和PGA的另一个理由是可预测性。你明确知道Shared Pool需要多大、Buffer Cache需要多大参数配置就是确定性的减少了运行时动态调整的不确定因素。对于核心交易系统稳定可控往往比自动伸缩更重要。2. 动手前先做这件事摸清当前内存配置2.1 用SQL快速体检参数、组件、命中率修改参数之前我习惯先执行一组诊断SQL把当前状态拍个快照免得改完无从对比。第一条看参数-- 当前内存相关参数 SELECT name, value, isdefault, ismodified FROM v$parameter WHERE name IN (memory_target,memory_max_target, sga_target,sga_max_size,pga_aggregate_target,pga_aggregate_limit) ORDER BY name;再看实例当前的SGA分布和动态组件情况-- SGA各区域汇总 SELECT * FROM v$sgainfo; -- 动态组件当前大小与最小值 SELECT component, current_size/1024/1024 AS curr_mb, min_size/1024/1024 AS min_mb, user_specified_size/1024/1024 AS user_mb FROM v$sga_dynamic_components; -- 按池子统计内存占用 SELECT pool, name, bytes/1024/1024 AS mb FROM v$sgastat WHERE pool IS NOT NULL ORDER BY bytes DESC;这三条语句能快速定位SGA_MAX_SIZE是否足够大、Buffer Cache和Shared Pool谁占大头、各池子是否出现了奇怪的组件膨胀。我遇到过好几次共享池里PL/SQL对象大量堆积就是靠第二条和第三条发现的。2.2 PGA真的吃紧了吗v$pgastat关键指标PGA健康度不能只看PGA_AGGREGATE_TARGET数值要结合v$pgastat观察。最关键的三个指标aggregate PGA target parameter当前目标值over allocation count历史累计的PGA超分配次数如果持续增长业务高峰期真实PGA需求超过目标需要调大PGA memory freed back to OS / total PGA inuse看当前PGA实际使用量与历史峰值。SELECT * FROM v$pgastat WHERE name IN (aggregate PGA target parameter,over allocation count, total PGA allocated,total PGA inuse,maximum PGA allocated);如果over allocation count很大说明当前PGA_AGGREGATE_TARGET设置偏小排序、哈希操作被迫频繁刷盘到临时表空间SQL性能会受到明显影响。反过来如果total PGA inuse长期不到目标值的一半说明PGA可能给大了可以考虑把多余内存匀给SGA。2.3 计算目标大小前先看操作系统层这一步很多人会漏。Oracle能用的内存上限受操作系统物理内存和内核参数约束改参数之前一定要先用free -g确认可用内存再用ipcs -lm看共享内存限制。free -g ipcs -lm | head -5 cat /proc/sys/kernel/shmmax cat /proc/sys/kernel/shmall尤其在Linux下SGA通常依赖共享内存。如果kernel.shmmax小于你设置的SGA_MAX_SIZE实例启动时会直接报ORA-27102: out of memory。虽然现代Linux很多用/dev/shm实现AMM但共享内存段的限制仍然不能忽略。先确认系统层容量再计算目标值才是稳妥顺序。3. 修改SGA与PGA的标准操作流程3.1 SCOPE参数的三档选择用ALTER SYSTEM修改初始化参数时最让人困惑的就是SCOPE。它有三个值MEMORY只改当前实例重启失效适合临时测试SPFILE只写服务器参数文件必须重启后生效BOTH既改当前实例又写入SPFILE部分参数不支持BOTH。判断一个参数是否支持动态修改可以查v$parameter的ISSYS_MODIFIABLE字段。比如SGA_TARGET是动态的可以BOTH;而SGA_MAX_SIZE是静态的只能SPFILE改完必须重启。有一个常见错误是SGA_MAX_SIZE没有预留余量SGA_TARGET却调到和它一样大。后续你想再扩大SGA_TARGET发现SGA_MAX_SIZE已是瓶颈又得安排一次重启。所以静态参数的修改一定要为未来留出空间。3.2 实操改SGA_TARGET和PGA_AGGREGATE_TARGET假设服务器物理内存64GB计划给Oracle分配32GB其中SGA 24GB、PGA 8GB并预留一部分给操作系统和其他中间件。在AMM关闭的前提下操作如下-- 1. 如果启用过AMM先关闭 ALTER SYSTEM SET memory_target0 SCOPESPFILE; ALTER SYSTEM SET memory_max_target0 SCOPESPFILE; -- 2. 设置SGA上限与目标 ALTER SYSTEM SET sga_max_size24G SCOPESPFILE; ALTER SYSTEM SET sga_target24G SCOPESPFILE; -- 3. 设置PGA目标 ALTER SYSTEM SET pga_aggregate_target8G SCOPESPFILE; ALTER SYSTEM SET pga_aggregate_limit16G SCOPESPFILE;这里有几个细节值得说明。SGA_TARGET如果设置成和SGA_MAX_SIZE一致相当于从一开始就告诉Oracle“整个SGA你都可以用”不会因为ASMM自动调整而束手束脚。PGA_AGGREGATE_LIMIT设置为PGA_AGGREGATE_TARGET的2倍给会话峰值留出缓冲又不至于无限侵蚀内存。PGA_AGGREGATE_LIMIT的默认值逻辑虽然是MAX(2*TARGET, 2GB)但在内存规划严格的生产环境最好显式指定。如果是11g之后默认AMM开启的库从MEMORY_TARGET管理切回SGA/PGA分别管理要注意MEMORY_TARGET设为0后SGA和PGA参数才真正独立生效。这个顺序不能反否则改完发现实例依然被MEMORY_TARGET控制着。3.3 从SPFILE反推PFILE的应急修改法生产库有时会遇到SPFILE损坏或者有人误改了参数导致实例无法启动。这时用PFILE启动并重建SPFILE是标准自救手段。步骤很固定# 1. 从现有SPFILE生成PFILE备份 sqlplus / as sysdba SQL CREATE PFILE/tmp/initORCL.ora FROM SPFILE; # 2. 编辑PFILE调整参数 vi /tmp/initORCL.ora # 3. 用PFILE启动实例 SQL STARTUP PFILE/tmp/initORCL.ora; # 4. 验证参数后重新生成SPFILE SQL CREATE SPFILE FROM PFILE/tmp/initORCL.ora; SQL SHUTDOWN IMMEDIATE; SQL STARTUP;这个方法我实际用过不止一次。大部分时候是因为改错了SGA_MAX_SIZE导致实例无法启动用PFILE把参数改回安全值再重建SPFILE就恢复。注意PFILE和SPFILE混用时要看清当前实例到底用哪个文件启动v$parameter里的spfile字段为NULL说明用的是PFILE启动。3.4 重启后验证配置是否生效修改静态参数后验证不只是SHOW PARAMETER看一眼。真正的验证要看v$sgainfo里的当前组件大小、v$pgastat里的目标值以及操作系统层面的实际内存占用。SHOW PARAMETER sga_target; SHOW PARAMETER sga_max_size; SHOW PARAMETER pga_aggregate_target; -- 确认SGA组件按预期分配 SELECT component, current_size/1024/1024/1024 AS curr_gb FROM v$sga_dynamic_components; -- 确认PGA目标 SELECT name, value/1024/1024/1024 AS val_gb FROM v$pgastat WHERE name IN (aggregate PGA target parameter,aggregate PGA limit parameter);另外建议观察启动后的buffer cache命中率、共享池空闲内存等指标。如果Buffer Cache命中率长期偏低说明给它的内存没有被有效利用;如果Shared Pool空闲内存很小且经常4031就要考虑增加共享池大小。命中率这类指标不能过度追求100%OLTP系统里逻辑读高、物理读低才是正常形态。4. 修改后最常踩的坑与排查方法4.1 ORA-27102和shmmax的限制ORA-27102是我见过最频繁的启动失败原因之一。报错信息会提示out of memory但实际往往是Linux内核参数限制。对照检查顺序# 查看当前共享内存限制 cat /proc/sys/kernel/shmmax cat /proc/sys/kernel/shmall # 查看已挂载共享内存 ipcs -m如果SGA_MAX_SIZE超过shmmax最简单的解决方式是让Oracle使用多个共享内存段但这会影响性能不推荐更合理的做法是调大shmmax。临时生效sysctl -w kernel.shmmax34359738368永久生效则在/etc/sysctl.conf写kernel.shmmax 34359738368 kernel.shmall 4194304然后执行sysctl -p。shmall的单位是内存页常见页大小4KB所以shmall4194304对应16GB。具体数值按实际需要计算。4.2 ORA-04031shared pool分配不足ORA-04031是“shared pool内存不足”的经典报错通常伴随大量硬解析、SQL/PL/SQL对象缓存过大或者共享池内存碎片化。排查思路查看共享池里哪些对象占用大SELECT * FROM ( SELECT pool, name, bytes/1024/1024 AS mb FROM v$sgastat WHERE pool shared pool ORDER BY bytes DESC ) WHERE ROWNUM 20;查看Library Cache命中率SELECT namespace, gets, gethitratio, pins, pinhitratio FROM v$librarycache WHERE namespace SQL AREA;短期内缓解可以ALTER SYSTEM FLUSH SHARED_POOL但这只是治标频繁flush反而增加硬解析。根本方案是评估SQL复用情况、绑定变量使用情况、以及是否要给shared_pool_reserved_size预留空间。如果代码里大量拼接字符串靠调整SGA参数治不好只能推动开发改绑定变量。这里要特别提醒不要在业务高峰期动态缩小SGA_TARGET来给Shared Pool腾内存SGA内部各池子之间自动调整本身有滞后性等你调完可能已经出了一堆4031。更好的方式是在低峰期整体规划SGA布局。4.3 大页HugePages踩过的雷Linux环境里如果启用了HugePages而Oracle同时用AMM实例启动可能失败。AMM和HugePages在Linux上不兼容因为AMM需要动态调整SGA大小而HugePages是固定分配共享内存的。生产环境如果追求稳定我的建议是要么关AMM用HugePages要么用AMM但不用HugePages。HugePages配置的常见流程# 查看当前大页情况 grep HugePages /proc/meminfo grep Hugepagesize /proc/meminfo # 配置nr_hugepages建议覆盖SGA大小 echo 30000 /proc/sys/vm/nr_hugepages同时把Oracle用户的内存锁限制放开设置use_large_pagesONLY或者改为TRUE。配置不当最常见的结果是实例启动时提示cannot allocate memory或者内存占用看起来很小但性能比预期差。排查HugePages是否被Oracle使用可以看实例启动日志或v$sgainfo里的大页状态。要记住算好需求HugePages_Total × Hugepagesize 要略大于SGA_MAX_SIZE最好留2%余量。4.4 参数改了但没按预期走另一类问题并不是报错而是参数修改后效果不对。典型场景明明设置了SGA_TARGET但v$sgainfo里Buffer Cache大小和Shared Pool大小几乎不变明显不是按比例分配。这多半是因为启用了AMMSGA_TARGET只是下级参数真正做主的是MEMORY_TARGET。修改PGA_AGGREGATE_TARGET后PGA实际占用不见下降。这是因为PGA_AGGREGATE_TARGET本身是目标值Oracle优先保证现有会话的PGA使用不被强拆只有当新会话需要内存时才按新目标执行。某些参数显示MODIFIED但ISSYS_MODIFIABLE为FALSE比如sga_max_size这表示改动其实没生效需要重启。判断这些问题最直接的方式是alert日志。每次STARTUP和ALTER SYSTEM操作后alert日志都会记录参数变化如果怀疑有参数没生效优先看日志而不是查视图。5. 日常运维中看得见效果的经验5.1 参数调整和业务低谷期怎么配合内存调整虽不像重建索引那样锁表但我们可以选择合适时间窗口。SGA_TARGET动态调大影响较小但PGA_AGGREGATE_TARGET在高峰期调小可能让当前活跃的排序会话瞬间超分配触发临时表空间爆满。所以凡是涉及PGA的调整我习惯放在业务低谷。实际运维中我会在每周维护窗口固定核对一次AWR报告中的内存指标而不是等到监控报警。在低峰期调整还有个好处方便对比。比如计划把Buffer Cache从8G调到12G同一张报表SQL在调前调后跑一次能直观看到物理读下降了多少。没有对比的调整就像蒙着眼睛开车。5.2 从AWR/ASH里读内存信号如果只是偶尔看一次v$pgastat很多问题看不出来。跨时间维度的负载变化还是要靠AWR。AWR报告里Memory Statistics和SGA Breakdown Difference两个部分直接告诉你两次快照之间内存分配发生了什么。另外Advisory Statistics也是很好的参考-- Buffer Cache建议 SELECT size_for_estimate, estd_physical_read_factor FROM v$db_cache_advice; -- Shared Pool建议 SELECT shared_pool_size_for_estimate, estd_lc_size, estd_lc_memory_object_hits FROM v$shared_pool_advice;这两个Advisor视图在内存问题初期排查时非常实用。看到estd_physical_read_factor从1.0降到0.8说明扩大缓存明显有利;如果变化不大说明再加大内存也只是浪费。我遇到过不少案例业务方很早就说“数据库慢”AWR一看Buffer Cache命中率99%明显瓶颈根本不在内存结果花大把时间调整SGA给错了方向。先看建议视图再决定动多少能少走弯路。5.3 内存监控的三个长期指标日常监控不建议每天盯着ORACLE的几百个动态性能视图看精力有限盯三个指标就够SGA各组件当前大小、PGA超分配次数、以及操作系统层面SGA实际驻留内存。把这三个指标拉成日曲线和业务高峰对比基本能看清内存配置和负载的关系。监控脚本我是这么写的每小时采集v$sga_dynamic_components和v$pgastat关键项落入一张历史表。出现异常时直接查历史趋势一眼看出是突发的负载问题还是缓慢的内存泄漏。DBA工作里最有价值的就是趋势数据没有历史曲线的参数调整出了问题都无从归因。实际操作中我在调整完SGA与PGA后会额外做一件事观察Linux的swap使用。Oracle和操作系统层面最怕出现swap抖动swap一旦增长意味着内存确实不够用此时不管SGA还是PGA的调整都只是拆东墙补西墙根本方案是扩容物理内存或减少库内多余进程。这听起来像废话但生产环境里就是有不少案例是物理内存只剩2GB还在纠结SGA要不要调大1GB这是先锋的错误方向。6. 几个实操中容易忽略的细节6.1 修改内存参数时先看spfile还是pfile不同环境Oracle实例启动文件不一样。用spfile启动时ALTER SYSTEM SET ... SCOPESPFILE是直接写进二进制参数文件用pfile启动时SCOPESPFILE会直接报错ORA-32017。所以在执行修改前我一直建议先确认启动方式SELECT DECODE(value, NULL, PFILE, SPFILE) AS startup_type FROM v$parameter WHERE name spfile;很多初学者在测试库上装完Oracle默认用pfile启动结果一执行SCOPESPFILE就报错就会以为命令有问题。实际上这是启动方式不匹配造成的。更稳妥的做法是先创建spfile让数据库以spfile方式启动之后再进行参数调整。6.2 动态参数改完别忘了保存SGA_TARGET支持SCOPEBOTH一个命令同时改实例和spfile很省事。但也有喜欢用SCOPEMEMORY先试效果的人试完发现效果好结果忘了写回spfile下次重启全回退。这是非常低级的失误却经常发生。我的习惯是任何临时调整都全程记录确认生效且稳定后立刻补一条SCOPESPFILE持久化命令。-- 临时测试 ALTER SYSTEM SET sga_target16G SCOPEMEMORY; -- 验证效果后持久化 ALTER SYSTEM SET sga_target16G SCOPESPFILE;6.3 调整内存不等于调整了一切最后说一个我踩过很多次才明白的道理内存参数优化经常只是表象。某一周我接手一个库AWR显示Buffer Cache命中率不到90%我花了一个晚上调整SGA布局命中率上去了但应用还是慢。最后定位下来根因是一条SQL在大表上做了全表扫描而表统计信息过期优化器选了错误的执行计划。修正统计信息之后物理读直接下降一个数量级内存指标自然就好看了。所以在内存调优这条路上方向不要搞反。先看SQL再看内存先看等待事件再动参数。内存调整是兜底手段而不是第一优先级的优化手段。如果SQL本身写得不合理调多少SGA都只是让慢SQL跑得快一点但永远不可能让它快到位。6.4 版本差异带来的参数名变化不同Oracle版本对内存管理的参数命名和行为略有差异。11g是AMM概念成型的关键版本12c之后引入了PGA_AGGREGATE_LIMIT19c中Streams Pool组件逐渐淡出视线。如果在旧版本库上把新版本才有的参数写进去数据库可能直接忽略或报错。尤其是从11g迁移到19c的项目一定要先对比两个版本的初始化参数模板19c新加的PGA_AGGREGATE_LIMIT如果没设置默认行为可能和源库完全不同。这种版本差异很难通过简单记忆去覆盖我通常的做法是在目标版本上创建一个空实例用正常的建库流程生成一份初始化参数文件再逐项和源库比对把不一致的参数挑出来逐项确认后再上线。内存参数这种全局影响的项甚至要提前在一套测试环境上完整演练一遍。我个人的体会是Oracle的内存管理尤其是SGA和PGA的调整看起来就是几个数字的改动真正决定成败的是修改前的判断和修改后的验证。每次调整都要有依据——要么是AWR里的等待事件要么是v$pgastat里的超分配计数要么是业务侧明确的容量需求。拍脑袋调内存短时间内可能看不出问题等到高峰期一到各种异常就会集中爆发。最后再分享一个小技巧每次调整前都可以导出一份全参数快照调整后再导一份做差异对比这样任何参数被意外改动你都能第一时间发现。这套方法我沿用多年省下了不少排障时间。