Home » RDBMS Server » Performance Tuning » Viewing Pinned Objects in KEEP cache (9.2.0.7)
Viewing Pinned Objects in KEEP cache [message #414105] Mon, 20 July 2009 07:03 Go to next message
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 #414107 is a reply to message #414105] Mon, 20 July 2009 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No object is pinned in buffer cache in the meaning an object is pinned in shared pool.
Please clarify your question.

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414116 is a reply to message #414107] Mon, 20 July 2009 07:22 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

I have high small table full-table scans. I have put these table in KEEP .

Now, I need to check which object is cached !

Regards
Brayan
Re: Viewing Pinned Objects in KEEP cache [message #414119 is a reply to message #414116] Mon, 20 July 2009 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dba_tables.cache
dba_tables.buffer_pool

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414127 is a reply to message #414119] Mon, 20 July 2009 07:58 Go to previous messageGo to next message
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 #414130 is a reply to message #414127] Mon, 20 July 2009 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The buffer used is KEEP.
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.

Regards
Michel

[Updated on: Mon, 20 July 2009 08:04]

Report message to a moderator

Re: Viewing Pinned Objects in KEEP cache [message #414134 is a reply to message #414130] Mon, 20 July 2009 08:10 Go to previous messageGo to next message
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 #414139 is a reply to message #414134] Mon, 20 July 2009 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No.

It requires:
Quote:
...property CACHE which is given by ALTER TABLE ... CACHE...

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414140 is a reply to message #414139] Mon, 20 July 2009 08:42 Go to previous messageGo to next message
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 #414142 is a reply to message #414140] Mon, 20 July 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you unable to read my posts?
What do them say? Repeat them.

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414631 is a reply to message #414142] Thu, 23 July 2009 01:20 Go to previous messageGo to next message
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 #414642 is a reply to message #414631] Thu, 23 July 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If they are in buffer cache and are assigned in KEEP pool by their properties (see dba_tables, dba_indexes and so on) then they are in KEEP pool.

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414680 is a reply to message #414642] Thu, 23 July 2009 03:40 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Michel,

Is the buffer cache you are refering is v$bh ?

Ronald.
Re: Viewing Pinned Objects in KEEP cache [message #414690 is a reply to message #414680] Thu, 23 July 2009 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Viewing Pinned Objects in KEEP cache [message #414705 is a reply to message #414631] Thu, 23 July 2009 04:17 Go to previous message
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?
Previous Topic: SQL tuning
Next Topic: How to avoid Full table scan for Like '%XYZ%'
Goto Forum:
  


Current Time: Mon Nov 25 23:14:13 CST 2024