CentOS 上 Oracle 运行慢的排查与优化步骤
一、快速定位瓶颈
- 在 Oracle Enterprise Manager 的 Performance 页面观察 Average Active Sessions，将视图设为 Real Time: 15 second Refresh，持续 5–10 分钟定位高峰期的等待事件与 Top SQL。
- 查看 ADDM（默认每 60 分钟自动运行并与 AWR 快照同步），点击影响最大的结果，优先处理被标注为“高负载 SQL”的对象。
- 对问题 SQL 使用 SQL Tuning Advisor（可创建 SQL Profile 改善执行计划），对缺失或低效的索引/物化视图使用 SQL Access Advisor 给出创建/删除建议。
- 若出现会话阻塞，进入 Blocking Sessions 定位并终止阻塞源头，随后复核业务事务设计以减少锁等待。
二、操作系统层优化
- 启用 tuned-profiles-oracle：这是面向 Oracle 的推荐系统调优集，能自动优化 CPU 调度、内存分配、I/O 调度 等；安装后启用并观察效果。
- 调整内核与资源参数（示例，需结合内存与负载计算）：
- 共享内存与信号量：
- kernel.shmmax ≈ 物理内存的 85%（单位 Byte）
- kernel.shmall = shmmax / 4096
- kernel.sem = 250 32000 100 128
- 文件句柄与 AIO：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；vm.nr_hugepages 可按“内存×0.8×0.8/2（MB→页）”估算后配置
- 存储与文件系统：优先 SSD/NVMe；选择 XFS/ext4 并合理挂载（如 noatime、barrier 等按存储特性设置）；确保 异步 I/O 可用并启用。
- 资源限制：在 /etc/security/limits.conf 为 oracle 用户设置 memlock（锁定内存，单位 KB，建议接近 SGA 大小）、nofile、nproc 等。
三、数据库层优化
- 内存与目标：
- 启用 AMM（自动内存管理）：设置 memory_target（如物理内存的 60–75%），SGA_TARGET 与 PGA_AGGREGATE_TARGET 设为 0；或手动设置 SGA/PGA：
- ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
- ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=BOTH;
- 日志缓冲区：log_buffer = 64M（视写入强度调整）。
- 并发与会话：合理设置最大连接与会话，避免连接风暴：
- ALTER SYSTEM SET processes = 200 SCOPE=SPFILE;
- ALTER SYSTEM SET sessions = 200 SCOPE=SPFILE;
- 统计信息与计划稳定性：定期收集统计信息，保证优化器选择更优执行计划：
- EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>‘SCHEMA_NAME’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
- 结构与访问路径：对大表使用 分区；删除 未使用/重复索引；避免 **SELECT *** 与隐式类型转换；为高频过滤/关联列建立合适索引；必要时用 SQL Access Advisor 评估索引/物化视图。
四、SQL 与索引专项优化
- 减少 全表扫描：通过合适的索引、分区裁剪、谓词下推与统计信息准确化降低扫描成本。
- 优化 JOIN 策略：结合数据量与驱动表选择 Hash Join/Nested Loops；为大表关联列建立索引。
- 使用 绑定变量 降低硬解析；避免频繁 DDL 导致共享池抖动。
- 对 Top SQL：
- 用 EXPLAIN PLAN 与执行计划对比确认是否走索引/分区；
- 通过 SQL Tuning Advisor 获取建议（如 SQL Profile、重构建议）；
- 对报表/聚合类查询，结合 SQL Access Advisor 评估 索引/物化视图 的收益与成本。
五、维护与监控
- 基线化与持续监控：保留 AWR 快照（默认 60 分钟采集），定期查看 ADDM 报告，围绕“数据库时间占比最高的等待/SQL”迭代优化。
- 例行维护：
- 定期收集统计信息（全库/关键 Schema）；
- 对高碎片表/索引评估重组或重建；
- 监控 I/O 响应时间、日志切换频率、会话/进程数、锁等待 等关键指标。
- 变更验证：任何参数或结构变更前在测试环境验证，变更后复核 AWR/ASH 与业务指标，确保收益与稳定性。