Ubuntu上提升Oracle查询速度的系统化做法
一 定位瓶颈
- 生成并阅读AWR/ADDM报告，聚焦“Top 5 Timed Events”、SQL 执行耗时与负载特征，优先处理占比最高的问题 SQL 与等待事件。命令示例：@?/rdbms/admin/awrrpt.sql、@?/rdbms/admin/addmrpt.sql。
- 对慢 SQL 使用EXPLAIN PLAN与DBMS_XPLAN.DISPLAY查看执行计划，重点排查：全表扫描（TABLE ACCESS FULL）、缺失或低效索引、不合理的连接方式等。
- 监控I/O 与缓存命中率：
- 数据文件读写统计与时间：
SELECT name, phyrd, phywr, readtim, writetim
FROM v$datafile f, v$iostat_file i
WHERE f.file# = i.file_no AND i.filetype_name = ‘Data File’;
- 缓冲池命中率：
SELECT (1 - (physical_reads - direct_reads) / (db_block_gets + consistent_gets)) * 100 AS cache_hit_ratio
FROM v$buffer_pool_statistics;
- 使用SQL Trace/SQL Tuning Advisor获取更细粒度的执行与优化建议。
二 数据库层优化
- 内存管理
- 启用自动内存管理（AMM）：
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET=8G SCOPE=SPFILE;
- 或手动分配：
ALTER SYSTEM SET SGA_TARGET=2G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=1G SCOPE=SPFILE;
- SQL 与索引
- 避免*SELECT ，使用绑定变量减少硬解析；必要时用提示（如 /*+ INDEX(…) */）引导执行计划。
- 为高频 WHERE/JOIN 列建索引，优先复合索引与覆盖索引；删除未使用或重复索引；对高碎片索引执行重建。
- 分区与并行
- 对大表按时间/范围分区，查询可只扫描相关分区，显著降低 I/O。
- 适度使用并行：ALTER TABLE t PARALLEL (DEGREE 4); 查询提示：SELECT /*+ PARALLEL(t,4) */ …
- 统计信息与计划稳定性
- 定期收集统计信息：EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>‘YOUR_SCHEMA’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
- 对反复执行的 SQL 建立SQL Plan Baseline以稳定计划。
三 操作系统与存储层优化
- 文件系统与挂载
- 选择XFS/ext4，挂载选项建议使用noatime,nodiratime；XFS 可配合data=writeback提升写入性能（权衡数据一致性）。
- 将数据文件、重做日志、控制文件分离到不同磁盘，优先使用SSD/NVMe。
- I/O 与调度
- 启用异步 I/O：ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
- I/O 调度器：SSD 优先deadline，HDD 可用cfq。查看/设置示例：
cat /sys/block/sda/queue/scheduler
echo deadline | sudo tee /sys/block/sda/queue/scheduler
- 内核与资源
- 提升文件句柄与网络/进程资源：
fs.file-max = 655360
kernel.sem = 250 32000 100 128
net.core.somaxconn = 1024
修改后执行：sudo sysctl -p
- 大内存可考虑HugePages减少页表开销（需按内存与实例参数谨慎规划）。
四 硬件与连接层优化
- 硬件
- 增加内存以扩大 SGA/PGA；优先SSD/NVMe降低 I/O 时延；利用多核 CPU配合并行查询。
- 连接与并发
- 合理设置最大连接数（如 PROCESSES、SESSIONS），避免连接风暴；应用侧使用连接池（如 UCP、HikariCP）复用连接，降低握手与销毁成本。
五 安全变更与验证流程
- 变更前在测试环境验证，变更中分批小步推进，变更后持续监控关键指标（逻辑读、物理读、执行时间、AWR Top SQL）。
- 重要参数（如 MEMORY_TARGET/SGA/PGA）修改后需重启实例；参数与 SQL 调整均应保留回滚方案。
- 定期复核：统计信息新鲜度、索引使用率、SQL 计划是否稳定、是否存在锁争用与热点对象。
