centos上oracle性能如何调优

CentOS 上 Oracle 性能调优实战指南

一 基线评估与瓶颈定位

• 建立可复现的基线：在业务低峰创建快照，收集 AWR/ADDM，对比优化前后关键指标（如 DB Time、AAS、Top 5 Timed Events、SQL ordered by Reads/Writes）。示例生成 AWR 报告：
  SQL> exec dbms_workload_repository.create_snapshot();
  SQL> @?/rdbms/admin/awrrpt.sql
  重点关注 IO 相关事件：db file sequential/scattered read、direct path read/write、log file sync、log file parallel write，以及 CPU time。这些事件直接指向索引/SQL 策略、IO 子系统与日志写入路径问题。
• 操作系统层确认 IO 健康度：用 iostat -x 1 10 或 sar -d 观察 await、r_await、w_await、svctm、util。一般单次 IO 响应时间低于 10 ms 为佳，>20 ms 需优先排查（存储缓存、链路、磁盘故障、热点等）。
• 会话与 SQL 层定位：
  • 查高 IO 与长时运行 SQL：V$SQLAREA 按 DISK_READS/ELAPSED_TIME 排序；V$SESSION_LONGOPS 查长操作。
  • 查系统级等待：V$SYSTEM_EVENT 取 Top 5；V$SESSION_WAIT 捕获实时阻塞与等待。
    以上步骤能快速识别是 SQL/索引、内存命中、IO 子系统 还是 并发/锁 成为主要瓶颈。

二 操作系统与存储层优化

• 使用 Oracle 推荐的 tuned-profiles-oracle：
  • 启用：tuned-adm profile oracle；验证：tuned-adm active。该配置会对 CPU 调度、内存分配、I/O 调度 等进行数据库友好优化，适合作为首选基线。
• 内核与资源参数（/etc/sysctl.conf，按内存与负载计算后应用）：
  • 共享内存与信号：
    • kernel.shmmax ≈ 物理内存的 85%（字节）
    • kernel.shmall = ceil(物理内存×0.85 / 4096)
    • kernel.sem = 250 32000 100 128
  • 文件句柄与 AIO：fs.file-max = 6815744；fs.aio-max-nr = 1048576
  • 网络：net.core.rmem_default/wmem_default = 262144；net.core.rmem_max/wmem_max = 4194304/1048576；net.ipv4.ip_local_port_range = 9000 65500
  • 内存回收：vm.swappiness = 10；脏页与回写：vm.dirty_ratio = 20；vm.dirty_background_ratio = 3；vm.dirty_writeback_centisecs = 100；vm.dirty_expire_centisecs = 500；vm.min_free_kbytes = 524288
    应用：sysctl -p。以上为常见安全起点，需结合压测微调。
• 透明大页与 NUMA：
  • 启用 HugePages 减少页表开销、提升大内存访问效率；计算目标值后写入 vm.nr_hugepages，并预留足够连续内存。
  • 在 NUMA 架构上，结合业务测试决定是否禁用 numa_balancing（部分负载下可提升稳定性/性能）。
• 存储与文件系统：
  • 优先 SSD/NVMe；使用 条带化 RAID、多控制器与多路径；考虑 ASM 做 IO 负载均衡与条带化。
  • 选择支持 O_DIRECT/AIO 的文件系统（如 XFS/ext4），并合理挂载（如 noatime、barrier 依据存储可靠性权衡）。
  • 若采用文件系统缓存，确保存储层 写缓存 策略与业务一致性要求匹配。
    上述要点能显著降低 IO 等待、提升并发与稳定性。

三 数据库内存与关键参数

• 内存总体策略：
  • 建议留出 20% 内存给 OS，其余用于数据库；在 OLTP 场景常见分配为 SGA:PGA ≈ 80%:20%，在 DSS 场景可提高 PGA 占比（如至 ~50%）。
  • 优先启用自动内存：MEMORY_TARGET（或 SGA_TARGET + PGA_AGGREGATE_TARGET），避免手工细粒度过度配置。
• SGA 组件要点：
  • Database Buffer Cache：命中率目标 ≥90%；过低则增大 DB_CACHE_SIZE 或整体 SGA。
  • Shared Pool：关注 Library Cache 与 Row Cache 命中率（目标 ≥90%）；硬解析高时考虑增加 SHARED_POOL_SIZE、使用 绑定变量、定期清理无效对象。
  • Log Buffer：常规 3–5 MB 起，若 redo log space requests 频繁或 log file sync 偏高，再适度增大。
  • Large Pool：共享服务器、并行查询、RMAN 备份等场景适当增大（常见 20–30 MB 起步）。
• PGA 与 Workarea：
  • 启用自动 PGA：workarea_size_policy = AUTO；设置 PGA_AGGREGATE_TARGET。
  • 用 V$PGASTAT/V$PGA_TARGET_ADVICE 评估命中率与 over-allocation，逐步调整到 ESTD_OVERALLOC_COUNT ≈ 0 且 PGA cache hit % 高。
• 典型设置示例（需按环境校准）：
  • ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
  • ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=BOTH;
  • ALTER SYSTEM SET log_buffer = 64M SCOPE=BOTH;
    以上方法能在不牺牲稳定性的前提下，最大化内存命中率并降低磁盘 IO。

四 SQL 与索引及对象级优化

• SQL 优化优先级最高：
  • 消除 全表扫描（合理索引、分区裁剪、谓词推进）；减少 **SELECT ***；使用 绑定变量 降低硬解析；用 EXPLAIN PLAN 与 SQL Monitor 验证计划。
  • 针对 Top SQL 进行重写、改写连接方式（如 Hash/Nested Loop 选择）、减少 SORT/Aggregation/Hash Join 的 IO 与内存占用。
• 索引策略：
  • 为高频过滤/连接/排序列建立 B-Tree/位图/函数索引；避免 过度索引（增删改代价上升）。
  • 对大表按业务键 分区（范围/列表/哈希），配合 分区索引 降低扫描与维护成本。
• 维护与统计：
  • 定期收集统计信息：EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>‘SCHEMA’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
  • 对高碎片对象按需 重组/重建（如分区拆分、压缩、LOB 重组），避免盲目全库重建。
• 并发与事务：
  • 合理设置 PROCESSES/SESSIONS，避免连接风暴；控制 事务大小与持有时间，降低 锁争用 与 日志同步 压力。
    这些手段往往能以最小代价获得最大吞吐提升。

五 监控验证与回退

• 持续监控与复盘：
  • 定期生成 AWR/ADDM，对比 DB Time、AAS、Top Events、SQL 读写 的变化；结合 ASH 做细粒度时段分析。
  • 操作系统侧持续观察 iostat/sar 的 await、util 与吞吐，确认 IO 优化成效。
• 变更管控：
  • 任何参数调整遵循“评估—备份—灰度—压测—回退”流程；参数修改前备份 spfile/pfile 与 sysctl.conf。
  • 对 HugePages、内核参数、SGA/PGA 等变更，务必在测试环境充分验证，避免生产震荡。
• 验收标准示例：
  • 关键 SQL 执行时间/逻辑 IO 下降；Buffer Cache/Library Cache 命中率达标；log file sync 与 db file sequential/scattered read 等待显著下降；存储 await < 10–20 ms 且 util 不过载。
    通过闭环的监控与验证，确保每次优化都可量化、可回退、可持续。