-- #############################################################################################
--
-- %Purpose: Buffer Cache Analysis - Objects (Analysis of V$CACHE)
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan off;
set verify off;
set termout on;

column  BT      format a29      heading 'Block Type'
column  KIND    format a12      heading 'Object Type'
column  CB      format 99990    heading 'Nr of Blocks'
column  NAME    format a24      heading 'Object Name'

ttitle left 'Buffer Cache Analysis - Objects' skip 2

spool   buffer_cache_analysis_obj.log

select  NAME,
        KIND,
        decode  (CLASS#,0, 'FREE',
                        1, 'DATA INDEX',
                        2, 'SORT',
                        3, 'SAVE UNDO',
                        4, 'SEG HEADER',
                        5, 'SAVE UNDO SH',
                        6, 'FREELIST BLOCK',
        'OTHER') as BT,
        count (BLOCK#) as CB
  from  V$CACHE
 group  by
    NAME,
    KIND,
    CLASS#
 order  by
    CB  desc,
    NAME,
    KIND
/

spool off;