- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How to keep and unkeep tables data in memory T...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 12:50 AM
тАО04-24-2003 12:50 AM
How to keep and unkeep tables data in memory Temporarly like procedures
I understand, we can keep and unkeep procedures in shared_pool memory using dbms_shared_pool.keep, unkeep.
Likewise how to keep and unkeep Tables data Temporarly in memory ?
Actually when I run particular procedure, I need to load a small table data in memory. After this process, I do not want that table
in Memory !
Thanks
R.Ezhil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 01:54 AM
тАО04-24-2003 01:54 AM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
why? do you have a scientific reason to do so?
besides, it is physically impossible to pin a table. Cannot be done.
You cannot pin a table in the sga at all. If you use the data, the data will be cached. If you do not use the data it will not be cached - that is the function of a cache. It'll take care of itself.
There are only three pools -- not too much of a headache - but it is the last step in the tuning process, very last.
Anyway, the ALTER TABLE
===========================================
simply makes a notation in the data dictionary that blocks from this table should be handled differently when they get into the cache. Usually, when we full scan an object, we put the blocks onto the least recently end of the list. These blocks are candidates for "aging" from the buffer cache. By altering the table to 'cache', we put the blocks onto the most recently used end -- making them less prone to being aged out of the buffer cache.
From the sql reference manual we see:
...
for data that is accessed frequently, specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
...
So, no -- issueing the alter table ... cache command does not put the table into memory, you must full scan the table for that to happen.
Consider looking into setting up multiple buffer pools and associating this table with its own buffer pool if you are dead serious about caching it. Buffer pools are
Schema objects are referenced with varying usage patterns; therefore, their cache behavior may be quite different. Multiple buffer pools enable you to address these differences. You can use a KEEP buffer pool to maintain objects in the buffer cache
and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is allocated to a cache, all blocks from that object are placed in that cache. Oracle maintains a DEFAULT buffer pool for objects that have
not been assigned to one of the buffer pools.
Bear in mind though... there is really no true way to a have a purely "in memory" table -- even cached tables are subject to aging from the buffer cache.
hope this helps!
best regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 02:29 AM
тАО04-24-2003 02:29 AM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
country: SINGAPORE
personal quote:
certification:
ITRC member since: January 10, 2003
last contribution date: April 24, 2003
This member has assigned points to 28 of 150 responses to his/her questions.
Please do something to the ratio 28/150 above - preferably increase it to 1:1. :)
No points required for my response here. Yup you can assign 0 points for dumb responses like these.
- ramd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 02:31 AM
тАО04-24-2003 02:31 AM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
- ramd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 09:31 AM
тАО04-24-2003 09:31 AM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
Hope this helps,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-24-2003 06:15 PM
тАО04-24-2003 06:15 PM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
exec sys.dbms_shared_pool.keep('sys.dbms_pipe');
For table we can create it as a cache table or use ALTER TABLE
eg. ALTER TABLE so_headers_all CACHE;
In my own option, I only alter small and frequent-access tables cache.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2003 10:40 AM
тАО04-25-2003 10:40 AM
Re: How to keep and unkeep tables data in memory Temporarly like procedures
I am impressed...
Tim