Re: Bigger blocksize on index TBS

From: Helma <>
Date: Mon, 14 Jan 2008 06:47:03 -0800 (PST)
Message-ID: <>

On 14 jan, 14:54, "Jonathan Lewis" <> wrote:
> "sybrandb" <> wrote in message
> On Jan 14, 1:23 pm, Helma <> 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
> and his big presentation on indexes:
> 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 Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ

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

Original text of this message