Re: i/o parameters on oracle and sun

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 20 Jan 2001 01:14:20 +1100
Message-ID: <3a684b9d@news.iprimus.com.au>

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3A643625.979_at_yahoo.com...
> 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

It *is* true that a large multi-block_read paramteter will delude Oracle into thinking that full tablescans are much cheaper in terms of I/O than it might otherwise think. That would accordingly persuade the optimiser to favour full tablescans (which scan via multi-block reads) over index accesses (which don't).

HJR
> >
> > 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"
Received on Fri Jan 19 2001 - 08:14:20 CST