I know I have been reminisce in sending out a "top-of-the-week" so here goes..
Have you ever wondering what's in the buffer cache ? You an run the below query and it will give you the number of blocks in memory by object, what the table_name is (for indexes) and total blocks for the object, and the percent of the object in the buffer cache.
This can useful to see where your buffer cache is going. I'm also thinking it can give you idea if an index is used.. If you look during the busy part of the day, and you don't see much an index cached, it's probably not used.
select owner,object_name,table_name,subobject_name,object_type,num_blocks,num_blocks_total, num_blocks/num_blocks_total Percent_in_memory
from
(
select
o.owner owner,
o.object_name object_name,
nvl((select table_name from dba_indexes i where index_name = o.object_name and i.owner=o.owner),object_name) table_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks,
(select sum (blocks) from dba_segments s where s.owner=o.owner and s.segment_name=o.object_name) Num_blocks_total
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
);