【Oracle数据库指南】第14篇：Oracle内存结构管理—

上一篇【第13篇】Oracle实例架构深度解析——SGA、进程与实例启动全流程
下一篇【第15篇】Oracle后台进程详解——DBWR、LGWR、CKPT、SMON、PMON工作机制

摘要

本文深入讲解Oracle数据库的内存管理机制，重点解析SGA的各组成部分——数据库缓冲区缓存、共享池、重做日志缓冲区的工作原理和参数配置，介绍Oracle 11g的自动内存管理（AMM）和自动共享内存管理（ASMM），并通过实际案例演示内存参数的调优方法。

一、Oracle内存架构总览

Oracle使用两个主要内存区域：

Oracle内存 ├── SGA（System Global Area，系统全局区）—— 所有用户共享 │ ├── Database Buffer Cache（数据库缓冲区缓存） │ ├── Shared Pool（共享池） │ │ ├── Library Cache（库缓存） │ │ └── Data Dictionary Cache（数据字典缓存） │ ├── Redo Log Buffer（重做日志缓冲区） │ ├── Large Pool（大型池，可选） │ ├── Java Pool（Java池，可选） │ └── Streams Pool（流池，可选） └── PGA（Program Global Area，程序全局区）—— 每个进程私有 ├── Sort Area（排序区） ├── Hash Area（哈希区） ├── Bitmap Merge Area（位图合并区） └── Session Memory（会话信息）

二、数据库缓冲区缓存（Database Buffer Cache）

2.1 工作机制

-- 缓冲区缓存使用LRU（Least Recently Used）算法管理-- 每个数据块在缓冲区中有三种状态：-- - Clean Buffer（干净缓冲区）：内容与磁盘一致-- - Dirty Buffer（脏缓冲区）：已修改，等待写入磁盘-- - Free Buffer（空闲缓冲区）：未使用-- 读取一个数据块的过程：-- 1. 检查缓冲区缓存（Hash Bucket）-- 2. 若命中（Cache Hit）：直接从内存读取（逻辑读）-- 3. 若未命中（Cache Miss）：从磁盘读取，放入缓冲区，然后读取（物理读）-- 查看缓冲区缓存的命中率SELECTROUND((1-phy.value/(cur.value+con.value))*100,2)ASbuffer_hit_pctFROMv$sysstat phy,v$sysstat cur,v$sysstat conWHEREphy.name='physical reads'ANDcur.name='db block gets'ANDcon.name='consistent gets';-- 更详细的统计（包含各类型读）SELECT'Buffer Cache Hit Ratio'ASmetric,ROUND(100*(1-SUM(DECODE(name,'physical reads',value,0))/(SUM(DECODE(name,'db block gets',value,0))+SUM(DECODE(name,'consistent gets',value,0)))),2)AShit_pctFROMv$sysstatWHEREnameIN('physical reads','db block gets','consistent gets');

2.2 缓冲区缓存的大小配置

-- 查看当前缓冲区缓存配置SHOWPARAMETER db_cache_sizeSHOWPARAMETER db_block_size-- 缓冲区缓存的大小 = db_cache_size / db_block_size 个数据块-- 动态调整缓冲区大小（无需重启）ALTERSYSTEMSETdb_cache_size=512M SCOPE=BOTH;-- 多块大小支持（当数据库有多种块大小时）SHOWPARAMETER db_2k_cache_sizeSHOWPARAMETER db_4k_cache_sizeSHOWPARAMETER db_8k_cache_sizeSHOWPARAMETER db_16k_cache_sizeSHOWPARAMETER db_32k_cache_size-- 为非标准块大小创建独立缓冲区ALTERSYSTEMSETdb_16k_cache_size=64M;-- 需要先创建16KB块的表空间才有意义-- Buffer Cache建议器（Oracle提供的内存调优建议）SELECTsize_for_estimate*8/1024AS"估算大小(MB)",buffers_for_estimateAS"缓冲区数",estd_physical_read_factorAS"物理读比率",ROUND((1-estd_physical_read_factor)*100,2)AS"预期命中率%"FROMv$db_cache_adviceWHEREname='DEFAULT'ANDblock_size=(SELECTvalueFROMv$parameterWHEREname='db_block_size')ORDERBYsize_for_estimate;

2.3 Keep和Recycle缓冲池

Oracle提供三种类型的缓冲池，适用于不同访问特性的数据：

-- DEFAULT Pool（默认池）：标准LRU管理-- KEEP Pool（保留池）：保持热数据块，防止被换出-- RECYCLE Pool（回收池）：大扫描数据，立即回收，防止污染默认池-- 配置各缓冲池大小ALTERSYSTEMSETdb_keep_cache_size=64M;-- 用于频繁访问的小表ALTERSYSTEMSETdb_recycle_cache_size=128M;-- 用于全表扫描的大表-- 将表分配到特定缓冲池ALTERTABLEemployees STORAGE(BUFFER_POOL KEEP);-- 频繁查询的小表ALTERTABLEorder_history STORAGE(BUFFER_POOL RECYCLE);-- 偶尔全表扫描的大表-- 查看各缓冲池的使用情况SELECTid,name,block_size,ROUND(physical_reads/DECODE(db_block_gets+consistent_gets,0,1,db_block_gets+consistent_gets)*100,2)ASmiss_pctFROMv$buffer_pool_statistics;

三、共享池（Shared Pool）

3.1 Library Cache（库缓存）

Library Cache缓存已解析的SQL语句和PL/SQL代码，避免重复解析，节省CPU和内存。

-- 查看Library Cache详细统计SELECTnamespace,gets,gethits,ROUND(gethits/DECODE(gets,0,1,gets)*100,2)AShit_pct,reloads,invalidationsFROMv$librarycacheORDERBYgetsDESC;-- 重要命名空间：-- SQL AREA：SQL语句-- TABLE/PROCEDURE：PL/SQL对象-- BODY：包体-- 查看共享SQL（高执行次数的SQL）SELECTROUND(elapsed_time/1000000,2)ASelapsed_sec,executions,ROUND(elapsed_time/DECODE(executions,0,1,executions)/1000000,4)ASavg_elapsed,buffer_gets,ROUND(buffer_gets/DECODE(executions,0,1,executions))ASbufgets_per_exec,SUBSTR(sql_text,1,80)ASsql_textFROMv$sqlareaWHEREexecutions>100ORDERBYelapsed_timeDESC;-- 查看未使用绑定变量的SQL（性能风险）SELECTCOUNT(*)ASnon_bind_sql_countFROMv$sqlareaWHEREexecutions=1ANDcommand_type=3-- SELECTANDelapsed_time>100000;-- 执行时间超过0.1秒-- 刷新共享池（谨慎使用，生产环境会短暂影响性能）-- ALTER SYSTEM FLUSH SHARED_POOL;

3.2 Data Dictionary Cache（数据字典缓存）

数据字典缓存保存数据库对象的元数据（表、列、权限等信息）。

-- 查看数据字典缓存命中率SELECTSUM(gets)AStotal_gets,SUM(getmisses)AStotal_misses,ROUND((1-SUM(getmisses)/DECODE(SUM(gets),0,1,SUM(gets)))*100,2)AShit_pctFROMv$rowcache;-- 如果数据字典缓存命中率低于95%，考虑增大共享池ALTERSYSTEMSETshared_pool_size=256M SCOPE=BOTH;

3.3 共享池调优

-- 共享池建议器SELECTshared_pool_size_for_estimate/1024/1024AS"估算大小(MB)",shared_pool_size_factorAS"大小因子",estd_lc_size/1024/1024AS"估算LC大小(MB)",estd_lc_memory_object_hitsAS"估算LC命中次数"FROMv$shared_pool_adviceORDERBYshared_pool_size_for_estimate;-- 锁定大型PL/SQL对象（防止被换出，减少重新加载次数）BEGINDBMS_SHARED_POOL.KEEP('HR.GET_TOTAL_SALARY','P');-- 锁定过程DBMS_SHARED_POOL.KEEP('HR.EMP_TOOLS','Q');-- 锁定包END;/-- 查看已被KEEP的对象SELECTowner,name,type,keptFROMv$db_object_cacheWHEREkept='YES';

四、重做日志缓冲区（Redo Log Buffer）

-- 重做日志缓冲区是循环结构-- LGWR在以下情况写入在线日志文件：-- 1. 事务提交（COMMIT）-- 2. 缓冲区1/3满时-- 3. 每隔3秒（定时）-- 4. DBWR需要写脏缓冲区之前-- 查看重做日志缓冲区配置SHOWPARAMETER log_buffer-- 监控重做日志缓冲区空间等待SELECTname,valueFROMv$sysstatWHEREname='redo log space requests';-- 如果redo log space requests值持续增长，考虑增大log_buffer-- log_buffer推荐大小：8MB到64MB（对于高并发OLTP系统）ALTERSYSTEMSETlog_buffer=16777216;-- 16MB（需要重启）-- 查看重做日志文件组SELECTgroup#, members, bytes/1024/1024 AS mb, status, archivedFROMv$log;

五、自动内存管理（AMM）

Oracle 11g引入了完全自动内存管理，只需设置MEMORY_TARGET，Oracle自动分配SGA和PGA。

-- ===== 自动内存管理（AMM，Oracle 11g+ 推荐）=====-- 设置MEMORY_TARGET：Oracle自动管理SGA+PGA的总量-- 设置MEMORY_MAX_TARGET：内存总量上限SHOWPARAMETER memory_targetSHOWPARAMETER memory_max_target-- 启用AMM（需要重启）ALTERSYSTEMSETmemory_target=2G SCOPE=SPFILE;ALTERSYSTEMSETmemory_max_target=3G SCOPE=SPFILE;ALTERSYSTEMSETsga_target=0SCOPE=SPFILE;-- 让AMM完全接管ALTERSYSTEMSETpga_aggregate_target=0SCOPE=SPFILE;-- 查看AMM自动分配的结果SELECTcomponent,current_size/1024/1024AScurrent_mb,min_size/1024/1024ASmin_mb,max_size/1024/1024ASmax_mb,user_specified_size/1024/1024ASuser_spec_mbFROMv$memory_dynamic_componentsORDERBYcurrent_sizeDESC;-- ===== 自动共享内存管理（ASMM，Oracle 10g+）=====-- SGA自动管理，PGA手动设置-- SGA_TARGET：SGA总量，Oracle在此范围内自动分配各组件SHOWPARAMETER sga_targetALTERSYSTEMSETsga_target=1G SCOPE=BOTH;-- SGA自动分配ALTERSYSTEMSETpga_aggregate_target=512M SCOPE=BOTH;-- PGA手动指定-- 设置各组件的最小值（AMM/ASMM不会将组件缩小到此值以下）ALTERSYSTEMSETdb_cache_size=256M;-- DB Cache最小256MALTERSYSTEMSETshared_pool_size=128M;-- 共享池最小128M

六、内存参数调优实战

场景一：OLTP系统（高并发事务处理）

-- OLTP特点：大量短事务，需要高并发，命中率要求高-- 推荐配置（假设8GB物理内存）：-- 方案1：使用AMMALTERSYSTEMSETmemory_target=6G SCOPE=SPFILE;-- 总内存6GBALTERSYSTEMSETmemory_max_target=7G SCOPE=SPFILE;-- 设置最小保证值ALTERSYSTEMSETdb_cache_size=2G SCOPE=SPFILE;ALTERSYSTEMSETshared_pool_size=512M SCOPE=SPFILE;-- 方案2：手动精细控制ALTERSYSTEMSETsga_target=4G SCOPE=SPFILE;ALTERSYSTEMSETdb_cache_size=2G SCOPE=SPFILE;-- 大缓冲区，提高命中率ALTERSYSTEMSETshared_pool_size=512M SCOPE=SPFILE;-- 大共享池，缓存更多SQLALTERSYSTEMSETlog_buffer=32M SCOPE=SPFILE;-- 大日志缓冲区ALTERSYSTEMSETpga_aggregate_target=1G SCOPE=SPFILE;-- 中等PGA

场景二：数据仓库/报表系统

-- DSS特点：大量数据扫描，排序，哈希连接，需要大PGA-- 推荐配置（假设16GB物理内存）：ALTERSYSTEMSETsga_target=4G SCOPE=SPFILE;ALTERSYSTEMSETdb_cache_size=1G SCOPE=SPFILE;-- 相对较小的Buffer CacheALTERSYSTEMSETdb_recycle_cache_size=2G SCOPE=SPFILE;-- 大的Recycle Pool防止全扫描污染ALTERSYSTEMSETshared_pool_size=256M SCOPE=SPFILE;-- 较小的共享池（SQL种类少）ALTERSYSTEMSETpga_aggregate_target=8G SCOPE=SPFILE;-- 大PGA支持排序和哈希操作

场景三：混合工作负载

-- 混合场景：既有OLTP又有报表查询-- 使用AMM让Oracle自动根据工作负载调整ALTERSYSTEMSETmemory_target=10G SCOPE=SPFILE;ALTERSYSTEMSETmemory_max_target=12G SCOPE=SPFILE;-- 设置合理的最小保证值，防止某个组件被过度压缩ALTERSYSTEMSETdb_cache_size=1G SCOPE=SPFILE;ALTERSYSTEMSETshared_pool_size=256M SCOPE=SPFILE;

七、内存使用诊断视图

-- 实时内存监控仪表盘SELECT'SGA Total'AScomponent,ROUND(value/1024/1024,2)AS"当前(MB)",NULLAS"目标(MB)"FROMv$sgaWHEREname='Variable Size'UNIONALLSELECTcomponent,ROUND(current_size/1024/1024,2),ROUND(user_specified_size/1024/1024,2)FROMv$memory_dynamic_componentsWHEREcurrent_size>0ORDERBY"当前(MB)"DESC;-- PGA使用TOP 10会话SELECTs.sid,s.serial#, s.username, s.program,ROUND(p.pga_alloc_mem/1024/1024,2)ASpga_alloc_mb,ROUND(p.pga_used_mem/1024/1024,2)ASpga_used_mb,ROUND(p.pga_max_mem/1024/1024,2)ASpga_max_mbFROMv$sessionsJOINv$process pONs.paddr=p.addrWHEREs.usernameISNOTNULLORDERBYp.pga_max_memDESCFETCHFIRST10ROWSONLY;

八、最佳实践

首选AMM：Oracle 11g及以上使用MEMORY_TARGET，让Oracle自动优化内存分配
合理设置MEMORY_MAX_TARGET：比MEMORY_TARGET大20-30%，留有弹性空间
使用建议器辅助决策：vd b c a c h e a d v i c e 和 v db_cache_advice和vdbcacheadvice和vshared_pool_advice提供基于历史数据的建议
避免频繁刷新共享池：ALTER SYSTEM FLUSH SHARED_POOL会短暂影响性能
监控内存交换：SGA不应引起操作系统的内存交换（swap），否则性能急剧下降