印刻数据库

外观

Oracle 参数优化 ​

Oracle 参数优化概述 ​

Oracle 数据库参数是控制数据库行为和性能的关键配置项。合理的参数配置可以优化数据库的资源使用，提高系统性能，确保系统的稳定性和可靠性。Oracle 数据库参数分为静态参数（需要重启数据库才能生效）和动态参数（可以在线修改），需要根据数据库的工作负载特点和业务需求进行调整。

参数优化的重要性 ​

  • 优化数据库资源分配，提高系统性能
  • 确保数据库的稳定性和可靠性
  • 避免系统资源瓶颈，提高系统的可扩展性
  • 满足特定业务场景的需求
  • 适应不同版本的 Oracle 数据库特性

参数优化的基本原则 ​

  • 参考 Oracle 官方文档：根据 Oracle 官方文档的建议调整参数
  • 根据工作负载调整：OLTP、OLAP 和混合工作负载需要不同的参数配置
  • 逐步调整：每次只调整少量参数，观察效果后再继续调整
  • 记录调整前后的性能：使用 AWR、ASH 等工具记录调整前后的性能变化
  • 定期回顾和调整：随着业务增长和系统变化，定期回顾和调整参数

参数分类 ​

Oracle 数据库参数可以按照不同的标准进行分类，常见的分类方式包括：

1. 按作用域分类 ​

  • 系统级参数：影响整个数据库实例的参数，如 sga_target、pga_aggregate_target 等
  • 会话级参数：只影响当前会话的参数，如 nls_date_format、optimizer_mode 等

2. 按修改方式分类 ​

  • 静态参数：需要重启数据库才能生效的参数，如 db_block_size、processes 等
  • 动态参数：可以在线修改的参数，如 sga_target、pga_aggregate_target 等

3. 按功能分类 ​

  • 内存管理参数：控制数据库内存分配的参数
  • I/O 管理参数：控制数据库 I/O 行为的参数
  • 并发管理参数：控制数据库并发处理的参数
  • 优化器参数：控制查询优化器行为的参数
  • 安全参数：控制数据库安全性的参数
  • 日志管理参数：控制数据库日志行为的参数

核心参数优化 ​

1. 内存管理参数 ​

内存管理是 Oracle 数据库性能优化的重要方面，合理的内存配置可以减少 I/O 操作，提高数据库性能。

1.1 SGA 相关参数 ​

sql
-- 自动内存管理（Oracle 11g 及以上版本）
ALTER SYSTEM SET memory_target = 48G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 48G SCOPE=SPFILE;

-- 手动内存管理
ALTER SYSTEM SET sga_target = 32G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size = 32G SCOPE=SPFILE;

-- 共享池大小
ALTER SYSTEM SET shared_pool_size = 8G SCOPE=SPFILE;

-- 缓冲区高速缓存大小
ALTER SYSTEM SET db_cache_size = 16G SCOPE=SPFILE;

-- 大池大小
ALTER SYSTEM SET large_pool_size = 1G SCOPE=SPFILE;

-- Java 池大小
ALTER SYSTEM SET java_pool_size = 512M SCOPE=SPFILE;

-- Streams 池大小
ALTER SYSTEM SET streams_pool_size = 512M SCOPE=SPFILE;

-- 重做日志缓冲区大小
ALTER SYSTEM SET log_buffer = 16M SCOPE=SPFILE;

1.2 PGA 相关参数 ​

sql
-- PGA 聚合目标大小
ALTER SYSTEM SET pga_aggregate_target = 16G SCOPE=SPFILE;

-- 自动工作区大小策略
ALTER SYSTEM SET workarea_size_policy = 'AUTO' SCOPE=SPFILE;

-- 手动工作区大小参数（仅当 workarea_size_policy = MANUAL 时生效）
ALTER SYSTEM SET sort_area_size = 65536 SCOPE=SPFILE;
ALTER SYSTEM SET hash_area_size = 131072 SCOPE=SPFILE;

2. I/O 管理参数 ​

I/O 是 Oracle 数据库的常见瓶颈，优化 I/O 相关参数可以提高数据库的 I/O 性能。

sql
-- 数据库块大小（静态参数，需要重启数据库）
ALTER SYSTEM SET db_block_size = 8192 SCOPE=SPFILE;

-- 多块读取大小
ALTER SYSTEM SET db_file_multiblock_read_count = 128 SCOPE=SPFILE;

-- 异步 I/O
ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;

-- 文件系统 I/O 选项
ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;

-- 日志文件同步方式
ALTER SYSTEM SET log_sync = 'IMMEDIATE' SCOPE=SPFILE;

-- 日志缓冲区写入触发条件
ALTER SYSTEM SET log_checkpoint_timeout = 1800 SCOPE=SPFILE;
ALTER SYSTEM SET log_checkpoint_interval = 10000 SCOPE=SPFILE;

-- 检查点完成目标时间
ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=SPFILE;

3. 并发管理参数 ​

并发管理参数控制数据库的并发处理能力，合理的配置可以提高数据库的并发性能。

sql
-- 最大进程数（静态参数，需要重启数据库）
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;

-- 最大会话数
ALTER SYSTEM SET sessions = 330 SCOPE=SPFILE;

-- 最大事务数
ALTER SYSTEM SET transactions = 363 SCOPE=SPFILE;

-- 连接超时时间
ALTER SYSTEM SET sqlnet.inbound_connect_timeout = 60 SCOPE=SPFILE;

-- 监听器超时时间
ALTER SYSTEM SET listener_timeout = 10 SCOPE=SPFILE;

-- 会话空闲超时时间
ALTER PROFILE default LIMIT idle_time 60;

4. 优化器参数 ​

优化器参数控制查询优化器的行为，合理的配置可以提高查询性能。

sql
-- 优化器模式
ALTER SYSTEM SET optimizer_mode = 'ALL_ROWS' SCOPE=SPFILE;

-- 优化器统计信息自动收集
ALTER SYSTEM SET optimizer_stats_auto_collection = TRUE SCOPE=SPFILE;

-- 自动索引（Oracle 19c 及以上版本）
ALTER SYSTEM SET optimizer_auto_index_mode = 'AUTO' SCOPE=SPFILE;

-- SQL 计划管理
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE SCOPE=SPFILE;

-- 自适应执行计划（Oracle 12c 及以上版本）
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET optimizer_adaptive_reporting_only = FALSE SCOPE=SPFILE;

-- 并行执行相关参数
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=SPFILE;
ALTER SYSTEM SET parallel_degree_limit = 16 SCOPE=SPFILE;
ALTER SYSTEM SET parallel_max_servers = 128 SCOPE=SPFILE;

5. 日志管理参数 ​

日志管理参数控制数据库日志的生成、写入和归档行为，合理的配置可以提高数据库的性能和可靠性。

sql
-- 归档模式
ALTER DATABASE ARCHIVELOG;

-- 归档日志目标
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/archivelog' SCOPE=SPFILE;

-- 归档日志格式
ALTER SYSTEM SET log_archive_format = 'arch_%t_%s_%r.log' SCOPE=SPFILE;

-- 重做日志文件大小（建议 1-2GB）
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oradata/redo04a.log', '/oradata/redo04b.log') SIZE 2G;

-- 最小重做日志组数（建议至少 3 组）
-- 可以通过添加日志组来增加组数

-- 闪回日志保留时间（以分钟为单位）
ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE=SPFILE;

6. 安全相关参数 ​

安全相关参数控制数据库的安全性，合理的配置可以提高数据库的安全性。

sql
-- 密码有效期
ALTER PROFILE default LIMIT password_life_time 90;

-- 密码复杂度验证
ALTER SYSTEM SET password_verify_function = 'ORA12C_STRONG_VERIFY_FUNCTION' SCOPE=SPFILE;

-- 审计设置
ALTER SYSTEM SET audit_trail = 'DB' SCOPE=SPFILE;

-- 远程登录密码文件
ALTER SYSTEM SET remote_login_passwordfile = 'EXCLUSIVE' SCOPE=SPFILE;

-- 禁用不必要的Oracle组件
ALTER SYSTEM SET recyclebin = 'OFF' SCOPE=SPFILE;
ALTER SYSTEM SET o7_dictionary_accessibility = FALSE SCOPE=SPFILE;

Oracle 19c 和 21c 新参数 ​

Oracle 19c 新参数 ​

1. 自动索引相关参数 ​

sql
-- 自动索引模式
ALTER SYSTEM SET optimizer_auto_index_mode = 'AUTO' SCOPE=SPFILE;

-- 自动索引创建
ALTER SYSTEM SET optimizer_auto_index_create = TRUE SCOPE=SPFILE;

-- 自动索引维护
ALTER SYSTEM SET optimizer_auto_index_maintain = TRUE SCOPE=SPFILE;

-- 自动索引报告
ALTER SYSTEM SET optimizer_auto_index_report = 'AUTO' SCOPE=SPFILE;

2. 实时统计信息相关参数 ​

sql
-- 表实时统计信息
ALTER SYSTEM SET statistics_level = 'TYPICAL' SCOPE=SPFILE;
ALTER SYSTEM SET optimizer_use_invisible_indexes = TRUE SCOPE=SPFILE;

3. SQL 计划管理增强 ​

sql
-- 自动计划演进
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE SCOPE=SPFILE;

Oracle 21c 新参数 ​

1. 智能内存管理 ​

sql
-- 自动内存管理增强
ALTER SYSTEM SET memory_target = 64G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 64G SCOPE=SPFILE;

-- 内存自适应调整
ALTER SYSTEM SET memory_advisory = TRUE SCOPE=SPFILE;

2. 并行执行增强 ​

sql
-- 并行度自适应调整
ALTER SYSTEM SET parallel_degree_adaptive = TRUE SCOPE=SPFILE;

-- 并行执行优先级
ALTER SYSTEM SET parallel_execution_priority = 'MEDIUM' SCOPE=SPFILE;

3. 云原生支持 ​

sql
-- Kubernetes 集成
ALTER SYSTEM SET container_database = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET pluggable_database = TRUE SCOPE=SPFILE;

4. 向量处理支持 ​

sql
-- In-Memory 向量处理
ALTER SYSTEM SET inmemory_vector_vectors = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET inmemory_vector_size = 64 SCOPE=SPFILE;

参数优化最佳实践 ​

1. 内存参数优化 ​

  • 使用自动内存管理：对于大多数数据库环境，推荐使用自动内存管理（memory_target），简化内存配置
  • 根据工作负载调整 SGA 和 PGA 比例：
    • OLTP 系统：SGA 占总内存的 70-80%，PGA 占 20-30%
    • OLAP 系统：SGA 占总内存的 50-60%，PGA 占 40-50%
    • 混合工作负载：根据实际情况平衡 SGA 和 PGA 比例
  • 确保有足够的共享池：共享池大小应能容纳频繁执行的 SQL 和 PL/SQL 代码
  • 合理设置缓冲区高速缓存：缓冲区高速缓存大小应能容纳常用的数据块，提高缓存命中率

2. I/O 参数优化 ​

  • 设置合适的数据库块大小：
    • OLTP 系统：8KB 或 16KB
    • OLAP 系统：16KB 或 32KB
  • 启用异步 I/O：disk_asynch_io = TRUE
  • 使用直接 I/O：filesystemio_options = 'SETALL'
  • 设置合适的多块读取大小：db_file_multiblock_read_count 应根据操作系统块大小和存储系统进行调整
  • 优化重做日志配置：
    • 重做日志文件大小建议为 1-2GB
    • 至少创建 3 个重做日志组
    • 每个重做日志组至少有 2 个成员，存储在不同的物理磁盘上

3. 并发参数优化 ​

  • 根据实际需求设置最大进程数：processes 参数应根据最大并发连接数进行调整
  • 设置合适的会话超时时间：避免空闲会话占用系统资源
  • 优化连接管理：使用连接池减少连接建立的开销

4. 优化器参数优化 ​

  • 选择合适的优化器模式：
    • OLTP 系统：ALL_ROWS 或 FIRST_ROWS
    • OLAP 系统：ALL_ROWS
  • 启用自动统计信息收集：确保优化器有准确的统计信息
  • 使用 SQL 计划管理：稳定 SQL 执行计划，避免计划波动
  • 考虑使用自动索引：在 Oracle 19c 及以上版本中，自动索引可以自动优化索引使用

5. 日志管理参数优化 ​

  • 启用归档模式：确保数据库可以进行时间点恢复
  • 设置合适的归档日志保留策略：根据业务需求设置归档日志的保留时间
  • 优化归档日志目标：将归档日志存储在与数据文件不同的物理磁盘上

参数监控与调优 ​

1. 参数查看方法 ​

sql
-- 查看所有参数
SHOW PARAMETERS;

-- 查看特定参数
SHOW PARAMETER sga_target;

-- 查看参数的详细信息
SELECT name, value, description FROM v$parameter WHERE name = 'sga_target';

-- 查看参数的修改历史
SELECT * FROM v$parameter_change WHERE name = 'sga_target';

2. 参数调优工具 ​

  • AWR 报告：包含参数统计信息和建议
  • ASH 报告：实时分析数据库性能和参数影响
  • SQL Tuning Advisor：提供 SQL 语句优化建议
  • Automatic Database Diagnostic Monitor (ADDM)：自动诊断数据库性能问题并提供建议
  • Oracle Enterprise Manager (OEM)：图形化界面监控和调整参数

3. 常见参数问题与解决方案 ​

1. 内存不足 ​

问题：数据库出现 ORA-04030（PGA 内存不足）或 ORA-04031（SGA 内存不足）错误

解决方案：

  • 增加 pga_aggregate_target 或 sga_target
  • 优化 SQL 语句，减少内存使用
  • 调整 workarea_size_policy 和相关参数

2. I/O 性能差 ​

问题：数据库出现大量 I/O 等待事件，如 db file sequential read 或 db file scattered read

解决方案：

  • 增加 db_cache_size，提高缓存命中率
  • 优化 SQL 语句，减少 I/O 操作
  • 调整 db_file_multiblock_read_count 参数
  • 优化存储系统

3. 并发性能差 ​

问题：数据库出现大量并发等待事件，如 enq: TX - row lock contention 或 latch free

解决方案：

  • 增加 processes 和 sessions 参数
  • 优化应用程序，减少锁持有时间
  • 调整 optimizer_mode 等优化器参数
  • 考虑使用并行执行

4. 优化器选择错误的执行计划 ​

问题：SQL 语句执行计划不佳，导致性能下降

解决方案：

  • 收集最新的统计信息
  • 使用 SQL 计划管理固定执行计划
  • 调整 optimizer_mode 等优化器参数
  • 考虑使用索引提示或优化器提示

常见问题（FAQ） ​

Q1: 如何确定哪些参数需要优化？ ​

A1: 可以通过以下方式确定需要优化的参数：

  • 分析 AWR 和 ASH 报告，识别性能瓶颈
  • 使用 ADDM 自动诊断数据库性能问题
  • 参考 Oracle 官方文档的参数建议
  • 监控系统资源使用情况，如 CPU、内存、I/O 等

Q2: 静态参数和动态参数有什么区别？ ​

A2: 静态参数需要重启数据库才能生效，如 db_block_size、processes 等；动态参数可以在线修改，立即生效或在会话级别生效，如 sga_target、pga_aggregate_target 等。

Q3: 如何回滚参数调整？ ​

A3: 回滚参数调整的方法包括：

  • 对于动态参数，可以直接修改回原来的值
  • 对于静态参数，需要修改参数文件后重启数据库
  • 使用 ALTER SYSTEM RESET parameter_name 命令重置参数为默认值

Q4: Oracle 19c 和 21c 的参数有什么区别？ ​

A4: Oracle 21c 在参数方面主要增强了：

  • 智能内存管理
  • 并行执行自适应调整
  • 云原生支持
  • 向量处理支持
  • 更完善的自动索引功能

Q5: 如何监控参数调整的效果？ ​

A5: 可以通过以下方式监控参数调整的效果：

  • 比较调整前后的 AWR 报告
  • 监控系统资源使用情况，如 CPU、内存、I/O 等
  • 观察 SQL 语句的执行计划和执行时间变化
  • 使用 ASH 报告实时分析数据库性能

Q6: 如何备份和恢复参数配置？ ​

A6: 备份和恢复参数配置的方法包括：

  • 备份 spfile 或 pfile 文件
  • 使用 CREATE PFILE FROM SPFILE 命令备份参数文件
  • 使用 CREATE SPFILE FROM PFILE 命令恢复参数文件
  • 使用 Oracle 数据泵或 RMAN 备份整个数据库，包括参数配置

总结 ​

Oracle 参数优化是数据库性能优化的重要组成部分，合理的参数配置可以显著提高数据库的性能和稳定性。参数优化涉及内存管理、I/O 管理、并发管理、优化器等多个方面，需要根据数据库的工作负载特点和业务需求进行综合考虑。

在实际生产环境中，DBA 需要定期监控数据库性能，识别性能瓶颈，并根据监控结果调整参数。同时，需要关注 Oracle 数据库的新版本特性，利用新特性进一步优化参数配置。

通过合理的参数优化，可以提高 Oracle 数据库的性能、稳定性和可靠性，为业务提供更好的支持。