上一篇【第13篇】Oracle实例架构深度解析——SGA、进程与实例启动全流程下一篇【第15篇】Oracle后台进程详解——DBWR、LGWR、CKPT、SMON、PMON工作机制摘要本文深入讲解Oracle数据库的内存管理机制重点解析SGA的各组成部分——数据库缓冲区缓存、共享池、重做日志缓冲区的工作原理和参数配置介绍Oracle 11g的自动内存管理AMM和自动共享内存管理ASMM并通过实际案例演示内存参数的调优方法。一、Oracle内存架构总览Oracle使用两个主要内存区域Oracle内存 ├── SGASystem Global Area系统全局区—— 所有用户共享 │ ├── Database Buffer Cache数据库缓冲区缓存 │ ├── Shared Pool共享池 │ │ ├── Library Cache库缓存 │ │ └── Data Dictionary Cache数据字典缓存 │ ├── Redo Log Buffer重做日志缓冲区 │ ├── Large Pool大型池可选 │ ├── Java PoolJava池可选 │ └── Streams Pool流池可选 └── PGAProgram Global Area程序全局区—— 每个进程私有 ├── Sort Area排序区 ├── Hash Area哈希区 ├── Bitmap Merge Area位图合并区 └── Session Memory会话信息二、数据库缓冲区缓存Database Buffer Cache2.1 工作机制-- 缓冲区缓存使用LRULeast Recently Used算法管理-- 每个数据块在缓冲区中有三种状态-- - Clean Buffer干净缓冲区内容与磁盘一致-- - Dirty Buffer脏缓冲区已修改等待写入磁盘-- - Free Buffer空闲缓冲区未使用-- 读取一个数据块的过程-- 1. 检查缓冲区缓存Hash Bucket-- 2. 若命中Cache Hit直接从内存读取逻辑读-- 3. 若未命中Cache Miss从磁盘读取放入缓冲区然后读取物理读-- 查看缓冲区缓存的命中率SELECTROUND((1-phy.value/(cur.valuecon.value))*100,2)ASbuffer_hit_pctFROMv$sysstat phy,v$sysstat cur,v$sysstat conWHEREphy.namephysical readsANDcur.namedb block getsANDcon.nameconsistent gets;-- 更详细的统计包含各类型读SELECTBuffer Cache Hit RatioASmetric,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_size512M SCOPEBOTH;-- 多块大小支持当数据库有多种块大小时SHOWPARAMETER db_2k_cache_sizeSHOWPARAMETER db_4k_cache_sizeSHOWPARAMETER db_8k_cache_sizeSHOWPARAMETER db_16k_cache_sizeSHOWPARAMETER db_32k_cache_size-- 为非标准块大小创建独立缓冲区ALTERSYSTEMSETdb_16k_cache_size64M;-- 需要先创建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_adviceWHEREnameDEFAULTANDblock_size(SELECTvalueFROMv$parameterWHEREnamedb_block_size)ORDERBYsize_for_estimate;2.3 Keep和Recycle缓冲池Oracle提供三种类型的缓冲池适用于不同访问特性的数据-- DEFAULT Pool默认池标准LRU管理-- KEEP Pool保留池保持热数据块防止被换出-- RECYCLE Pool回收池大扫描数据立即回收防止污染默认池-- 配置各缓冲池大小ALTERSYSTEMSETdb_keep_cache_size64M;-- 用于频繁访问的小表ALTERSYSTEMSETdb_recycle_cache_size128M;-- 用于全表扫描的大表-- 将表分配到特定缓冲池ALTERTABLEemployees STORAGE(BUFFER_POOL KEEP);-- 频繁查询的小表ALTERTABLEorder_history STORAGE(BUFFER_POOL RECYCLE);-- 偶尔全表扫描的大表-- 查看各缓冲池的使用情况SELECTid,name,block_size,ROUND(physical_reads/DECODE(db_block_getsconsistent_gets,0,1,db_block_getsconsistent_gets)*100,2)ASmiss_pctFROMv$buffer_pool_statistics;三、共享池Shared Pool3.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 AREASQL语句-- TABLE/PROCEDUREPL/SQL对象-- BODY包体-- 查看共享SQL高执行次数的SQLSELECTROUND(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$sqlareaWHEREexecutions100ORDERBYelapsed_timeDESC;-- 查看未使用绑定变量的SQL性能风险SELECTCOUNT(*)ASnon_bind_sql_countFROMv$sqlareaWHEREexecutions1ANDcommand_type3-- SELECTANDelapsed_time100000;-- 执行时间超过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_size256M SCOPEBOTH;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_cacheWHEREkeptYES;四、重做日志缓冲区Redo Log Buffer-- 重做日志缓冲区是循环结构-- LGWR在以下情况写入在线日志文件-- 1. 事务提交COMMIT-- 2. 缓冲区1/3满时-- 3. 每隔3秒定时-- 4. DBWR需要写脏缓冲区之前-- 查看重做日志缓冲区配置SHOWPARAMETER log_buffer-- 监控重做日志缓冲区空间等待SELECTname,valueFROMv$sysstatWHEREnameredo log space requests;-- 如果redo log space requests值持续增长考虑增大log_buffer-- log_buffer推荐大小8MB到64MB对于高并发OLTP系统ALTERSYSTEMSETlog_buffer16777216;-- 16MB需要重启-- 查看重做日志文件组SELECTgroup#, members, bytes/1024/1024 AS mb, status, archivedFROMv$log;五、自动内存管理AMMOracle 11g引入了完全自动内存管理只需设置MEMORY_TARGETOracle自动分配SGA和PGA。-- 自动内存管理AMMOracle 11g 推荐-- 设置MEMORY_TARGETOracle自动管理SGAPGA的总量-- 设置MEMORY_MAX_TARGET内存总量上限SHOWPARAMETER memory_targetSHOWPARAMETER memory_max_target-- 启用AMM需要重启ALTERSYSTEMSETmemory_target2G SCOPESPFILE;ALTERSYSTEMSETmemory_max_target3G SCOPESPFILE;ALTERSYSTEMSETsga_target0SCOPESPFILE;-- 让AMM完全接管ALTERSYSTEMSETpga_aggregate_target0SCOPESPFILE;-- 查看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;-- 自动共享内存管理ASMMOracle 10g-- SGA自动管理PGA手动设置-- SGA_TARGETSGA总量Oracle在此范围内自动分配各组件SHOWPARAMETER sga_targetALTERSYSTEMSETsga_target1G SCOPEBOTH;-- SGA自动分配ALTERSYSTEMSETpga_aggregate_target512M SCOPEBOTH;-- PGA手动指定-- 设置各组件的最小值AMM/ASMM不会将组件缩小到此值以下ALTERSYSTEMSETdb_cache_size256M;-- DB Cache最小256MALTERSYSTEMSETshared_pool_size128M;-- 共享池最小128M六、内存参数调优实战场景一OLTP系统高并发事务处理-- OLTP特点大量短事务需要高并发命中率要求高-- 推荐配置假设8GB物理内存-- 方案1使用AMMALTERSYSTEMSETmemory_target6G SCOPESPFILE;-- 总内存6GBALTERSYSTEMSETmemory_max_target7G SCOPESPFILE;-- 设置最小保证值ALTERSYSTEMSETdb_cache_size2G SCOPESPFILE;ALTERSYSTEMSETshared_pool_size512M SCOPESPFILE;-- 方案2手动精细控制ALTERSYSTEMSETsga_target4G SCOPESPFILE;ALTERSYSTEMSETdb_cache_size2G SCOPESPFILE;-- 大缓冲区提高命中率ALTERSYSTEMSETshared_pool_size512M SCOPESPFILE;-- 大共享池缓存更多SQLALTERSYSTEMSETlog_buffer32M SCOPESPFILE;-- 大日志缓冲区ALTERSYSTEMSETpga_aggregate_target1G SCOPESPFILE;-- 中等PGA场景二数据仓库/报表系统-- DSS特点大量数据扫描排序哈希连接需要大PGA-- 推荐配置假设16GB物理内存ALTERSYSTEMSETsga_target4G SCOPESPFILE;ALTERSYSTEMSETdb_cache_size1G SCOPESPFILE;-- 相对较小的Buffer CacheALTERSYSTEMSETdb_recycle_cache_size2G SCOPESPFILE;-- 大的Recycle Pool防止全扫描污染ALTERSYSTEMSETshared_pool_size256M SCOPESPFILE;-- 较小的共享池SQL种类少ALTERSYSTEMSETpga_aggregate_target8G SCOPESPFILE;-- 大PGA支持排序和哈希操作场景三混合工作负载-- 混合场景既有OLTP又有报表查询-- 使用AMM让Oracle自动根据工作负载调整ALTERSYSTEMSETmemory_target10G SCOPESPFILE;ALTERSYSTEMSETmemory_max_target12G SCOPESPFILE;-- 设置合理的最小保证值防止某个组件被过度压缩ALTERSYSTEMSETdb_cache_size1G SCOPESPFILE;ALTERSYSTEMSETshared_pool_size256M SCOPESPFILE;七、内存使用诊断视图-- 实时内存监控仪表盘SELECTSGA TotalAScomponent,ROUND(value/1024/1024,2)AS当前(MB),NULLAS目标(MB)FROMv$sgaWHEREnameVariable SizeUNIONALLSELECTcomponent,ROUND(current_size/1024/1024,2),ROUND(user_specified_size/1024/1024,2)FROMv$memory_dynamic_componentsWHEREcurrent_size0ORDERBY当前(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.paddrp.addrWHEREs.usernameISNOTNULLORDERBYp.pga_max_memDESCFETCHFIRST10ROWSONLY;八、最佳实践首选AMMOracle 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和vdbc​achea​dvice和vshared_pool_advice提供基于历史数据的建议避免频繁刷新共享池ALTER SYSTEM FLUSH SHARED_POOL会短暂影响性能监控内存交换SGA不应引起操作系统的内存交换swap否则性能急剧下降九、总结本文深入讲解了Oracle SGA的内存管理体系Buffer Cache数据块缓存Keep/Recycle/Default三种缓冲池Shared PoolLibrary Cache和Data Dictionary Cache的工作机制Redo Log Buffer重做日志缓冲区的写入时机AMM/ASMMOracle 11g的自动内存管理降低DBA手动调优负担调优实战OLTP、DSS、混合场景的内存配置方案下一篇将详细讲解Oracle的后台进程体系深入理解DBWR、LGWR、CKPT、SMON、PMON等进程的工作机制。上一篇【第13篇】Oracle实例架构深度解析——SGA、进程与实例启动全流程下一篇【第15篇】Oracle后台进程详解——DBWR、LGWR、CKPT、SMON、PMON工作机制参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database Concepts - Memory ArchitectureOracle官方文档Database Administrator’s Guide - Managing Memory