重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: Oracle数据库存储与 I/O 参数优化 [打印本页]

---

作者: mahan    时间: 2025-10-19 22:24
标题: Oracle数据库存储与 I/O 参数优化
注意：所有内存参数的修改都应在测试环境充分验证后再应用于生产环境，避免因内存分配不当导致数据库性能问题或实例无法启动。

重要提示：不同Oracle版本可能存在差异，生产环境调整前请务必查阅对应版本的官方文档并进行测试。所有修改操作前请做好参数文件备份

-- 创建PFILE备份
CREATE PFILE='/backup/initORCL_20231201.ora' FROM SPFILE;
-- 直接复制SPFILE（需要数据库关闭）
-- 在操作系统层面复制
-- cp $ORACLE_HOME/dbs/spfileORCL.ora /backup/
Oracle 数据库存储与 I/O 参数优化设置笔记
1. 核心存储与 I/O 参数总览
参数名        类型        作用        是否动态        默认值（19c）        版本差异
DB_BLOCK_SIZE        静态        数据块大小        否        8192        安装后不可改
DB_FILE_MULTIBLOCK_READ_COUNT        动态        全表扫描一次读取块数        是        128        12c 起自动调优
DB_WRITER_PROCESSES        静态        DBWR 进程数        否        CPU_COUNT/8        11g 起支持多进程
DISK_ASYNCH_IO        动态        是否启用异步 I/O        是        TRUE        所有版本支持
DB_WRITER_IO_SLAVES        动态        DBWR I/O 从属进程        是        0        11g 起建议关闭
LOG_BUFFER        静态        Redo 日志缓冲区        否        16MB (19c)        11g 为 8MB
FAST_START_MTTR_TARGET        动态        控制检查点频率        是        0（禁用）        12c 起增强
DB_CACHE_SIZE        动态        默认缓冲区缓存        是        0（由 SGA_TARGET 控制）        11g 起支持自动管理
DB_KEEP_CACHE_SIZE        动态        Keep 池缓存        是        0        所有版本
DB_RECYCLE_CACHE_SIZE        动态        Recycle 池缓存        是        0        所有版本
2. 参数详解与计算规则(参考)
1.1 DB_BLOCK_SIZE
作用：定义 Oracle 数据块大小，影响 I/O 效率与存储结构。
建议值：
OLTP：8KB（默认）
OLAP：16KB 或 32KB
计算规则：不可更改，建库时设定。
设置示例：
-- 仅在建库时设置
db_block_size=8192
1.2 DB_FILE_MULTIBLOCK_READ_COUNT
作用：控制全表扫描时一次读取的块数，影响大表扫描性能。
计算规则：
min(1048576 / DB_BLOCK_SIZE, DB_CACHE_SIZE / (SESSIONS * 2))
建议值：
8KB 块：128
16KB 块：64
设置示例：
ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=128 SCOPE=BOTH;
1.3 DB_WRITER_PROCESSES
作用：控制 DBWR 进程数，影响脏块写入效率。
计算规则：
max(1, min(20, CPU_COUNT/8))
设置示例：
ALTER SYSTEM SET DB_WRITER_PROCESSES=4 SCOPE=SPFILE;
1.4 LOG_BUFFER
作用：缓存 redo 日志，减少 LGWR I/O 频率。
计算规则：
max(1MB, 512KB * CPU_COUNT)
建议值：
小型系统：8MB
大型系统：16~32MB
设置示例：
ALTER SYSTEM SET LOG_BUFFER=16777216 SCOPE=SPFILE; -- 16MB
1.5 FAST_START_MTTR_TARGET
作用：控制实例恢复时间（秒级），间接影响检查点频率。
计算规则：
目标恢复时间（秒）* 平均redo产生速率（MB/s）
建议值：
OLTP：60~300
OLAP：600~1800
设置示例：
ALTER SYSTEM SET FAST_START_MTTR_TARGET=120 SCOPE=BOTH;
1.6 DB_CACHE_SIZE
作用：定义默认缓冲区缓存大小。
计算规则：
(SGA_TARGET * 0.6) - SHARED_POOL_SIZE - LOG_BUFFER - LARGE_POOL_SIZE
设置示例：
ALTER SYSTEM SET DB_CACHE_SIZE=4G SCOPE=BOTH;
1.7 DB_KEEP_CACHE_SIZE / DB_RECYCLE_CACHE_SIZE
作用：用于缓存频繁访问（keep）或不常使用（recycle）的表。
建议值：
Keep：热点表总大小的 120%
Recycle：大表扫描总大小的 50%
设置示例：
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=1G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=512M SCOPE=BOTH;
3. I/O 参数优化
3.1 数据库块大小配置
DB_BLOCK_SIZE
版本差异：

11g/12c/19c：默认通常 8KB，支持多种块大小
可设置范围：2KB-32KB，取决于操作系统
设置策略：

OLTP 系统：8KB 块大小
DSS/数据仓库：16KB-32KB 块大小
修改示例（12c+）：

-- 修改块大小为 16KB（需重启数据库）
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM SET DB_BLOCK_SIZE=16384 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
3.2 异步 I/O 设置
FILESYSTEMIO_OPTIONS 和 DISK_ASYNCH_IO
参数说明：控制数据库是否使用异步 I/O。

设置建议：

-- 使用文件系统时启用异步 I/O
ALTER SYSTEM SET filesystemio_options=asynch SCOPE=SPFILE;
ALTER SYSTEM SET disk_asynch_io=TRUE SCOPE=SPFILE;
版本注意事项：

使用 ASM 存储时默认开启异步 I/O
对于 EXT4/XFS 文件系统建议显式启用
3.3 多块读取参数
DB_FILE_MULTIBLOCK_READ_COUNT
计算规则：DB_FILE_MULTIBLOCK_READ_COUNT = (最大 I/O 大小) / DB_BLOCK_SIZE

设置示例（8KB 块大小，1MB 最大 I/O）：

-- 1MB = 1024KB, 1024KB / 8KB = 128
ALTER SYSTEM SET db_file_multiblock_read_count=128 SCOPE=SPFILE;
版本差异：

11g/12c：自动调整，也可手动设置
当 MEMORY_TARGET 启用时，Oracle 可自动优化此参数
3.4 重做日志优化
LOG_BUFFER
设置规则：

最小建议：64MB
大型事务系统：128MB-256MB
监控公式：log buffer 空间等待次数 = (redo log space requests) / (redo entries)
设置示例：

ALTER SYSTEM SET log_buffer=64M SCOPE=SPFILE;
重做日志文件大小
最佳实践：

设置至少 3 组重做日志组
每组 2 个成员（分布在不同磁盘）
文件大小：500MB-1GB（减少日志切换频率）
3.5 其他关键 I/O 参数
DB_WRITER_PROCESSES
设置规则：

单 CPU 系统：1 个进程
多 CPU 系统：CPU 数量/8 （最多 20 个）
设置示例（8 CPU 系统）：

ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE;
4. 操作系统层优化
4.1 Linux 内核参数
/etc/sysctl.conf 优化：

# 降低交换倾向
vm.swappiness=10
# 优化脏页写入

vm.dirty_background_ratio=10

vm.dirty_ratio=20


# 增加文件描述符

fs.file-max=6815744


# 提高网络连接队列

net.core.somaxconn=4096


4.2 I/O 调度器优化
设置策略：

SSD/NVMe 设备：设置为 none（禁用调度器）
SATA SSD/HDD：使用 deadline 调度器
永久生效配置（/etc/udev/rules.d/60-ioscheduler.rules）：

# SSD 设备使用 none 调度器
ACTION=="add|change", KERNEL=="sd[a-z]*", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}="none"
# HDD 设备使用 deadline 调度器

ACTION"add|change", KERNEL"sd[a-z]*", ATTR{queue/rotational}==“1”, ATTR{queue/scheduler}=“deadline”


4.3 文件系统挂载选项
XFS/EXT4 优化选项：

# /etc/fstab 配置示例
/dev/sdb1   /u01/oradata   xfs   noatime,nodiratime,allocsize=1G,inode64   0   0
/dev/sdc1   /u02/redo      xfs   noatime,nodiratime,allocsize=1G,nobarrier  0   0
5. 存储结构优化
5.1 I/O 分离策略
组件分离建议：

/u01/oradata    -- 数据文件
/u02/redo       -- 重做日志文件（至少3组，每组2个成员）
/u03/archive    -- 归档日志文件
/u04/temp       -- 临时表空间
/u05/fra        -- 快速恢复区
5.2 ASM 存储优化
ASM 磁盘组配置：

-- 创建高可用磁盘组
CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP controller1 DISK '/dev/sdb1'
FAILGROUP controller2 DISK '/dev/sdc1';
– 检查 ASM 磁盘组性能

SELECT group_number, name, total_mb, free_mb

FROM v$asm_diskgroup;


6. 版本差异汇总
参数        11g        12c        19c        21c
DB_FILE_MULTIBLOCK_READ_COUNT        手动设置        自动调优        自动调优        自动调优
LOG_BUFFER        最大 16MB        最大 64MB        最大 256MB        最大 512MB
DB_WRITER_PROCESSES        最大 20        最大 36        最大 64        最大 128
DISK_ASYNCH_IO        支持        支持        支持        支持（默认开启）
FAST_START_MTTR_TARGET        支持        增强        增强        增强
INMEMORY_SIZE        不支持        支持（12.1.0.2）        支持        支持
6.1 Oracle 11g 特定参数
参数        建议设置        默认值        说明
_undo_autotune        FALSE        TRUE        禁用 undo 自动优化
_use_adaptive_log_file_sync        FALSE        TRUE        禁用日志文件同步自适应切换
deferred_segment_creation        FALSE        TRUE        避免段延迟创建
6.2 Oracle 12c/19c 特定参数
参数        建议设置        说明
_optimizer_aggr_groupby_elim        FALSE        避免聚合 group by 消除的 BUG
_optimizer_unnest_scalar_sq        FALSE        避免标量子查询错误
_high_priority_processes        'LMS*|VKTM|LGWR'        设置高优先级进程（19c）
19c 高优先级进程设置：

ALTER SYSTEM SET "_high_priority_processes"='LMS*|VKTM|LGWR' SCOPE=SPFILE;
6.3 SecureFiles 参数 (11g+)
参数说明：11g 引入的改进 LOB 存储。

设置示例：

-- 启用 SecureFiles
ALTER SYSTEM SET db_securefile='PERMITTED' SCOPE=SPFILE;
– 创建 SecureFiles LOB

CREATE TABLE doc_table (

id NUMBER,

doc CLOB

) LOB(doc) STORE AS SECUREFILE (

COMPRESS HIGH

ENCRYPT USING ‘AES256’

CACHE

);


7. 设置模板（19c 示例）
-- 初始化参数文件（spfile）设置模板
ALTER SYSTEM SET DB_BLOCK_SIZE=8192 SCOPE=SPFILE;
ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=128 SCOPE=BOTH;
ALTER SYSTEM SET DB_WRITER_PROCESSES=8 SCOPE=SPFILE;
ALTER SYSTEM SET DISK_ASYNCH_IO=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_BUFFER=16777216 SCOPE=SPFILE;
ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH;
ALTER SYSTEM SET DB_CACHE_SIZE=8G SCOPE=BOTH;
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=2G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=1G SCOPE=BOTH;
8. 诊断与验证视图
-- 查看当前参数值
SELECT name, value, isdefault FROM v$parameter WHERE name LIKE '%db_cache%';
– 查看 I/O 等待事件

SELECT event, total_waits, time_waited FROM v$system_event WHERE event LIKE ‘db file%’;


– 查看检查点频率

SELECT mttr_target_for_estimate, estimated_mttr FROM v$instance_recovery;


– 查看缓存命中率

SELECT name, physical_reads, db_block_gets, consistent_gets,

round((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) AS cache_hit_ratio

FROM v$buffer_pool_statistics;


9. 监控与诊断
9.1 内存使用监控
综合内存查询：

SELECT name, total,
       ROUND(total-free,2) used,
       ROUND(free,2) free,
       ROUND((total-free)/total*100,2) pctused
FROM (
  SELECT 'SGA' name,
         (SELECT SUM(value/1024/1024) FROM v$sga) total,
         (SELECT SUM(bytes/1024/1024) FROM v$sgastat WHERE name='free memory') free
  FROM dual
)
UNION
SELECT name, total,
       ROUND(used,2) used,
       ROUND(total-used,2) free,
       ROUND(used/total*100,2) pctused
FROM (
  SELECT 'PGA' name,
         (SELECT value/1024/1024 total FROM v$pgastat WHERE name='aggregate PGA target parameter') total,
         (SELECT value/1024/1024 used FROM v$pgastat WHERE name='total PGA allocated') used
  FROM dual
);
9.2 I/O 性能监控
操作系统层面：

# 监控磁盘利用率
iostat -xm 1 5
# 实时 I/O 监控

iotop -o


# 深度 I/O 分析

blktrace -d /dev/sdb1 -o tracefile


数据库 I/O 统计：

-- 数据文件 I/O 统计
SELECT name, phyrds, phywrts, readtim, writetim
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#;
– 等待事件分析

SELECT event, total_waits, time_waited

FROM v$system_event

WHERE event LIKE ‘%db file%’ OR event LIKE ‘%log file%’;


10. 建议与注意事项
场景        建议
OLTP        使用 8KB 块，启用 Keep 池，减少全表扫描
OLAP        使用 16KB 块，调大多块读参数，启用并行 I/O
高并发写        增加 DBWR 进程数，调大 LOG_BUFFER，启用异步 I/O
低延迟要求        设置 FAST_START_MTTR_TARGET < 120，频繁检查点
大内存系统        设置 SGA_MAX_SIZE = 物理内存 70%，DB_CACHE_SIZE 占 SGA 60%
11. 最佳实践总结
11.1 参数设置检查清单（示例）
内存设置：

配置 MEMORY_TARGET 或分别设置 SGA_TARGET 和 PGA_AGGREGATE_TARGET
根据工作负载类型调整 SGA/PGA 比例
考虑启用 12c+ In-Memory 选项用于分析负载
I/O 设置：

配置合适的 DB_BLOCK_SIZE
启用异步 I/O
优化重做日志大小和配置
设置合适的 DB_FILE_MULTIBLOCK_READ_COUNT
版本特定优化：

根据版本调整隐藏参数
启用 SecureFiles for LOBs (11g+)
配置高优先级进程 (19c)
11.2 维护建议（参考）
定期监控：

每周检查内存和 I/O 统计信息
每月分析 AWR 报告识别性能趋势
季度性进行存储性能评估
容量规划：

监控表空间使用情况
预测存储增长需求
定期评估内存需求变化
12. 附录：参数计算脚本示例（SQL）
-- 计算建议 DB_CACHE_SIZE
SELECT ROUND((SELECT VALUE FROM v$parameter WHERE name='sga_max_size') * 0.6) AS suggested_db_cache
FROM dual;
– 计算建议 LOG_BUFFER

SELECT GREATEST(1048576, 524288 * (SELECT VALUE FROM v$parameter WHERE name=‘cpu_count’)) AS suggested_log_buffer

FROM dual;

---

欢迎光临 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)

Powered by Discuz! X3.2