Viewing Pinned Objects in KEEP cache [message #414105] |
Mon, 20 July 2009 07:03 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
I have configured
db_keep_cche_size=512M
db_recycle_cache_size = 512M
Alter table REF_ETL_GEOG_LOC_CD storage (buffer_pool keep)
Now, using which view I can check whether this object is pinned or not.
Brian
|
|
|
|
|
|
Re: Viewing Pinned Objects in KEEP cache [message #414127 is a reply to message #414119] |
Mon, 20 July 2009 07:58 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Thanks Michel.
SQL> select segment_name, segment_type, buffer_pool from dba_segments where segment_name = 'OFSA_LEAF_DESC' ;
SEGMENT_NAME SEGMENT_TYPE BUFFER_
-------------------- ------------------ -------
OFSA_LEAF_DESC TABLE KEEP
SQL> select buffer_pool, cache from dba_tables where owner = 'BOIDW' and table_name = 'OFSA_LEAF_DESC' ;
BUFFER_ CACHE
------- -----
KEEP N
SQL>
For the above said table I have mentioned buffer_pool as "KEEP", In the code I'm referencing this object repeatedly, still in the dba_tables.cache says "N". Why this mismatch ?
Regards,
Brayan.
|
|
|
|
Re: Viewing Pinned Objects in KEEP cache [message #414134 is a reply to message #414130] |
Mon, 20 July 2009 08:10 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
Quote: | The table does not have the property CACHE which is given by ALTER TABLE ... CACHE and does not refer to anything with current cache content.
|
I did not understand what you mean by table does not have the property CACHE. Does this require gathering stats!
Regards,
Brayan.
|
|
|
|
Re: Viewing Pinned Objects in KEEP cache [message #414140 is a reply to message #414139] |
Mon, 20 July 2009 08:42 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
I have altered the table using
Alter table OFSA_LEAF_DESC storage (buffer_pool KEEP)
I'm using this table repeatedly in the code, Still dba_tables.cache = 'N'.
When the dba_tables.cache = 'N' will become 'Y' ?
Regards,
Ronald.
|
|
|
|
Re: Viewing Pinned Objects in KEEP cache [message #414631 is a reply to message #414142] |
Thu, 23 July 2009 01:20 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
Using following scripts I have identified tables, indexes assigned KEEP POOL.
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and o.owner not in ('SYS','SYSTEM')
and bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by count(distinct file# || block#) desc
Alter table OFSA_LEAF_DESC storage (buffer_pool KEEP)
Alter table OFSA_LEAF_DESC Cache
Now, how do I check whether these objects present in KEEP or not.
Regards,
Brayan.
|
|
|
|
|
|
Re: Viewing Pinned Objects in KEEP cache [message #414705 is a reply to message #414631] |
Thu, 23 July 2009 04:17 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: | Now, how do I check whether these objects present in KEEP or not.
|
If you have assigned the table to use the KEEP buffer pool with an Alter table statement, then any blocks read from that table will go to the Keep pool (or cache if you prefer).
It almost sounds as if you're asking how to see if there are any blocks from that table in that buffer pool - is this the case?
|
|
|