RE: how to check table been "cache"?

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 2 Feb 2010 12:31:25 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0167ED18_at_WIN02.hotsos.com>

Not sure if anyone got back to you on this. There are several different things going on here that are not necessarily related.

The CACHE option for a table changes the behavior of a full table scan on a table. If a table has the cache option on it, and it's a large table, then oracle will try to read the entire table in during a full table scan. The default is to only read in part of it at a time and cycle thru a small set of buffers as it read thru the table. There is no "cache" so to say, it's an attribute of the table:

SQL> create table drop1 (abc number);
SQL> select table_name, cache from dba_tables where table_name='DROP1';

TABLE_NAME                     CACHE


------------------------------ -----

DROP1                              N

SQL> alter table drop1 cache;
SQL> select table_name, cache from dba_tables where table_name='DROP1';

TABLE_NAME                     CACHE


------------------------------ -----

DROP1                              Y

The KEEP pool (if allocated) is just a subset of the over all buffer pool. The buffers in the keep pool can and will flush out of cache just like any other buffer. There is some difference to this pool vs. the default but not much.

The KEPT column you are looking at I think only applies to program units which can be pinned (kept) in the shared pool.

There isn't any way in Oracle that I know of that will allow you to keep a buffer in cache such that it can never be aged out.

Ric Van Dyke
Hotsos Enterprises, Ltd.  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dba1 mcc Sent: Monday, February 01, 2010 9:25 AM
To: oracle-l_at_freelists.org
Cc: oracle-db-l_at_Groups.ITtoolbox.com
Subject: how to check table been "cache"?

I have table want put on "cache". I used following way to do:

alter table table1 cache;

select owner, name, type, kept from v$db_object_cache;

OWNER                NAME
TYPE            KEP


--------------------

------------------------------------------------------------

--------------- ---

USER1               table1
NOT LOADED      NO

this table in "cache" but not "KEPT".

I used another way to do this:

alter table table1 storage (buffer_pool keep);

select owner, name, type, kept from v$db_object_cache; no rows selected

Anyone know where is "cache"?

Thanks.       

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 02 2010 - 12:31:25 CST