CentOS Oracle如何实现性能调优

CentOS 上 Oracle 性能调优实战指南

一基线评估与瓶颈定位

建立性能基线：采集 AWR/ADDM 报告，关注 DB Time、Top 5 Timed Foreground Events、SQL ordered by Elapsed Time/Cost，先找“最贵”的 SQL 与等待事件，再决定调优方向。示例生成 AWR 报告（按实际 DBID/INSTANCE_NUMBER 调整快照区间）：
```
SELECT * FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    (SELECT DBID FROM V$DATABASE),
    (SELECT INSTANCE_NUMBER FROM V$INSTANCE),
    1, 2
  )
);
```
操作系统与 I/O 体检：用 iostat -x 1、vmstat 1、top 观察 CPU 等待（如 wa%）、I/O 队列与饱和度；在数据库侧用 V$FILESTAT 查看数据文件读写热点，确认是否存在单盘/单文件争用。
内存与等待事件联动：若发现 direct path read/write、db file sequential/scattered read 偏高，多与磁盘或对象布局相关；若是 latch: cache buffers chains、buffer busy waits，多与 SQL 逻辑读高、热块/索引设计 相关，需结合 SQL 与对象层优化。

二操作系统与存储层优化

内核与资源参数（/etc/sysctl.conf，按内存与负载调整）：

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104   # 建议 ≥ SGA_MAX_SIZE
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
vm.vfs_cache_pressure = 50
net.core.somaxconn = 4096

执行 sysctl -p 生效；同时确保 /dev/shm ≥ Oracle 使用的总内存目标（启用 AMM 时尤为重要）。

I/O 架构与布局：
- 将数据文件、重做日志、归档与 TEMP 分离到不同磁盘/阵列，减少头摆与写放大；对数据文件使用 条带化（RAID 0/条带卷） 以摊薄 I/O。
- 优先选择 SSD/NVMe 或 Oracle Linux 上的 Database Smart Flash Cache（仅 Oracle Linux/Solaris 且 11gR2+ 支持）来扩展缓冲层、降低随机 I/O 延迟。
- 合理规划 条带大小 与 I/O 大小（如 8–64 KB 常见），用压测校准不同块大小下的读写时延，避免“条带过大/过小”导致吞吐不达标。

三内存与实例参数调优

内存目标与分配（按工作负载设定上限，避免抢占 OS 内存）：
- 通用建议：SGA+PGA ≤ 物理内存的 80%，预留 20% 给 OS 与文件系统缓存。
- 场景化配比（示例公式）：
  - OLTP：PGA ≈ (物理内存×80%)×20%；SGA ≈ 余量
  - DSS：PGA ≈ (物理内存×80%)×50%；SGA ≈ 余量

自动内存管理（AMM，Oracle 11g+）：

ALTER SYSTEM SET memory_max_target=8G SCOPE=SPFILE;
ALTER SYSTEM SET memory_target=8G   SCOPE=SPFILE;

自动共享内存（ASMM，Oracle 10g+）：

ALTER SYSTEM SET sga_target=6G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;

手动细调（仅在确需时）：

ALTER SYSTEM SET db_cache_size=2G SCOPE=BOTH;
ALTER SYSTEM SET shared_pool_size=1G SCOPE=BOTH;
ALTER SYSTEM SET large_pool_size=256M SCOPE=BOTH;
ALTER SYSTEM SET log_buffer=64M SCOPE=SPFILE;  -- 静态参数，需重启

PGA 工作区与监控（确保排序/哈希尽量在内存完成）：

-- 启用自动工作区
ALTER SYSTEM SET workarea_size_policy=AUTO SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;

-- 监控目标：optimal 执行率 ≥ 90%，multipass = 0%
SELECT name, value/1024/1024 MB FROM v$pgastat;
SELECT name, value, 100*value/SUM(value) OVER() pct
FROM v$sysstat WHERE name LIKE 'workarea executions%';

-- 容量建议
SELECT pga_target_for_estimate/1024/1024 pgamb,
       estd_pga_cache_hit_percentage hit_pct,
       estd_overalloc_count overalloc
FROM v$pga_target_advice ORDER BY pgamb;

缓冲池分层（高频表/索引放入 KEEP，一次性扫描放入 RECYCLE）：

ALTER SYSTEM SET db_keep_cache_size=512M SCOPE=BOTH;
ALTER SYSTEM SET db_recycle_cache_size=256M SCOPE=BOTH;
CREATE TABLE t (id NUMBER) STORAGE (BUFFER_POOL KEEP);

连接与会话（按压测校准，避免“过度开连接”）：
```
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=700 SCOPE=SPFILE;
```
注：以上参数需结合 AWR/ADDM、v$pgastat、v$sysstat 持续校准，避免“拍脑袋”设置。

四 SQL 与对象层优化

SQL 优化要点：
- 避免 **SELECT ***，只查需要的列；使用 绑定变量 降低硬解析；优先走索引避免 全表扫描；合理选择 连接算法（如 Hash Join/Nested Loops）与驱动表。
- 用 EXPLAIN PLAN 与 SQL Monitor 定位高成本操作（如大排序、跨表扫描、笛卡尔积）。
索引策略：
- 为高频过滤/连接列建立 B-Tree 索引；高选择性与高基数场景收益明显；避免 过度索引 以免 DML 维护成本飙升。
- 大表与历史数据建议 分区表/分区索引，减少扫描范围、提升维护效率。
典型等待与处理思路：
- latch: cache buffers chains：多由 热块/低效 SQL（逻辑读高） 引起；可用分区、打散热点（如 HASH 分区）、优化 SQL 与统计信息缓解。
- buffer busy waits：常见于并发访问同一数据块；可通过分区、重建/重组索引、调整 PCTFREE/PCTUSED 降低块争用。

五维护与监控闭环

统计信息与计划稳定性：

-- 全库统计（按对象粒度与采样率调整）
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCHEMA_NAME', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

空间与碎片治理：
- 对 高碎片 的堆表/索引执行 重组/重建；分区表按业务周期 分裂/合并/交换分区，减少一次性大操作。
监控与复盘：
- 以 AWR/ADDM 为核心，结合 OEM 或自建看板，固化“采集—分析—改动—复盘”的闭环；每次变更后保留 基线对比，验证 TPS、响应时间、I/O、CPU、等待事件 是否按预期改善。

最新问答

相关标签