Re: Can we keep a table in memeory for faster access? Oracle 10g
Date: Fri, 7 Mar 2008 10:37:06 -0800 (PST)
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 -- Received on Fri Mar 07 2008 - 12:37:06 CST