Re: On AIX, blocksize an DataWarehouses

We use AIX 5.3 for our databases and IBM recommends CIO and JFS2. This is better than direct IO.

Using different block sizes does not effect IO, an IO request for 8 disk bocks ( 4 x 8k block size) is the same to the IO subsystem as a 8 disk blocks (1 x 32k block size). To reduce your IO response time you need a fast IO subsystem and a good sized buffer cache. If you are getting single row queries then reading 8k of blocks is better than 32k of block reads but in a warehouse environment you will most likely be getting many full tablescans. What you gain with bigger block sizes is faster block processing. Each block has to be looked for in the buffercache has table (requires a latch), then either it is found in the buffer cache or disk. as oracle runs through all the buffers it wants it will send batches of blocks needed on disk to the IO subsystem and buffers in memory have to read the header, maybe do a current read and so on. When dealing your situation you have to realize that the total number of logical IO's consumes many resources and bigger blocks reduces the amount of work that has to happen for each block. Physical IO should be thought of as a missed LIO, and if your PIO is high you either need to do one of two things: Tune the sql to touch less blocks or increase the buffer cache to hold more blocks of data.

You also have large waits on your parallel queries, you may need to reduce the parallel queries so that you are not overloading the IO subsystem with more than it can handle. Making sure your files are appropriately spread out and your raid system is fast enough to handle your requirements will be the best for your IO, along with using CIO/jfs2 and filesystemio_options=setall. Using these options also reduces the AIX buffer cache which gives you more memory to use for the oracle buffer cache as well. You should really read the oracle datawarehousing guide:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/toc.htm

>From this point your next step is to tune queries.
Frank R. wrote:
> On 21 nov, 17:29, DA Morgan <damor..._at_psoug.org> wrote:
> > Frank R. wrote:
> > > Hello everybody,
> >
> > > I'm currently involved in a Siebel Analytics/Datawarehouse deployment.
> > > The datawarehouse database (9.2.0.8 AIX 5.3) was originally created
> > > with a 8kb blocksize (standard) which as far as i've read anywhere or
> > > asked anyone, is quite small, reccommending 32kb instead.
> >
> > > Apart from a stripping strategy between several disks, blocksize seems
> > > critical in I/O improvement and I need advice on the relationship
> > > between oracle blocksize and AIX blocksize.http://www.dizwell.com/prod/node/58
> >
>
> Thanks for the link. Very interesting, really.
>
> > > ATM, the OS blocksize is
> > > 4kb but, what would be the perfect size for it ¿32k too? ¿any oracle
> > > blocksize multiple?
> >
> > > Thanks in advance for your help.Based on what metric(s) are you concluding that block size has any
> > relationship to any performance issues you are trying to correct?
> > If you don't have numbers that support the conclusion you are most
> > likely going in the wrong direction.
>
> Yeah, I know, i need "much" more testing, but:
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> PX Deq Credit: send blkd 98,776 13,100
> 51.44
> db file sequential read 341,578 6,892
> 27.06
> PX Deq Credit: need buffer 53,359 3,447
> 13.53
> buffer busy waits 23,228 968
> 3.80
> log buffer space 1,012 333
> 1.31
>
> Also, 600 ms datafile read access seemed bad so, right now the data is
> being redistributed between all disks
>
> Moreover, I'm using async I/O, gonna try direct I/O (where
> db_block_size "doesn't matter") instead, and see the differences.
>
> Thanks again.
>
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
Received on Tue Nov 21 2006 - 17:09:51 CST