外观
Oracle 系统参数优化
Oracle 系统参数优化概述
Oracle系统参数是控制数据库实例运行行为的关键配置，合理的参数设置对于数据库性能、稳定性和安全性至关重要。系统参数优化是DBA日常运维工作中的重要组成部分，直接影响数据库的整体性能表现。
参数优化的基本原则
- 基于实际负载：参数优化应根据数据库的实际负载情况进行，不同类型的应用（OLTP、OLAP、混合负载）需要不同的参数设置
- 循序渐进：参数优化应逐步进行，每次只修改少量参数，观察效果后再进行下一步优化
- 监控与验证：优化前后必须进行充分的监控和验证，确保优化效果符合预期
- 版本兼容性：不同版本的Oracle数据库支持的参数和默认值可能不同，需要注意版本兼容性
- 文档化：详细记录每次参数修改的原因、内容和效果
参数优化的工具和方法
- AWR报告：通过AWR报告分析数据库性能瓶颈，确定需要优化的参数
- ASH报告：实时分析数据库的活动会话，找出当前的性能问题
- Statspack：Oracle 10g及以前版本的性能分析工具
- 动态性能视图：通过V$视图实时监控数据库性能
- 自动优化工具：Oracle 11g及以后版本提供的SQL Tuning Advisor和Memory Advisor
内存相关参数优化
内存是Oracle数据库性能的关键因素，合理的内存配置可以显著提高数据库的性能。Oracle数据库的内存主要分为SGA（系统全局区）和PGA（程序全局区）两部分。
自动内存管理参数
Oracle 11g及以后版本引入了自动内存管理（Automatic Memory Management，AMM），可以自动调整SGA和PGA的大小。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| MEMORY_TARGET | 自动内存管理的总内存大小 | 服务器内存的40%-70% | 11g+ |
| MEMORY_MAX_TARGET | 自动内存管理的最大内存大小 | 大于等于MEMORY_TARGET，建议为服务器内存的70%-80% | 11g+ |
配置示例
sql
-- 启用自动内存管理
ALTER SYSTEM SET MEMORY_TARGET=8G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET=12G SCOPE=SPFILE;SGA相关参数
SGA（System Global Area）是Oracle实例的共享内存区域，包含数据缓存、共享池、重做日志缓冲区等。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| SGA_TARGET | SGA的目标大小 | 自动内存管理模式下不需要手动设置；手动模式下建议为服务器内存的30%-50% | 10g+ |
| SGA_MAX_SIZE | SGA的最大大小 | 大于等于SGA_TARGET | 10g+ |
| DB_CACHE_SIZE | 数据库缓冲区高速缓存大小 | 手动模式下建议为SGA的40%-60% | 8i+ |
| SHARED_POOL_SIZE | 共享池大小 | 手动模式下建议为SGA的15%-25% | 8i+ |
| LARGE_POOL_SIZE | 大池大小 | 用于RMAN备份、共享服务器模式等，建议为SGA的5%-10% | 8i+ |
| JAVA_POOL_SIZE | Java池大小 | 用于Java存储过程，建议为SGA的5%-10% | 8i+ |
| LOG_BUFFER | 重做日志缓冲区大小 | 建议为32M-128M，不超过512M | 8i+ |
配置示例
sql
-- 手动配置SGA参数
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET DB_CACHE_SIZE=4G SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE=1.5G SCOPE=SPFILE;
ALTER SYSTEM SET LARGE_POOL_SIZE=512M SCOPE=SPFILE;
ALTER SYSTEM SET JAVA_POOL_SIZE=256M SCOPE=SPFILE;
ALTER SYSTEM SET LOG_BUFFER=64M SCOPE=SPFILE;PGA相关参数
PGA（Program Global Area）是Oracle实例的私有内存区域，包含排序区、哈希区、游标区等。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| PGA_AGGREGATE_TARGET | PGA的目标大小 | 自动内存管理模式下不需要手动设置；手动模式下建议为SGA的50%左右 | 10g+ |
| PGA_AGGREGATE_LIMIT | PGA的最大大小 | 建议为PGA_AGGREGATE_TARGET的2倍 | 12c+ |
| SORT_AREA_SIZE | 排序区大小 | 自动PGA管理模式下不需要手动设置 | 8i+ |
| HASH_AREA_SIZE | 哈希区大小 | 自动PGA管理模式下不需要手动设置 | 8i+ |
配置示例
sql
-- 手动配置PGA参数
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=4G SCOPE=SPFILE;进程相关参数优化
进程相关参数控制Oracle实例可以创建的最大进程数、会话数等，直接影响数据库的并发处理能力。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| PROCESSES | 数据库允许的最大进程数 | 根据实际并发需求设置，建议为SESSIONS的80%左右 | 8i+ |
| SESSIONS | 数据库允许的最大会话数 | 建议为PROCESSES的1.1-1.5倍 | 8i+ |
| TRANSACTIONS | 数据库允许的最大事务数 | 建议为SESSIONS的1.1倍 | 8i+ |
| TRANSACTIONS_PER_SESSION | 每个会话允许的最大事务数 | 默认1，一般不需要修改 | 8i+ |
配置示例
sql
-- 配置进程相关参数
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS=400 SCOPE=SPFILE;
ALTER SYSTEM SET TRANSACTIONS=440 SCOPE=SPFILE;存储相关参数优化
存储相关参数控制Oracle数据库的存储结构和I/O行为，合理的存储参数设置可以提高数据库的I/O性能。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| DB_BLOCK_SIZE | 数据库块大小 | OLTP系统建议8K；数据仓库建议16K或32K | 8i+ |
| DB_FILE_MULTIBLOCK_READ_COUNT | 多块读的块数 | 根据DB_BLOCK_SIZE设置，建议总大小为64K-128K | 8i+ |
| DB_FILES | 数据库允许的最大数据文件数 | 根据实际需求设置，默认200 | 8i+ |
| MAXDATAFILES | 控制文件中记录的最大数据文件数 | 建议大于等于DB_FILES | 8i+ |
配置示例
sql
-- 配置存储相关参数
ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=16 SCOPE=SPFILE; -- 8K块大小
ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=8 SCOPE=SPFILE; -- 16K块大小
ALTER SYSTEM SET DB_FILES=500 SCOPE=SPFILE;日志相关参数优化
日志相关参数控制Oracle数据库的重做日志和撤销日志行为，合理的日志参数设置可以提高数据库的事务处理能力和恢复能力。
重做日志相关参数
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| LOG_BUFFER | 重做日志缓冲区大小 | 建议为32M-128M，不超过512M | 8i+ |
| LOG_CHECKPOINT_INTERVAL | 检查点间隔的OS块数 | 建议设置为0，使用LOG_CHECKPOINT_TIMEOUT | 8i+ |
| LOG_CHECKPOINT_TIMEOUT | 检查点超时时间（秒） | 建议设置为1800（30分钟） | 8i+ |
| FAST_START_MTTR_TARGET | 实例恢复的目标时间（秒） | 建议设置为300-900秒 | 10g+ |
| LOG_FILE_SIZE | 重做日志文件大小 | 建议2G-4G，根据数据库负载调整 | 8i+ |
配置示例
sql
-- 配置重做日志相关参数
ALTER SYSTEM SET LOG_BUFFER=64M SCOPE=SPFILE;
ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL=0 SCOPE=SPFILE;
ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT=1800 SCOPE=SPFILE;
ALTER SYSTEM SET FAST_START_MTTR_TARGET=600 SCOPE=SPFILE;撤销日志相关参数
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| UNDO_TABLESPACE | 默认撤销表空间 | 建议创建专门的撤销表空间 | 9i+ |
| UNDO_RETENTION | 撤销数据的保留时间（秒） | OLTP系统建议900-1800秒；数据仓库建议3600秒以上 | 9i+ |
| UNDO_MANAGEMENT | 撤销管理方式 | 建议使用AUTO（自动撤销管理） | 9i+ |
| RETENTION_GUARANTEE | 是否保证撤销数据的保留时间 | 建议设置为NO，避免表空间不足 | 10g+ |
配置示例
sql
-- 配置撤销日志相关参数
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_RETENTION=1800 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;并行处理参数优化
并行处理参数控制Oracle数据库的并行执行行为，合理的并行参数设置可以提高大数据量操作的性能。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| PARALLEL_MAX_SERVERS | 最大并行服务器进程数 | 建议为CPU核心数的2-4倍 | 8i+ |
| PARALLEL_SERVERS_TARGET | 并行服务器进程的目标数 | 建议为CPU核心数的1-2倍 | 11g+ |
| PARALLEL_MIN_SERVERS | 最小并行服务器进程数 | 建议设置为0或较小值 | 8i+ |
| PARALLEL_DEGREE_POLICY | 并行度策略 | 建议设置为AUTO或LIMITED | 11g+ |
| PARALLEL_ADAPTIVE_MULTI_USER | 多用户并行自适应 | 建议设置为TRUE | 10g+ |
配置示例
sql
-- 配置并行处理参数
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=64 SCOPE=SPFILE; -- 16核CPU
ALTER SYSTEM SET PARALLEL_SERVERS_TARGET=32 SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=LIMITED SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER=TRUE SCOPE=SPFILE;网络相关参数优化
网络相关参数控制Oracle数据库的网络连接行为，合理的网络参数设置可以提高数据库的远程访问性能。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| SQLNET.INBOUND_CONNECT_TIMEOUT | 入站连接超时时间（秒） | 建议设置为60-120秒 | 10g+ |
| SQLNET.OUTBOUND_CONNECT_TIMEOUT | 出站连接超时时间（秒） | 建议设置为60-120秒 | 10g+ |
| SQLNET.RECV_TIMEOUT | 接收超时时间（秒） | 建议设置为600秒 | 10g+ |
| SQLNET.SEND_TIMEOUT | 发送超时时间（秒） | 建议设置为600秒 | 10g+ |
| DISPATCHERS | 共享服务器调度器配置 | 建议根据并发连接数设置 | 8i+ |
| PROCESSES | 数据库允许的最大进程数 | 根据实际并发需求设置 | 8i+ |
配置示例
sql
-- 在sqlnet.ora中配置网络参数
SQLNET.INBOUND_CONNECT_TIMEOUT=60
SQLNET.OUTBOUND_CONNECT_TIMEOUT=60
SQLNET.RECV_TIMEOUT=600
SQLNET.SEND_TIMEOUT=600
-- 配置共享服务器调度器
ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP) (SERVICE=orclXDB)' SCOPE=SPFILE;
ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP) (DISPATCHERS=4)' SCOPE=SPFILE;安全相关参数优化
安全相关参数控制Oracle数据库的安全行为，合理的安全参数设置可以提高数据库的安全性。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| AUDIT_TRAIL | 审计日志配置 | 建议设置为DB或DB,EXTENDED | 8i+ |
| REMOTE_LOGIN_PASSWORDFILE | 密码文件配置 | 建议设置为EXCLUSIVE | 8i+ |
| SEC_CASE_SENSITIVE_LOGON | 密码大小写敏感 | 建议设置为TRUE | 11g+ |
| PASSWORD_LIFE_TIME | 密码有效期（天） | 建议设置为90-180天 | 8i+ |
| PASSWORD_GRACE_TIME | 密码宽限期（天） | 建议设置为7-14天 | 8i+ |
| PASSWORD_REUSE_MAX | 密码重用前的最小更改次数 | 建议设置为10-20 | 8i+ |
| PASSWORD_REUSE_TIME | 密码重用前的最小天数 | 建议设置为365天 | 8i+ |
| OS_AUTHENT_PREFIX | OS认证前缀 | 建议设置为空字符串或删除 | 8i+ |
配置示例
sql
-- 配置安全相关参数
ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=TRUE SCOPE=SPFILE;
-- 配置密码策略
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 14
PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 365
PASSWORD_VERIFY_FUNCTION NULL;性能监控参数优化
性能监控参数控制Oracle数据库的性能监控行为，合理的监控参数设置可以帮助DBA及时发现和解决性能问题。
| 参数名称 | 描述 | 建议值 | 版本支持 |
|---|---|---|---|
| STATISTICS_LEVEL | 统计信息收集级别 | 建议设置为TYPICAL | 10g+ |
| TIMED_STATISTICS | 时间统计信息收集 | 建议设置为TRUE | 8i+ |
| DBMS_STATS_STATS$ | 统计信息历史保留 | 建议设置为31天 | 10g+ |
| AWR_RETENTION | AWR报告保留时间（分钟） | 建议设置为43200（30天） | 10g+ |
| AWR_SNAPSHOT_INTERVAL | AWR快照间隔时间（分钟） | 建议设置为60分钟 | 10g+ |
配置示例
sql
-- 配置性能监控参数
ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL SCOPE=SPFILE;
ALTER SYSTEM SET TIMED_STATISTICS=TRUE SCOPE=SPFILE;
-- 配置AWR
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>43200, interval=>60);不同Oracle版本的参数差异
Oracle 10g 新增参数
- SGA_TARGET：自动SGA管理
- PGA_AGGREGATE_TARGET：自动PGA管理
- FAST_START_MTTR_TARGET：实例恢复目标时间
- AWR相关参数：AWR_RETENTION、AWR_SNAPSHOT_INTERVAL
Oracle 11g 新增参数
- MEMORY_TARGET：自动内存管理
- MEMORY_MAX_TARGET：最大自动内存大小
- PARALLEL_DEGREE_POLICY：并行度策略
- PARALLEL_SERVERS_TARGET：并行服务器目标数
- SQLNET.INBOUND_CONNECT_TIMEOUT：入站连接超时
Oracle 12c 新增参数
- PGA_AGGREGATE_LIMIT：PGA最大大小
- ENABLE_PLUGGABLE_DATABASE：启用可插拔数据库
- CDB_SETTINGS：多租户相关参数
- UNDO_RETENTION_GUARANTEE：撤销保留保证
Oracle 19c 新增参数
- ENCRYPT_NEW_TABLESPACES：新表空间加密
- ENABLE_AUTO_CDR：自动冲突检测和解决
- WALLET_ROOT：钱包根目录
- PDB_OS_CREDENTIAL：PDB操作系统凭证
系统参数优化的最佳实践
- 了解业务需求：不同类型的应用（OLTP、OLAP、混合负载）需要不同的参数设置
- 使用自动内存管理：对于大多数场景，建议使用自动内存管理（MEMORY_TARGET），简化管理
- 合理设置重做日志大小：重做日志文件大小建议为2G-4G，减少检查点次数
- 监控PGA使用情况：定期检查V$PGASTAT视图，确保PGA使用合理
- 调整并行度设置：根据CPU核心数和负载情况调整并行参数
- 定期收集统计信息：确保优化器有准确的统计信息
- 监控等待事件：通过V$SESSION_WAIT和AWR报告监控等待事件，找出性能瓶颈
- 测试参数变更：在测试环境中充分测试参数变更，然后再应用到生产环境
- 文档化所有变更：详细记录每次参数变更的原因、内容和效果
- 定期审查参数设置：定期审查参数设置，确保它们仍然符合当前的业务需求
参数优化的实际案例
案例1：OLTP系统内存优化
问题：某OLTP系统出现大量的缓冲区忙等待和共享池 latch 等待
分析：通过AWR报告发现，SGA_TARGET设置为4G，而服务器内存为16G，内存分配不足；共享池命中率低，只有85%
优化方案：
- 增加MEMORY_TARGET到10G
- 调整SGA_TARGET到8G
- 增加SHARED_POOL_SIZE到2G
优化效果：
- 缓冲区忙等待减少了90%
- 共享池命中率提高到99%
- 系统响应时间从平均500ms降低到100ms
案例2：数据仓库并行处理优化
问题：某数据仓库系统在执行ETL作业时，并行度设置不合理，导致CPU利用率过高，作业执行时间长
分析：通过ASH报告发现，并行服务器进程数达到了200个，而CPU只有32核，导致大量的上下文切换
优化方案：
- 设置PARALLEL_MAX_SERVERS为64
- 设置PARALLEL_SERVERS_TARGET为32
- 设置PARALLEL_DEGREE_POLICY为LIMITED
- 设置PARALLEL_ADAPTIVE_MULTI_USER为TRUE
优化效果：
- CPU利用率稳定在80%左右
- 上下文切换减少了70%
- ETL作业执行时间从平均4小时降低到2小时
常见问题（FAQ）
Q1: 如何确定需要优化哪些参数？
A: 可以通过以下方法确定需要优化的参数：
- 分析AWR报告，查看Top 5等待事件和性能指标
- 使用ASH报告实时监控当前的性能瓶颈
- 检查动态性能视图，如V$SGASTAT、V$PGASTAT、V$SESSION_WAIT等
- 比较数据库当前参数设置与最佳实践的差异
- 根据业务需求和负载变化调整参数
Q2: 如何在不重启数据库的情况下修改参数？
A: 可以使用ALTER SYSTEM命令修改参数，并指定SCOPE=BOTH或SCOPE=MEMORY：
sql
-- 修改参数并立即生效，同时保存到spfile
ALTER SYSTEM SET MEMORY_TARGET=10G SCOPE=BOTH;
-- 修改参数只在内存中生效，数据库重启后失效
ALTER SYSTEM SET TIMED_STATISTICS=TRUE SCOPE=MEMORY;Q3: 如何恢复默认参数值？
A: 可以使用以下方法恢复默认参数值：
sql
-- 恢复单个参数的默认值
ALTER SYSTEM RESET MEMORY_TARGET SCOPE=SPFILE;
-- 恢复所有参数的默认值（谨慎使用）
CREATE PFILE FROM SPFILE;
-- 编辑pfile，删除所有自定义参数
CREATE SPFILE FROM PFILE;Q4: 如何监控参数修改后的效果？
A: 可以通过以下方法监控参数修改后的效果：
- 生成参数修改前后的AWR报告，比较性能指标
- 使用ASH报告监控实时性能变化
- 监控关键性能指标，如响应时间、吞吐量、等待事件等
- 观察业务系统的实际运行效果
Q5: 自动内存管理和手动内存管理哪个更好？
A: 这取决于具体的应用场景：
- 自动内存管理：适合大多数场景，特别是对于DBA经验不足或系统负载变化较大的情况
- 手动内存管理：适合有经验的DBA，对于特定负载类型（如纯OLTP或纯OLAP）可能会有更好的性能
一般建议先使用自动内存管理，然后根据实际性能情况进行微调。
Q6: 如何设置合适的PROCESSES和SESSIONS参数？
A: 可以通过以下方法确定合适的PROCESSES和SESSIONS参数：
监控当前的最大并发会话数：
sqlSELECT MAX(COUNT(*)) OVER () AS max_sessions FROM V$SESSION;根据业务需求和预期增长设置参数，建议预留一定的缓冲区
一般来说，SESSIONS = PROCESSES * 1.1 + 5
Q7: 如何优化重做日志性能？
A: 可以通过以下方法优化重做日志性能：
- 增大重做日志文件大小，建议为2G-4G
- 确保重做日志文件分布在不同的磁盘上，避免I/O瓶颈
- 调整LOG_BUFFER大小，建议为32M-128M
- 优化CHECKPOINT设置，建议使用FAST_START_MTTR_TARGET
Q8: 如何优化撤销表空间性能？
A: 可以通过以下方法优化撤销表空间性能：
- 确保撤销表空间有足够的大小，避免频繁扩展
- 设置合适的UNDO_RETENTION参数，根据实际需求调整
- 监控撤销表空间的使用情况，定期检查DBA_UNDO_EXTENTS视图
- 对于长时间运行的查询，考虑使用FLASHBACK QUERY
系统参数优化的总结
Oracle系统参数优化是一个持续的过程，需要DBA根据业务需求、系统负载和版本特性不断调整和优化。合理的参数设置可以显著提高数据库的性能、稳定性和安全性，减少故障发生的概率，提高系统的可用性。
在进行参数优化时，DBA应该遵循以下原则：
- 基于实际负载和业务需求进行优化
- 循序渐进，每次只修改少量参数
- 充分测试，确保优化效果符合预期
- 文档化所有变更，便于后续维护和回滚
- 定期审查和调整参数，适应业务变化
通过不断的学习和实践，DBA可以积累丰富的参数优化经验，为企业的数据库系统提供更好的支持和保障。