Linux Oracle数据库性能调优技巧

Linux 上 Oracle 数据库性能调优要点

操作系统层优化

• I/O 调度器：数据库盘优先使用 deadline（低延迟）或 noop（SSD/NVMe、虚拟化环境）；可用命令查看与调整：
  find /sys/block/*/queue -name scheduler -exec sh -c ‘echo -n "$0 : "; cat $0’ {} ;
  虚拟化场景通常设为 noop，避免与宿主机调度重复。
• 内存与脏页：降低交换倾向、优化写回以稳定 I/O：
  vm.swappiness=0；vm.dirty_background_ratio=3；vm.dirty_ratio=80；vm.dirty_expire_centisecs=500；vm.dirty_writeback_centisecs=100。
• 共享内存与信号量：为 SGA 预留充足共享内存段：
  kernel.shmmax≈物理内存的1/2（如 256GB 内存可设约 128GB）；kernel.shmall=物理内存/页面大小（4KB）；kernel.shmmni=4096；kernel.sem=250 32000 100 128。
• 文件句柄与 AIO：提升并发与异步 I/O 能力：fs.file-max=6815744；fs.aio-max-nr=1048576。
• 网络栈：扩大本地端口与缓冲区，提升高并发连接与吞吐：
  net.ipv4.ip_local_port_range=9000 65500；net.core.rmem_default=262144；net.core.rmem_max=4194304；net.core.wmem_default=262144；net.core.wmem_max=1048576。
• 时间同步：启用 NTP 并以 slewing 模式运行（OPTIONS=“-x -u ntp:ntp …”），保障日志、AWR、RAC 一致性。
• 大页内存 HugePages：为 SGA 配置 HugePages 可减少页表开销、提升 TLB 命中率；数量可用 Oracle 提供的 hugepages_settings.sh 脚本评估后写入 /etc/sysctl.conf 并重启生效。

Oracle 内存与实例参数

• 自动内存管理（AMM）：启用 MEMORY_TARGET/MEMORY_MAX_TARGET，让 Oracle 自动在 SGA 与 PGA 间平衡；或手动设置：
  ALTER SYSTEM SET SGA_TARGET= SCOPE=BOTH;
  ALTER SYSTEM SET PGA_AGGREGATE_TARGET= SCOPE=BOTH;
  在 11g 中常见为 SGA_TARGET + PGA_AGGREGATE_TARGET；在 12c+ 更推荐统一到 MEMORY_TARGET。
• 日志缓冲区：联机重做日志频繁写入场景可适当增大：ALTER SYSTEM SET LOG_BUFFER=64M;（需重启或在维护窗口执行）。
• 并发与会话：按业务峰值规划进程与会话上限，避免连接风暴：
  ALTER SYSTEM SET PROCESSES= SCOPE=SPFILE;
  ALTER SYSTEM SET SESSIONS= SCOPE=SPFILE;（通常 SESSIONS > PROCESSES）。
• 并行执行：对大表扫描/聚合/ETL 合理启用并行：
  ALTER TABLE t PARALLEL 8; 或查询提示：SELECT /*+ PARALLEL(t,8) */ …；会话级策略可设为 AUTO。

SQL 与索引优化

• 执行计划与诊断：用 EXPLAIN PLAN 与 DBMS_XPLAN.DISPLAY 查看访问路径；结合 AWR/ASH/ADDM 定位 Top SQL、等待事件与瓶颈。
  示例：
  EXPLAIN PLAN FOR SELECT … ;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
• SQL 编写要点：
  • 仅查需要的列，避免 **SELECT ***；
  • 使用 绑定变量 减少硬解析；
  • 优先 JOIN 替代复杂子查询；
  • 避免在 WHERE 列上使用函数或表达式，减少索引失效；
  • 大批量 DML 采用批量提交/数组处理，降低日志与解析开销。
• 索引策略：为高频过滤/连接/排序列建立合适索引；删除冗余/低效索引；必要时使用覆盖索引减少回表；对大表结合分区降低扫描范围。

存储与 I/O 子系统

• 存储介质：优先 SSD/NVMe 或高性能阵列（RAID10/RAID5 视写负载而定），缩短 I/O 等待。
• 文件系统与挂载：选择 XFS/ext4 等成熟文件系统，按负载设置挂载选项（如 noatime、barrier 等），并保持 条带化/对齐 与合适的 块大小。
• I/O 调度：数据库盘用 deadline/noop；重 I/O 负载下避免 CFQ。
• AIO 能力：确保 fs.aio-max-nr 足够，避免 AIO 瓶颈。

监控维护与压测验证

• 基线报告：定期生成 AWR/ADDM，对比 DB Time、Top SQL、等待事件（如 db file sequential/scattered read、log file sync）并针对性优化。
• 统计信息：保持对象统计信息新鲜，使优化器生成更优计划：
  EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>‘SCHEMA’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>‘FOR ALL COLUMNS SIZE AUTO’, cascade=>TRUE);
• 索引维护：定期重建/重组高碎片索引，清理无用索引，控制维护窗口。
• 变更流程：任何参数/结构/SQL 调整先在测试环境验证，采用灰度/回滚策略，变更后复核 AWR 与业务指标。