| 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: Cache a table
Craig Shalahamer still refers to the cache as LRU/MRU. What has changed
are the algorithms that determine the lifespan and placement of a buffer
in the cache.
www.orapub.com
Jared
Richard Foote <richard.foote_at_bigpond.com>
Sent by: ml-errors_at_fatcity.com
10/23/2003 08:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: Cache a table
Hi
It depends on how you define an LRU list I guess. When I close my eyes and
picture the cache, I still see a LRU in there somewhere. Please note I
don't
often close my eyes in this manner ;)
Also when you say that the CACHE option has no effect, that's also a
little
questionable. This is just a portion of a post I recently sent to
comp.databases.oracle.server in the "Cache A Table" thread:
Simple demo on 9.2, the BOWIE table is approximately 13,000 blocks, SMALL
is
117 blocks:
SQL> alter table bowie nocache;
Table altered.
SQL> select object_name, object_id, data_object_id from dba_objects where
object
_name in ('BOWIE', 'SMALL');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID --------------- ---------- --------------
BOWIE 31379 31379 SMALL 31457 31457
SQL> select * from bowie; (run with autotrace traceonly)
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
18
Note that only the last few blocks from the FTS actually remain in memory.
If I repeat the select, I still have the same result from x$bh and the
same
number of *physical reads" occur each time.
If I run the same thing with my "small" table which has about 117 blocks, the same thing happens ....
SQL> alter table small nocache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
18
Note that again only the last few blocks from the FTS actually remain in memory. If I repeat the select, I still have the same result from x$bh and again the same number of physical reads occur each time.
OK, lets change my small table and cache the thing and see if I get a different result ...
SQL> alter table small cache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
117
I now see that all 117 blocks (that's all data blocks + segment header)
are
all now cached as expected. Repeated reruns of the select now generate
*no*
physical I/Os.
But what if I now run a select on my "big" BOWIE table, what effect will this have on the SMALL cached blocks ?
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
18
Nothing new here, only the last few blocks again remain from the BOWIE
table
with the same physical I/Os generated.
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
117
and thankfully nothing has changed with the SMALL table as a result. These
blocks still remain cached and have not been "dislodged" as a result of
the
FTS on the big BOWIE table (as they sit safely somewhere near the middle,
cold side of the LRU)
Finally, what if we play silly buggers and decide to cache the big BOWIE table ...
SQL> alter table bowie cache;
Table altered.
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
1338
We now see that a whole heap of buffers have now been cached,
approximately
10%. However, again the physical I/Os remain constant because we are
still
not effectively caching the table (the undocumented parameters behind the
scene kick in to prevent the whole cache from flooding).
But the effect on poor SMALL...
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
1
only one poor block (the header) has survived the experience :(
Hope this clears something up !!
Cheers
Richard Foote
> Mike:
>
> I guess we are aware there is no concept of LRU or MRU in current
> versions of Oracle and I don't think CACHE option will influence the
> behavior. With the new algorithm the MFU blocks are already in the hot
> end (unless they are read using CR read in that case they will be in
> cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
> need to cache the blocks explicitely.
>
> You can monitor the behavior of this using the X$BH (espicially the
> last two columns TCH and TIM).
>
>
>
>
> =====
> Have a nice day !!
> ------------------------------------------------------------
> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: richard.foote_at_bigpond.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Oct 23 2003 - 14:39:32 CDT
 |
 |