Re: Bigger blocksize on index TBS

"sybrandb" <sybrandb_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-rebuilding-the-truth.pdf

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
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html