Oracle教程（6）SGA\PGA\REDO核心参数调优

一、Oracle核心参数介绍

1、SGA

SGA（System Global Area）是 Oracle 实例启动时分配的共享内存区域，所有连接到该实例的会话都共用这块内存，是整个数据库实例对外提供服务的公共内存池（和 MySQL的 InnoDB Buffer Pool 类似）。SGA主要包含以下部分：

· Shared Pool：用于缓存 SQL 解析结果、执行计划和数据字典信息

· Buffer Cache：用于缓存数据块，减少物理读

· Redo Log Buffer：用于临时存放尚未写入磁盘的重做日志条目。

Oracle 的 SGA 是集合体，而 MySQL 的内存管理相对分散，Buffer Pool、Sort Buffer、Join Buffer 等是各自独立配置的参数，没有一个统一的实例级共享内存区概念

2、PGA

PGA（Program Global Area）是 Oracle 实例中每个进程或会话所私有的内存区域，每个区域不与其他会话共享。主要用于排序操作、哈希连接等需要临时空间的操作。当一个会话 ORDER BY、GROUP BY 或者大表之间的 HASH JOIN 时，如果数据量超过 PGA 能提供的内存上限，就会发生排序溢出到磁盘，导致性能下降。在 MySQL 中与之对应的是每个连接独立分配的 sort_buffer_size、join_buffer_size、tmp_table_size 等参数。

3、REDO LOG

在 Oracle 中 REDO LOG 和 MySQL 一样都是通过重做日志记录数据库所有数据变更操作的"重放记录"，这样可以在实例崩溃后，通过 Redo Log 可以把数据恢复到崩溃前的状态，保证事务的持久性。在 MySQL 中 REDO LOG 是循环覆盖写入的，而 Oracle 采用多个日志组循环写入的机制，当一个日志组写满后会触发日志切换（log switch），同时触发增量检查点（checkpoint），把脏数据写回数据文件。如果日志组设置过小，会导致切换过于频繁，对性能造成明显影响。

Oracle 的 REDO LOG 搭配归档日志（归档日志是 Redo Log 切换后被复制保存的历史副本）来实现时间点恢复，也就是说 Oracle 用 REDO LOG 兼顾实例恢复和时间点恢复两个目的，而 MySQL 把这两个职责拆成了 REDO LOG （实例恢复）和 Binlog（时间点恢复、主从复制）两套独立的日志系统。

二、Oracle内存管理方式介绍

Oracle 提供了三种内存管理方式

之所以不推荐通过 AMM 方式来自动进行内存管理，是因为通过 MEMORY_TARGET 自动管理 SGA + PGA听起来方便，但由于 AMM 模式在高并发场景下会频繁的出现内存移动，反而增大了性能开销

1、SGA配置最佳实践

通常建议 SGA 总量不超过服务器物理内存的 60%，剩余部分留给 OS、PGA 以及其他进程

-- 查看当前 SGA 配置
SHOW PARAMETER SGA;

-- 设置 SGA_TARGET，让 Oracle 在这个总量内自动分配各子池
ALTER SYSTEM SET SGA_TARGET=8G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=8G SCOPE=SPFILE;

-- 设置 Shared Pool 子池最小值，防止 AMM/ASMM 压缩得过小出现ORA-04031共享池耗尽的情况
ALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=SPFILE;

子池参考比例（以 16G SGA 为例）：

· Buffer Cache：10～12G ，SGA 里占比最大，越大越好。命中率低于 95% 就需要考虑加大 Buffer Cache，正常生产环境应该维持在 98% 以上

-- 查询 Buffer Cache 命中率
SELECT ROUND((1 - (phys.value / (cur.value + con.value))) * 100, 2) AS "Buffer命中率%" FROM v$sysstat phys, v$sysstat cur, v$sysstat con
WHERE phys.name = 'physical reads' AND cur.name  = 'db block gets' AND con.name  = 'consistent gets';

· Shared Pool：2～4G，OLTP 系统多，SQL 复杂度高则需要更大。硬解析率超过 30% 就需要排查是否 Shared Pool 太小，或应用层没有使用绑定变量

-- 查询 Shared Pool 的硬解析率
SELECT ROUND(hard.value / parse.value * 100, 2) AS "硬解析率%" FROM v$sysstat hard, v$sysstat parse
WHERE hard.name = 'parse count (hard)' AND parse.name = 'parse count (total)';

· Large Pool：512M～1G，RMAN、并行查询、Shared Server 使用

· Redo Log Buffer：128M～256M，一般不需要特别大，写满即刷盘

2、PGA配置最佳实践

高并发场景下 PGA 的总消耗是所有活跃会话 PGA 之和，所以在估算服务器内存分配时，不能忽略这块消耗。通用经验是PGA_AGGREGATE_TARGET = 物理内存 × 20%，如果是以批量处理、数据仓库为主的系统可以提高到 30%，OLTP 系统则适当降低。

-- 查看当前 PGA 配置
SHOW PARAMETER PGA;

-- 设置 PGA 总目标值（Oracle 会尽可能在此范围内为各会话分配内存）
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=SPFILE;

-- 设置 PGA 硬上限，防止撑爆内存，超过后 Oracle 会强制终止消耗最多 PGA 的会话，生产环境建议设置为 PGA_AGGREGATE_TARGET 的2倍
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=8G SCOPE=SPFILE;

排查 PGA 是否成为瓶颈，如果sorts (disk) 磁盘排序次数过高，说明 PGA 不够用

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

3、REDO LOG配置最佳实践

Redo Log 最常见的问题就是日志组太小导致切换过于频繁。Oracle 官方建议最少保留 3 组日志组（高并发写入场景 4～6 组），日志切换频率控制在每 15～30 分钟一次，如果每小时切换次数超过 4 次就需要考虑扩大日志组。如果发现某些高峰时段每小时切换次数很高（如 50MB 大小的日志组每小时上百次切换），就需要立即扩大日志组大小

-- 查看当前切换频率
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,COUNT(*) AS switches FROM v$log_history WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24') ORDER BY 1;

日志组大小参考，注意这里是单个日志组大小，不是所有组加起来的总量。

新增日志组

日志组大小不允许直接修改，只能新增更大的日志组，再删掉原来小的，操作顺序如下

-- 第一步：新增日志组，指定序号和日志路径、日志大小
ALTER DATABASE ADD LOGFILE GROUP 4 '/u01/oradata/orcl/redo04.log' SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/oradata/orcl/redo05.log' SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 6 '/u01/oradata/orcl/redo06.log' SIZE 1G;

-- 第二步：手动触发切换，让旧日志组变为 INACTIVE 状态
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- 第三步：确认旧日志组状态为 INACTIVE
SELECT group#, status, members, bytes/1024/1024 AS mb FROM v$log;

-- 第四步：删除状态为INACTIVE的旧日志组
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

-- 第五步：删除操作系统上的旧日志文件（Oracle 不会自动删除物理文件）
rm /u01/oradata/orcl/redo01.log

数据库oracle