内存铁三角深度解析Oracle共享池、数据缓冲区与重做日志缓冲区的协作与区别一、开篇三问一条UPDATE语句触碰了哪些内存区二、全景协作图一条UPDATE语句的内存之旅三、三大内存区域的核心定义它们到底存什么1.共享池Shared PoolSQL与数据字典的“编译缓存”2.数据库缓冲区缓存Database Buffer Cache数据的“高速公路服务区”3.重做日志缓冲区Redo Log Buffer数据的“黑匣子飞行记录仪”四、三大内存区域的五大核心区别五、三大内存区域的协作关系UPDATE的完整生命周期1.第一阶段共享池解析SQL2.第二阶段数据缓冲区读取与修改数据3.第三阶段重做日志缓冲区记录变更六、三大内存区域的调优策略1.共享池调优解决硬解析问题2.数据缓冲区调优解决物理读问题3.重做日志缓冲区调优解决日志写入等待问题七、三大内存区域对比速查表八、总结记住这个“厨房三角”类比就够了The Begin点点关注收藏不迷路⬇ ⬇ 底部 ⬇ ⬇一、开篇三问一条UPDATE语句触碰了哪些内存区当你在SQL*Plus中执行一条最简单的更新语句UPDATEempSETsalarysalary*1.1WHEREemp_id100;COMMIT;这条语句在Oracle内部需要经过三个完全不同的内存区域每个区域负责完全不同的任务SQL文本放哪里解析→共享池Shared Pool数据块从哪里读取和修改→数据库缓冲区缓存Database Buffer Cache修改记录写哪里保证不丢失→重做日志缓冲区Redo Log Buffer这三个内存区域构成了Oracle SGA中的**“内存铁三角”**它们各司其职又紧密协作共同保证了SQL执行的效率和数据的安全性。今天这篇文章带你从一条UPDATE的视角彻底拆解这三者的关系与区别。二、全景协作图一条UPDATE语句的内存之旅先通过一张完整的流程图看清一条UPDATE从发起到提交是如何在三大内存区域之间流转的物理磁盘LGWR后台进程重做日志缓冲区-Redo Buffer数据缓冲区-Buffer Cache共享池-Library Cache用户进程物理磁盘LGWR后台进程重做日志缓冲区-Redo Buffer数据缓冲区-Buffer Cache共享池-Library Cache用户进程alt[未命中-物理读]DBWn稍后异步将脏块写入数据文件1.发送SQL文本2.硬解析/软解析3.语法检查、语义检查4.生成执行计划5.请求读取emp_id100的数据块6.检查Buffer Cache是否命中7.从数据文件读取数据块8.数据块加载到Buffer Cache9.返回数据块给用户进程10.在Buffer Cache中修改salary字段11.标记为脏块(Dirty Buffer)12.生成重做记录(变更向量)13.重做记录写入Redo Log Buffer14.用户执行COMMIT15.触发LGWR写日志16.Redo Log Buffer写入联机重做日志17.提交成功返回关键路径标注蓝色路径步骤1-4共享池负责SQL解析与执行计划生成。绿色路径步骤5-11数据缓冲区负责数据块的读取与修改。橘色路径步骤12-17重做日志缓冲区负责变更记录与提交保护。红色节点步骤16事务提交的关键路径——LGWR必须将日志写入磁盘。三、三大内存区域的核心定义它们到底存什么1.共享池Shared PoolSQL与数据字典的“编译缓存”精确描述共享池是SGA中用于缓存已解析的SQL语句、PL/SQL代码和执行计划的内存区域。它的核心目标是避免重复解析相同的SQL减少CPU消耗。共享池的两大子区域子区域存储内容核心作用库缓存Library Cache解析过的SQL文本、执行计划、PL/SQL代码实现软解析避免重复硬解析数据字典缓存Dictionary Cache数据字典的元数据表定义、列信息、权限加速SQL解析时的对象引用共享池的核心特征共享访问所有用户会话共享同一块共享池。LRU淘汰使用最近最少使用算法淘汰旧条目。碎片风险大量小SQL可能导致共享池碎片化。关键参数SHARED_POOL_SIZE查看共享池大小与命中率-- 查看共享池大小SELECTcomponent,current_size/1024/1024ASsize_mbFROMv$sga_dynamic_componentsWHEREcomponentshared pool;-- 查看库缓存命中率应99%SELECTnamespace,gets,gethits,ROUND(gethits/gets*100,2)AShit_ratioFROMv$librarycacheWHEREnamespaceIN(SQL AREA,TABLE/PROCEDURE);2.数据库缓冲区缓存Database Buffer Cache数据的“高速公路服务区”精确描述数据库缓冲区缓存是SGA中用于缓存从数据文件读取的数据块副本的内存区域。所有用户的数据查询和修改操作都必须在Buffer Cache中完成不能直接操作磁盘上的数据文件。Buffer Cache的三种缓冲区状态状态说明何时产生干净缓冲Clean Buffer内存数据与磁盘数据完全一致数据文件读入后未修改脏缓冲Dirty Buffer内存数据已被修改但尚未写入磁盘执行UPDATE/DELETE/INSERT后空闲缓冲Free Buffer未使用的缓冲区等待被填入数据DBWn写入后释放Buffer Cache的核心特征按块缓存以Oracle数据块通常8KB为单位管理。多池设计可分为Keep池、Recycle池、Default池。LRU淘汰使用LRU算法淘汰冷数据块。关键参数DB_CACHE_SIZE查看Buffer Cache大小与命中率-- 查看Buffer Cache大小SELECTcomponent,current_size/1024/1024ASsize_mbFROMv$sga_dynamic_componentsWHEREcomponentDEFAULT buffer cache;-- 查看Buffer Cache命中率应95%SELECTname,valueFROMv$sysstatWHEREnameIN(physical reads,consistent gets,db block gets);-- 命中率 1 - (physical reads) / (consistent gets db block gets)3.重做日志缓冲区Redo Log Buffer数据的“黑匣子飞行记录仪”精确描述重做日志缓冲区是SGA中一个小而快的循环缓冲区用于记录数据块的所有变更向量Change Vector。它的核心目标是确保已提交的事务永不丢失是实现数据库恢复的基石。Redo Log Buffer记录的内容修改了哪个数据块。修改了数据块中的哪个偏移位置。修改前的值是什么用于UNDO。修改后的值是什么用于REDO。Redo Log Buffer的核心特征循环写入写满后从头部重新开始覆盖。极快极小通常只有几十MB但写入频率极高。先写日志原则脏块写入数据文件前对应的重做记录必须先写入磁盘。关键参数LOG_BUFFER查看Redo Log Buffer大小-- 查看Redo Log Buffer大小SHOWPARAMETER log_buffer;-- 查看Redo Log Buffer的等待事件SELECTevent,total_waits,average_waitFROMv$system_eventWHEREeventLIKE%log buffer%;四、三大内存区域的五大核心区别序号对比维度共享池数据缓冲区重做日志缓冲区1存储内容SQL文本、执行计划、数据字典缓存从数据文件读出的数据块副本数据块变更的“重做记录”2核心目标避免重复解析SQL减少CPU消耗避免重复物理读减少磁盘I/O保证事务持久性支持崩溃恢复3分配粒度按SQL语句分配大小不等按数据块分配固定大小8KB按重做记录分配大小不等4淘汰算法LRU淘汰旧SQL和执行计划LRU淘汰冷数据块不淘汰循环覆盖5写盘时机不需要写盘解析结果可重建DBWn异步批量写盘LGWR同步写盘提交时或提前刷盘五、三大内存区域的协作关系UPDATE的完整生命周期1.第一阶段共享池解析SQLUPDATEempSETsalarysalary*1.1WHEREemp_id100;发生了什么服务器进程接收SQL文本计算其哈希值。在共享池的库缓存中查找哈希值。如果命中软解析直接获取执行计划。如果未命中硬解析进行语法检查→语义检查→生成执行计划→存入共享池。如果共享池太小频繁发生硬解析CPU飙升。已缓存的执行计划被挤出形成“颠簸”。2.第二阶段数据缓冲区读取与修改数据发生了什么执行计划指示需要读取emp_id100的数据块。在Buffer Cache中查找该数据块。如果命中逻辑读直接在内存中读取。如果未命中物理读从数据文件读入Buffer Cache。在Buffer Cache中修改salary字段的值。将该数据块标记为“脏块”Dirty Buffer。如果Buffer Cache太小频繁发生物理读磁盘I/O飙升。热点数据块被反复换入换出查询性能骤降。3.第三阶段重做日志缓冲区记录变更发生了什么服务器进程生成一条重做记录“将emp_id100的salary从1000改为1100”。将重做记录写入Redo Log Buffer。用户执行COMMIT。LGWR进程立即将Redo Log Buffer中的对应记录写入联机重做日志磁盘文件。写入成功后事务才算正式提交。向用户返回“提交成功”。如果Redo Log Buffer太小log buffer space等待事件增加。日志写入频繁LGWR压力增大。六、三大内存区域的调优策略1.共享池调优解决硬解析问题核心指标库缓存命中率 99%调优策略使用绑定变量这是解决硬解析的最根本手段。增大SHARED_POOL_SIZE当命中率偏低时扩容。固定关键SQL使用DBMS_SHARED_POOL.KEEP锁定重要执行计划。监控视图V$LIBRARYCACHE,V$SQLAREA-- 找出未使用绑定变量的SQL硬解析大户SELECTsql_id,executions,parse_callsFROMv$sqlareaWHEREparse_callsexecutionsORDERBYparse_callsDESCFETCHFIRST10ROWSONLY;2.数据缓冲区调优解决物理读问题核心指标Buffer Cache命中率 95%调优策略增大DB_CACHE_SIZE当物理读占比过高时扩容。使用多缓冲池热点表放入Keep池冷数据放入Recycle池。优化SQL减少全表扫描多用索引。监控视图V$BUFFER_POOL_STATISTICS-- 查看缓冲区池的命中率SELECTname,physical_reads,db_block_gets,consistent_gets,ROUND(1-(physical_reads)/(db_block_getsconsistent_gets),4)*100AShit_ratioFROMv$buffer_pool_statistics;3.重做日志缓冲区调优解决日志写入等待问题核心指标log buffer space等待事件接近0调优策略增大LOG_BUFFER当redo log space requests不为0时扩容。优化提交频率减少不必要的频繁提交。使用批量提交批处理作业使用批量提交而非逐行提交。监控视图V$SYSSTAT-- 检查Redo Log Buffer是否有空间等待SELECTname,valueFROMv$sysstatWHEREnameIN(redo log space requests,redo entries);-- 如果 redo log space requests 0说明需要增大LOG_BUFFER七、三大内存区域对比速查表序号内存区域存的什么太小会怎样多大合适调优核心指标1共享池SQL文本、执行计划、字典缓存CPU飙升频繁硬解析SGA的20%-30%库缓存命中率99%2数据缓冲区数据文件的数据块副本磁盘I/O飙升物理读频繁SGA的50%-60%Buffer Cache命中率95%3重做日志缓冲区数据变更的重做记录日志写入等待事务提交变慢通常16MB-64MBredo log space requests0八、总结记住这个“厨房三角”类比就够了【共享池是菜谱架数据缓冲区是操作台重做日志缓冲区是监控录像】共享池菜谱架存放做菜的配方SQL执行计划。如果菜谱架太小每次做菜都得临时找配方效率极低。数据缓冲区操作台存放食材原料数据块。如果操作台太小得频繁去仓库磁盘取食材时间全浪费在路上。重做日志缓冲区监控录像记录每一次刀起刀落数据变更。万一厨房着火系统崩溃靠录像能还原出每一道菜做到哪一步。理解了这三大内存区域的分工与协作你就掌握了Oracle SGA调优的三大核心命脉。调优时先看三个命中率哪个指标异常就针对性地解决哪个内存区的问题做到有的放矢而不是盲目增加内存。你在日常运维中最常遇到哪个内存区域的性能问题是用什么手段发现和解决的欢迎在评论区分享你的实战经验。The End点点关注收藏不迷路⬆ ⬆ 顶部 ⬆ ⬆