centos oracle性能调优的秘诀

CentOS 上 Oracle 性能调优秘诀

一 系统层优化

• 内存与大页
  • 合理预留内存，避免系统过度换页；适度降低 vm.swappiness（如 10），减少不必要的交换。
  • 启用 HugePages 降低共享内存页表开销，提升大 SGA 场景下的稳定性与性能（需重启，且确保 HugePages 总量大于 SGA）。
• 文件系统与 I/O 调度
  • 数据文件、重做日志、归档目录优先使用 XFS/EXT4，并采用 O_DIRECT/NOATIME 等挂载选项以减少双重缓存与元数据开销。
  • 存储优先 SSD/NVMe；I/O 调度器建议：SSD 用 none/mq-deadline，机械盘用 mq-deadline。
• 内核与网络
  • 提升文件句柄与 AIO 能力：fs.file-max、fs.aio-max-nr。
  • 放宽本地端口范围：net.ipv4.ip_local_port_range=9000 65500，缓解高并发短连接端口耗尽。
  • 适度增大套接字缓冲：net.core.rmem_default/wmem_default、rmem_max/wmem_max，优化高吞吐网络场景。
• 资源与后台进程
  • 关闭不必要的系统服务与守护进程，减少资源竞争；为 Oracle 绑定 HugePages、CPU 亲和性、NUMA 绑定（如适用）。

二 数据库内存与关键参数

• 内存目标与分配
  • OLTP 优先启用 自动内存管理（AMM）：设置 memory_target/memory_max_target；或采用 ASMM（设置 sga_target、pga_aggregate_target）。
  • 经验分配（需结合实际负载与压测微调）：SGA 约占物理内存的 60%–75%，PGA 约占 15%–25%；避免内存抖动与操作系统换页。
• 关键 SGA/PGA 组件
  • 合理设置 shared_pool_size、db_cache_size、large_pool_size、java_pool_size；并行、RMAN、共享服务器等场景务必给足 Large Pool。
  • 重做相关：适度增大 log_buffer；关注 Redo Log Buffer Retry Ratio < 1% 与 redo log space requests，必要时增大在线重做日志文件大小以减少检查点频率。
• 连接与会话
  • 依据并发规划设置 processes、sessions、open_cursors；会话数可按公式：sessions ≈ 1.1 × processes + 5 配置，避免连接风暴与游标耗尽。
• 示例（需按环境调整）
  • ALTER SYSTEM SET sga_target=4G SCOPE=BOTH;
  • ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=BOTH;
  • ALTER SYSTEM SET processes=1200 SCOPE=spfile;
  • ALTER SYSTEM SET sessions=1325 SCOPE=spfile;
  • ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;

三 SQL 与索引及对象设计

• SQL 写法
  • 避免 **SELECT ***，只查需要的列；使用 绑定变量 减少硬解析；用 EXPLAIN PLAN 与执行计划分析定位全表扫描、低效连接与隐式转换。
• 索引策略
  • 为高频过滤、连接、排序与分组列建立合适索引；删除 未使用/重复 索引，控制维护成本；必要时 重建/重组 碎片化严重的索引。
• 分区与并行
  • 大表按时间、地域等维度使用 分区表/分区索引，提升扫描与维护效率；在合适场景启用 并行查询 提升吞吐。
• 统计信息与计划稳定性
  • 定期收集统计信息：EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCHEMA_NAME’);；结合 SQL Plan Baselines 稳定关键 SQL 执行计划。

四 存储与重做日志优化

• 在线重做日志
  • 增大 在线重做日志文件 与日志组数，使检查点间隔建议 20–30 分钟 一次，降低日志切换与检查点抖动带来的等待。
• I/O 子系统
  • 数据文件、重做日志、归档目录尽量 分离磁盘/阵列，避免单点 I/O 瓶颈；优先 SSD/NVMe 或高性能阵列（RAID10/条带化）。
• 表空间与对象布局
  • 热点对象与索引分散到不同 表空间/磁盘，减少争用；合理设置 自动段空间管理（ASSM） 与 UNDO 表空间 大小，避免快照过旧与回滚段争用。

五 监控诊断与持续优化

• 基线与分析
  • 定期生成 AWR/ADDM 报告定位 Top SQL、等待事件与配置短板；结合 ASH 做短时段热点分析。
  • 示例：SELECT * FROM table(dbms_workload_repository.awr_report_html(dbid, instance_number, 1, 2));
• 关键指标与阈值
  • Buffer Cache Hit Ratio > 90%；Redo Log Buffer Retry Ratio < 1%；关注 Free Buffer Waits、Buffer Busy Waits、log file switch（checkpoint incomplete） 等事件。
• 变更与回退
  • 任何参数调整务必先 备份 spfile/pfile，在 测试环境验证，并准备 回退方案；变更后持续观察 AWR/ASH 与业务指标，形成闭环优化。