Tuning Disk I/O for Oracle Database (Part A)

Optimizing Disk I/O in Oracle Database: A Key to Performance

Alireza Kamrani 

28/02/2025

Efficient disk I/O tuning is essential for achieving optimal performance in Oracle databases. 

Poorly tuned I/O can lead to bottlenecks, increased latency, and overall system slowdowns. 

By understanding workload patterns, leveraging Oracle’s Automatic Storage Management (ASM), and optimizing datafile placement, organizations can significantly improve query response times and reduce contention.

🔻Key strategies include:

✅ Balancing Read/Write Operations – Distributing I/O across multiple disks to avoid hotspots.

✅ Leveraging ASM & Flash Storage – Using Oracle ASM for automatic load balancing and flash storage for high-speed access.

✅ Tuning DB Writer & Log Writer Processes – Adjusting database writer parameters to enhance checkpoint performance.

✅ Using AWR & ASH Reports – Identifying bottlenecks and optimizing SQL execution plans accordingly.

Effective disk I/O tuning requires a combination of best practices, proactive monitoring, and database-specific configurations.

*******************

Tuning Disk I/O for Oracle Database involves both database parameters and Linux kernel settings to optimize performance.

Part 🅰️:

Note: All the following settings and parameters should be customized based on your environment and also some of setting works only on Non ASM env.

1. Oracle Database Parameters for Disk I/O Optimization

1.1 Memory & Buffer Pool Tuning

• DB_CACHE_SIZE: Determines the size of the database buffer cache.

• PGA_AGGREGATE_TARGET: Controls the size of the Process Global Area (PGA).

• SGA_TARGET & SGA_MAX_SIZE: Defines the total memory allocated for SGA components.

1.2 Redo Log & Transaction Log Tuning

• LOG_BUFFER: Adjusts the size of the redo log buffer to reduce I/O.

• FAST_START_MTTR_TARGET: Controls automatic checkpointing to minimize recovery time.

• DB_BLOCK_CHECKSUM: Controls block checksum verification (set to TYPICAL for performance, FULL for safety).

• DB_BLOCK_SIZE: Typically, 8K for OLTP, 16K or 32K for DSS.

1.3 Checkpoint & Write Behavior

• DB_WRITER_PROCESSES: Increases the number of DB writers to spread the I/O load.

• LOG_CHECKPOINT_INTERVAL & LOG_CHECKPOINT_TIMEOUT: Adjust checkpoint frequency.

• DISK_ASYNCH_IO & FILESYSTEMIO_OPTIONS:

o Set to SETALL to enable Direct I/O and Asynchronous I/O.

o For file systems like EXT4, XFS, or ASM, use DIRECTIO.

1.4 Parallel I/O & Read Ahead

• DB_FILE_MULTIBLOCK_READ_COUNT: Defines the number of blocks read in a full table scan.

• ASM_DISKSTRING: Helps Oracle Automatic Storage Management (ASM) discover disks.

1.5 ASM (Automatic Storage Management) Parameters

• ASM_DISKSTRING: Defines the disk discovery path.

•ASM_PREFERRED_READ_FAILURE_GROUPS: Helps define failover read paths.

•ASM_POWER_LIMIT: Controls the speed of disk rebalance operations.

2. Linux Kernel & OS-Level Disk I/O Tuning for Oracle 

2.1 Disk I/O Scheduler Optimization

• Use none or mq-deadline for SSD, deadline for HDD:

# echo "none" > /sys/block/sda/queue/scheduler

Check current scheduler:

# cat /sys/block/sda/queue/scheduler

2.2 File System Optimization

• Use XFS or EXT4 (Avoid NTFS and older file systems).

• Enable direct I/O for Oracle files:

# chown oracle:oinstall /u02/oradata

# chmod 750 /u02/oradata

# mount -o rw,noatime,nodiratime /dev/sdb1 /u02/oradata

• Increase Read-Ahead Buffer:

# blockdev --setra 4096 /dev/sda

Read-ahead speeds up file access by pre-fetching data and loading it into the page cache so that it can be available earlier in memory instead of from disk.

2.3 Kernel Parameters for Oracle Performance 

Modify /etc/sysctl.conf:

fs.aio-max-nr = 1048576

fs.file-max = 6815744

vm.swappiness = 10

vm.dirty_background_ratio = 5

vm.dirty_ratio = 20

vm.dirty_expire_centisecs = 500

vm.dirty_writeback_centisecs = 100

Apply changes:

# sysctl -p

2.4 Asynchronous & Direct I/O

• Set FILESYSTEMIO_OPTIONS=SETALL in Oracle.

• Enable direct I/O in mount options for database files.

2.5 Disk Caching & Write-Back Mode

Enable write-back caching if supported:

# hdparm -W1 /dev/sda

#cat /sys/block/sdb/queue/write_cache

write through

3. Storage-Specific Tuning

3.1 ASM (Automatic Storage Management) Best Practices

• Use 4MB ASM allocation units for large I/O workloads.

• Distribute disks evenly across failure groups.

3.2 NVMe/SSD Optimization

• Disable Write Barriers:

# mount -o nobarrier /dev/nvme0n1 /oradata

• Enable TRIM for SSDs:

# fstrim -av

In the next post, I will more explain the capability of database and Linux configurations to tuning I/O on write-heavy operations.

#################

Alireza Kamrani .