Hi Richard,
Did you test the effect of Nocache after caching ?
What we noticed is "cache followed by nocache" is not
making the blocks to be flushed out. This has been
that way for months now in a production database of
ours.
Thx,
Ravi.
- Richard Foote <richard.foote_at_bigpond.com> wrote:
> 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"
> <ORACLE-L_at_fatcity.com>
> 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
>
=== message truncated ===
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ravi Kulkarni
INET: nandagokul_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).
Received on Wed Dec 31 2003 - 17:34:24 CST
