Re: Oracle IO tuning tips/practices on Windows

On 1 Feb 2006 17:12:30 -0800, bdbafh@xxxxxxxxx wrote:

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.

I concur.


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.

The SQL gets various summaries... There is no doubt in my mind that
the SQL or the app could be tuned significantly, the plan is over 60
lines long and has more than a dozen 'nested loops' and a # of outer
joins. I don't really want to try to 'tune' it, as I am not going to
be too involved in this app (this is just a favor to someone, but I
know nothing about oracle on windows). I know this is a wrong approach
to 'tune' things, but I'd like to help to get the IO perform
adequately.


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.

The array is Dell PowerVault V220. There is a 128M cache.. more
details below

There is a paper on the hotsos site called "aligning blocks with stripe
sizes" or something to that effect.

I believe we already tried that (see below)


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).

Brand new array, nothing to defrag


please list the following:

db server software version and patchset

9.2.0.7

db_block_size
8K and 16K
number of SCSI RAID controllers and channels
1 and 1 (I think)
number of hard drives
12
RAID configuration (e.g. 8 disk RAID 10)
12 disk RAID10 (6disk stripe)
stripe size (e.g. 256 KB stripe)
64K
Read-ahead settings for the RAID volume
adaptive read ahead and direct I/o for write

filesystem block size
yet unknown..

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.

Their db_file_multiblock_read_count is set to 8. Which is fine for an
8K block size and 64K stripe unit size. I don't really know how the
parameter works with you also have 16K tablespaces.. I advised playing
with the parameter (e.g. set it to 8x6disks=48), but it didn't seem to
help much.

Their SGA size is set like this:
db_16k_cache_size 1367343104
db_cache_size 209715200

The majority of stuff is in the 16K tablespaces I guess
(You can see I don't know that much about the system, I've just seen
some trace files and statspack reports)


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.

This is a 'DSS'.

A little more background: the 'customer' has been trying to offload
reporting off the main 2 databases. They're using some 3rd party tool
to combine the 2 databases into 1 'reporting' database. What they have
found though, is that their SAN (where the production databases are
sitting) way outperforms the little 'reporting' SCSI array they got.
As you and others have noted, this relatively new hardware should be
performing much better.


........
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email
.

Relevant Pages

  • Re: Measuring IOPS and Raid penalty
    ... Why 3 storage groups and 6 databases? ... reason for this is that log bufferes are allocated on a per storage group ... RAID 10 4 drives SG1 Databases ... Megacycles per mailbox and IOPS per mailbox change ...
    (microsoft.public.exchange.admin)
  • Re: New PCI IDE controller or new mainboard - advice needed
    ... Currently '\Program Files' is a junction that points to a folder on a RAID ... Installing to stripe will also allow windows to start faster and all I/O ... When I purchased the two new IDE disks 2 months ago, ...
    (microsoft.public.windowsxp.hardware)
  • Re: Poor raid 1 performance?
    ... I basically assumed a 1-sector stripe ... > Or that the stripe size is lower than available free cache on the RAID ... > delivered to the main system by the RAID controller, ... interface mismatch between drives and host interface. ...
    (comp.sys.ibm.pc.hardware.storage)
  • RE: Raid Config for Ex 2007
    ... Thus 9 databases across 9 storage ... The above config will let you have 4 SG's split evenly across two Raid 5 ... We have a Barracuda SPAM filter, and an OWA server. ...
    (microsoft.public.exchange.setup)
  • Re: Fibre Channel Storage - Access by multiple hosts
    ... disksuite to provide the stripe as the software RAID would probably be too ... I see the hardware external RAID controller as they ... > all of our JBODs with EMC's disk arrays with hot swapable disks. ...
    (comp.unix.solaris)