Author: Mike Ault

I/O Waits:

Concurrent access is one of the most widely encountered problems in any database environment. The ever-present I/O wait is usually the major wait event in any database system. The problem of I/O wait is due to the fact that I/O to memory is in the nanosecond range while I/O to disk is in the millisecond range. Therefore your system may get crippled when blocked access is added due to multi-disk I/O requests.

Disks in an array:

The disks in an array should be based on I/O needs.

Stripe Width:

The stripe width should be based on concurrency needs

Increase in Stripe Unit per Disk:

The base stripe unit per disk has been increased to 64K by the manufacturers.

Increase in Maximum I/O Size:

Systems such as SUN and Windows utilize maximum I/O sizes of 1 megabyte or larger.

Primary Disk I/O:

The primary I/O size for database system and the I/O size of the disk array system of the database must match each other.

Stripe Unit per Disk:

The stripe unit per disk must match the expected majority I/O request from database application.

Oracle Block Size:

The oracle block size should be based on a multiple of your db_file_multiblock_read and block size being equal to the stripe width and your data needs.

Tuning disk array stripe units size based on expected concurrency:

• The I/O speed is measured by average seek time.
• The I/O rate is measured in megabytes per second
• The I/O speed and I/O rate for a disk determine the stripe size for performance in an array even when the number of concurrent accesses is not known.

Stripe Unit per Disk for Known Concurrency Calculations:

Below is the formula for non-RAID5 arrays when concurrency is known:

Stripe Unit per Disk for Unknown Concurrency Calculations:

For a system with unknown concurrency the calculation the stripe unit will be

Increase in Overall Value of Combined Factor:

When the average seek times drop, the transfer rate increases. For example on a Ultra3 SCSI 15K drive the spec for average seek may drop to 4.7 ms, however the transfer rate leaps to 70 Mbyte per second. so the over all value of the combined factor goes from 112 to 329, a 293% increase.

Conclusion:

To conclude I would say that a system dependent on a single block reads and writes with single point index reads will benefit from a small block size (4-8K) while a system that depends on full table scans and index scans will benefit from a larger block size (16k, 32k).

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Is Relational DBMS Dying?
   Oracle: Achieving performance goal - Part I
   Great Tips on Recovering Controlfile without Resetlogs!
   Oracle Recovery from an Unplanned Outage!!