How to keep and unkeep tables data in memory Temporarly like procedures

[Ezhilarasan]

Hi,

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

[Yogeeraj_1]

"keep and unkeep Tables data Temporarly in memory "???

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 CACHE;
===========================================
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

[Ramkumar Devanathan]

Ezhil,

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.

[Ramkumar Devanathan]

BTW, Great response Yogeeraj-jee. You are *the* oracle guru.

- ramd.

[Brian Crabtree]

In 8i and 9i, you have the option of using "buffer_pools" (keep, recycle, and default) that use different options for how the data would be saved in the buffer cache. This is probably what you are looking for. By placing a table into the 'KEEP' buffer pool, it will be kept in memory as long as blocks are available in the pool. You should try to make sure that the pool is large enough to hold all of the objects you want to put into it, and you should not try to load all of your objects into this pool, just things that are read from constantly (config tables, users, etc).

Hope this helps,

Brian

[twang]

For object we can use dbms_shared_pool.keep to keep objects on mem. eg.
exec sys.dbms_shared_pool.keep('sys.dbms_pipe');

For table we can create it as a cache table or use ALTER TABLE CACHE to keep it on mem.
eg. ALTER TABLE so_headers_all CACHE;
In my own option, I only alter small and frequent-access tables cache.

[Tim Sanko]

I am impressed with the answers. Fine analysis and the first answer was right...

I am impressed...

Tim