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:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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" > <[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 > === 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: [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).
<<winmail.dat>>
