Re: How to identify what db objects are on the cache

From: Mike Ault <mikerault_at_earthlink.net>
Date: 18 Sep 2002 10:29:26 -0700
Message-ID: <37fab3ab.0209180929.359ca18b_at_posting.google.com>

Which database cache? The object cache? The SQL cache? The Object cache can be monitored using the v$db_object_cache view, for exampe to get the top 20 users of the cache by executions:

select * from (select

	OWNER, 
	NAMESPACE,
	TYPE,
	NAME,
	SHARABLE_MEM,
	LOADS,  
	EXECUTIONS,   
	LOCKS,    
	PINS,
	KEPT
from 
	v$db_object_cache
where 
	type not in ('NOT LOADED','NON-EXISTENT')

order by executions,owner,namespace,type,loads desc) where rownum<21;

The KEPT column tells if the object has been pinned or not. Another method is to use the DBMS_SHARED_POOL package, it has a procedure that will identifiy kept objects for you.

This SQL was extracted from a o_cache.sql script available in the SQL scripts download on the ROBO Books site: www.robonerd.com in the Download discussion area in the free stuff board.

Mike Ault

elire_at_amdocs.com (Elir) wrote in message news:<e7cbfa45.0209180224.1ec8c113_at_posting.google.com>...
> How to identify what db objects are currently pin on the database cache.
Received on Wed Sep 18 2002 - 19:29:26 CEST