DB / Oracle / 性能诊断

Oracle 数据库参数调优

2026.09.06

Oracle 数据库参数调优

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

参数调优是 Oracle 性能优化基础[1]：

类型：

  • 内存参数
  • 进程参数
  • I/O 参数
  • 优化器参数

2. 内存参数

2.1 SGA

-- 自动管理
ALTER SYSTEM SET sga_target = 16G SCOPE=SPFILE;
ALTER SYSTEM SET memory_target = 24G SCOPE=SPFILE;  -- AMM

-- 或 ASMM
ALTER SYSTEM SET sga_target = 16G;
ALTER SYSTEM SET pga_aggregate_target = 8G;

2.2 SGA 组件

-- 自动管理下设置最小值
ALTER SYSTEM SET db_cache_size = 8G;
ALTER SYSTEM SET shared_pool_size = 2G;
ALTER SYSTEM SET large_pool_size = 512M;
ALTER SYSTEM SET java_pool_size = 256M;
ALTER SYSTEM SET streams_pool_size = 256M;

2.3 PGA

ALTER SYSTEM SET pga_aggregate_target = 8G;
ALTER SYSTEM SET pga_aggregate_limit = 16G;  -- 12c+

2.4 命中率检查

-- Buffer Cache
SELECT 1 - (physical_reads / (db_block_gets + consistent_gets)) AS hit_ratio
FROM v$buffer_pool_statistics;

-- Library Cache
SELECT SUM(gets - getmisses) / SUM(gets) AS hit_ratio
FROM v$librarycache;

3. 进程参数

3.1 关键参数

ALTER SYSTEM SET processes = 500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions = 750 SCOPE=SPFILE;
ALTER SYSTEM SET open_cursors = 1000;

3.2 并行

ALTER SYSTEM SET parallel_max_servers = 64;
ALTER SYSTEM SET parallel_min_servers = 8;
ALTER SYSTEM SET parallel_servers_target = 24;

3.3 JOB

ALTER SYSTEM SET job_queue_processes = 20;

4. I/O 参数

4.1 DBWn

ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

4.2 LGWR

-- Redo 写入优化
ALTER SYSTEM SET log_buffer = 67108864 SCOPE=SPFILE;  -- 64M

4.3 ARCn

ALTER SYSTEM SET log_archive_max_processes = 4;

5. 优化器参数

5.1 优化器模式

ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE=BOTH;
-- ALL_ROWS / FIRST_ROWS_n / CHOOSE

5.2 统计信息

ALTER SYSTEM SET optimizer_dynamic_sampling = 2;
ALTER SYSTEM SET optimizer_use_pending_statistics = FALSE;

5.3 自适应

-- 12c+
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;
ALTER SYSTEM SET optimizer_adaptive_cursor_sharing = TRUE;

5.4 绑定变量

ALTER SYSTEM SET cursor_sharing = EXACT;  -- 默认
-- EXACT / FORCE / SIMILAR

6. Undo 参数

ALTER SYSTEM SET undo_retention = 3600;  -- 1 小时
ALTER SYSTEM SET undo_management = AUTO;

7. Redo 参数

-- 在线 Redo 日志组
-- 至少 3 组，每组多成员

-- 大小
-- 至少每 15-20 分钟切换一次

8. Block 参数

8.1 数据块

-- db_block_size 安装时确定
SHOW PARAMETER db_block_size

8.2 多块读

ALTER SYSTEM SET db_file_multiblock_read_count = 16;
-- 自动管理

9. 网络

9.1 SDU

# sqlnet.ora
DEFAULT_SDU_SIZE = 32767

9.2 入站连接

ALTER SYSTEM SET sec_max_failed_login_attempts = 10;

10. 安全

10.1 失败登录

ALTER SYSTEM SET failed_login_attempts = 10;
ALTER SYSTEM SET password_lock_time = 1;

10.2 资源

ALTER SYSTEM SET sessions_per_user = 10;  -- profile

11. 19c 推荐参数

11.1 内存

-- AMM 或 ASMM
sga_target = 50% 物理内存
pga_aggregate_target = 20% 物理内存
pga_aggregate_limit = 2 * pga_aggregate_target

11.2 优化器

optimizer_mode = ALL_ROWS
optimizer_adaptive_plans = TRUE
optimizer_adaptive_statistics = FALSE
cursor_sharing = EXACT

12. 监控

12.1 参数查看

SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
ORDER BY name;

12.2 修改历史

SELECT * FROM v$parameter_valid_values WHERE name LIKE '%...%';

12.3 隐藏参数

SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b
WHERE a.indx = b.indx;
-- 不推荐修改

13. 常见坑与排错

13.1 参数修改失败

-- 1. SCOPE
ALTER SYSTEM SET ... SCOPE=SPFILE;  -- 需重启
ALTER SYSTEM SET ... SCOPE=BOTH;
ALTER SYSTEM SET ... SCOPE=MEMORY;

-- 2. 是否可动态修改
SELECT issys_modifiable FROM v$parameter WHERE name = '...';

13.2 ORA-00821

-- SGA 不足
-- 检查物理内存
-- 调整大小

13.3 ORA-04031

-- Shared Pool 不足
ALTER SYSTEM FLUSH SHARED_POOL;
-- 增大 shared_pool_size

14. 最佳实践

  1. AMM/ASMM 自动：默认
  2. 内存 70-80% 物理：合理
  3. processes 留余量：连接
  4. open_cursors 大：游标
  5. Redo 足够大：减少切换
  6. 优化器 19c 默认：现代
  7. adaptive_statistics 关：稳定
  8. 测试验证：影响
  9. 文档化：变更记录
  10. 基线对比：性能

15. 参考资料

[1] Oracle Database Reference 19c, “Initialization Parameters” https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/