 | |
Can we keep a table in memeory for faster access? Oracle 10g
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: Can we keep a table in memeory for faster access? Oracle 10g -
03-07-2008
, 12:40 PM
|
On Mar 7, 3:45*am, JACKY <zhp... (AT) gmail (DOT) com> wrote: On Mar 7, 3:18*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 4, 12:53*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote: On Mar 4, 7:12*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 3, 3:28*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) 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....rver/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 - |
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 --
#32
| |||
| |||
|
|
On Mar 7, 3:45*am, JACKY <zhp... (AT) gmail (DOT) com> wrote: On Mar 7, 3:18*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 4, 12:53*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote: On Mar 4, 7:12*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 3, 3:28*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) 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....rver/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 - |
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 --
#33
| |||
| |||
|
|
On Mar 7, 3:45*am, JACKY <zhp... (AT) gmail (DOT) com> wrote: On Mar 7, 3:18*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 4, 12:53*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote: On Mar 4, 7:12*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote: On Mar 3, 3:28*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) 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....rver/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 - |
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 --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.