RE: Advice about why not setting multi-block sizes
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: "'Juan Carlos Reyes Pacheco'" <juancarlosreyesp@xxxxxxxxx>
- Date: Tue, 2 Aug 2005 12:01:02 +0200
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@xxxxxxxxx]
Sent: Monday, August 01, 2005 22:24
To: Lex de Haan
Cc: Oracle-L@xxxxxxxxxxxxx
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.
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
