Saturday 6 March 2010

multible blocksizes in an Oracle Database


In short: Not useful.

More elaborated: Messy, waste of memory-space and admin-effort.


Let me explain:

I've come across this discussion again and I considered myself lucky that Charles Hooper has done all the research already.

I'm summarizing his findings here:

Multiple Blocksizes do NOT offer Any Proven Advantage.

The theory about more efficient indexes and better managed cache is good, and I dont deny there is good reasoning behind it. But in practice, having multiple blocksizes and multiple db-nK-caches doesnt make a difference.

It is a waste of the extra (little bit of) work.

And most likely, you end up wasteing cache-space because you hard-divide the cache space into chunks and you prevent the Oracle LRU-mechanism from utilizing all available cache as it sees fit.

Of course, you may be the exception that proves the rule, and I would like to hear from you in that case, but until further notice, I'll stick with my motto:

Simple = Better.

2 comments:

Colin 't Hart said...

I always thought the only reason to have multiple blocksizes was to support transporting tablespaces between instances with differing default block sizes?

PdV said...

Hi Colin, long time not seen.

You are correct, and I also remember that: the original reason to have multiple blocksizes was to allow TTS with other blocksizes to be plugged into a database.

however, you cannot stop an engineer from tinkering with a feature for whatever purpose he can think of. And the Multiple blocksize feature _could_ potentially be used/abused to control cache-behaviour. Not to mention the supposed benefit of having indexes in XL blocks.

All good fun to try.
But in the end, simplicity is most often the best option.