Oracle 性能相关参数

查询优化参数

优化器参数

参数名称	默认值	说明	建议值
optimizer_mode	ALL_ROWS	优化器模式	ALL_ROWS：适合OLTP和DSS系统，FIRST_ROWS_n：适合需要快速返回前n行的场景
optimizer_features_enable	数据库版本	优化器特性启用	建议设置为数据库版本，或根据需要降级以兼容旧系统
optimizer_dynamic_sampling	2	动态采样级别	0-11，建议2-4，提高统计信息质量
optimizer_index_caching	0	索引缓存百分比	0-100，建议70-90，模拟索引块在缓存中的比例
optimizer_index_cost_adj	100	索引成本调整	1-10000，建议50-90，降低索引访问成本
optimizer_use_invisible_indexes	FALSE	使用不可见索引	FALSE：默认不使用，TRUE：允许使用不可见索引
optimizer_use_sql_plan_baselines	TRUE	使用SQL计划基线	TRUE：启用SQL计划管理，FALSE：禁用
optimizer_capture_sql_plan_baselines	FALSE	自动捕获SQL计划基线	FALSE：默认不捕获，TRUE：自动捕获重复执行的SQL计划
optimizer_adaptive_plans	TRUE	自适应计划	TRUE：启用自适应计划，FALSE：禁用
optimizer_adaptive_statistics	FALSE	自适应统计信息	FALSE：默认禁用，Oracle 12c后推荐禁用

SQL执行参数

参数名称	默认值	说明	建议值
cursor_sharing	EXACT	游标共享模式	EXACT：精确匹配，FORCE：强制共享，SIMILAR：相似共享
open_cursors	300	每个会话的最大打开游标数	建议500-2000，根据应用需求调整
session_cached_cursors	50	会话缓存游标数	建议100-500，减少硬解析
plsql_optimize_level	2	PL/SQL优化级别	0-3，建议2或3，提高PL/SQL性能
plsql_code_type	INTERPRETED	PL/SQL代码类型	INTERPRETED：解释执行，NATIVE：本地编译
result_cache_mode	MANUAL	结果缓存模式	MANUAL：手动，FORCE：强制，AUTO：自动
result_cache_max_size	自动	结果缓存最大大小	0：禁用，建议0或SGA的1-5%
result_cache_max_result	5	单结果最大缓存比例	1-100，建议5-10%

并行处理参数

并行度控制参数

参数名称	默认值	说明	建议值
parallel_degree_policy	MANUAL	并行度策略	MANUAL：手动，AUTO：自动，LIMITED：有限自动
parallel_min_time_threshold	AUTO	并行最小时间阈值	AUTO：自动，或指定时间（秒）
parallel_max_servers	自动	最大并行服务器进程数	建议2*CPU核心数
parallel_min_servers	0	最小并行服务器进程数	建议0，根据负载动态调整
parallel_servers_target	自动	并行服务器目标数	建议CPU核心数*2
parallel_execution_message_size	16384	并行执行消息大小	建议16384或32768
parallel_force_local	FALSE	强制本地并行	TRUE：强制本地执行，FALSE：允许跨实例并行
parallel_adaptive_multi_user	TRUE	自适应多用户并行	TRUE：根据负载调整并行度

并行执行参数

参数名称	默认值	说明	建议值
parallel_query_mode	ENABLED	并行查询模式	ENABLED：启用，DISABLED：禁用
parallel_dml	DISABLED	并行DML模式	DISABLED：默认禁用，需要显式启用
parallel_ddl	ENABLED	并行DDL模式	ENABLED：启用，DISABLED：禁用
parallel_degree_limit	CPU	并行度限制	CPU：基于CPU数量，IO：基于I/O能力，或指定数值
parallel_servers_instances	0	并行服务器实例数	0：自动，或指定实例数

I/O 优化参数

磁盘I/O参数

参数名称	默认值	说明	建议值
db_file_multiblock_read_count	自动	多块读的块数	自动优化，建议8-64，根据OS块大小调整
db_file_sequential_read_batch	1	顺序读批次数	1-10，建议1-4，提高顺序读性能
disk_asynch_io	TRUE	磁盘异步I/O	TRUE：启用，FALSE：禁用，建议启用
filesystemio_options	NONE	文件系统I/O选项	NONE：默认，ASYNCH：异步I/O，DIRECTIO：直接I/O，SETALL：启用所有选项
dbwr_io_slaves	0	DBWn I/O从进程数	0：禁用，建议0或根据需要设置
db_writer_processes	自动	DBWn进程数	自动计算，1个进程处理8个CPU核心
log_writer_processes	自动	LGWR进程数	自动计算，建议1-4个，根据日志写入负载调整
log_buffer	自动	重做日志缓冲区大小	建议10-100MB，避免频繁的日志写入

存储参数

参数名称	默认值	说明	建议值
db_create_file_dest	空	数据文件默认位置	ASM环境建议+DATA，文件系统环境建议具体路径
db_create_online_log_dest_1	空	在线日志默认位置1	建议与数据文件分开，提高I/O性能
db_recovery_file_dest	空	恢复文件目标位置	建议+FRA或单独路径，用于闪回日志、控制文件自动备份等
db_recovery_file_dest_size	0	恢复文件目标大小	建议数据库大小的10-20%
storage_clause	自动	默认存储子句	建议根据表空间设置合理的存储参数

内存管理参数

缓冲区缓存参数

参数名称	默认值	说明	建议值
db_cache_size	自动	数据库缓冲区大小	占SGA的40-60%，根据数据库类型调整
db_keep_cache_size	0	保持缓冲区大小	0：禁用，或根据需要设置，存放频繁访问且不常修改的表
db_recycle_cache_size	0	回收缓冲区大小	0：禁用，或根据需要设置，存放临时访问的表
db_cache_advice	ON	缓冲区缓存建议	ON：启用，OFF：禁用，READY：准备就绪
db_block_size	8192	数据库块大小	创建数据库时指定，不可修改，建议8KB
db_block_checking	FALSE	数据库块检查	FALSE：默认禁用，TRUE：启用块检查
db_block_checksum	TYPICAL	数据库块校验和	TYPICAL：典型，FULL：完全，OFF：禁用

共享池参数

参数名称	默认值	说明	建议值
shared_pool_size	自动	共享池大小	占SGA的15-25%，复杂查询多的系统可适当增大
shared_pool_reserved_size	自动	共享池保留大小	自动计算，占共享池的5-10%
shared_pool_reserved_min_alloc	4000	共享池保留最小分配	4000-8000，建议4000
cursor_space_for_time	FALSE	游标空间保留时间	FALSE：默认禁用，TRUE：启用会增加内存使用
session_cached_cursors	50	会话缓存游标数	建议100-500，减少硬解析
open_cursors	300	每个会话的最大打开游标数	建议500-2000，根据应用需求调整

PGA参数

参数名称	默认值	说明	建议值
pga_aggregate_target	自动	PGA聚合目标大小	一般为系统内存的20-30%，或SGA的50-75%
pga_aggregate_limit	200% of pga_aggregate_target	PGA聚合上限	建议为pga_aggregate_target的2-3倍
workarea_size_policy	AUTO	工作区大小策略	AUTO：自动管理，MANUAL：手动管理
sort_area_size	65536	排序区大小	仅在手动模式下使用，建议1-4MB
hash_area_size	131072	哈希区大小	仅在手动模式下使用，建议2-8MB
bitmap_merge_area_size	1048576	位图合并区大小	仅在手动模式下使用，建议1-4MB

事务处理参数

事务管理参数

参数名称	默认值	说明	建议值
transactions	派生值	最大事务数	自动计算，一般为sessions的1.1
transactions_per_rollback_segment	5	每个回滚段的事务数	建议5-10
rollback_segments	自动	回滚段列表	自动管理，建议使用UNDO表空间
undo_management	AUTO	UNDO管理方式	AUTO：自动，MANUAL：手动
undo_tablespace	自动	默认UNDO表空间	建议显式指定
undo_retention	900	UNDO保留时间（秒）	建议1800-7200，根据闪回查询需求调整
undo_retention_guarantee	FALSE	UNDO保留保证	FALSE：不保证，TRUE：保证
fast_start_mttr_target	0	故障恢复目标时间（秒）	建议300-900，根据业务需求调整
fast_start_parallel_rollback	LOW	并行回滚级别	LOW：低并行度，HIGH：高并行度

锁管理参数

参数名称	默认值	说明	建议值
dml_locks	派生值	DML锁数量	自动计算，一般为processes的4倍
ddl_locks	100	DDL锁数量	建议100-500
enqueue_resources	派生值	队列资源数量	自动计算，建议足够大以避免锁争用
lock_sga	FALSE	锁定SGA到内存	FALSE：默认不锁定，TRUE：锁定SGA
max_rollback_segments	30	最大回滚段数	建议30-100

统计信息参数

统计信息收集参数

参数名称	默认值	说明	建议值
statistics_level	TYPICAL	统计级别	TYPICAL：典型，BASIC：基本，ALL：所有
timed_statistics	TRUE	时间统计	TRUE：启用，FALSE：禁用
timed_os_statistics	0	操作系统时间统计间隔	0：禁用，建议60-300秒
stat_sid	0	统计会话ID	0：所有会话，或指定会话ID
collect_cpu_statistics	TRUE	收集CPU统计信息	TRUE：启用，FALSE：禁用
_optimizer_gather_stats_on_load	TRUE	加载时收集统计信息	TRUE：启用，FALSE：禁用

统计信息使用参数

参数名称	默认值	说明	建议值
optimizer_use_pending_statistics	FALSE	使用待处理统计信息	FALSE：默认不使用，TRUE：使用待处理统计信息
optimizer_inmemory_aware	TRUE	内存列存储感知	TRUE：启用，FALSE：禁用
optimizer_secure_view_merging	TRUE	安全视图合并	TRUE：启用，FALSE：禁用
optimizer_check_stats_on_conventional_dml	TRUE	常规DML时检查统计信息	TRUE：启用，FALSE：禁用

常见问题（FAQ）

Q1: 如何优化SQL查询性能？

A: 可以通过以下参数优化SQL查询性能：

1. 设置合适的优化器模式：

   ALTER SYSTEM SET optimizer_mode = 'ALL_ROWS' SCOPE=BOTH;

2. 调整共享池大小，减少硬解析：

   ALTER SYSTEM SET shared_pool_size = 1G SCOPE=BOTH;
   ALTER SYSTEM SET session_cached_cursors = 200 SCOPE=BOTH;

3. 启用SQL计划管理：

   ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE SCOPE=BOTH;

Q2: 如何启用并行查询？

A: 可以通过以下方式启用并行查询：

1. 设置并行度策略：

   ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;

2. 设置并行服务器目标数：

   ALTER SYSTEM SET parallel_servers_target = 32 SCOPE=BOTH;

3. 为表或索引设置并行度：

   ALTER TABLE employees PARALLEL 4;

Q3: 如何优化I/O性能？

A: 可以通过以下参数优化I/O性能：

1. 启用异步I/O和直接I/O：

   ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;
   ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;

2. 调整DBWn进程数：

   ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

3. 设置合适的日志缓冲区大小：

   ALTER SYSTEM SET log_buffer = 50M SCOPE=SPFILE;

Q4: 如何优化内存使用？

A: 可以通过以下参数优化内存使用：

1. 使用自动内存管理：

   ALTER SYSTEM SET memory_target = 8G SCOPE=SPFILE;
   ALTER SYSTEM SET memory_max_target = 10G SCOPE=SPFILE;

2. 调整SGA和PGA比例：

   ALTER SYSTEM SET sga_target = 6G SCOPE=SPFILE;
   ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;

3. 优化缓冲区缓存：

   ALTER SYSTEM SET db_cache_size = 4G SCOPE=SPFILE;

Q5: 如何优化事务处理性能？

A: 可以通过以下参数优化事务处理性能：

1. 设置合适的UNDO保留时间：

   ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

2. 调整故障恢复目标时间：

   ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;

3. 启用自动UNDO管理：

   ALTER SYSTEM SET undo_management = 'AUTO' SCOPE=SPFILE;

最佳实践

1. 根据系统类型调整参数：OLTP系统和DSS系统的参数配置差异较大，需要根据实际工作负载调整。
2. 启用自动管理功能：对于大多数系统，建议启用自动内存管理、自动UNDO管理等自动功能，简化管理。
3. 合理设置并行度：并行度不是越高越好，需要根据CPU核心数和I/O能力合理设置。
4. 定期收集统计信息：定期收集数据库对象的统计信息，确保优化器生成高效的执行计划。
5. 使用SQL计划管理：启用SQL计划管理，防止执行计划突然变化导致性能问题。
6. 监控性能指标：使用AWR报告、ASH报告等工具监控性能指标，及时调整参数。
7. 测试参数修改：在生产环境修改参数前，必须在测试环境进行充分测试，评估修改的影响。
8. 保持参数文档化：记录所有参数的修改历史和原因，便于后续维护和故障排查。
9. 遵循Oracle建议：对于不确定的参数，建议先使用Oracle的默认值或建议值，然后根据实际情况调整。
10. 定期回顾参数配置：随着业务发展和系统变化，定期回顾和调整参数配置，确保最佳性能。