RE: how to check table been "cache"?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <mccdba1@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Feb 2010 12:31:25 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of dba1 mcc
Sent: Monday, February 01, 2010 9:25 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-db-l@xxxxxxxxxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
