Re: Bigger blocksize on index TBS
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 14 Jan 2008 13:54:48 -0000
Message-ID: <s4qdndcXy6s-9BbanZ2dnUVZ8ternZ2d@bt.com>
Date: Mon, 14 Jan 2008 13:54:48 -0000
Message-ID: <s4qdndcXy6s-9BbanZ2dnUVZ8ternZ2d@bt.com>
"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.htmlReceived on Mon Jan 14 2008 - 07:54:48 CST