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

On Mar 7, 3:18 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

 you can use DBMS_SHARED_POOL.KEEP function !!! Received on Fri Mar 07 2008 - 02:45:58 CST