Linux上Oracle查询性能优化实战指南
一 基线诊断与瓶颈定位
@?/rdbms/admin/awrrpt.sql、@?/rdbms/admin/addmrpt.sql。V$SQLAREA 按 BUFFER_GETS/DISK_READS/EXECUTIONS 排序找出“高消耗 SQL”。EXPLAIN PLAN FOR ... + DBMS_XPLAN.DISPLAY 查看是否出现 FULL TABLE SCAN、INDEX RANGE SCAN、NESTED LOOPS、HASH JOIN 等,并核对是否使用了合适的索引与连接顺序。DBMS_STATS.GATHER_SCHEMA_STATS),避免因过期统计导致次优计划。db file sequential/scattered read、log file sync 等等待;确认数据文件位于 SSD/NVMe、I/O 子系统无瓶颈。二 SQL与索引优化要点
SELECT *,减少 I/O 与网络开销。UPPER(col)=...、col/2=...)会导致索引失效,改写为“列在右、常量在左”。varchar2 列与数字常量比较)会阻止索引使用,统一数据类型或在列上显式转换。LIKE '%abc%' 无法使用 B-Tree 索引,改为 LIKE 'abc%' 或全文/倒排方案。NOT、<>、IS NULL(单列 B-Tree 不存 NULL),必要时用默认值或改写逻辑。IN/NOT IN;能用 UNION ALL 就不用 UNION(避免去重排序)。EXPLAIN PLAN 验证访问路径;必要时谨慎使用提示(如 /*+ INDEX(...)、/*+ PARALLEL(...))验证效果,避免长期依赖。三 内存、并行与分区策略
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;(并确保 MEMORY_MAX_TARGET 足够)。SGA_TARGET(如 2G)与 PGA_AGGREGATE_TARGET(如 500M),根据负载微调。ALTER TABLE t PARALLEL (DEGREE 4); 或 SQL 提示 /*+ PARALLEL(t,4) */;会话级可设 ALTER SESSION SET parallel_degree_policy=AUTO;。并行度应与 CPU 核数、I/O 吞吐匹配,避免过度并行导致争用。四 Linux与存储层面的优化
noatime、barrier 依据阵列策略);确保 I/O 调度 与阵列缓存策略匹配业务特征。vm.nr_hugepages、kernel.shmmax 等按实例 SGA 规划;启用 HugePages 可减少页表开销并提升稳定性。ulimit -n(如 65536 或更高)以支撑高并发连接与后台进程。net.ipv4.tcp_fin_timeout、tcp_tw_reuse 等以减少连接开销。五 可操作的优化清单与注意事项
EXPLAIN PLAN 与 DBMS_XPLAN 核查计划;3) 补充或改写索引(B-Tree/函数索引/组合索引/分区索引),并验证执行计划命中;4) 消除索引失效场景(函数/隐式转换/前导 %/NOT/<>/IS NULL 等);5) 统一 SQL 写法,使用 绑定变量,减少硬解析;6) 调整 SGA/PGA/MEMORY_TARGET,开启并行(按 CPU/I/O 适度);7) 对大表实施 分区 与 分区裁剪;8) 必要时引入 物化视图/查询重写/结果缓存;9) 在 Linux 上配置 HugePages、文件描述符、I/O 调度 与文件系统挂载参数;10) 变更前备份并在测试环境验证,变更后复核 AWR 指标与 SQL 计划稳定性。