Re: Memory-resident table -- VMS

In article <1993Jul21.084258.1_at_cbr.hhcs.gov.au> pihlab_at_cbr.hhcs.gov.au writes:
>In article <CABMFJ.Iz1_at_news.cso.uiuc.edu>, jfedorko_at_ncsa.uiuc.edu (Joel Fedorko) writes:
>>
>> While Oracle will not allow you to "lock" a small table in memory, to increase the
>> chance of that table being there, crank up you're SGA some since you're apparently
>> not hurting for memory, then fire up a detached process that does a select * from
>> table_name every so often. Use the logical/physical I/O requests from sqldba
>> (mentioned previously) to tune the interval that the detached process executes
>> the select. If your'e seeing 0 physical I/Os from your'e detached process then
>> your'e table is in memory. It's crude, but can be effective.
>
>I thought Oracle had "tweaked" their buffer use so that full table scans like
>the one suggested above didn't flush out the buffer cache. I can remember
>seeing it in some Oracle manuals/documents around the time Oracle V6 first
>arrived.
>

As I under stand it, Tables smaller than 5 oracle blocks are pushed into the SGA and put at the end of the timeout queue. So they are pushed out like every thing else.

But if the file is create than 6 blocks they are put at the begining of the queue and reused. This way the SGA is not flushed.

The stats of how offten this happens are stored in v$sysstat as

table scans (short tables) and
table scans (long tables)

So if the small in the small table means under 5 oracle blocks this this will work.

I didn't pay much attention to the original posting so I won't say that if the table is important enough to pin in memory, it is being accessed enough so that it should alreay be there and if it is so small reading it shouldn't be a big deal anyway.

But I wont say that :-)

-- 
Regards, 

Lee E. Parsons                  		Baker-Huges Inteq, Inc
Oracle Database Administrator 			lparsons_at_exlog.com