印刻数据库

外观

Oracle 内存参数优化 ​

内存参数配置基础 ​

内存配置策略 ​

  • 自动内存管理：Oracle 11g及以上版本推荐使用，设置MEMORY_TARGET和MEMORY_MAX_TARGET参数
  • 自动共享内存管理：设置SGA_TARGET和SGA_MAX_SIZE参数，手动设置PGA_AGGREGATE_TARGET
  • 手动内存管理：手动设置各个内存组件的大小，适合经验丰富的DBA

内存分配原则 ​

  • 总内存分配：数据库服务器内存的70-80%分配给Oracle，预留20-30%给操作系统
  • SGA与PGA比例：OLTP系统SGA:PGA = 70:30，OLAP系统SGA:PGA = 50:50
  • 缓冲区高速缓存：通常占SGA的40-50%
  • 共享池：通常占SGA的20-30%

内存参数优先级 ​

  1. 首先设置总内存大小（MEMORY_TARGET或SGA_TARGET+PGA_AGGREGATE_TARGET）
  2. 然后设置SGA各组件大小
  3. 最后设置PGA相关参数

自动内存管理 ​

配置方法 ​

sql
-- 设置自动内存管理
ALTER SYSTEM SET MEMORY_MAX_TARGET=16G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=12G SCOPE=SPFILE;

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

-- 检查内存参数
SELECT * FROM V$MEMORY_TARGET_ADVICE ORDER BY MEMORY_SIZE;

优势 ​

  • 简化管理：Oracle自动调整SGA和PGA大小
  • 动态调整：根据工作负载自动调整内存分配
  • 避免过度分配：不会超过MEMORY_TARGET限制
  • 适合大多数环境：特别是混合工作负载环境

注意事项 ​

  • MEMORY_MAX_TARGET：应设置为服务器内存的70-80%
  • MEMORY_TARGET：应根据实际工作负载设置，避免设置过大导致操作系统内存不足
  • 重启要求：修改MEMORY_MAX_TARGET需要重启数据库
  • 兼容性：与手动设置的SGA和PGA参数可能存在冲突

自动共享内存管理 ​

配置方法 ​

sql
-- 设置SGA大小
ALTER SYSTEM SET SGA_MAX_SIZE=12G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=10G SCOPE=SPFILE;

-- 设置PGA大小
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6G SCOPE=SPFILE;

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

-- 检查SGA建议
SELECT * FROM V$SGA_TARGET_ADVICE ORDER BY SGA_SIZE;

-- 检查PGA建议
SELECT * FROM V$PGA_TARGET_ADVICE ORDER BY PGA_TARGET_FOR_ESTIMATE;

优势 ​

  • 灵活控制：可以分别控制SGA和PGA大小
  • 动态调整：SGA各组件大小自动调整
  • 适合特定工作负载：可以根据工作负载类型调整SGA和PGA比例
  • 兼容性好：与手动设置的SGA组件参数兼容

注意事项 ​

  • SGA_MAX_SIZE：应设置为预期的最大SGA大小
  • SGA_TARGET：应根据实际工作负载设置
  • PGA_AGGREGATE_TARGET：应根据排序、哈希等操作的需求设置
  • PGA_AGGREGATE_LIMIT：12c及以上版本，限制PGA最大使用量

手动内存管理 ​

配置方法 ​

sql
-- 禁用自动内存管理
ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

-- 设置共享池大小
ALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=SPFILE;

-- 设置缓冲区高速缓存大小
ALTER SYSTEM SET DB_CACHE_SIZE=4G SCOPE=SPFILE;

-- 设置大型池大小
ALTER SYSTEM SET LARGE_POOL_SIZE=512M SCOPE=SPFILE;

-- 设置Java池大小
ALTER SYSTEM SET JAVA_POOL_SIZE=256M SCOPE=SPFILE;

-- 设置流池大小
ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=SPFILE;

-- 设置PGA大小
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=SPFILE;

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

适用场景 ​

  • 特殊工作负载：需要针对特定工作负载优化内存分配
  • 资源受限：服务器内存有限，需要精确控制各组件大小
  • 性能调优：需要精细调整内存分配以获得最佳性能
  • 旧版本数据库：Oracle 10g及以下版本

注意事项 ​

  • 需要经验：需要丰富的DBA经验才能合理设置
  • 动态调整有限：手动设置的参数不会自动调整
  • 容易过度分配：可能导致内存过度分配，影响系统稳定性
  • 定期监控：需要定期监控内存使用情况，及时调整

关键内存参数优化 ​

共享池优化 ​

关键参数 ​

  • SHARED_POOL_SIZE：共享池大小
  • SHARED_POOL_RESERVED_SIZE：共享池保留空间大小
  • SHARED_POOL_RESERVED_MIN_ALLOC：保留空间最小分配大小

优化建议 ​

sql
-- 设置共享池大小
ALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=SPFILE;

-- 设置共享池保留空间
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE=200M SCOPE=SPFILE;

-- 检查共享池使用情况
SELECT * FROM V$SHARED_POOL_ADVICE ORDER BY SHARED_POOL_SIZE_FOR_ESTIMATE;

-- 检查共享池保留空间使用情况
SELECT * FROM V$SHARED_POOL_RESERVED;

缓冲区高速缓存优化 ​

关键参数 ​

  • DB_CACHE_SIZE：默认块大小的缓冲区高速缓存大小
  • DB_nK_CACHE_SIZE：指定块大小的缓冲区高速缓存大小
  • DB_KEEP_CACHE_SIZE：保持缓冲区大小
  • DB_RECYCLE_CACHE_SIZE：回收缓冲区大小

优化建议 ​

sql
-- 设置默认缓冲区高速缓存大小
ALTER SYSTEM SET DB_CACHE_SIZE=4G SCOPE=SPFILE;

-- 设置保持缓冲区大小
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=512M SCOPE=SPFILE;

-- 检查缓冲区高速缓存使用情况
SELECT * FROM V$DB_CACHE_ADVICE WHERE NAME='DEFAULT' ORDER BY SIZE_FOR_ESTIMATE;

-- 检查缓冲区命中率
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) AS HIT_RATIO
FROM V$BUFFER_POOL_STATISTICS;

PGA优化 ​

关键参数 ​

  • PGA_AGGREGATE_TARGET：PGA总大小
  • PGA_AGGREGATE_LIMIT：PGA最大限制（12c及以上）
  • WORKAREA_SIZE_POLICY：工作区大小策略（AUTO/MANUAL）
  • SORT_AREA_SIZE：排序区大小（手动内存管理）

优化建议 ​

sql
-- 设置PGA大小
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=SPFILE;

-- 设置PGA限制
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6G SCOPE=SPFILE;

-- 检查PGA使用情况
SELECT * FROM V$PGA_TARGET_ADVICE ORDER BY PGA_TARGET_FOR_ESTIMATE;

-- 检查工作区执行统计
SELECT * FROM V$SQL_WORKAREA_ACTIVE;

大型池优化 ​

关键参数 ​

  • LARGE_POOL_SIZE：大型池大小

优化建议 ​

sql
-- 设置大型池大小
ALTER SYSTEM SET LARGE_POOL_SIZE=512M SCOPE=SPFILE;

-- 检查大型池使用情况
SELECT * FROM V$SGASTAT WHERE POOL='large pool';

流池优化 ​

关键参数 ​

  • STREAMS_POOL_SIZE：流池大小

优化建议 ​

sql
-- 设置流池大小
ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=SPFILE;

-- 检查流池使用情况
SELECT * FROM V$SGASTAT WHERE POOL='streams pool';

内存使用监控 ​

实时监控 ​

sql
-- 检查内存使用概览
SELECT * FROM V$SGAINFO;

-- 检查SGA各组件大小
SELECT * FROM V$SGASTAT ORDER BY POOL, NAME;

-- 检查PGA使用情况
SELECT * FROM V$PGASTAT;

-- 检查会话内存使用
SELECT SID, SERIAL#, PROGRAM, PGA_USED_MEM/1024/1024 AS PGA_USED_MB, 
       PGA_ALLOC_MEM/1024/1024 AS PGA_ALLOC_MB
FROM V$SESSION ORDER BY PGA_USED_MEM DESC;

-- 检查内存调整建议
SELECT * FROM V$MEMORY_TARGET_ADVICE ORDER BY MEMORY_SIZE;

性能视图 ​

  • V$SGA：显示SGA总体大小
  • V$SGA_DYNAMIC_COMPONENTS：显示SGA动态组件大小
  • V$SGA_RESIZE_OPS：显示SGA调整操作历史
  • V$PGASTAT：显示PGA使用统计信息
  • V$MEMORY_RESIZE_OPS：显示内存调整操作历史
  • V$SQL_WORKAREA_HISTOGRAM：显示工作区执行历史

AWR报告 ​

  • 内存部分：AWR报告的"Memory Statistics"部分
  • PGA统计：AWR报告的"PGA Aggr Target Stats"部分
  • SGA统计：AWR报告的"SGA Statistics"部分
  • 内存建议：AWR报告的"Memory Advisory"部分

内存问题诊断 ​

内存不足 ​

症状 ​

  • ORA-04030：PGA内存不足
  • ORA-04031：共享池内存不足
  • 系统性能下降：内存交换频繁，响应时间变长
  • 操作系统告警：内存使用率过高

解决方法 ​

sql
-- 检查内存使用情况
SELECT * FROM V$MEMORY_RESIZE_OPS WHERE STATUS='COMPLETED' ORDER BY END_TIME DESC;

-- 增加内存分配
ALTER SYSTEM SET MEMORY_TARGET=16G SCOPE=SPFILE;

-- 或增加SGA和PGA大小
ALTER SYSTEM SET SGA_TARGET=12G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=6G SCOPE=SPFILE;

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

内存泄漏 ​

症状 ​

  • 内存使用率持续增长：没有下降趋势
  • 会话内存使用异常：个别会话内存使用量异常大
  • 共享池内存使用异常：共享池内存使用量持续增长

解决方法 ​

sql
-- 检查会话内存使用
SELECT SID, SERIAL#, PROGRAM, PGA_USED_MEM/1024/1024 AS PGA_USED_MB
FROM V$SESSION ORDER BY PGA_USED_MEM DESC;

-- 检查共享池内存使用
SELECT * FROM V$SHARED_POOL_RESERVED;

-- 刷新共享池（谨慎使用）
ALTER SYSTEM FLUSH SHARED_POOL;

-- 重启问题会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

内存分配不均衡 ​

症状 ​

  • 某些内存组件使用率过高：如共享池使用率接近100%
  • 某些内存组件使用率过低：如大型池使用率低于10%
  • 性能问题：特定操作性能差

解决方法 ​

sql
-- 检查各内存组件使用率
SELECT POOL, NAME, BYTES/1024/1024 AS MB FROM V$SGASTAT ORDER BY POOL, BYTES DESC;

-- 调整内存分配
ALTER SYSTEM SET SHARED_POOL_SIZE=3G SCOPE=SPFILE;
ALTER SYSTEM SET DB_CACHE_SIZE=5G SCOPE=SPFILE;

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

版本差异 ​

Oracle 10g ​

  • 内存管理：支持自动共享内存管理（SGA_TARGET）
  • PGA管理：支持PGA_AGGREGATE_TARGET
  • 内存参数：需要手动设置各个内存组件大小
  • 监控工具：提供基本的内存使用监控视图

Oracle 11g ​

  • 内存管理：引入自动内存管理（MEMORY_TARGET）
  • 内存调整：增强了内存自动调整能力
  • 监控工具：增强了内存使用监控和建议视图
  • 内存参数：保留了手动内存管理的兼容性

Oracle 12c及以上 ​

  • 内存管理：增强了自动内存管理功能
  • PGA限制：引入PGA_AGGREGATE_LIMIT参数
  • 多租户：支持多租户架构的内存管理
  • 内存优化：增强了内存使用效率
  • 监控工具：提供更详细的内存使用监控和建议

最佳实践 ​

内存配置最佳实践 ​

  • 使用自动内存管理：Oracle 11g及以上版本推荐使用
  • 合理设置内存大小：数据库服务器内存的70-80%分配给Oracle
  • 根据工作负载调整：OLTP系统SGA:PGA = 70:30，OLAP系统SGA:PGA = 50:50
  • 预留操作系统内存：预留20-30%内存给操作系统
  • 定期监控：定期监控内存使用情况，及时调整

内存监控最佳实践 ​

  • 建立监控机制：使用Oracle Enterprise Manager或自定义脚本监控内存使用
  • 设置告警阈值：设置内存使用率告警阈值
  • 定期分析：定期分析AWR报告中的内存部分
  • 趋势分析：分析内存使用趋势，预测未来需求
  • 基准测试：建立内存使用基准，评估优化效果

内存问题处理最佳实践 ​

  • 建立应急预案：制定内存不足等问题的应急预案
  • 快速诊断：使用V$视图快速诊断内存问题
  • 谨慎调整：内存参数调整应在测试环境验证后进行
  • 记录变更：详细记录内存参数变更和效果
  • 持续优化：根据业务变化持续优化内存配置

常见问题（FAQ） ​

Q1: 如何确定数据库服务器的内存分配比例？ ​

A1: 确定数据库服务器内存分配比例的方法：

  • 总内存分配：数据库服务器内存的70-80%分配给Oracle，预留20-30%给操作系统
  • SGA与PGA比例：根据工作负载类型调整
    • OLTP系统：SGA:PGA = 70:30
    • OLAP系统：SGA:PGA = 50:50
    • 混合工作负载：SGA:PGA = 60:40
  • 考虑因素：服务器内存大小、操作系统类型、其他运行的服务

Q2: 自动内存管理与手动内存管理如何选择？ ​

A2: 自动内存管理与手动内存管理的选择：

  • 自动内存管理：适合大多数环境，特别是：
    • 缺乏经验的DBA
    • 混合工作负载环境
    • 希望简化管理的环境
    • Oracle 11g及以上版本
  • 手动内存管理：适合：
    • 经验丰富的DBA
    • 特殊工作负载环境
    • 资源受限的环境
    • 需要精细调优的环境

Q3: 如何监控Oracle数据库的内存使用情况？ ​

A3: 监控Oracle数据库内存使用情况的方法：

  • 实时监控：查询V$SGAINFO、V$SGASTAT、V$PGASTAT等视图
  • AWR报告：分析AWR报告中的内存部分
  • Oracle Enterprise Manager：使用OEM监控内存使用
  • 自定义脚本：编写自定义脚本监控内存使用趋势
  • 操作系统监控：监控操作系统内存使用情况

Q4: 遇到ORA-04031共享池内存不足错误，如何解决？ ​

A4: 解决ORA-04031共享池内存不足错误的方法：

  • 增加共享池大小：ALTER SYSTEM SET SHARED_POOL_SIZE=3G SCOPE=SPFILE;
  • 增加共享池保留空间：ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE=300M SCOPE=SPFILE;
  • 使用绑定变量：修改应用程序使用绑定变量，减少硬解析
  • 刷新共享池：ALTER SYSTEM FLUSH SHARED_POOL;（谨慎使用）
  • 检查大对象：查找占用共享池的大对象并优化

Q5: 如何优化缓冲区高速缓存以提高性能？ ​

A5: 优化缓冲区高速缓存以提高性能的方法：

  • 调整大小：根据缓冲区命中率调整DB_CACHE_SIZE
  • 使用保持缓冲区：将频繁访问的表放入保持缓冲区
  • 使用回收缓冲区：将不频繁访问的表放入回收缓冲区
  • 监控命中率：保持缓冲区命中率在90%以上
  • 考虑多块大小：对于不同大小的表，考虑使用不同的块大小

Q6: 如何确定PGA_AGGREGATE_TARGET的合理值？ ​

A6: 确定PGA_AGGREGATE_TARGET合理值的方法：

  • 经验值：OLTP系统设置为SGA的30%，OLAP系统设置为SGA的50%
  • 使用PGA建议：查询V$PGA_TARGET_ADVICE视图
  • 监控PGA使用：监控V$PGASTAT视图中的PGA使用情况
  • 观察排序操作：监控排序操作是否溢出到磁盘
  • 基准测试：通过基准测试确定最佳值

Q7: 内存参数调整后，如何验证优化效果？ ​

A7: 验证内存参数优化效果的方法：

  • 性能指标：监控响应时间、吞吐量等性能指标
  • 内存使用率：检查内存使用率是否合理
  • 命中率：检查缓冲区高速缓存命中率、共享池命中率
  • 等待事件：检查内存相关的等待事件是否减少
  • AWR报告：比较优化前后的AWR报告
  • 业务指标：检查业务操作的执行时间是否减少

Q8: 大型数据库的内存参数如何优化？ ​

A8: 大型数据库的内存参数优化方法：

  • 使用自动内存管理：简化管理
  • 合理分配内存：根据数据库大小和工作负载分配足够的内存
  • 优化缓冲区高速缓存：增加缓冲区高速缓存大小，提高缓存命中率
  • 使用保持缓冲区：将频繁访问的大表放入保持缓冲区
  • 监控内存使用：定期监控内存使用情况，及时调整
  • 考虑使用列式存储：对于OLAP系统，考虑使用列式存储减少内存使用
  • 使用分区表：减少单个查询的内存需求