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
 
 

 

Understand Oracle I/O profiles

Mike Ault
Senior Consultant
Burleson Consulting
 


In order to get the maximum performance from your disk system you must understand the IO characteristics (the profile) of your database system, be it Oracle, SQL Server, Informix, UDB or MySQL. You must tune your disk architecture to support the expected IO profile and must tune the database system to take advantage of the disk architecture. For example, an Oracle database has different IO characteristics depending on whether it is reading or writing data and what type of read or write it is doing. Other databases have fixed read/write sizes.

You must determine the IO profile for your database and then use the IO profile of the database to determine 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.

Once you have the IO per second you can determine the IO capacity (number of drives) needed to support your database.

The first rule of tuning your disk system is:

Size first for IO capacity, then for volume.

Some back of the envelope calculations for the number of spindles needed to support IO rate are:

RAID10 with active read/write to all mirrors:

MAX(CEILING(IOR/(NSIOR*M),M),2*M)

Where:

IOR is expected maximum IO rate in IO/sec
NSIOR is the average non-sequential IO rate of the disks in IO/sec (range of 90-100 for RAID10)
M is the number of mirrors

(The maximum of the IO rate divided by the average non-sequential IO rate per disk times the number of mirrors to the nearest power of M or 2*M)

RAID5 assuming 1 parity disk:

MAX((IOR/CNSIOR)+1,3)

Where:

IOR is expected maximum IO rate in IO/sec
CNSIOR is the corrected average non-sequential IO rate of the disks in IO/sec (range of 60-90 for RAID5)

(The maximum of the IO rate divided by the average non-sequential IO rate per disk corrected for RAID5 penalties plus 1 disk for the parity disk)

The correction for the non-sequential IO rate for RAID is due to the up to 400% penalty on writes (writes take 4 times linger than reads on the same drive). In some cases on RAID5 I have seen this go as high as 6400% (writes take 64 times as long as reads for the same file) when combined with other problems such as fragmentation.

A case in point, early RAID architectures utilized the "stripe shallow and wide" mind set where files where broken into small pieces and spread over a large number of disks. For example, stripe unites per disk of as small as 8K were common. Many system read in IO sizes of 64K or larger. This means that to satisfy a single IO request 8 disks of the RAID set were required, if there were fewer than 8 disks in the set. disks would have to undergo 2 or more IOs to satisfy the request. This sounds fine if you are talking about a single user wanting to read a large file from a large group of disks very quickly, however, what happens when you have 10 or 100 or 1000 concurrent users all wanting to do the same thing? 

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


 

 

 
 Hot Oracle News

Mike Ault Joins Burleson Consulting

Rampant books 50% off sale

Free Oracle10g reference poster

Oracle Asia pushes outsourcing

Oracle Corporation Seeking Evangelist

 

 

 

Burleson Consulting

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

Copyright 1996 - 2015 by Burleson . All rights reserved.