Re: 9i multi cache buffer

"Geoff Ingram" <geoff_at_dbcool.com> wrote in message
>
> Here's a different view.
>

Uh huh. Different, in this case, means wrong.

> Oracle announced a record breaking 4CPU TCP-C (Linux and HPUX,
> identical Intel hardware, Oracle10) to coincide with OracleWorld.

You believe all this marketing stuff? Really??

>This
> was achieved using multiple buffer caches:
>
> db_cache_size = 3500M
> db_8k_cache_size = 512M
> db_16k_cache_size = 3000M
> db_keep_cache_size = 37750M

Just hold on right there. In this "real world" test, it would appear we have getting on for 43 GIGAbytes of RAM in use. Mmmmm. This is not going to be telling us an awful lot that is of any relevance.

> db_recycle_cache_size = 50M
>
> TPC-C is an extreme OLTP workload characterised by small transactions
> and concurrent block access by multiple sessions. In this case a small
> blocksize is good - evidenced by the fact the TPC-C database uses
> db_block_size=2048.
>
> Smaller block size means latches for block access (data and index) are
> held for shorter time, increasing concurrency.

Of course the issue in OLTP environments, even "extreme" ones like this fairy-land of a test you mention, is concurrent access to data by many users. And the larger your blocks, the more of a concurrency problem you have.

If I have a headache, however, any suggestion that I cure it by chopping off my head is going to be effective, but a tad drastic. Likewise, curing the concurrency issue by picking a small block size is a ludicrous response that only (a) a bad DBA or (b) a marketing department in a hurry would do. We have PCTFREE, INITRANS and MAXTRANS to address concurrency issues in a rather more thoughtful and skilful way.

>For other workloads
> (e.g DSS) larger block sizes may better suit the usage profile of the
> data.
> Same for UNDO, for which Oracle used 8K blocks in the benchmark.
> What about a situation where your table has large rows (say 16K each)
> with small index entries (say a numeric primary key)? Do you really
> want them both stored in blocks of the same size?
>

Yup. The more index entries I can pack in to a block, the smaller my index will be. Small indexes mean faster reading, and are intrinsically more likely to be picked by the optimizer in the first place.

> Multiple buffer caches can significantly improve performance in the
> right circumstances.

TPC benchmarks prove nothing.

Multiple buffer caches ignore the fact that your database does not live in a vaccuum, but on a FILE SYSTEM. And File Systems have buffers. And any block size which doesn't match the buffer size is going to induce poor I/O.

I didn't make any of that up, and you are of course entirely free to ignore it.

>Only you can tell whether they apply to your
> situation.
>

That's the 'all morally equivalent' school of thought. Not in this case. There is a right and wrong answer.

HJR
> Geoff Ingram
>
> http://www.dbcool.com The all-free no-timeout Oracle tuning tool
>
> Author "High Performance Oracle"
Received on Thu Nov 14 2002 - 10:46:24 CST