Disk I/O Tuning with Oracle RAID Stripe Size!!

The IO characteristics of your database system must be understood in order to gain maximum performance from your disk. Making db_file_multi_block_read_size a multiple of disk block size doesn't fit into current disk environments. The disk architecture must be tuned to support the expected IO profile and the database system must be tuned to take advantage of the disk architecture. With the use of RAID technology Oracle database administrators must look beyond the disk block size when choosing IO parameters for Oracle databases.

Stripe Width:

The major parameter of concern for IO in RAID is the stripe width. Stripe width is the size of each stripe placed on a single disk spindle. For highly concurrent environments for example with large numbers of simultaneous users, you have to optimize disk access in order to serve as many people as possible at the same time.

Buffering:

Most modern disk arrays provide buffering to help, but for Oracle this usually only helps with the read portion of a disk access and in many cases doesn't help at all with the write portion of a request.

Oracle Parameters:

• db_block_size: 8K
• db_file_multi_block_read_count: 8
• direct_io_count: 64
• sort_block_io_count: 2

Disk Parameters:

• Number of disks in array: 4X1 (four data disks, 1 parity disk)
• RAID5
• Stripe Width: 8K per disk

Database IO Profile:

We can derive the below IO profile for the database by using above parameters. The IO profile of the database determines the maximum and minimum IO size. The IO profile will tell you what percentage of IO is large IO and what percentage is small IO, it will also give you the expected IO rate in IO/second.

If our application only does single block level IO or sorts and hash type activity, we will probably have acceptable performance. However, any direct IO or full table or index scans will cause the disks to be blocked causing waits and poor performance. In the above situation there is no good solution to fixing the IO issues because the stripe width is too small.

Array Rebuilding:

You can rebuild the array with a stripe width of at least 64K and maybe as high as 512K if the number of full or index scans or direct IO were high enough.

Driving the stripe width:

Essentially, we have a two-way equation that ties the value of db_file_multi_block_read_count and db_block_size to the stripe width. In the case where direct IO is a major factor, it would drive the stripe width.

Basically we are attempting to satisfy one IO operation with a single disk whenever possible so the other disks can satisfy other users' requests.

You can try the stripe width to the max_io_size for the platform, usually a value between 64K and 1 megabyte. We tested a NT4 -6a system against a RAID5 6X1 disk array by matching the value of db_file_multi_block_read_count times db_block_size against the stripe width. We achieved an IO time savings of 70%.

The IO timing results showed that the average read time dropped from 0.279 milliseconds to 0.264 milliseconds per read, the major change being in the average write time which dropped from an average of 5.646 milliseconds to 1.324 milliseconds. Putting this in perspective, for a process which writes out 1,000,000 records this could be a savings of 1 hour and 12 minutes if they were all single record writes. Of course, Oracle batches writes so the actual savings will be less.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle-SQL Guide: Look out for Fragmented Indexes
   Effectively Prevent Oracle Minimum Downtime Migration Error!!
   Data Warehousing – Do or Don’t?
   Improve performance by Optimizing SQL!!