Oracle 内存管理最佳实践

内存管理是Oracle数据库性能优化的核心组成部分，直接影响数据库的响应速度、I/O效率和系统资源利用率。有效的内存管理可以减少磁盘I/O、提高SQL执行效率，并确保数据库在各种负载下稳定运行。

内存管理概述

Oracle内存结构

Oracle数据库的内存结构主要包括两大部分：

SGA（System Global Area）：系统全局区，是数据库实例的共享内存区域，所有服务器进程和后台进程都可以访问
PGA（Program Global Area）：程序全局区，是每个服务器进程或后台进程私有的内存区域

核心目标

优化内存资源的分配和使用
减少磁盘I/O操作
提高SQL执行效率
确保数据库在高负载下的稳定性
便于监控和管理内存资源

SGA管理

SGA的组成

组件	描述	最佳实践
数据库缓冲区高速缓存（DB Cache）	存储从数据文件中读取的数据块，减少磁盘I/O	对于OLTP系统，建议分配SGA的40%-60%；对于数据仓库系统，建议分配SGA的20%-30%
共享池（Shared Pool）	存储共享SQL和PL/SQL代码、数据字典缓存等	建议分配SGA的15%-25%，根据SQL复杂度和并发量调整
重做日志缓冲区（Redo Log Buffer）	存储待写入重做日志文件的重做记录	建议设置为50MB-200MB，通常不需要太大
大型池（Large Pool）	用于共享服务器模式、RMAN备份恢复、并行执行等	根据实际需求调整，建议设置为200MB-1GB
Java池（Java Pool）	用于Java虚拟机（JVM）相关的内存需求	不使用Java时可设置较小，建议设置为100MB-500MB
流池（Streams Pool）	用于Oracle Streams相关的内存需求	不使用Streams时可设置较小，建议设置为100MB-500MB

SGA配置方法

自动内存管理（AMM）

自动内存管理是Oracle 11g及以上版本提供的全自动化内存管理方式，Oracle会根据系统负载自动调整SGA和PGA的大小。

sql

-- 启用自动内存管理
ALTER SYSTEM SET memory_target=6G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target=8G SCOPE=SPFILE;

-- 重启数据库使配置生效
SHUTDOWN IMMEDIATE;
STARTUP;

适用场景：适合大多数数据库环境，特别是对内存管理不太熟悉的DBA 优势：配置简单，无需手动调整SGA和PGA的比例 注意事项：memory_max_target应大于等于memory_target，建议预留20%-30%的内存给操作系统

自动共享内存管理（ASMM）

自动共享内存管理允许DBA手动设置SGA和PGA的总体大小，Oracle会自动调整SGA内部各组件的大小。

sql

-- 启用自动共享内存管理
ALTER SYSTEM SET sga_target=4G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size=6G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=SPFILE;
ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE; -- 禁用AMM

-- 重启数据库使配置生效
SHUTDOWN IMMEDIATE;
STARTUP;

适用场景：适合对内存管理有一定了解的DBA，希望手动控制SGA和PGA的总体比例优势：提供了更精细的控制，同时保持一定的自动化 注意事项：sga_max_size应大于等于sga_target，建议SGA和PGA的比例为70:30或80:20

手动内存管理

手动内存管理需要DBA手动调整SGA内部各组件的大小，适合对内存管理非常熟悉的DBA。

sql

-- 手动配置SGA各组件
ALTER SYSTEM SET sga_max_size=6G SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size=2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size=1G SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size=512M SCOPE=SPFILE;
ALTER SYSTEM SET java_pool_size=256M SCOPE=SPFILE;
ALTER SYSTEM SET streams_pool_size=256M SCOPE=SPFILE;
ALTER SYSTEM SET log_buffer=100M SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=0 SCOPE=SPFILE; -- 禁用ASMM
ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE; -- 禁用AMM

-- 重启数据库使配置生效
SHUTDOWN IMMEDIATE;
STARTUP;

适用场景：适合特殊的数据库环境，需要精确控制每个SGA组件的大小优势：提供了最精细的控制，可以针对特定组件进行优化 注意事项：需要定期监控各组件的使用情况，手动调整大小

SGA监控

sql

-- 查看SGA总体大小和使用情况
SELECT name, value/1024/1024 AS size_mb FROM v$sga;

-- 查看SGA各组件的动态大小
SELECT component, current_size/1024/1024 AS current_size_mb, 
       min_size/1024/1024 AS min_size_mb, 
       max_size/1024/1024 AS max_size_mb
FROM v$sga_dynamic_components;

-- 查看SGA动态调整历史
SELECT begin_time, component, oper_type, oper_mode, 
       parameter, 
       initial_size/1024/1024 AS initial_size_mb, 
       target_size/1024/1024 AS target_size_mb, 
       final_size/1024/1024 AS final_size_mb
FROM v$sga_resize_ops
ORDER BY begin_time DESC;

-- 查看共享池使用情况
SELECT namespace, pins, pinhits, reloads, invalidations
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'PL/SQL');

-- 查看数据库缓冲区高速缓存使用情况
SELECT name, pool, bytes/1024/1024 AS size_mb, 
       current_size/1024/1024 AS current_size_mb
FROM v$buffer_pool;

-- 计算缓冲区命中率
SELECT 
  1 - (physical_reads / (db_block_gets + consistent_gets)) AS buffer_cache_hit_ratio
FROM v$sysstat;

PGA管理

PGA的组成

组件	描述	最佳实践
排序区（Sort Area）	用于SQL语句的排序操作	由Oracle自动管理，无需手动调整
哈希区（Hash Area）	用于SQL语句的哈希连接操作	由Oracle自动管理，无需手动调整
游标状态（Cursor State）	存储游标相关的信息	由Oracle自动管理，无需手动调整
会话信息（Session Information）	存储会话相关的信息	由Oracle自动管理，无需手动调整
堆栈空间（Stack Space）	用于PL/SQL过程和函数的执行	由Oracle自动管理，无需手动调整

PGA配置

自动PGA管理

自动PGA管理是推荐的PGA管理方式，Oracle会根据工作区大小策略自动调整PGA的使用。

sql

-- 启用自动PGA管理
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;
ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=BOTH;

-- 查看PGA自动管理配置
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER workarea_size_policy;

适用场景：适合大多数数据库环境优势：配置简单，Oracle会根据SQL语句的需求自动调整PGA的使用 注意事项：pga_aggregate_target应根据系统负载和SQL复杂度调整

手动PGA管理

手动PGA管理需要DBA手动调整排序区和哈希区的大小，不推荐在生产环境中使用。

sql

-- 启用手动PGA管理（不推荐）
ALTER SYSTEM SET workarea_size_policy=MANUAL SCOPE=BOTH;
ALTER SYSTEM SET sort_area_size=65536 SCOPE=BOTH;
ALTER SYSTEM SET hash_area_size=131072 SCOPE=BOTH;

适用场景：仅适合特殊的测试环境优势：提供了最精细的控制 注意事项：手动调整容易导致内存浪费或不足，不推荐在生产环境中使用

PGA监控

sql

-- 查看PGA总体使用情况
SELECT name, value/1024/1024 AS size_mb FROM v$pgastat;

-- 查看PGA使用统计信息
SELECT 
  ROUND((SELECT value FROM v$pgastat WHERE name = 'total PGA allocated') / 1024/1024, 2) AS total_pga_allocated_mb,
  ROUND((SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') / 1024/1024, 2) AS max_pga_allocated_mb,
  ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / 1024/1024, 2) AS pga_target_mb,
  ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA auto target') / 1024/1024, 2) AS pga_auto_target_mb
FROM dual;

-- 查看单个进程的PGA使用情况
SELECT spid, username, program, 
       pga_used_mem/1024/1024 AS pga_used_mb, 
       pga_alloc_mem/1024/1024 AS pga_alloc_mb, 
       pga_max_mem/1024/1024 AS pga_max_mb
FROM v$process
ORDER BY pga_max_mem DESC;

-- 查看工作区执行统计
SELECT operation_type, policy, 
       estimated_optimal_size/1024 AS estimated_optimal_kb, 
       estimated_onepass_size/1024 AS estimated_onepass_kb, 
       optimal_executions, 
       onepass_executions, 
       multipass_executions
FROM v$sysstat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name LIKE '%workarea%';

内存优化

SGA优化

数据库缓冲区高速缓存优化

监控缓冲区命中率
sql
```
SELECT 
  1 - (physical_reads / (db_block_gets + consistent_gets)) AS buffer_cache_hit_ratio
FROM v$sysstat;
```
- 目标：命中率应保持在90%以上
- 低于90%：考虑增加db_cache_size或优化SQL语句
- 高于99%：可能存在过度分配，考虑减少db_cache_size

监控缓冲区使用情况

sql

SELECT 
  name, 
  pool, 
  bytes/1024/1024 AS size_mb, 
  current_size/1024/1024 AS current_size_mb
FROM v$buffer_pool;

使用多缓冲区池

sql

-- 创建一个大表缓冲区池
ALTER SYSTEM SET db_keep_cache_size=500M SCOPE=SPFILE;
ALTER SYSTEM SET db_recycle_cache_size=500M SCOPE=SPFILE;

-- 将表移到keep缓冲区池
ALTER TABLE my_large_table STORAGE (BUFFER_POOL KEEP);

共享池优化

监控共享池命中率
sql
```
SELECT 
  1 - (sum(reloads) / sum(pins)) AS shared_pool_hit_ratio
FROM v$librarycache;
```
- 目标：命中率应保持在95%以上
- 低于95%：考虑增加shared_pool_size或优化SQL语句

监控共享池碎片

sql

SELECT 
  free_space, 
  avg_free_size, 
  free_count, 
  max_free_size
FROM v$shared_pool_reserved;

配置共享池保留区域

sql

ALTER SYSTEM SET shared_pool_reserved_size=100M SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_reserved_min_alloc=4000 SCOPE=SPFILE;

使用绑定变量
- 避免使用字面量SQL，减少硬解析
- 使用绑定变量可以提高共享池的利用率

重做日志缓冲区优化

监控重做日志缓冲区命中率

sql

SELECT 
  1 - (redo_buffer_allocation_retries / redo_entries) AS redo_buffer_hit_ratio
FROM v$sysstat;

目标：命中率应保持在99%以上
低于99%：考虑增加log_buffer

监控重做日志写入情况

sql

SELECT 
  name, 
  value
FROM v$sysstat 
WHERE name IN ('redo writes', 'redo entries', 'redo buffer allocation retries');

PGA优化

监控PGA使用率

sql

SELECT 
  ROUND((SELECT value FROM v$pgastat WHERE name = 'total PGA allocated') / 
        (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') * 100, 2) AS pga_utilization_percent
FROM dual;

目标：使用率应保持在70%以下
高于90%：考虑增加pga_aggregate_target

监控多遍执行次数
sql
```
SELECT 
  name, 
  value
FROM v$sysstat 
WHERE name LIKE '%multipass%';
```
- 目标：多遍执行次数应尽可能为0
- 大于0：考虑增加pga_aggregate_target或优化SQL语句
优化排序和哈希操作
- 减少排序的数据量：使用索引、优化WHERE条件
- 避免不必要的排序：使用UNION ALL代替UNION，使用INDEX_ASC/DESC提示
- 优化连接方式：考虑使用嵌套循环连接代替哈希连接

Oracle 19c vs 21c 内存管理差异

特性	Oracle 19c	Oracle 21c
内存管理模式	支持AMM、ASMM和手动内存管理	支持AMM、ASMM和手动内存管理，增强了自动内存管理的性能
内存Advisor	基础功能	新增了更多的内存调优建议和自动化程度
内存压缩	支持	增强了内存压缩功能，减少内存占用
内存监控	基础功能	新增了更多的内存监控视图和指标
自动内存调整	支持	优化了自动内存调整算法，提高了调整效率
内存诊断	支持	增强了内存诊断功能，便于排查内存问题
内存保护	基础功能	新增了内存保护机制，防止内存过度分配
内存重用	支持	优化了内存重用机制，提高了内存利用率

Oracle 21c 新特性

增强的自动内存管理
- 优化了内存分配算法，减少了内存碎片
- 提高了内存调整的响应速度
- 增强了内存使用预测能力
新的内存监控视图
- V$MEMORY_RESIZE_OPS_DETAILS：提供更详细的内存调整信息
- V$MEMORY_TARGET_ADVICE：提供更精确的内存目标建议
- V$MEMORY_USAGE_HISTORY：记录内存使用历史
内存压缩增强
- 支持更多类型的内存压缩
- 提高了内存压缩的效率
- 减少了内存压缩的CPU开销
内存保护机制
- 防止内存过度分配导致系统性能下降
- 自动调整内存分配，避免内存不足
- 提供内存使用告警

常见问题（FAQ）

如何确定SGA和PGA的最佳大小？

确定SGA和PGA最佳大小的方法：

根据系统总内存分配：
- 对于OLTP系统，建议SGA:PGA比例为80:20
- 对于数据仓库系统，建议SGA:PGA比例为60:40
- 总体内存分配不应超过系统总内存的80%

使用AWR报告分析：

sql

-- 创建AWR快照
EXECUTE dbms_workload_repository.create_snapshot;

-- 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

分析AWR报告中的内存使用情况
查看"Memory Statistics"部分的建议

使用内存Advisor：

sql

-- 查看SGA Advisor建议
SELECT * FROM v$sga_target_advice;

-- 查看PGA Advisor建议
SELECT * FROM v$pga_target_advice;

监控内存命中率：
- 缓冲区命中率应保持在90%-99%
- 共享池命中率应保持在95%以上
- 重做日志缓冲区命中率应保持在99%以上
- PGA使用率应保持在70%以下

如何处理SGA内存不足的问题？

处理SGA内存不足的步骤：

监控SGA各组件的使用情况：

sql

SELECT 
  component, 
  current_size/1024/1024 AS current_size_mb
FROM v$sga_dynamic_components;

分析SGA不足的具体原因：
- 是数据缓冲区不足还是共享池不足
- 查看相关命中率指标

调整相应的SGA组件大小：

sql

-- 增加数据缓冲区
ALTER SYSTEM SET db_cache_size=3G SCOPE=SPFILE;

-- 增加共享池
ALTER SYSTEM SET shared_pool_size=1.5G SCOPE=SPFILE;

考虑增加SGA_MAX_SIZE和SGA_TARGET：

sql

ALTER SYSTEM SET sga_max_size=7G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=6G SCOPE=SPFILE;

优化SQL语句：
- 使用绑定变量减少硬解析
- 优化查询计划减少内存使用
- 避免不必要的大表扫描

如何处理PGA内存不足的问题？

处理PGA内存不足的步骤：

监控PGA使用情况：

sql

SELECT 
  name, 
  value/1024/1024 AS size_mb
FROM v$pgastat 
WHERE name IN ('total PGA allocated', 'maximum PGA allocated', 'aggregate PGA target parameter');

查看多遍执行次数：

sql

SELECT 
  name, 
  value
FROM v$sysstat 
WHERE name LIKE '%multipass%';

调整PGA_AGGREGATE_TARGET：

sql

ALTER SYSTEM SET pga_aggregate_target=3G SCOPE=BOTH;

优化SQL语句：
- 减少排序和哈希操作的数据量
- 使用索引避免排序
- 优化连接方式
考虑增加系统内存：
- 如果系统内存不足，考虑添加更多物理内存
- 或考虑使用内存优化技术，如列式存储

如何监控内存泄漏？

监控内存泄漏的方法：

定期监控SGA和PGA的使用情况：

sql

-- 监控SGA使用情况
SELECT 
  TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS time,
  SUM(bytes)/1024/1024 AS sga_total_mb
FROM v$sga;

-- 监控PGA使用情况
SELECT 
  TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS time,
  value/1024/1024 AS pga_total_mb
FROM v$pgastat 
WHERE name = 'total PGA allocated';

监控单个进程的PGA使用情况：

sql

SELECT 
  spid, 
  username, 
  program, 
  pga_used_mem/1024/1024 AS pga_used_mb, 
  pga_alloc_mem/1024/1024 AS pga_alloc_mb, 
  pga_max_mem/1024/1024 AS pga_max_mb
FROM v$process
ORDER BY pga_max_mem DESC;

使用AWR报告分析内存使用趋势：
- 生成多个AWR快照，分析内存使用的变化趋势
- 查看是否存在持续增长的情况

使用ASH报告分析内存密集型SQL：

sql

-- 生成ASH报告
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

自动内存管理和手动内存管理哪个更好？

自动内存管理和手动内存管理各有优缺点：

特性	自动内存管理	手动内存管理
配置复杂度	简单	复杂
管理工作量	低	高
适应负载变化	好	差
资源利用率	高	取决于DBA经验
适合场景	大多数数据库环境	特殊测试环境

推荐：对于大多数数据库环境，建议使用自动内存管理（AMM或ASMM），可以减少DBA的工作量，同时获得较好的性能。

如何优化共享池性能？

优化共享池性能的方法：

使用绑定变量：
- 避免使用字面量SQL，减少硬解析
- 使用绑定变量可以提高共享池的利用率

调整shared_pool_size：

sql

ALTER SYSTEM SET shared_pool_size=1.5G SCOPE=SPFILE;

配置共享池保留区域：

sql

ALTER SYSTEM SET shared_pool_reserved_size=100M SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_reserved_min_alloc=4000 SCOPE=SPFILE;

监控共享池碎片：

sql

SELECT 
  free_space, 
  avg_free_size, 
  free_count, 
  max_free_size
FROM v$shared_pool_reserved;

定期刷新共享池（谨慎使用）：

sql

-- 谨慎使用，会导致所有游标失效
ALTER SYSTEM FLUSH SHARED_POOL;

使用RESULT_CACHE提示：

sql

SELECT /*+ RESULT_CACHE */ * FROM my_table WHERE column1 = 'value';

如何优化PGA性能？

优化PGA性能的方法：

调整pga_aggregate_target：

sql

ALTER SYSTEM SET pga_aggregate_target=3G SCOPE=BOTH;

优化SQL语句：
- 减少排序和哈希操作的数据量
- 使用索引避免排序
- 优化连接方式

监控PGA使用率：

sql

SELECT 
  ROUND((SELECT value FROM v$pgastat WHERE name = 'total PGA allocated') / 
        (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') * 100, 2) AS pga_utilization_percent
FROM dual;

监控多遍执行次数：

sql

SELECT 
  name, 
  value
FROM v$sysstat 
WHERE name LIKE '%multipass%';

使用自动PGA管理：

sql

ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=BOTH;

最佳实践

使用自动内存管理：对于大多数数据库环境，建议使用AMM或ASMM
合理分配内存比例：根据数据库类型和负载，合理分配SGA和PGA的比例
监控内存命中率：定期监控缓冲区命中率、共享池命中率和PGA使用率
根据实际情况调整：根据数据库的实际负载和性能指标，及时调整内存配置
避免过度分配内存：不要将SGA和PGA的总和分配超过系统总内存的80%
使用Oracle Advisor工具：定期使用AWR报告和内存Advisor工具分析内存使用情况
优化SQL语句：良好的SQL语句可以减少内存的使用，提高内存效率
定期监控内存泄漏：观察内存使用趋势，及时发现和处理内存泄漏问题
考虑使用内存压缩：在内存紧张的情况下，可以考虑使用Oracle的内存压缩功能
建立内存管理基线：记录正常负载下的内存使用情况，作为性能调优的参考
使用多缓冲区池：对于大型表，可以考虑使用keep缓冲区池
配置共享池保留区域：为大型SQL语句预留共享池空间
使用绑定变量：减少硬解析，提高共享池利用率
优化排序和哈希操作：减少PGA的使用
定期分析AWR报告：了解内存使用情况和性能瓶颈

总结

内存管理是Oracle数据库性能优化的关键组成部分。通过合理配置SGA和PGA，监控内存使用情况，及时调整内存参数，可以提高数据库的响应速度，减少I/O操作，优化系统资源的使用。

在实际生产环境中，需要根据数据库的类型、负载和系统资源情况，选择合适的内存管理方式。对于大多数数据库环境，推荐使用自动内存管理（AMM或ASMM），可以减少DBA的工作量，同时获得较好的性能。

Oracle 21c增强了内存管理的功能和性能，提供了更好的监控和管理能力，DBA可以考虑升级到新版本以获得更好的支持。

通过遵循最佳实践，DBA可以确保Oracle数据库的内存资源得到有效管理，为业务提供可靠的数据库服务。

Oracle 内存管理最佳实践 ​

内存管理概述 ​

Oracle内存结构 ​

核心目标 ​

SGA管理 ​

SGA的组成 ​

SGA配置方法 ​

自动内存管理（AMM） ​

自动共享内存管理（ASMM） ​

手动内存管理 ​

SGA监控 ​

PGA管理 ​

PGA的组成 ​

PGA配置 ​

自动PGA管理 ​

手动PGA管理 ​

PGA监控 ​

内存优化 ​

SGA优化 ​

数据库缓冲区高速缓存优化 ​

共享池优化 ​

重做日志缓冲区优化 ​

PGA优化 ​

Oracle 19c vs 21c 内存管理差异 ​

Oracle 21c 新特性 ​

常见问题（FAQ） ​

如何确定SGA和PGA的最佳大小？ ​

如何处理SGA内存不足的问题？ ​

如何处理PGA内存不足的问题？ ​

如何监控内存泄漏？ ​

自动内存管理和手动内存管理哪个更好？ ​

如何优化共享池性能？ ​

如何优化PGA性能？ ​

最佳实践 ​

总结 ​