Re: Bigger blocksize on index TBS

On 14 jan, 14:54, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "sybrandb" <sybra..._at_gmail.com> wrote in message
>
> news:f8469d48-a6f8-4bdb-8af0-448e57f6f4e6_at_s12g2000prg.googlegroups.com...
> On Jan 14, 1:23 pm, Helma <helma.vi..._at_hotmail.com> wrote:
>
> > Hello everyone,
>
> > How to investigate in advance if a bigger blocksize on an index
> > tablespace will benefit performance?
>
> > Thanks for pointers
> > H.
>
> It will NOT benefit performance. There will be less I/O, but blocks
> will be bigger: the number of bytes being read will be identical.
> This has been recently concluded from a post by Don Burleson on OTN,
> promoting bigger blocksizes, which was exposed by Jonathan Lewis and
> Richard Foote as an incorrect assertion.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> Sybrand, your observation is incorrect.
>
> A bigger block size MAY benefit the user but is unlikely to do so
> for a variety of reasons.
>
> In this case the OP is taking the sensible approach and asking for
> suggestions on how to INVESTIGATE before simply doing it.
>
> If there is a good post on OTN covering the points that they
> need to consider, why not post the URL, it will save people
> from typing in the pros and cons yet again.
>
> Alternatively, the OP could go to Richard's blog
> http://richardfoote.wordpress.com/
> and his big presentation on indexes:
> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebui...
>
> For the OP, a few ideas:
>
> Pros
> Bigger blocks mean more entries - so if you typically have
> range scans that cover lots of leaf blocks and don't visit the
> table, then a bigger block size may help
>
> Cons
> Bigger blocks mean more entries - so if you have a lot of
> insert/update/delete activity on the index, you increase the
> chances of leaf block contention.
>
> If you double the block splits are twice as expensive on
> undo and redo and twice as wasteful of space - although
> only happen half as often.
>
> If you have different sizes of blocks in the database, you have
> different sizes of cache - which tends to mean less effective
> use of cache by the internal LRU/TCH mechanisms and a
> need for closer DBA involvement (at least in the initial period).
>
> So the first part of the investigation would be:
> Do you have any reason to think that any of your indexes
> are 'interesting' or 'special' in the way they are used.
>
> If so, what would happen on updates if you doubled/halved the
> block size, what would happen on queries if you doubled/halved
> the block size.
>
> To test the effect properly you actually have to have a full scale
> size and concurrency test, because the side effects are only
> likely to show up under full cache management load. (Although
> for some special cases you may be able get a reasonable degree of
> confidence from a relatively simple model).
>
> Frankly, though, if it's not obvious that an index is currently
> behaving
> really badly, it's probably not worth the effort.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thank you for the pointers. I've read opinions about the use of different blocksizes, but they were often anecdotical. I don't have the opportunity to test if it makes a difference - the database is just too big for such high impact experiments with low revenue expectations.
I think i've got bigger fish to catch.
thanks again,
H. Received on Mon Jan 14 2008 - 08:47:03 CST