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

On Mar 4, 12:53 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Mar 4, 7:12 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Mar 3, 3:28 pm, Robert Klemme <shortcut..._at_googlemail.com> 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  10.2.1.0
> > > >> OS: Windows Server 2003
> > > >http://www.psoug.org/reference/tables.html
>
> > > > 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:http://groups.google.com/group/comp.databases.oracle.server/browse_th...
> 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.
>
> jg
> --
> @home.com is bogus.
> Please it does not affix propagandas of the work on this group. The
> place appropriate to announce he is in the group
> comp.databases.oracle.marketplace.  It removes its announcement please
> and one excuses it the group. The group is English only, it does not
> affix on other languages.- Hide quoted text -
>
> - Show quoted text -

In Joel's link Richard Foote explains the "new" touch count feature of the LRU chain. The feature being "new" in the sense that most DBA's still seem unaware of the change to the LRU logic.

Good find Joel.

HTH -- Mark D Powell -- Received on Thu Mar 06 2008 - 13:18:06 CST