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: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Thu, 02 Feb 2006 14:43:15 -0500
Message-ID: <nom4u1hajl4hc9579kcj60bfhhv1iv7m6q@4ax.com>


On 1 Feb 2006 17:12:30 -0800, bdbafh_at_gmail.com 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 Received on Thu Feb 02 2006 - 13:43:15 CST

Original text of this message

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