RE: recycle and keep buffer in other tablespace block sizes
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Jul 2004 14:11:18 -0400
excellent point. ignore my previous musings regarding temporary confusion of
cache flag and pools.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Richard Foote
Sent: Thursday, July 22, 2004 8:14 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: recycle and keep buffer in other tablespace block sizes
----- Original Message -----
From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 22, 2004 1:23 AM
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
