Re: Memory-resident table -- VMS

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:
|> >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

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