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

On Mar 7, 1:37 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Mar 7, 3:45 am, JACKY <zhp..._at_gmail.com> wrote:
>
>
>
>
>
> > 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 !!!- Hide quoted text -
>
> > - Show quoted text -
>
> Jack, no you cannot at least through version 10gR2 per the PL/SQL
> Packages and Types manual entry for dbms_shared_pool.  Tables are
> cached in the buffer cache while dbms_shared_pool works to cache
> objects in the shared pool.  While the buffer cache and the shared
> pool are both part of the SGA these are separate parts of the SGA and
> hold different kinds of objects.
>
> You can cache stored code: packages, procedures, and functions plus
> triggers, sequences, and types in the shared pool.  In prior versions
> you could also cache cursors in the shared pool.  I do not know if the
> failure of 'C' to be a parameter is a documentation error or a change.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Minor correction. For a cursor you just pass the address as the first parameter and Oracle understands it is a cursor so the second can be any of the other 4 valid values. Just been long time since I used that specific feature.

• Mark D Powell --