Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle IO tuning tips/practices on Windows

Re: Oracle IO tuning tips/practices on Windows

From: <bdbafh_at_gmail.com>
Date: 1 Feb 2006 17:12:30 -0800
Message-ID: <1138842750.019797.128420@g44g2000cwa.googlegroups.com>


If I read the figures correctly, your average wait for single block io is 11.6 ms.
That is horrid.
I would think that direct attached scsi raid with relatively new hardware should have an average response time under 5 ms - and if a SAN with a decent size cache is used - around 1 to 2 ms.

But you're starting in the wrong place.
Tune the SQL first.
45741 blocks being accessed is likely too high, but as the SQL is not displayed here along with information regarding rows returned, row source stats, version for the db server software on this box that doesn't match your sig - we're just guessing.

As far as IO throughput, if you're only fetching 1 block at a time, you're not going to see 80 MB/sec. You didn't list how many controllers, controller channels and hard drives are in this configuration. Say that a drive supports 500 IOPs and you have 4 drives as RAID 10. The max throughput one would likely see would be 16.3 MB/sec - cache aside.

There is a paper on the hotsos site called "aligning blocks with stripe sizes" or something to that effect.
You might also be interested in James Morle's "Scaling 8i" that is downloadable from his website - check out the Oaktable site for links - http://www.oaktable.net
Juan Loiza's paper "How to start living and stop defragmenting" is a classic (introduces the SAME concept).

please list the following:

db server software version and patchset
db_block_size
number of SCSI RAID controllers and channels number of hard drives
RAID configuration (e.g. 8 disk RAID 10) stripe size (e.g. 256 KB stripe)
Read-ahead settings for the RAID volume
filesystem block size

An 8 drive RAID 10 volume of stripe size 256 KB would be a good start. with an 8192 byte db block size, a db_file_multiblock_read_count = 32 would read a full stripe. That doesn't help much with single block io, unless the blocks are prefetched and accessed before they are aged out.

If this is strictly an OLTP app you might want to consider a smaller stripe size, such as 128 KB. Make sure that the RAID stripe size is a multiple of the db_block_size.

What filesystem block size did you choose for the filesystem(s) that support your datafiles? I hope that it matches your db_block_size.

hth

-bdbafh Received on Wed Feb 01 2006 - 19:12:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US