Re: Pin a table in memory

R. Wayne Linton <linton_wayne/SCAN6_at_shell.ca> wrote in article <5cqq9g$ott_at_nr1.ottawa.istar.net>...
> A cached table will be loaded into the 'most frequently accesses data'
> end of the SGA data buffers upon the first time that a FULL TABLE SCAN is
 

> done on the table. This is not a guarantee that the data will stay in
> the buffers forever, but significantly improves its chances of being
> there when you need it. typically the small reference tables are
> frequently accessed so would tend to stay in memory once loaded by the
> table scan.
>
> --
> R. Wayne Linton, I.S.P.
> Database & Systems Management
> Shell Canada Ltd.
>

There is a way to ensure that a table stays in memory, with Oracle7 (not sure of which sub-release. Check your doc.)

ALTER TABLE tablename CACHE;

This will "mark" the blocks at the head of the LRU chain and ensure that they don't get paged out. Good for small lookup tables, etc. Careful that you don't do this to a very large table, or your buffer cache will fill in a hurry!

-- 
Gregory A. Burris
Practice Manager, Performance and Architecture Group
Oracle Services Central Area
Troy, MI  810-614-5029