Oracle 19c入门学习教程，从入门到精通，Oracle系统调优 —— 内存结构与参数优化详解(15）

Oracle系统调优 —— 内存结构与参数优化详解

一、环境准备：Oracle 安装与调优前提

注意：系统调优需具备 DBA 权限，建议在测试环境（如 Oracle 21c XE）中操作。

1. 安装 Oracle Database 21c XE（简要回顾）

Windows / Linux 安装后验证：

sqlplus sys/your_password@localhost:1521/XE as sysdba

2. 调优前必备权限

确保当前用户具有以下权限（通常为SYS或SYSTEM）：

GRANTALTERSYSTEMTOyour_dba_user;-- 查看动态性能视图GRANTSELECTONv_$sgaTOyour_dba_user;GRANTSELECTONv_$parameterTOyour_dba_user;-- 其他常用视图：v$sgastat, v$pgastat, v$sysstat 等

💡 Oracle 21c 默认使用自动内存管理（AMM）或自动共享内存管理（ASMM），但本章将深入手动调优细节。

二、核心语法知识点详解与案例

1. Oracle 初始化参数分类

Oracle 参数分为三类：

✅ 推荐使用spfile（二进制参数文件），而非pfile（文本文件）。

查看参数当前值：

-- 查看所有参数SHOWPARAMETER;-- 查看特定参数（如 sga_target）SHOWPARAMETER sga_target;-- 查询 v$parameter 视图SELECTname,value,isdefault,issys_modifiableFROMv$parameterWHEREnameLIKE'%sga%';

2. 主要系统调优参数介绍

⚠️ 若设置MEMORY_TARGET > 0，则SGA_TARGET和PGA_AGGREGATE_TARGET由 Oracle 自动分配。

3. 系统全局区（SGA）优化

3.1 理解 SGA 内存结构

SGA 由以下主要组件构成：

• Database Buffer Cache：缓存数据块
• Shared Pool：缓存 SQL、执行计划、数据字典
• Redo Log Buffer：缓存重做日志
• Large Pool（可选）：用于 RMAN、并行查询
• Java Pool（可选）：Java 存储
• Streams Pool（可选）：流复制

查看 SGA 分配：

-- 查看 SGA 总体SHOWSGA;-- 详细各组件使用情况SELECT*FROMv$sgastatORDERBYpool,name;

4. 调整日志缓冲区（Redo Log Buffer）

作用：

• 缓存事务产生的重做记录，减少磁盘 I/O。
• 大小由LOG_BUFFER控制（静态参数，需重启）。

查看当前大小：

SHOWPARAMETER log_buffer;-- 输出示例：log_buffer = 67108864 (约 64MB)

调整方法（需重启）：

-- 创建 pfile 临时修改（不推荐生产直接改 spfile）CREATEPFILE='/tmp/initXE.ora'FROMSPFILE;-- 编辑 initXE.ora，添加：-- *.log_buffer=134217728 -- 128MB-- 重启数据库SHUTDOWNIMMEDIATE;STARTUP PFILE='/tmp/initXE.ora';-- 重建 spfile（可选）CREATESPFILEFROMPFILE='/tmp/initXE.ora';

✅ 一般建议：OLTP 系统可设为 100–500 MB；若频繁出现"log buffer space"等待事件，则需增大。

5. 调整共享池（Shared Pool）

作用：

• 存储SQL 语句、执行计划、PL/SQL 代码、数据字典缓存。
• 过小 → 频繁硬解析（Hard Parse）→ CPU 飙升。
• 过大 → 内存浪费。

关键参数：

-- 手动指定大小（若未启用 SGA_TARGET）ALTERSYSTEMSETshared_pool_size=512M SCOPE=BOTH;

监控共享池效率：

-- 计算软解析率（越高越好，>95% 为佳）SELECT1-(SUM(reloads)/SUM(pins))AS"Soft Parse Ratio"FROMv$librarycache;-- pins: 总访问次数，reloads: 重新加载次数（硬解析）-- 查看共享池使用情况SELECTpool,name,bytes/1024/1024ASmbFROMv$sgastatWHEREpool='shared pool'ANDnameIN('free memory','sql area','dictionary cache');

✅ 建议：启用CURSOR_SHARING = FORCE（谨慎）或使用绑定变量减少硬解析。

6. 调整数据库缓冲区（DB Cache）

作用：

• 缓存从磁盘读取的数据块，减少物理 I/O。
• 由DB_CACHE_SIZE控制（默认块大小缓存）。

调整语法：

-- 设置默认缓冲区为 1GBALTERSYSTEMSETdb_cache_size=1G SCOPE=BOTH;

监控缓存命中率：

-- 计算逻辑读 vs 物理读SELECT1-(phy.value/(cur.value+con.value))AS"Buffer Cache Hit Ratio"FROMv$sysstat cur,v$sysstat con,v$sysstat phyWHEREcur.name='db block gets'ANDcon.name='consistent gets'ANDphy.name='physical reads';

✅ 健康值：OLTP > 90%，DSS（数据仓库）可低至 70%。

7. SGA 调优建议

启用自动 SGA 管理（推荐）：

-- 关闭手动参数ALTERSYSTEM RESET shared_pool_size SCOPE=SPFILE SID='*';ALTERSYSTEM RESET db_cache_size SCOPE=SPFILE SID='*';-- 设置 SGA_TARGETALTERSYSTEMSETsga_target=2G SCOPE=SPFILE;-- 重启生效SHUTDOWNIMMEDIATE;STARTUP;

🔁 Oracle 会自动在 Shared Pool、DB Cache、Large Pool 之间动态分配内存。

8. 排序区优化（PGA 相关）

8.1 排序区与其他内存区的关系

• 专用服务器模式（Dedicated Server）：排序内存从PGA分配。
• 共享服务器模式（Shared Server）：排序内存从UGA（位于 SGA Large Pool）分配。

✅ Oracle 9i+ 使用PGA_AGGREGATE_TARGET统一管理 PGA，不再使用SORT_AREA_SIZE（已废弃）。

查看 PGA 使用：

SELECT*FROMv$pgastat;-- 关注：aggregate PGA target parameter, total PGA allocated

调整 PGA：

-- 设置 PGA 总目标为 1GBALTERSYSTEMSETpga_aggregate_target=1G SCOPE=BOTH;

监控排序活动：

-- 查看排序相关统计SELECTname,valueFROMv$sysstatWHEREnameLIKE'%sort%';-- 关键指标：-- sorts (memory) → 内存排序（快）-- sorts (disk) → 磁盘排序（慢，应尽量避免）

✅ 健康比例：sorts (disk) / sorts (memory) < 5%

9. 专用模式 vs 共享模式下的排序区调整

判断当前连接模式：

-- 查看是否使用共享服务器SHOWPARAMETER shared_servers;-- 若 shared_servers > 0，则部分会话使用共享模式

调整 Large Pool（共享模式）：

ALTERSYSTEMSETlarge_pool_size=256M SCOPE=BOTH;

💡 共享模式下，若LARGE_POOL_SIZE不足，UGA 会从 Shared Pool 分配，导致 Shared Pool 碎片。

三、综合性调优案例

场景：OLTP 电商数据库性能下降

症状：

• 用户反馈“下单慢”
• AWR 报告显示：高 CPU、大量硬解析、磁盘排序

调优步骤：

步骤 1：检查内存配置

-- 当前内存设置SHOWPARAMETER memory_target;-- 0（未启用 AMM）SHOWPARAMETER sga_target;-- 0（未启用 ASMM）SHOWPARAMETER pga_aggregate_target;-- 200M（太小！）-- SGA 手动分配SHOWPARAMETER shared_pool_size;-- 128MSHOWPARAMETER db_cache_size;-- 512M

步骤 2：启用自动内存管理（假设服务器有 8GB 内存）

-- 设置总内存为 4GB（留一半给 OS）ALTERSYSTEMSETmemory_target=4G SCOPE=SPFILE;ALTERSYSTEMSETmemory_max_target=4G SCOPE=SPFILE;-- 重启数据库SHUTDOWNIMMEDIATE;STARTUP;

步骤 3：验证自动分配效果

-- 查看实际分配SELECTcomponent,current_size/1024/1024ASmbFROMv$memory_dynamic_components;-- 输出示例：-- SHARED POOL 1024 MB-- DEFAULT BUFFER CACHE 2048 MB-- PGA TARGET 1024 MB

步骤 4：监控硬解析与排序

-- 软解析率（应 >95%）SELECT1-(SUM(reloads)/SUM(pins))FROMv$librarycache;-- 排序磁盘比（应 <5%）SELECTdisk.valueAS"Disk Sorts",mem.valueAS"Memory Sorts",ROUND(disk.value/NULLIF(mem.value,0)*100,2)AS"Disk Sort %"FROM(SELECTvalueFROMv$sysstatWHEREname='sorts (disk)')disk,(SELECTvalueFROMv$sysstatWHEREname='sorts (memory)')mem;

步骤 5：应用层配合（开发建议）

• 使用绑定变量替代字面量，减少硬解析：

  -- ❌ 差：每次都是新 SQLSELECT*FROMordersWHEREuser_id=1001;SELECT*FROMordersWHEREuser_id=1002;-- ✅ 好：同一 SQL，不同绑定值SELECT*FROMordersWHEREuser_id=:user_id;

四、常用诊断脚本汇总

1. 内存总体视图

-- SGA + PGA 总览SELECT'SGA'ASarea,SUM(value)/1024/1024ASmbFROMv$sgaUNIONALLSELECT'PGA',value/1024/1024FROMv$pgastatWHEREname='total PGA allocated';

2. 缓存命中率

-- Buffer Cache Hit RatioSELECT1-(phy.value/(cur.value+con.value))AShit_ratioFROMv$sysstat cur,v$sysstat con,v$sysstat phyWHEREcur.name='db block gets'ANDcon.name='consistent gets'ANDphy.name='physical reads';

3. 共享池健康度

-- Free memory in Shared Pool（应 >10%）SELECTbytes/1024/1024ASfree_mb,(bytes/(SELECTSUM(bytes)FROMv$sgastatWHEREpool='shared pool'))*100ASpct_freeFROMv$sgastatWHEREpool='shared pool'ANDname='free memory';

五、总结与最佳实践

✅调优不是一次性的：需结合业务增长、数据量变化持续监控与调整。

✅ 本章覆盖 Oracle SGA/PGA 内存结构、关键参数、监控方法及实战调优流程，适用于 DBA 日常性能优化工作。