centos oracle性能调优指南

CentOS 上 Oracle 性能调优指南

一 基线评估与总体原则

• 明确业务类型与瓶颈：区分 OLTP（高并发小事务）与 DSS/OLAP（大数据量扫描、排序/哈希），据此分配 SGA/PGA 与并发策略。
• 建立监控基线：采集 AWR/ASH 报告，关注 Top SQL、等待事件（如 db file sequential read、enq: TX - row lock contention）、CPU/IO/内存利用率。
• 内存总体原则：至少预留 1–2GB 给操作系统；Oracle 总内存占用不宜超过物理内存的 80%；SGA 与 PGA 按负载分配（见下表）。

二 操作系统级优化

• 内核参数（/etc/sysctl.conf，执行 sysctl -p 生效）：
  • 共享内存与信号量：
    • kernel.shmmax ≈ 物理内存的 80%–85%（字节）
    • kernel.shmall = shmmax / 4096
    • kernel.shmmni = 4096
    • kernel.sem = 250 32000 100 128
  • 文件句柄与异步 IO：
    • 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 = 1048586
  • 虚拟内存：
    • vm.swappiness = 10（减少换页，生产可更低，如 1–5，需充分测试）
• 资源限制（/etc/security/limits.conf）：
  • oracle soft nproc 2047；oracle hard nproc 16384
  • oracle soft nofile 1024；oracle hard nofile 65536
• 大页 HugePages（建议启用，减少页表开销）：
  • 估算公式（2MB 大页）：HugePages ≈ SGA_SIZE(MB) × 0.8 × 0.8 / 2
  • 例：16GB 内存、SGA≈12GB 时，HugePages ≈ (12288×0.64)/2 ≈ 3932；在 /etc/sysctl.conf 设置 vm.nr_hugepages=3932 并重启前确保内存锁定（limits.conf 中 oracle hard memlock 不小于 SGA）。
• 文件系统与挂载：优先 XFS/ext4；挂载选项建议 noatime,nodiratime；如为 OLTP 且一致性允许，可考虑 data=writeback（需评估风险）。
• 安全组件（仅测试环境建议关闭，生产请按合规配置放行端口与策略）：
  • 可临时执行：systemctl stop firewalld && systemctl disable firewalld；setenforce 0 并修改 /etc/selinux/config 中 SELINUX=permissive/disabled。

三 数据库内存与关键参数

• 自动内存管理（AMM，简化运维）：
  • 设置 MEMORY_TARGET（如物理内存的 70%–80%）与 MEMORY_MAX_TARGET；启用后由 Oracle 自动在 SGA/PGA 间分配。
  • 示例：ALTER SYSTEM SET memory_target=12G SCOPE=SPFILE; ALTER SYSTEM SET memory_max_target=13G SCOPE=SPFILE;
• 自动共享内存管理（ASMM）：
  • 设置 SGA_TARGET（如 8G–12G）与 SGA_MAX_SIZE（≥ SGA_TARGET）；Oracle 自动调节 DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE 等组件。
  • 示例：ALTER SYSTEM SET sga_target=8G SCOPE=SPFILE; ALTER SYSTEM SET sga_max_size=10G SCOPE=SPFILE;
• 手动组件（按需）：
  • DB_CACHE_SIZE（建议占 SGA 的 40%–60%）、SHARED_POOL_SIZE（15%–25%）、LARGE_POOL_SIZE（100M–500M）、LOG_BUFFER（高并发事务可至 64M）。
• PGA 自动管理（APMM）：
  • PGA_AGGREGATE_TARGET：OLTP 取 10%–20%，DSS 取 20%–50%；默认 WORKAREA_SIZE_POLICY=AUTO。
  • 示例：ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=SPFILE;（DSS 可设 4G–8G）
• 连接与会话：
  • 建议 processes=200–500、sessions≈processes×1.1–1.2（如 processes=300，sessions=330）。
  • 应用侧使用连接池（如 UCP、HikariCP）复用连接，避免频繁创建/销毁。
• I/O 相关：
  • disk_asynch_io = TRUE（启用异步 IO）
  • db_file_multiblock_read_count = DB_BLOCK_SIZE × 8（如 8KB×8=64）
  • 归档模式可适度提高 log_archive_max_processes（如 4–8）。

四 存储与 I/O 子系统

• 硬件与阵列：优先 SSD/NVMe；阵列推荐 RAID 10（性能与冗余均衡），避免 RAID 5（写惩罚高）。
• 文件分离：将数据文件、重做日志、归档日志、临时表空间置于不同物理磁盘/阵列，降低争用。
• 条带化：使用 LVM 条带或硬件 RAID 条带（如 -i 4 -I 64），提升并发 IO 能力。
• 表空间与空间管理：创建时预估容量，尽量 避免频繁自动扩展；为大表/历史数据采用 分区（范围/列表/哈希），配合 本地分区索引 降低维护成本。

五 SQL、索引与并发及监控维护

• SQL 优化：
  • 避免 **SELECT ***；使用 绑定变量 减少硬解析；避免在索引列上使用函数（如 UPPER(name)=‘X’ 导致索引失效）；优先 INNER JOIN；用 EXPLAIN PLAN 与 DBMS_XPLAN 分析执行计划。
• 索引策略：
  • 为 WHERE/JOIN/ORDER BY 高频列建索引；定期 重建碎片化索引（如 REBUILD ONLINE）；清理 未使用/重复 索引（结合 DBA_INDEXES 等视图）。
• 分区与并行：
  • 大表按 时间/范围 分区；复杂查询结合 并行查询 利用多核（注意资源争用与 License 影响）。
• 统计信息与计划稳定性：
  • 定期收集统计信息：EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCHEMA_NAME’);；必要时使用 SQL Plan Baselines 稳定执行计划。
• 监控与诊断：
  • 定期生成 AWR/ADDM 报告定位瓶颈；用 ASH 实时查看活跃会话；结合 OEM 或自建看板持续观测。
• 变更流程与风险控制：
  • 任何参数调整前先 备份；在 测试环境验证；变更后持续 监控回放 关键指标，必要时 回滚。