Re: i/o parameters on oracle and sun

rkatofiasc_at_classifiedventures.com wrote:
>
> Thanks for your reply,
> Is it not true that having a large multiblock read count will effect
> the cost based optimizer into issuing full table scans as opposed to
> using the indexes? Also I read a white paper that stated that the
> strip width = interlace size * number of drives in the array = max i/o
> size
>
> example max i/o = 128 K and # drives in the array = 4
> stripe width = 128 K = interlace size 32 k * drives in array 4 = 128 k
> max i/o
> or
> max i/o = 1024 K and # drives in the array = 4
> stripe width = 1024k = interlace size 256 k * drives in array 4 = 1024
> k max i/o
>
> In article <3A62F300.6806_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > rkatofiasc_at_classifiedventures.com wrote:
> > >
> > > We are moving our data warehouse to a sun 4500 6X6 with 2 sun 5200
> > > (18G) drive arrays. Can you gurus give me some thoughts on what
 would
> > > be the optimal parameter sizes? I am looking for the db_block_size,
> > > strip width size, db_multiblock_size. I think we are leaning
 towards a
> > > 16 K block size. The file system will my managed using Veritas for
> > > Oracle (1+ 0 raid or 0+1). It seems the default is 16k for the strip
> > > width. Should the strip width change based on what the file is used
> > > for i.e data, index or logs. The data warehouse uses a
 multidimensional
> > > architecture, which minimize full table scans by using bitmapped
> > > indexes on the fact tables. I was hoping that someone might be
 working
> > > on a similar system and can give me some feedback.
> > >
> > > Thanks Rob
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> > Without full investigation into requirements etc...
> >
> > block size: 8k if on file systems, possibly 16k if on raw
> > multiblock read count = large as possible which means you probably
 want
> > to increase the maxphys setting in /etc/system, eg if maxphys = 256k,
> > and block size = 8k, then multi-read-count should be 32 (256k/8k)
> > Stripes typically same a single multiblock read
> >
> > But rest assured, hoping that things will work out well just be
 setting
> > a few parameters is a sure fire way to problems...
> >
> > HTH
> > Connor
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk (mirrored at
> > http://www.oradba.freeserve.co.uk)
> >
> > "Some days you're the pigeon, some days you're the statue"
> >
>
> Sent via Deja.com
> http://www.deja.com/

Yes - the larger the multiblock the more scans tend to be preferred. If you're app has more of dependency on nested loop performance, then could consider an optimiser mode of first_rows in init.ora or selected sessions.

I would have thought for a data warehouse that scans would tend to be the norm rather than exception

Similarly there are a lot of things to consider when planning the layout. For file system based db's, I tend to have a stripe size of one multiblock read (eg 128k), that is, 128k on each disk in the stripe, mainly because with file systems the data on each disk could all over the place anyway. With raw this could be avoided to some degree and thus smaller stripe sizes could perform better - but as you said, you would not want a single multblock read to be larger than no. disks * stripe size since you would get nasty 'wraparound'.

Then you get to the issue if you're doing a lot of parallel query - since a query gets broken up into many chunks, then having a stripe may actually hinder performance due to contention amongst the slaves - and possibly standard disk separation or using a concatenation may be better...

Lots of things to think about -

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"