I have a rule - don't hand out advise with
limited information - it may be "factually challenged". and sometimes I break my
own rules - as in this case. But then again, this is not "advise"; merely my
opinion based on the very limited background information I had.
I agree - the panacea to an index "buffer busy
waits" is not simply the block size; rather a complex co-determination of
several factors and solutions like reverse key indexes, block size, free lists,
elimination of synthetic keys, the load pattern, etc. But given
that
- selects are 60% of the queries
- transaction rate is high to very high
- OLTP is primary for performance tuning
what options do we have? Elimination of synthetic
keys _MAY_ not be an option in this case, due to third party application, etc.
Reverse key Indexes have their own problems - they consume more space and
sometimes the optimizer does a index full scan instead of a row get. So the
only thing a DBA can possibly do at this time is in block sizes. Again, this is,
of course, my assumption.
So given the information by the questioner - the
biggest bang for the buck is by specifying the smaller block size. It may
however be different if a bench mark is performed at the site using the specific
customer profile.
As to the article, Vivek, I read it thoroughly and
I still stand by what I said. The author primarily advocates that
- more blevels are bad for performance
- a higher block size contains more rows per block
- more rows implies less blevel
- and, therefore, a large block size implies better performance
However, this probably ignores the buffer busy
waits condition. More rows in a block implies that two sessions are more likely
to access rows in the same block - creating a buffer busy condition - more so in
a RAC environment. So I advocate less number of rows in a block. The author also
relies heavily on the index rebuilds a lot to eliminate the blevels, not just
block sizes.
Caveat - see the most waits occurring in your
system - if after a smaller block size your index "db file sequential read"
increases, then you are better off using a larger block size and suffer buffer
busy waits. Benchmark, benchmark. There is no substitute for benchmark specific
to your situation.
In my shop, we have 9.2 in a DW environment where
the block size is the same (16K) for indexes and tables. For other two OLTP
based apps, I have defined 8K tables and 4K indexes for a third party app. I did
a benchmark to see the effects and that was the conclusion. Unfortunately I
didn't save it.
HTH.
Arup Nanda
----- Original Message -----
From: "Jonathan Lewis" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Wednesday, December 18, 2002 6:19
AM
Subject: Re: db block Size for Indexes Tablespaces
in 9.2 ?
> I think there are too many generic arguments
> available for picking the 'right' block size for
> your indexes.
>
> The one that is most appropriate is likely to
> depend on the nature of the activity (load
> vs. query), nature of the index (unique,
> nearly unique, far from unique), data clustering,
> order of data arrival, frequency of data arrival,
> pattern of data deletion/update, stability of volume,
> nature of queries (big or small range scans),
> potential of modifying number of branches,
> buffering effects, and whether or not you are
> using a filesystem with or without direct i/o.
>
> Given another 10 minutes I might come up
> with a few more ideas.
>
> Your strategy should be to identify the extreme,
> and critical, characteristics of your system and
> play to them - small block size may be appropriate,
> reverse indexes may be appropriate, getting rid of
> the synthetic key that is likely to cause a problem
> may be appropriate. But don't assume that anything
> as trivial as tweaking a block size is a driving
> feature of making your index work well.
>
> Which test case would you like to see - the one
> I did for company X that showed they needed a
> small block size, or the one I did for company Y
> that showed they needed a large block size ?
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: 18 December 2002 05:59
>
>
> >Hi Arup , List
> >
> >Your point is Correct about High "buffer busy wait" Contention During
> Large OLTP Insert /Updates.
> >
> >High "buffer busy wait" on Corresponding INDEX during INSERT
> Operations was Observed
> >during our previous benchmark which overcame by Converting to REVERSE
> Index as the Field Value
> >was Sequentially Increasing .
> >
> >Following Article advocates HIGHER Block Size for Index , Strangely
> :-
> >
> >http://www.tusc.com/oracle/download/author.html#loneyk
> >
> >What may be the Commonly followed Best practice for DB_BLOCK_SIZE for
> Index Tablespaces in 9.2 ?
> >
> >Thanks
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Lewis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
