上一期发布了关于oracle数据库安装后的参数优化文档Oracle数据库安装后参数优化文档，此文档只给出了参考指引，没有给出对应的设置规则，接下来几期文档将详细的写写配置规则相关的给大家参考，这里列举依然是常规设置，实际项目中还是需要根据实际情况灵活调整。

本期先分享内存参数调优相关部分

注意：所有内存参数的修改都应在测试环境充分验证后再应用于生产环境，避免因内存分配不当导致数据库性能问题或实例无法启动。

Oracle内存结构概述

Oracle实例中的内存使用主要分为两部分：

• 系统全局区（SGA）：由所有Oracle进程共享的内存区域
• 程序全局区（PGA）：为每个会话单独分配的内存区域

系统全局区（SGA）参数优化

SGA_TARGET 与 SGA_MAX_SIZE

这两个参数是SGA自动内存管理的核心：

• SGA_TARGET：指定SGA的总内存大小，Oracle自动在此范围内分配各个SGA组件
• SGA_MAX_SIZE：设置SGA的最大大小，SGA_TARGET必须小于或等于此值

设置建议：

• 对于专用数据库服务器，通常配置为物理内存的60%
• OLTP系统：SGA占比可稍高；DSS系统：需为PGA留更多空间

计算规则：

• OLTP系统：总Oracle内存的60-70%
• OLAP系统：总Oracle内存的50-60%
• 混合系统：总Oracle内存的55-65%

计算公式：

SGA_TARGET = 总Oracle内存 × 分配比例

计算示例：

-- OLTP系统，48G总Oracle内存
SGA_TARGET = 48G × 0.65 = 31.2G ≈ 32G

ALTER SYSTEM SET sga_max_size=32G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=32G SCOPE=SPFILE;


配置示例：

-- 设置SGA参数
ALTER SYSTEM SET SGA_TARGET=12G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=12G SCOPE=SPFILE;

– 检查SGA分配情况
SELECT name, bytes/1024/1024 as “Size(M)” FROM v$sgainfo;


Oracle 12c+ In-Memory 参数

从Oracle 12.1.0.2开始，引入了内存列存储（In-Memory Column Store）特性：

INMEMORY_SIZE参数：

• 控制In-Memory Area大小，至少设置为100M
• 该区域静态分配于SGA内，设置后需重启实例

配置示例：

-- 开启In-Memory特性
ALTER SYSTEM SET inmemory_size=10G SCOPE=SPFILE;

– 为表启用In-Memory存储
ALTER TABLE sales INMEMORY;

– 查看In-Memory区域使用情况
SELECT * FROM v$inmemory_area;


最佳实践：

• In-Memory对重复值多的表压缩效果显著（压缩比可达5-12倍）
• 适合分析型查询、全表扫描，但在高离散度数据通过索引查询时，传统行存储可能更优

程序全局区（PGA）参数优化

PGA_AGGREGATE_TARGET

此参数控制所有会话PGA内存的总和，是PGA自动管理的核心。

设置建议：

• OLTP系统：PGA_AGGREGATE_TARGET = (<总物理内存> * 80%) * 20%
• DSS/OLAP系统：PGA_AGGREGATE_TARGET = (<总物理内存> * 80%) * 50%

计算规则：

• OLTP系统：总Oracle内存的20-25%
• OLAP系统：总Oracle内存的30-40%
• 混合系统：总Oracle内存的25-30%

计算公式：

PGA_AGGREGATE_TARGET = 总Oracle内存 × PGA比例

PGA使用评估：

-- 监控PGA使用情况
SELECT * FROM v$pgastat;

– 检查PGA缓存命中率
SELECT name, value
FROM v$pgastat
WHERE name IN (‘cache hit percentage’, ‘over allocation count’);

– 目标：缓存命中率 > 90%，避免过度分配


计算示例：

-- OLTP系统，48G总Oracle内存
PGA_AGGREGATE_TARGET = 48G × 0.22 = 10.56G ≈ 10.5G

ALTER SYSTEM SET pga_aggregate_target=10.5G SCOPE=SPFILE;
ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=SPFILE;


配置示例：

-- 启用PGA自动管理
ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=SPFILE;

– 设置PGA目标大小（假设服务器有16G内存，OLTP系统）
ALTER SYSTEM SET pga_aggregate_target=2457M SCOPE=SPFILE; – (16G*0.8)*0.2 ≈ 2.45G

– 查看PGA使用情况
SELECT * FROM v$pgastat;


内存使用情况查询

以下SQL可全面查看SGA和PGA的使用情况：

SELECT name, total, 
       ROUND(total-free,2) used, 
       ROUND(free,2) free,
       ROUND((total-free)/total*100,2) pctused 
FROM (
  SELECT 'SGA' name,
         (SELECT SUM(value/1024/1024) FROM v$sga) total,
         (SELECT SUM(bytes/1024/1024) FROM v$sgastat WHERE name='free memory') free 
  FROM dual
)
UNION
SELECT name, total,
       ROUND(used,2) used,
       ROUND(total-used,2) free,
       ROUND(used/total*100,2) pctused 
FROM (
  SELECT 'PGA' name,
         (SELECT value/1024/1024 total FROM v$pgastat WHERE name='aggregate PGA target parameter') total,
         (SELECT value/1024/1024 used FROM v$pgastat WHERE name='total PGA allocated') used 
  FROM dual
);

其他关键内存相关参数

DB_CACHE_SIZE

计算规则：

• OLTP系统：SGA的50-60%
• OLAP系统：SGA的60-70%
• 基于数据活跃度调整

计算公式：

DB_CACHE_SIZE = SGA_TARGET × 缓存比例

命中率评估：

-- 检查当前缓存命中率
SELECT (1 - (phy.value / (cur.value + con.value))) * 100 "缓存命中率"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

– 目标：保持命中率 > 90%


计算示例：

-- OLTP系统，32G SGA
DB_CACHE_SIZE = 32G × 0.55 = 17.6G ≈ 18G

ALTER SYSTEM SET db_cache_size=18G SCOPE=SPFILE;


SHARED_POOL_SIZE

计算规则：

• 基础需求：500MB – 1GB
• 每GB SGA增加：50-100MB
• 特殊考虑：大量PL/SQL使用时适当增加

计算公式：

SHARED_POOL_SIZE = 基础值 + (SGA_TARGET in GB × 每GB增量)

共享池命中率检查：

SELECT (1 - (sum(reloads) / sum(pins))) * 100 "共享池命中率"
FROM v$librarycache;

– 目标：保持命中率 > 95%


计算示例：

-- 32G SGA的系统
SHARED_POOL_SIZE = 800MB + (32 × 80MB) = 800MB + 2560MB = 3360MB ≈ 3.3G

ALTER SYSTEM SET shared_pool_size=3.3G SCOPE=SPFILE;


其他池大小计算

-- LARGE_POOL_SIZE：主要用于并行查询和RMAN备份
-- 计算规则：通常为SGA的5-10%
LARGE_POOL_SIZE = 32G × 0.08 = 2.56G ≈ 2.5G

– JAVA_POOL_SIZE：如使用Java存储过程则设置，否则保持默认
JAVA_POOL_SIZE = 256M – 默认值通常足够

– STREAMS_POOL_SIZE：如使用Oracle Streams则设置
STREAMS_POOL_SIZE = 512M


版本差异重要说明

不同Oracle版本中，部分参数的默认值已发生变化：

重要版本变化：

• Oracle 12c：引入In-Memory选件，需要单独设置INMEMORY_SIZE参数
• Oracle 19c：高优先级进程参数(_high_priority_processes)默认包含更多进程
• Oracle 23ai：系统表空间默认使用Bigfile，SecureFiles成为LOB存储标准

内存参数设置步骤

修改步骤示例

以下是修改SGA和PGA的完整步骤：

1. 连接到数据库：

   sqlplus / as sysdba
   

2. 检查当前设置：

   SHOW PARAMETER sga_target
   SHOW PARAMETER pga_aggregate_target
   

3. 修改参数（使用SPFILE）：

   ALTER SYSTEM SET SGA_TARGET=8G SCOPE=SPFILE;
   ALTER SYSTEM SET SGA_MAX_SIZE=8G SCOPE=SPFILE;
   ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2G SCOPE=SPFILE;
   

4. 重启数据库：

   SHUTDOWN IMMEDIATE
   STARTUP
   

参数文件类型说明

• PFILE：静态文本参数文件，修改后需重启生效
• SPFILE：服务器参数文件，可在线修改，动态参数立即生效，静态参数需重启

SCOPE参数说明：

• SCOPE=SPFILE：仅修改服务器参数文件，重启后生效
• SCOPE=MEMORY：仅修改内存中的值，立即生效但不持久
• SCOPE=BOTH：同时修改内存和参数文件

优化总结

Oracle内存优化需要根据具体业务类型和负载特点进行调整：

• OLTP系统：SGA占比可稍高，关注Buffer Cache和Shared Pool
• DSS/OLAP系统：需要更大的PGA支持排序、哈希操作
• 混合系统：采用自动内存管理，定期监控调整

注意：所有内存参数的修改都应在测试环境充分验证后再应用于生产环境，避免因内存分配不当导致数据库性能问题或实例无法启动。

想了解更多干货，可通过下方扫码关注

可扫码添加上智启元官方客服微信👇