Re: Cache a table

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 23 Oct 2003 21:54:02 +1000
Message-Id: <3f97c125$0$24515$afc38c87@news.optusnet.com.au>

Howard J. Rogers wrote:

> Geomancer wrote:
> 

>> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
>> news:<3f9660e8$0$9554$afc38c87_at_news.optusnet.com.au>...
>>

>>> Disagree. The issue is not whether it would tend to want to stay in the
>>> buffer cache or not, but whether it is at risk of being dislodged by a
>>> rogue, huge, tablescan.

>>
>> Howard, "Dislodged" is another of those Myths you hate so much!
>>
>> In Oracle9i, full-table scans changed to make FTS rows go directly
>> into the PGA for the user, completely bypassing the block cache.
>>
>> Remember, back in Oracle7 there was a special recycle area at the LRU
>> end of the cache for FTS, (which could NEVER page-out MRU blocks).
>>
>> This was changed in 9i to put FTS blocks directly into the PGA because
>> Oracle knew that no other task would use them.
>>
>> In Oracle's OTN site we see the "Oracle Expert" article (your beloved
>> Mr. Niemeic) state "a full table scan is put at the cold end of the
>> LRU (Least Recently Used) list."
>>
>> I verified this with a simple experiment. Start a fresh instance,
>> read a small table, then do an FTS gainist a huge tables and run a
>> query against v$bh. You will NOTsee the FTS rows in the data buffer!
>>
>> It seems that this "dislodging" may be one of those urban myths!

Having re-read your post several times, I still can't quite work out what you're on about, so here's a test I've just done on Oracle 9i Release 2, Red Hat 9:

create tablespace bhtest
datafile '/u01/app/oracle/oradata/lx92/bh01.dbf' size 10m;

select file#, name from v$datafile;
SQL> select file#,name from v$datafile;

     FILE# NAME

---

         1          /u01/app/oracle/oradata/lx92/system01.dbf
         2          /u01/app/oracle/oradata/lx92/undotbs01.dbf
         3          /u01/app/oracle/oradata/lx92/example01.dbf
         4          /u01/app/oracle/oradata/lx92/indx01.dbf
         5          /u01/app/oracle/oradata/lx92/tools01.dbf
         6          /u01/app/oracle/oradata/lx92/users01.dbf
         7          /u01/app/oracle/oradata/lx92/bh01.dbf

[You might note the new datafile is number 7]

create table t1 tablespace bhtest
as select * from dba_objects; (7000+ rows inserted)

insert into t1 select * from t1 (repeat until you run out of space) commit;

[That's a big table now, with 49,680 rows, and approx. 10M in size]

startup force;
select * from scott.emp [14 rows... a small table, as you specified] select * from t1 (wait for the scrolling to stop)

select count(*) from v$bh where file#=7; COUNT(*)

---

    2304

Since datafile 7's only just been created, and since it only has one table in it, and since I did a small scan and then a FTS like you asked, what were you saying about blocks from a FTS not going into the buffer cache??

By the way, the blocks from EMP were no longer in my (non-multi-pool) cache: they'd been "dislodged" by the FTS.

You might want to repeat your test.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Thu Oct 23 2003 - 06:54:02 CDT