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).