Free Oracle Tips

HTML Text
Oracle Consulting Oracle Support
Oracle Training
SQL Tuning Consulting
Oracle Tuning Consulting
Data Warehouse Consulting
Oracle Project Management
Oracle Security Assessment
Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Internals Magazine
Oracle Links
Oracle Monitoring
Remote Support Benefits
Remote Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices
 

Free Oracle Tips


 
HTML Text AOL
 
 

 

Tune Oracle for Concurrent disk I/O

Mike Ault
Senior Consultant
Burleson Consulting
 


This problem with concurrent access and RAID arrays is one of the most prevailing in the industry. The ubiquitous IO wait is usually the predominant wait event in any database system simply due to the fact that IO to memory is in the nanosecond range while IO to disk is in the millisecond range, when you add in blocked access due to multi-disk IO requests you get a snowball effect that can cripple your IO subsystem.

Array manufacturers have begun to recognize this concurrent access problem and have increased the base stripe unit per disk to 64K, matching the IO unit for many systems. Of course now systems such as
SUN and Windows utilize maximum IO sizes of 1 megabyte or larger, so again the array manufacturers are playing catch up to the server manufacturers.

So what is our second rule of tuning disks? Based on the above information the rule is:

Always ensure that the primary IO size for your database system is matched to the IO size of the disk array system.

Of course the inverse also holds true:

Always match the stripe unit per disk to the expected majority IO request from your (database) application.

In the 1990's Paul Chen of the University Of Berkeley computer center published a series of papers on tuning disk array stripe units size based on expected concurrency. In these papers by Mr. Chen and his associates they determined that the IO speed (as measured by average seek time) and IO rate (as measured in megabytes per second) for a disk determined the stripe size for performance in an array even when the number of concurrent accesses is not known. There were three formulae derived from these papers:

For non-RAID5 arrays when concurrency is known:

SU = (S*APT*DTR*(CON-1)*1.024)+.5K

Where:
 

SU - Striping unit per disk
S - Concurrency slope coefficient (~.25)
APT - Average positioning time (milliseconds)
DTR - Data transfer rate (Megabyte/sec)
CON - number of concurrent users.
1.024 = 1s/1000ms*1024K/1M (conversion factors for units)


So for a drive that has an average seek time of 5.6 ms and a transfer rate of 20 Mbyte/second the calculated stripe unit for a 20 concurrent user base would be:

(.25*5.6*20*(19)*1.024)+.5 = 545K (or ~512K)

For a system where you didn't know the concurrency the calculation becomes:

SU =(2/3*APT*DTR)

So for the same drive:

2/3*5.6*20*1.024 = 76.46K so rounding up ~128K or rounding down 64K

And from Chen's final paper, a formula for RAID5 arrays is:

0.5*5.6*20*1.024 = 57.34 (rounding up 64K)

The values for average access time and transfer rate used in these examples is actually fairly low when compared to more advanced drives so the stripe sizes shown above are probably low by at least a factor of 2 or more. I say this because will 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.

To summarize all of the above material:

1. Disks in an array should be based on IO needs
2. Stripe width would be based on concurrency needs
3. 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.

Generally, a system that depends on single block reads and writes (OLTP) with single point index reads (not scans) 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).

 

This is an excerpt from my book "Oracle Disk I/O Tuning, and it's only $19.95 at this link:

 

http://www.dba-oracle.com/bp/bp_book15_oi_disk.htm


Need Oracle Tuning Support?

Burleson Consulting now offers a one-day performance review for your Oracle database.  Working with top experts who tune hundreds of databases each year, you can get fast expert tuning advice to hypercharge your Oracle database.

We also provide expert upgrades to Oracleand Oracle10g, and our DBAs can quickly show you how to implement the important new features of new Oracle releases.

Call now for remote Oracle support.

 

 

 
 Hot Oracle News

Rampant books 50% off sale

Free Oracle10g reference poster

Oracle Asia pushes outsourcing

Oracle Corporation Seeking Evangelist

 

 

 

Burleson Consulting
 Kittrell, NC, 27544

Email: info@remote-dba.net Phone (800) 7676-1884

Copyright 1996 - 2015 by Burleson , Inc. All rights reserved.