内存架构生死战详解Oracle SGA与PGA的作用与调优艺术一、开篇暴击一个连接引发的血案二、全景对比流程图SQL执行时的SGA与PGA协作三、核心定义透析SGA与PGA的本质区别1.SGASystem Global Area 系统全局区2.PGAProgram Global Area 程序全局区四、SGA的“五脏六腑”必须搞懂的5大池子1.数据库缓冲区缓存Database Buffer Cache2.共享池Shared Pool3.重做日志缓冲区Redo Log Buffer4.大池Large Pool5.Java池与流池Java Pool Streams Pool五、PGA的“工作车间”排序与哈希的秘密六、黄金参数配置自动内存管理的正确姿势1.物理内存预估法2.独立控制模式更精细七、总结平衡的艺术The Begin点点关注收藏不迷路⬇ ⬇ 底部 ⬇ ⬇一、开篇暴击一个连接引发的血案假设你是一家电商的DBA大促期间数据库突然变慢CPU飙到100%。登录系统后发现没有死锁SQL逻辑没变但磁盘I/O暴增。问题根源往往不在于磁盘而在于内存。准确地说是SGA系统全局区和PGA程序全局区的配置失衡导致Oracle被迫用磁盘当内存使。今天我们就来一次说透Oracle内存架构中这对“黄金搭档”到底是如何分工的。二、全景对比流程图SQL执行时的SGA与PGA协作在深入理论前先通过一张流程图看清一条SQL从发起到返回结果是如何在SGA和PGA之间流转的。请重点关注橘色PGA私有区和蓝色SGA共享区的切换逻辑SGA专属职责PGA专属职责实例内存架构客户端1.专用连接2.传递SQL文本3.软解析直接执行3.硬解析需查询字典4.构建执行计划5.内存命中返回5.未命中触发物理读6.结果集返回7.数据发送私有内存区共享内存区用户发送SQLPGA-私有SQL工作区SGA-共享池-库缓存PGA-排序/哈希区SGA-共享池-字典缓存请求数据块SGA-数据库缓冲区缓存DBWn读数据文件排序/哈希/位图合并数据缓存/锁管理/重做缓冲三、核心定义透析SGA与PGA的本质区别1.SGASystem Global Area 系统全局区本质定义SGA是一块在实例启动时分配、所有进程共享的巨大内存池。它是一块“公共黑板”张三写了数据李四能立刻看到不需要刷盘。核心特征三个“共享”共享访问所有用户进程都能读写SGA中的特定区域。共享竞争因为共享所以需要“门闩”Latch和“队列”Lock来保护内存块这是高并发热点。实例级生命周期数据库实例启动时分配关闭时释放。2.PGAProgram Global Area 程序全局区本质定义PGA是每个服务进程私有的、独占的内存区。它不是一块整体而是与每个会话一一对应。它是一张“私有草稿纸”你草稿上的中间结果别人看不到也不需要锁保护。核心特征三个“私有”进程独占进程A无法读取进程B的PGA数据。工作区域主要用于存放排序、哈希连接等操作的临时中间结果。会话级生命周期会话创建时分配结束后释放。差异化对比表对比维度SGA系统全局区PGA程序全局区所有权实例级全库共享进程级会话独占主要内容数据块缓存、解析过的SQL、锁信息会话信息、排序临时空间、游标状态调优痛點争夺Library Cache Latch、Buffer Busy Waits磁盘排序过多、PGA内存溢出耗尽管理方式ASMM自动共享内存管理自动PGA管理PGA_AGGREGATE_TARGET关键参数SGA_TARGET, SGA_MAX_SIZEPGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY四、SGA的“五脏六腑”必须搞懂的5大池子SGA不是一整块铁板它内部划分了精细的功能区。配置不当会导致经典的“内存抖震”。1.数据库缓冲区缓存Database Buffer Cache作用存放从数据文件读取的数据块副本。查询与修改的第一站。色彩定位绿色——数据的高速公路服务区。灾难场景如果太小Oracle会频繁老化热点数据导致高代价的物理读I/O。2.共享池Shared Pool作用分为库缓存存SQL/PLSQL代码和字典缓存存数据字典元数据。色彩定位蓝色——编译器的缓存与字典的目录。硬解析之痛如果不使用绑定变量每一条SQL都硬解析一遍库缓存瞬间被撑爆CPU全耗在解析而非执行上。3.重做日志缓冲区Redo Log Buffer作用小而快的循环缓冲区记录数据变更的“重做记录”。色彩定位橘色——数据的黑匣子飞行记录仪。核心规则LGWR必须将这里的记录写到磁盘日志文件后事务才算提交完成。4.大池Large Pool作用分担共享池的压力专门给RMAN备份、共享服务器会话内存使用。色彩定位紫色——共享池的“减负外挂”。5.Java池与流池Java Pool Streams Pool作用特定功能池数据库内跑Java代码或使用Streams复制时启用。色彩定位红色——特种兵专用装备库。五、PGA的“工作车间”排序与哈希的秘密PGA分为两部分私有SQL区持久区存绑定变量、游标状态。生命周期随游标关闭而释放。SQL工作区运行时区排序、哈希连接、位图索引合并全在这里发生。单次操作的内存模型按操作类型分操作类型PGA工作区需求内存不足时的影响排序ORDER BY存放全排序或归并排序的中间段溢出到临时表空间磁盘排序极慢哈希连接HASH JOIN构建哈希表必须分片做哈希反复扫描大表位图索引合并存放各片段位图频繁的DML操作会导致位图膨胀六、黄金参数配置自动内存管理的正确姿势现代Oracle10g后推荐使用自动内存管理但两个关键阈值一定要设对。1.物理内存预估法总物理内存减去操作系统所需的余量留给Oracle。MEMORY_TARGET给Oracle的总内存含SGAPGA0表示禁用。MEMORY_MAX_TARGET上限值不可在线动态调大。2.独立控制模式更精细-- 修改SGA大小需重启生效如果超过SGA_MAX_SIZEALTERSYSTEMSETSGA_TARGET2G SCOPESPFILE;-- 修改PGA总量即时生效ALTERSYSTEMSETPGA_AGGREGATE_TARGET1G;最佳实践建议OLTP系统SGA占Oracle总内存的80%PGA占20%。重点缓存热点数据块和执行计划OLAP/DSS系统PGA占Oracle总内存的50%~60%。排序和哈希连接需要巨大的工作区监控视图V$PGA_TARGET_ADVICE和V$SGA_TARGET_ADVICE是你的调优罗盘。七、总结平衡的艺术SGA是共享的丰巢柜解决所有用户共性的缓存需求PGA是私人的密码箱解决单个进程个性的计算需求。一个优秀的DBA就是在SGA的内存命中率与PGA的工作区溢出之间找到那个完美的资源配比。对于SGA和PGA的配置你更倾向于使用MEMORY_TARGET的一刀切全自动托管还是分开设置去压榨最后一点性能欢迎在评论区写下你的运维哲学。The End点点关注收藏不迷路⬆ ⬆ 顶部 ⬆ ⬆
