That was what we expected. This is a highly active
PeopleSoft Database. Will it take several months to
push those blocks out ? Though not true, it appears
the nocache had no effect at all ..
-Ravi.
- "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> wrote:
> Well, that's not really a surprise, is it? If you
> do CACHE first, and
> cache all the tables blocks, then do NOCACHE, Oracle
> isn't going to
> immediately explicitly flush those blocks. I'd
> expect that as demand
> on the buffer cache increased, the blocks would age
> out. Oracle almost always
> follows the "delay any work I can till later, cause
> with any luck, I won't
> have to do it later, either!" rule.
>
> If you set the table to NOCACHE and then try doing
> other activity which will
> impose a load on the buffer cache, I'd expect to see
> (at least some of) those
> blocks age out.
>
> -Mark
>
>
> -----Original Message-----
> From: Ravi Kulkarni [mailto:nandagokul_at_yahoo.com]
> Sent: Wed 12/31/2003 6:34 PM
> To: Multiple recipients of list ORACLE-L
> Cc:
> Subject: Re: Cache a table
> 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(*)
>
=== message truncated ===
> ATTACHMENT part 2 application/ms-tnef
name=winmail.dat
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 Fri Jan 02 2004 - 09:59:32 CST