| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: recycle and keep buffer in other tablespace block sizes
excellent point. ignore my previous musings regarding temporary confusion of
cache flag and pools.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Richard Foote
Sent: Thursday, July 22, 2004 8:14 AM
To: oracle-l_at_freelists.org
Subject: Re: recycle and keep buffer in other tablespace block sizes
Hi Jurijs, thanks,
my default block size is 8K
If have a table in a 2k tablespace with the flag to be cached in the keep
pool.
How it is cached?
It takes 8K???? in the keep cache
Could you please give more information :)
Hi Juan,
I don't think anyone has yet explained the rather basic mistake with your logic above so forgive me if someone has already stated the obvious.
A segment (say table) belongs to the one tablespace and a tablespace can only have the one block size. Therefore it's rather impossible for 2 objects in the same tablespace to have differing block sizes and it's impossible for a table to change it's block size without moving it to a different tablespace.
With me so far ?
The cache or nocache of a segment (say table) is a characteristic of an individual object. It can be changed for a segment and different segments within the same tablespace can have differing cache characteristics (via of course the buffer_pool storage parameter).
You have also been correctly informed that the KEEP pool can only have the default blocksize and that there are no KEEP (or RECYCLE) pools for non-default blocksizes.
Therefore armed with the above knowledge, hopefully you can see how your comment "If have a table in a 2k tablespace with the flag to be cached in the keep pool" makes absolutely no sense.
There is *no* relationship per se between the cache characteristic/attribute of a segment and the KEEP pool. You have completely misunderstood the meaning of CACHEing a segment as it does not mean the segment is placed in the KEEP pool. It means that segment blocks when read via a FTS is not placed at the cold end of the LRU list (as they are by default) but are treated in a manner similar to those read via an index scan *in the buffer pool associated with the blocksize of the tablespace* in which the segment resides.
Make sense ?
I say similar because cached objects are treated somewhat differently when it comes to Oracle's touch count algorithm. Note also that the nocache attribute is somewhat meaningless for objects in the KEEP pool as the cache attribute is implied and so is ignored for segments assigned to the keep pool. Segments stored in the keep pool can truly be cached, providing the keep pool is sized appropriately. Segments that are simply "cached" don't have the same luxury.
Note that the above is generally totally ignored by those "experts" that promote the virtues of using multiple block sizes in a database for *performance* benefits. Not being able to make use of the KEEP or RECYCLE pools for non default block sizes, having to configure and manage multiple and *independent* buffer pools and having localised cache inefficiencies are rather significant disadvantages.
Note also that the justification of such multiple blocks is often stated simplistically (and incorrectly) as being a perceived benefit in the number of LIOs. I have see written numerous times that having an 8K blocksize table that takes 1000 LIOs to read can have it's LIOs cut in half, down to an incredible 500 LIOs simply by doubling the block size to 16K. Yes, double the block size and half your IO overheads is the implied benefit. No joke, this is some experts perceived "proof" that multiple sized blocks are a good thing. I won't insult your intelligence to find the rather obvious (for some) flaw with this logic but suffice to say the *differing costs* associated with *differing LIOs* are not generally discussed nor are the differences in *response times* which is a far more important consideration.
When you test the "benefits" of using differing block sizes, please benchmark and consider the differing *response times* of your *tuned* previous configuration, along with the various disadvantages discussed above. Fundamentally, does your new database configuration result in better response times without compromising scalability, manageability and other such considerations simply by having *different block sizes*, rather than simply having a *better "database" block size* (which is a totally different discussion in it's own right).
Be prepared to be disappointed ....
One final point. You may want to more closely consider your "main sources of knowledge". One is undoubtedly one of the worlds most knowledgeable Oracle experts. The other, well Mark rather said it all ...
Good Luck
Richard
 |
 |