RE: Advice about why not setting multi-block sizes

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 2 Aug 2005 12:01:02 +0200
Message-Id: <20050802100104.7527A1D9D29@turing.freelists.org>

you need to set up a test case, and try to prove your assumptions with real and reliable numbers. if you think something is faster, you already have the hypothesis -- so the only other thing you need is the proof :-) in other words: don't try to convince us with words, but give us numbers...

the fact is that if you create a cache for just one table, that memory is *completely* useless for anything else. if you create indexes in a tablespace with a non-default block size, the corresponding memory can *only* be used for indexes.

how on earth can you precisely predict how much cache memory you need for indexes versus tables? I am pretty sure this ratio is pretty unstable -- sometimes you perform more full table scans, sometimes you perform more index access paths. so are you going to monitor and change the subcache sizes all the time? possibly change them every day, when you switch from oltp activity to batch processing?

by segmenting the buffer cache, you make it more difficult for Oracle to do smart things with shared memory. it is as simple as that. also, we are all trying to move away from using ratios -- but you introduce a new one: the index/table cache ratio!

kind regards,

Lex.

Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----
From: Juan Carlos Reyes Pacheco [mailto:juancarlosreyesp_at_gmail.com] Sent: Monday, August 01, 2005 22:24
To: Lex de Haan
Cc: Oracle-L_at_freelists.org
Subject: Advice about why not setting multi-block sizes

Hi Lex,
If you can helpme to understand please

> I repeat that this is a *bad* advice, regardless whether your database
> is very big or very small.
> there are better ways to influence cache performance/behavior, without
> the disadvantages of multiple block sizes and a segmented buffer
> cache. a segmented cache needs constant attention and
> (re)configuration, and even then it always leads to suboptimal shared memory
usage.

My logic goes in this way, I have different system on the same database. One is an accountatn sytem and other is a software production system.

BLOB 32K TABLESPACE In the software production system therei s a table where I store in blobs all the fmb and rdf,

The accounting sytem has configured their buffers. If I create a different tablespace of 32K only for that table(table storing blobs), i think:
1) this is not going to bother memory configuration of accounting system. 2) this will improve reads, because forms and rdfs are big, bigger block size will be better.
3) This allows me to set a reduced memory to that units, for example I don't want oracle use 100 MB for this table, I want only to use 20MB, so I can restring more efficiently the memory for that specific table.

Now this table is used for few users(10), several times a day. not too frequently.

INDEX 16K TABLEPACE Our market is small, our tables are small, our system is fast enough. We have few customers, we don't have time to optimize every singly form we do, because we don't have time, and beacuse we don't need it. If we detect some performance problem we solve it, and the overal performance is ok. We use a good common sense design, and everything works ok.

I moved all indexes to a different tablespace for recovery purposes, this means if some day a block is damaged and this block is in the index tablespace, and there is no way to recreate it, I know I can recreate the tablespace and reindex, everything and all is ok.

I use a 16k block size because
1) 16k blocksize is better for index access. 2) I can reserve memory only for indexes, so I can give more memory for index access, because most of access are through indexes, not through full-scans. I can't optimize database per database, some customers have 4 database in the same server. So what I want is to guarantee an area for indexes. They use virtual memory and this works ok for them, they don't want to buy another server, they are happy so, finally is a small database.

Please Lex, could you helpme to, explaining your position about it: Why is wrong, how should I do that.

Thanks a lot for your advice.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 02 2005 - 05:03:03 CDT