Re: Memory-resident table -- VMS

In article <CAMnCo.1C6_at_unixhub.SLAC.Stanford.EDU> ian_at_tethys.SLAC.Stanford.EDU (Ian A. MacGregor) writes:
>In article <1993Jul22.194739.27346_at_exlog.com>, lparsons_at_exlog.com (Lee Parsons) writes:
>|> In article <1993Jul21.084258.1_at_cbr.hhcs.gov.au> pihlab_at_cbr.hhcs.gov.au writes:

[...]

>|> >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.
>|>
>
>Lee, can you explain the use iof the SMALL_TABLE_THRESHOLD parameter under
>Oracle7. It was my understanding that this parameter controlled how many blocks
>of a table are placed in the most recently used end of the buffer list. All
>others go to the least recently used end. The default for this init.ora
>parameter is 4 blocks, thus any full tablescan of more than four blocks requires
>a physical reads. Is this incorrect?
>
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> (415) 926-3528

My comments above applied only to V6, when this behavior was still one of those widely known secrets. Ian is of course right. In V7, we now have an option in the init.ora to control all this.

At this rate all the useful information I have picked up over the years is all going to be put in the pfile by version 8. At least that gives me 10-15 years to move into another line of work :-}.

-- 
Regards, 

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