news 2026/5/15 16:06:43

【Oracle数据库指南】第46篇：Oracle内存与参数调优

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
【Oracle数据库指南】第46篇：Oracle内存与参数调优

上一篇【第45篇】Oracle SQL优化基础
下一篇【第47篇】Oracle 11g在Linux下的安装详解

摘要

内存是Oracle数据库性能的核心资源，合理配置SGA和PGA直接决定数据库的吞吐能力和响应速度。本文深入讲解Oracle 11g的内存管理体系：从自动内存管理（AMM）到手动精细调优，结合内存建议器工具的使用、关键初始化参数的含义与调整策略，以及一套完整的内存调优实战方法论，帮助DBA在有限硬件资源下最大化数据库性能。

一、Oracle内存架构回顾

1.1 内存组件层次

┌─────────────────────────────────────────────────────────┐ │ Oracle实例内存 │ │ ┌─────────────────────────────────┐ ┌──────────────┐ │ │ │ SGA（共享全局区） │ │ PGA │ │ │ │ ┌──────────┐ ┌──────────────┐ │ │（进程全局区） │ │ │ │ │ Buffer │ │ Shared Pool │ │ │ 每个进程 │ │ │ │ │ Cache │ │ （共享池） │ │ │ 独立分配 │ │ │ │ └──────────┘ └──────────────┘ │ └──────────────┘ │ │ │ ┌──────────┐ ┌──────────────┐ │ │ │ │ │ Redo Log │ │ Large Pool │ │ │ │ │ │ Buffer │ │ Java Pool │ │ │ │ │ └──────────┘ └──────────────┘ │ │ │ └─────────────────────────────────┘ │ └─────────────────────────────────────────────────────────┘

1.2 三种内存管理模式

管理模式控制参数说明适用场景
AMM（自动内存管理）MEMORY_TARGETOracle自动管理SGA+PGA总量开发/测试环境
ASMM（自动共享内存管理）SGA_TARGETOracle自动管理SGA内部分配，PGA手动控制生产环境（推荐）
手动内存管理各组件分别设置完全手动控制每个SGA组件精细调优场景

二、自动内存管理（AMM）

2.1 AMM配置

AMM通过一个参数MEMORY_TARGET控制SGA和PGA的总量，Oracle自动在两者之间动态调整。

-- 查看当前内存配置SHOWPARAMETER memory_target;SHOWPARAMETER memory_max_target;SHOWPARAMETER sga_target;SHOWPARAMETER pga_aggregate_target;-- 启用AMMALTERSYSTEMSETmemory_max_target=4G SCOPE=SPFILE;-- 需重启ALTERSYSTEMSETmemory_target=3G SCOPE=SPFILE;-- 实际使用量-- 确认启用：sga_target和pga_aggregate_target均应为0ALTERSYSTEMSETsga_target=0SCOPE=SPFILE;ALTERSYSTEMSETpga_aggregate_target=0SCOPE=SPFILE;

⚠️AMM注意事项：

  • Linux上使用AMM需要/dev/shm挂载点，大小需>=MEMORY_MAX_TARGET
  • AMM不能与大页（HugePages）同时使用——生产环境若使用大页，必须用ASMM或手动管理
  • AMM适合测试环境，生产环境更推荐ASMM以获得更可预测的性能

2.2 监控AMM内存分配

-- 查看当前SGA动态组件分配SELECTcomponent,current_size/1024/1024||'M'current_mb,min_size/1024/1024||'M'min_mb,max_size/1024/1024||'M'max_mb,user_specified_size/1024/1024||'M'user_spec_mbFROMv$memory_dynamic_componentsORDERBYcurrent_sizeDESC;-- 查看内存调整历史SELECTcomponent,oper_type,oper_mode,initial_size/1024/1024||'M'from_mb,target_size/1024/1024||'M'to_mb,start_time,end_timeFROMv$memory_resize_opsORDERBYstart_timeDESCFETCHFIRST20ROWSONLY;

三、SGA调优

3.1 Buffer Cache调优

Buffer Cache（数据库高速缓存）缓存数据块，是SGA中对性能影响最大的组件。

核心指标：Buffer Cache命中率

-- 计算Buffer Cache命中率（目标>95%）SELECTROUND((1-phys.value/(db.value+con.value))*100,2)||'%'hit_ratioFROMv$sysstat phys,v$sysstat db,v$sysstat conWHEREphys.name='physical reads'ANDdb.name='db block gets'ANDcon.name='consistent gets';

使用Buffer Cache建议器：

-- 查看Buffer Cache大小对命中率的影响SELECTsize_for_estimate/1024/1024||'M'cache_size_mb,buffers_for_estimate,estd_physical_read_factor,estd_physical_readsFROMv$db_cache_adviceORDERBYsize_for_estimate;

典型输出分析：

CACHE_SIZE_MB BUFFERS ESTD_READ_FACTOR ESTD_PHYS_READS -------------- -------- ----------------- --------------- 512M 65536 2.15 2,150,000 1024M 131072 1.32 1,320,000 ← 当前大小 2048M 262144 1.00 1,000,000 ← 如此大小，读减少25% 4096M 524288 0.95 950,000 ← 收益递减

调整Buffer Cache大小：

-- ASMM模式下调整（动态生效）ALTERSYSTEMSETdb_cache_size=2G;-- 手动管理模式ALTERSYSTEMSETdb_cache_size=2G SCOPE=BOTH;

3.2 Shared Pool调优

Shared Pool缓存SQL游标、PL/SQL代码、数据字典信息。

核心指标：库缓存命中率

-- 库缓存命中率（Library Cache Hit Ratio，目标>99%）SELECTSUM(pins)total_executions,SUM(reloads)reloads,ROUND((1-SUM(reloads)/SUM(pins))*100,4)hit_ratioFROMv$librarycacheWHEREnamespaceIN('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');-- 数据字典命中率（目标>95%）SELECTROUND(SUM(gets-getmisses)/SUM(gets)*100,2)dd_hit_ratioFROMv$rowcacheWHEREgets>0;

使用Shared Pool建议器：

-- 查看Shared Pool大小建议SELECTshared_pool_size_for_estimate/1024/1024||'M'sp_size,estd_lc_time_saved_factor,estd_lc_memory_object_hitsFROMv$shared_pool_adviceORDERBYshared_pool_size_for_estimate;

Shared Pool常见问题：ORA-04031（内存碎片）

-- 当发生碎片时，清空Shared Pool（慎用！会清除所有缓存）ALTERSYSTEM FLUSH SHARED_POOL;-- 固定常用的大型PL/SQL对象（防止被换出）EXECUTEdbms_shared_pool.keep('SCOTT.MY_PACKAGE','P');

3.3 Large Pool与Java Pool

Large Pool调整：

-- Large Pool主要用于：并行查询、RMAN备份、共享服务器模式-- 若使用这些功能且遇到ORA-04031，需增大Large PoolALTERSYSTEMSETlarge_pool_size=256M;-- 查看Large Pool使用情况SELECTname,bytes/1024/1024||'M'size_mbFROMv$sgastatWHEREpool='large pool'ORDERBYbytesDESC;

3.4 重做日志缓冲区调优

-- 查看重做日志缓冲区等待（目标：等待比率<1%）SELECTNAME,VALUEFROMv$sysstatWHEREnameIN('redo buffer allocation retries','redo entries');-- 计算等待比率SELECTROUND(retries/entries*100,4)||'%'retry_ratioFROM(SELECTSUM(DECODE(name,'redo buffer allocation retries',value,0))retries,SUM(DECODE(name,'redo entries',value,0))entriesFROMv$sysstatWHEREnameIN('redo buffer allocation retries','redo entries'));-- 若等待比率>1%，增大LOG_BUFFERALTERSYSTEMSETlog_buffer=32M SCOPE=SPFILE;-- 需重启

四、PGA调优

4.1 PGA概述

PGA（Program Global Area）是每个Oracle进程的私有内存区域，主要用于：

  • 排序操作（Sort Area）：ORDER BY、GROUP BY的内存排序
  • 哈希连接（Hash Area）：Hash Join的哈希表
  • 位图合并（Bitmap Merge Area）：位图索引操作

4.2 自动PGA管理

-- 查看PGA配置SHOWPARAMETER pga_aggregate_target;SHOWPARAMETER workarea_size_policy;-- 启用自动PGA管理（默认已启用）ALTERSYSTEMSETworkarea_size_policy=AUTO;ALTERSYSTEMSETpga_aggregate_target=1G;-- 所有进程PGA总量上限

4.3 使用PGA建议器

-- PGA大小建议SELECTpga_target_for_estimate/1024/1024||'M'pga_size,estd_pga_cache_hit_percentage hit_pct,estd_overalloc_count over_allocFROMv$pga_target_adviceORDERBYpga_target_for_estimate;

典型输出解读：

PGA_SIZE HIT_PCT OVER_ALLOC -------- -------- ---------- 512M 62% 234 ← 过小，大量磁盘排序 1024M 85% 0 ← 当前配置 2048M 95% 0 ← 命中率提升明显，值得增加 4096M 98% 0 ← 边际收益已很小

关键指标：

  • hit_pct > 90%：磁盘排序少，PGA大小合适
  • over_alloc_count > 0：PGA不足，存在内存过度分配

4.4 监控PGA使用情况

-- 当前PGA使用情况总览SELECTROUND(value/1024/1024,2)mbFROMv$pgastatWHEREnameIN('total PGA inuse','total PGA allocated','maximum PGA allocated','cache hit percentage');-- 各会话PGA使用情况SELECTs.sid,s.username,s.status,p.pga_used_mem/1024/1024||'M'pga_used,p.pga_alloc_mem/1024/1024||'M'pga_alloc,p.pga_max_mem/1024/1024||'M'pga_maxFROMv$sessionsJOINv$process pONs.paddr=p.addrWHEREs.type='USER'ORDERBYp.pga_alloc_memDESCFETCHFIRST10ROWSONLY;

五、关键初始化参数调优

5.1 内存相关参数汇总

参数说明推荐设置
MEMORY_TARGETAMM总内存目标系统内存的60-70%（测试环境）
SGA_TARGETASMM下SGA总量系统内存的40-60%（生产环境）
SGA_MAX_SIZESGA最大值>= SGA_TARGET，需重启
PGA_AGGREGATE_TARGETPGA总量目标系统内存的20-30%
DB_CACHE_SIZEBuffer Cache固定大小手动管理时设置
SHARED_POOL_SIZEShared Pool固定大小手动管理时设置，建议>=256M
LARGE_POOL_SIZELarge Pool大小使用并行/RMAN时设置，建议64-256M
LOG_BUFFER重做日志缓冲区建议16M-64M，很少需要超过128M

5.2 重要非内存参数

-- 查看和修改关键参数-- DB_FILE_MULTIBLOCK_READ_COUNT：全表扫描时的多块读大小SHOWPARAMETER db_file_multiblock_read_count;-- 计算：最优值 = db_block_size_bytes * db_file_multiblock_read_count_bytes / os_io_size-- 通常设为128，让Oracle根据I/O系统自动优化-- OPEN_CURSORS：每个会话最大打开游标数SHOWPARAMETER open_cursors;-- 监控：若应用报ORA-01000，需增大此值SELECTMAX(count_open_cur)FROMv$sesstat s,v$statname nWHEREs.statistic# = n.statistic# AND n.name = 'opened cursors current';-- PROCESSES/SESSIONS：最大进程/会话数SHOWPARAMETER processes;SHOWPARAMETER sessions;-- sessions ≈ processes * 1.1 + 5（Oracle自动计算）-- UNDO_RETENTION：UNDO数据保留时间（秒）SHOWPARAMETER undo_retention;-- 建议设置为长时间报表查询的执行时间+缓冲（如3600秒）

5.3 参数修改的作用域

-- SCOPE说明：-- MEMORY：仅在当前实例生效，重启后失效-- SPFILE：写入SPFILE，重启后生效-- BOTH：同时写入内存和SPFILE（最常用）-- 动态参数（可立即生效）示例ALTERSYSTEMSETdb_cache_size=2G SCOPE=BOTH;ALTERSYSTEMSETpga_aggregate_target=1G SCOPE=BOTH;-- 静态参数（需重启）示例ALTERSYSTEMSETsga_max_size=8G SCOPE=SPFILE;ALTERSYSTEMSETprocesses=300SCOPE=SPFILE;-- 查看参数是否为静态（STATIC）或动态SELECTname,issys_modifiable,isses_modifiableFROMv$parameterWHEREnameIN('db_cache_size','pga_aggregate_target','processes');

六、内存调优实战案例

案例一：Buffer Cache命中率低（频繁物理读）

症状：AWR报告显示db file sequential read等待事件居首，Buffer Cache命中率82%。

诊断：

-- 1. 确认命中率SELECTROUND((1-p/(c+d))*100,2)hit_ratioFROM(SELECTSUM(DECODE(name,'physical reads',value))p,SUM(DECODE(name,'consistent gets',value))c,SUM(DECODE(name,'db block gets',value))dFROMv$sysstatWHEREnameIN('physical reads','consistent gets','db block gets'));-- 结果：82%（低于90%阈值）-- 2. 查看Buffer Cache建议器SELECTsize_for_estimate/1024/1024mb,estd_physical_read_factorFROMv$db_cache_adviceORDERBYsize_for_estimate;-- 建议器显示：增加到4G可将物理读减少40%

解决：

-- 调整Buffer Cache（ASMM模式）ALTERSYSTEMSETdb_cache_size=4G SCOPE=BOTH;-- 2小时后复查，命中率升至96%

案例二：磁盘排序过多（PGA不足）

症状：AWR中sorts (disk)每秒超过50次，应用排序类查询性能差。

-- 诊断：磁盘排序比例SELECTROUND(disk/(disk+mem)*100,2)disk_sort_pctFROM(SELECTSUM(DECODE(name,'sorts (disk)',value))disk,SUM(DECODE(name,'sorts (memory)',value))memFROMv$sysstatWHEREnameIN('sorts (disk)','sorts (memory)'));-- 结果：8%（超过5%警戒线）-- 查看PGA建议器SELECTpga_target_for_estimate/1024/1024||'M'pga,estd_pga_cache_hit_percentage hit_pctFROMv$pga_target_adviceORDERBYpga_target_for_estimate;

解决：

ALTERSYSTEMSETpga_aggregate_target=2G SCOPE=BOTH;-- 复查：磁盘排序比例降至1%以下

七、内存调优参考基准

OLTP系统内存分配参考：

系统内存SGA_TARGETPGA_AGGREGATE_TARGET说明
8 GB4 GB1.5 GB小型OLTP
16 GB8 GB3 GB中型OLTP
32 GB18 GB6 GB大型OLTP
64 GB38 GB12 GB企业级OLTP

DSS/数仓系统内存分配参考（排序、哈希连接需求大）：

系统内存SGA_TARGETPGA_AGGREGATE_TARGET
32 GB10 GB15 GB
64 GB20 GB30 GB
128 GB40 GB60 GB

八、总结

Oracle内存调优遵循"先测量，后调整，再验证"的原则：

  1. 建立基线：记录系统在正常负载下的内存使用情况
  2. 发现问题：通过Buffer Cache命中率、磁盘排序率、PGA命中率等指标定位瓶颈
  3. 利用建议器：Oracle的内存建议器提供了科学的调整依据，优先参考
  4. 小步调整：每次调整幅度不要太大（建议10-25%），观察效果再继续
  5. 关注全局：内存调优不是独立的，需与SQL优化、I/O优化协同进行

最常见的内存优化路径：

  • 命中率低 → 增大Buffer Cache
  • 磁盘排序多 → 增大PGA
  • 硬解析多 → 检查绑定变量使用（不是增大Shared Pool能解决的）
  • ORA-04031 → 增大Shared Pool + 检查碎片

上一篇【第45篇】Oracle SQL优化基础
下一篇【第47篇】Oracle 11g在Linux下的安装详解

参考资料

  • Oracle Database Performance Tuning Guide 11g Release 2
  • Oracle Database Administrator’s Guide 11g Release 2
  • Oracle Database Reference 11g（v$memory_dynamic_components等）
  • Oracle MOS Note: Memory Management Best Practices
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/5/15 16:06:10

构建AI Agent工作流时集成Taotoken作为统一模型层

&#x1f680; 告别海外账号与网络限制&#xff01;稳定直连全球优质大模型&#xff0c;限时半价接入中。 &#x1f449; 点击领取海量免费额度 构建AI Agent工作流时集成Taotoken作为统一模型层 在开发基于OpenClaw或Hermes Agent等框架的AI应用时&#xff0c;一个常见的需求…

作者头像 李华
网站建设 2026/5/15 16:04:17

WebToEpub：3步将网页小说转为EPUB电子书的终极解决方案

WebToEpub&#xff1a;3步将网页小说转为EPUB电子书的终极解决方案 【免费下载链接】WebToEpub A simple Chrome (and Firefox) Extension that converts Web Novels (and other web pages) into an EPUB. 项目地址: https://gitcode.com/gh_mirrors/we/WebToEpub 还在为…

作者头像 李华
网站建设 2026/5/15 16:04:16

FlicFlac音频转换工具：终极轻量级Windows音频格式转换解决方案

FlicFlac音频转换工具&#xff1a;终极轻量级Windows音频格式转换解决方案 【免费下载链接】FlicFlac Tiny portable audio converter for Windows (WAV FLAC MP3 OGG APE M4A AAC) 项目地址: https://gitcode.com/gh_mirrors/fl/FlicFlac FlicFlac是一款专为Windows平…

作者头像 李华
网站建设 2026/5/15 16:04:16

Chrome for Testing：构建企业级Web自动化测试基础设施的终极指南

Chrome for Testing&#xff1a;构建企业级Web自动化测试基础设施的终极指南 【免费下载链接】chrome-for-testing 项目地址: https://gitcode.com/gh_mirrors/ch/chrome-for-testing Chrome for Testing项目是Google Chrome团队为Web自动化测试场景专门设计的标准化浏…

作者头像 李华
网站建设 2026/5/15 16:03:44

Win10下CUDA 11.7和PyTorch保姆级安装避坑指南（含Anaconda换源与驱动检查）

Win10深度学习环境配置全攻略&#xff1a;从CUDA到PyTorch的零失败实践 刚接触深度学习的新手往往在第一步——环境配置上就遭遇重重阻碍。驱动版本混乱、下载速度缓慢、环境变量缺失、版本兼容性问题……这些看似简单的步骤背后隐藏着无数可能让初学者崩溃的"坑"。本…

作者头像 李华
网站建设 2026/5/15 16:03:12

Talon语音眼控系统：开源人机交互新范式部署与脚本实战

1. 项目概述与核心价值最近在折腾个人生产力工具链&#xff0c;特别是语音交互这块&#xff0c;发现了一个叫 Talon 的开源项目&#xff0c;准确说是 Gojer16 维护的一个 Talon 分支。如果你和我一样&#xff0c;对传统键盘鼠标操作效率感到瓶颈&#xff0c;或者因为某些原因&a…

作者头像 李华