Re: 9i multi cache buffer

"Mark Townsend" <markbtownsend_at_attbi.com> wrote in message news:B9FAE621.283B%markbtownsend_at_attbi.com...
> in article jmbB9.77282$g9.218121_at_newsfeeds.bigpond.com, Howard J. Rogers
at
> howardjr2000_at_yahoo.com.au wrote on 11/15/02 10:59 AM:
>
> > My troubles start when Oracle introduces a new feature (such as multiple
> > blocksizes or ASSM) which then get touted as the latest cure-all and
hence
> > get implemented without due consideration.
>
> We never, ever, ever intended multiple blocksizes to be used primarily as
a
> performance enhancement - it was done to make transportable tablespaces
easy
> to use between an OLTP and DW system, where the 'in use' blocksizes may
> vary. And then, the aim is that you would use it only to stage the moved
> data, and then merge or CTAS into the actual warehouse data.
>
> As a side note, during the original inhouse discussion of the feature,it
was
> mentioned that multiple blocksizes did also have some performance tuning
> advantages - unfortunately, this then became over emphazised in the
training
> and the doc, and has fast become another Oracle myth. This situation has
now
> been reversed, and newer updates to the doc and training deliberately
under
> emphasize the performance value of this feature.
>
> The reality is that for most workloads (and I'd dare to say all customer
> workloads), the performance benefits from multiple block sizes will not
make
> a significant impact, and in fact, I doubt would be measurable at all.
>
> Hopefully this will close this thread (as it did the one that went through
> Oracle about 6 months ago on this same topic)
>

Hi Mark,

I understand the initial purpose and aim of multiblock sizes was to accommodate transportable tablespaces and I understand that performance tuning advantages was never it's main purpose in life. I also understand that such performance tuning advantages are often doubtful and somewhat dubious.

However, I'm with Jonathon with this in that it doesn't necessarily mean there are *no* tuning benefits with multiblock databases.

For a very basic example: If I was using a direct I/O disk system with a nice large and generally speaking desirable block size but had a large and very regularly randomly accessed table, I can see some performance benefits in such a table having a smaller block size. For a start, each and every I/O would be slightly more efficient and there would be some benefit in the way such blocks behave when subsequently cached.

The fact that such a table (and perhaps others with similar attributes) are the only blocks that can be loaded into the smaller block cache also means the cache can have "recycle" like characteristics.

How "measurable" such benefits are of course need to be determined and could quite well be negligible. But then again, maybe not ...

I guess my concern with some of the things that have been said in this thread is that although suggestions that multiblock tablespaces don't offer tuning benefits is generally speaking true, it's not necessarily *always* true. We need to be careful that in our eagerness to destroy one myth, we don't create another in it's wake.

Just my thoughts on this fine Monday evening ;)

Richard Received on Mon Nov 18 2002 - 07:10:48 CST