Re: Myth revisited ...

"Hans Forbrich" <forbrich_at_yahoo.net> wrote in message news:3FB6E237.FA9608F_at_yahoo.net...
> At the risk of being shot, drawn and quartered:
>
> I know (and agree with) the fundemental discussion that separating
> indexes and tables into separate tablespaces should not be done for
> performance reasons - in pre-Oracle9i environments!
>
> However, with Oracle9i and it's support for multiple block sizes: Is
> there a possible performance benefit to be obtained by placing the
> tables and [some] indexes in separate tablespaces, IF the tablespaces
> have different blocksizes?
>
> (If this has been previously discussed, please just point me to the
> approximate time frame so I can review the archives.)
>

Hi Hans,

I hate to be a party poop, but there's a fundamental issue that everyone has missed thus far.

The vast majority of Oracle databases sit on top of O/S file systems and file systems use buffered I/O (unless direct I/O is set). Therefore, although having large block sizes for indexes sounds like a great idea in principle, in practice it will actually *hurt* performance. The correct, optimal block size for these environments is the I/O buffer size. Setting the block size any larger, say twice the I/O buffer size (eg. 8K on AIX) would result in every logical I/O on the index requiring two O/S calls. Having double (or more) O/S calls per logical read on your indexes is going to have a detrimental effect, even if such larger blocks succeed in reducing the index's overall height.

The whole thing is counter productive and will have a negative impact on overall performance. The concept of having multiple block sizes to aid *performance* is generally questionable. Multi block sizes are there to aid transportable tablespaces, not performance per se.

That said, there are *very* specific examples where multi sized blocks may provide some benefit. Interestingly going the other way, small block sizes for many numbers of tiny sized, read only lookup tables (smaller than a 'small' block, say 2K) as commonly defined in large financials type applications could be advantages, but I emphasize the benefits are not generally significant. And any benefits in reading and effectively caching such data needs to be carefully balanced with any writes that may occur (hence why R/O is better).

With raw I/O, larger is nearly always better. Not necessarily just for indexes, but for larger row tables, ASSM objects, undo segments, higher data density, etc.

In summary, using multi sized blocks to aid performance, especially in buffered I/O environments is likely to end in tears.

Cheers

Richard Received on Mon Nov 17 2003 - 06:50:13 CST