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
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 10:59 PM
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
