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