Linux Oracle性能调优方法

Linux 上 Oracle 性能调优方法

一 整体思路与瓶颈定位

• 采用自顶向下与自底向上结合的调优路径：先明确业务目标（如TPS、响应时间），再测量现有系统的I/O 能力与SQL 负载，最后针对瓶颈实施优化并回归验证。I/O 调优应在内存调优之后进行，只有在无法通过内存减少 I/O 时才着手 I/O 优化。并行、分区、索引与 SQL 改写往往能显著降低 I/O 请求次数。
• 用数据说话：在 Linux 侧用iostat -x 1、sar -d、pidstat -d观察await、r/s、w/s、util；在数据库侧用AWR/ASH/ADDM识别 Top SQL 与等待事件（如db file sequential/scattered read、direct path read/write、log file sync、log file parallel write），先抓“少数 SQL 导致多数 I/O”的根因，再决定是 SQL、索引、并行度、内存还是存储层优化。

二 Linux 操作系统层优化

• I/O 调度器：数据库磁盘优先使用noop（SSD/NVMe、虚拟化环境）或deadline（传统阵列、强调延迟的场景）；避免 CFQ。示例检查与设置：
  • 查看：find /sys/block/*/queue -name scheduler -exec sh -c ‘echo -n "$0 : "; cat $0’ {} ;
  • 建议：SSD/NVMe 或 VMware/KVM 上设为 noop；物理磁盘阵列可设为 deadline。
• 文件系统与挂载：选择XFS/ext4等成熟文件系统，合理设置挂载选项（如 noatime、barrier/relatime 视存储可靠性取舍），并保持条带化/对齐以获得稳定吞吐。
• 内核与资源：按负载调整内核参数（如file-max、sem、shmall/shmmni等），关闭不必要的服务，减少资源争用；确保NTP时间同步，避免日志与回放受影响。

三 Oracle 内存与关键参数

• 内存总体策略：优先使用自动内存管理（AMM）或自动共享内存管理（ASMM），在11g及以上可用MEMORY_TARGET/MEMORY_MAX_TARGET统一管理 SGA+PGA；如需手动，再分别设置SGA_TARGET与PGA_AGGREGATE_TARGET。经验上数据库可用内存约占物理内存的1/2～2/3，需为 OS 与其他进程预留充足空间。
• SGA 组件要点：
  • DB_CACHE_SIZE：关注Buffer Cache Hit Ratio，OLTP 场景通常期望≥95%；过低则增大缓冲池。
  • SHARED_POOL_SIZE：关注Library Cache与Data Dictionary Cache命中率；库缓存建议**>90%，数据字典建议>95%**，否则增大共享池。
  • LOG_BUFFER：关注redo log space requests；若“申请失败率”偏高（如**>1%**），考虑增大日志缓冲。
• PGA 与排序：在自动 PGA 下关注PGA cache hit percentage与over allocation count；若大量排序落到磁盘（v$sysstat 中sorts (disk)偏高），适度增大SORT_AREA_SIZE（或提高 PGA 目标），并权衡内存占用与换页风险。
• 诊断 SQL（示例）：
  • 命中率：select 1-(phys.value/(cur.value+con.value)) from v$sysstat cur, v$sysstat con, v$sysstat phys where cur.name=‘db block gets’ and con.name=‘consistent gets’ and phys.name=‘physical reads’;
  • 库/字典缓存：select sum(pins) executions, sum(reloads) misses, sum(reloads)/sum(pins) from v$librarycache; 与 select 1-sum(getmisses)/sum(gets) from v$rowcache;
  • 日志缓冲：select (req.value*5000)/entries.value from v$sysstat req, v$sysstat entries where req.name=‘redo log space requests’ and entries.name=‘redo entries’;
  • 排序：select name,value from v$sysstat where name in (‘sorts (memory)’,‘sorts (disk)’);

四 I/O 与存储层优化

• 布局与条带化：按对象类型与访问特征分散 I/O，利用手动或软件条带降低热点与争用；评估文件系统 vs 裸设备在读写特性上的差异，必要时进行多盘并行与控制器/缓存优化。
• 块大小与多块读：结合访问模式选择DB_BLOCK_SIZE（OLTP 常用8K；大行/顺序访问可用16K–32K），并合理设置DB_FILE_MULTIBLOCK_READ_COUNT以提升全表扫描/顺序读效率（避免过大导致单次 I/O 过长或内存压力）。
• 关键进程与检查点：关注LGWR（日志写入）与DBWn（脏块写出）效率，合理控制检查点频率与日志切换行为，减少“log file sync”“free buffer waits”等等待；将重做/归档/数据分离到不同磁盘组以分散 I/O。

五 SQL 与执行计划优化

• 减少无效 I/O：优先通过索引/分区/改写 SQL降低扫描与重复读取；利用绑定变量减少硬解析；避免SELECT *** 与在 WHERE 中对列做函数计算；能用JOIN替代复杂子查询；大批量操作采用批量提交**。
• 执行计划与并行：用EXPLAIN PLAN/DISPLAY_CURSOR与AWR/ASH定位高成本操作（如全表扫描、哈希/排序溢出），通过创建/重建索引、收集统计信息、调整并行度等手段优化；必要时使用SQL Tuning Advisor与SQL Plan Baselines固化好计划。