Re: Can we keep a table in memeory for faster access? Oracle 10g

From: joel garry <>
Date: Tue, 4 Mar 2008 09:53:00 -0800 (PST)
Message-ID: <>

On Mar 4, 7:12 am, Mark D Powell <> wrote:
> On Mar 3, 3:28 pm, Robert Klemme <> wrote:
> > On 03.03.2008 20:15, DA Morgan wrote:
> > > Sam wrote:
> > >> Hi There,
> > >> I have a table with heavy select queries on it, with almost no Insert
> > >> or Update.
> > >> Is there a way to make Oracle keep this table or a View of this table
> > >> in memory(RAM)? for faster access?
> > >> I have enough RAM on the system and tables are not that big,
> > >> Any suggestions?
> > >> I use:
> > >> Oracle: 10g
> > >> OS: Windows Server 2003
> > >
> > > Look up entries related to CACHE and POOL.
> > Well, Oracle will cache tables anyway so it might not even be needed to
> > pin a particular table in memory.  Sam, why do you think you need to do
> > this?
> > Kind regards
> >         robert
> I agree with Robert in that before making use of the cache hint within
> a query or the Keep Pool for storing the table blocks in the buffer
> cache (See Daniel's referenced material) you want to be sure you
> really need to do this.
> If the table is small and the blocks are heavily referenced then the
> table blocks will have a tendency to hang around in the buffer cache
> as modern Oracle uses a touch count on the blocks to determine the
> blocks position in the LRU chain.
> HTH -- Mark D Powell --

An older (and therefore somewhat suspect, though it has demos to test) interesting discussion about this: I'm sure some googling would turn up some more modern discussion.

I'd add that all tuning is an iterative process. Someone starting with a modern system that has not had its buffers inspected for actual usage may be futzing with something that has no need to be bothered, or may have a quite thrashed SGA. My experience with packaged enterprise software has been that moving a few critical objects to a recycle pool makes a big noticeable difference, but I the heavily accessed lookup tables seem to be kept in the ordinary course of events if the SGA isn't thrashed - and it really shouldn't be with a modern large memory system. YMMV severely, of course.

Something about the CACHE docs specifying full table scans on lookup tables bothers me, but I'm not sure what. Perhaps I'm expecting random lookups for inserts to precede tell-all reports.


-- is bogus.
Please it does not affix propagandas of the work on this group. The
place appropriate to announce he is in the group  It removes its announcement please
and one excuses it the group. The group is English only, it does not
affix on other languages.
Received on Tue Mar 04 2008 - 11:53:00 CST

Original text of this message