Re: Disk I/O

10 ms is not a normal average wait time for a setup using EMC Clarion storage.
I see averages under 2 ms for systems not heavily loaded in v$filestat and the resulting statspack reports.
Stripe size, dbfmbrc, block_size, filesystem block size all come into play here.

Perhaps your database blocks are fractured across multiple drives in the RAID stripe.
Perhaps your stripe size is too fine.
You had best examine the RAID configuration of the "3 RAID 5 volumes". If those are just 3 x 3 physical drives, you might want to consider rebuilding that as 8 x 1 RAID 10 with a hotspare.

check out filemon from the sysinternals site http://www.sysinternals.com.
You'll be able to observe/log file IO requests. This will be helpful in terms of understanding why you want to place controlfiles on a 16 KB block size filesystem, online redo logs on a 512 byte filesystem, data blocks for an 8 KB tablespace on a 8 KB filesystem and observing the actual physical IO (as far as the operating system is concerned).
w2k adv svr does support an IO size of 1 MB, so for a db_file_multiblock_read_count of 128 and a db_block_size of 8KB, you can actually get a 1 MB read for full table scans and fast full scans, as well as sorts/hash joins to disk.
But putting the online redo logs on a 1 MB stripe? sounds bad to me. Putting the controlfiles on a 1 MB stripe? sounds bad to me. Placing all files on the same filesystem that has one blocksize to fit all? sounds bad to me.

The effect of setting the db_file_multiblock_read_count to effect a (FTS, FFS) read of 1 MB is an entirely different matter and may cause hugely adverse effects on execution plans in 9.2.

All this aside, what you want to concentrate on is the Oracle Wait Interface.
Gather statspack data and examine what the top wait events are, and start from there.
In this manner you will have evidence as to what the problem is and be able to predict what the potential improvement will be prior to making the changes.
Think of this as good ammo that will be useful to counter the Storage Engineer/SysAdmin that could be resistant to effecting any changes in the existing system.

check out the library at hotsos.com. there are a few papers there that will interest you.

hth.

-bdbafh Received on Sat Mar 19 2005 - 15:18:41 CST