Ubuntu 上 Oracle 查询性能优化实操指南
一 硬件与操作系统层优化
- 存储优先：将数据文件、重做日志、归档日志分别放在不同的高速磁盘上，优先使用 SSD/NVMe，可显著减少 I/O 等待。
- 内存与 CPU：为数据库分配充足的 内存，并启用 多核 CPU 并行能力，提升并发与吞吐。
- 网络：保障足够的 带宽 与稳定的低时延；应用侧使用 连接池 减少频繁建连开销。
- 文件系统与内核：选择合适的文件系统并优化挂载选项；按需调整 TCP 与文件系统相关内核参数；关闭不必要的系统服务，减少资源竞争。
- 资源隔离：将 数据、日志、索引 分离到不同 I/O 通道或设备，降低争用。
二 数据库内存与实例参数
- 内存结构：合理调优 SGA（如共享池、大型池、Java 池）与 PGA，避免内存不足导致频繁磁盘访问或内存颠簸。
- 日志与并发：根据负载调整 日志缓冲区 与 最大连接数/并发连接数，减少日志写入瓶颈与连接风暴。
- 执行效率：启用并规范使用 绑定变量，降低硬解析比例；对大批量处理合理使用 并行执行。
三 SQL 与执行计划优化
- 访问路径：避免 全表扫描，为高频过滤/排序/连接列建立合适的索引；删除不再使用的索引，减少维护成本。
- SQL 写法：避免 **SELECT ***，只返回必要列；减少不必要排序与聚合；避免 隐式转换（保持比较字段数据类型一致）；尽量使用 绑定变量。
- 统计信息：定期收集表和索引的 统计信息，保证优化器生成更优执行计划。
- 结构优化：对大表采用 分区表；必要时使用 物化视图 预计算聚合结果。
- 执行计划分析：使用 EXPLAIN PLAN 与 AWR/SQL Monitor 定位瓶颈（如全表扫描、重复扫描、错误驱动表），必要时谨慎使用 Hint 引导访问路径。
四 大数据量场景的专项优化
- 减少返回量：分页/限流，避免一次性拉取海量数据；只查需要的列与行。
- 索引策略：控制单表索引数量（一般不超过 6 个），在更新频繁列上谨慎建索引；优先使用 数字型 字段存储数值，尽量用 VARCHAR/NVARCHAR 替代 CHAR/NCHAR 减少存储与比较开销。
- 批量与临时对象：批量提交避免长事务；在合适场景用 TRUNCATE 替代 DELETE（注意 TRUNCATE 为 DDL、不可回滚）；重复引用大型中间结果时，合理使用 临时表 或 WITH 子句减少重复扫描。
- 锁与竞争：排查 锁/死锁 与读写竞争，优化事务粒度与隔离级别，降低等待。
五 监控与维护流程
- 建立基线：持续采集 AWR/ASH 报告与关键指标（逻辑读、物理读、执行时间、等待事件），形成对比基线。
- 例行维护：定期 收集统计信息、重建或重组碎片化严重的索引，清理无用对象，保持执行计划稳定。
- 变更管控：SQL/索引/参数变更前后进行 回归测试 与性能对比，避免引入劣化。
- 变更前务必做好 完整备份，并在可控窗口内实施调优操作。