olilogu

 Profile Answers by olilogu 

• May 11th, 2009

 

Check on x$bh it show you what objects are in the buffer.
Try this query;
SELECT   /*+ ordered use_hash(o) use_hash(bh) */
         bp.NAME BUFFER_POOL, u.NAME owner, so.object_type segment_type,
         o.NAME segment_name, COUNT (*) cached_blocks
    FROM v$buffer_pool bp,
         SYS.seg$ s,
         SYS.sys_objects so,
         SYS.obj$ o,
         SYS.user$ u,
         x$bh bh
   WHERE bh.obj = o.dataobj#
     AND o.obj# = so.object_id
     AND o.type# = so.object_type_id
     AND o.owner# = u.user#
     AND so.segment_type_id = s.type#
     AND so.ts_number = s.ts#
     AND so.header_file = s.file#
     AND so.header_block = s.block#
     AND DECODE (s.cachehint, 0, 3, s.cachehint) = bp.ID
GROUP BY u.NAME, so.object_type, o.NAME, bp.NAME
ORDER BY bp.NAME, cached_blocks DESC;