-- #############################################################################################
--
-- %Purpose: Buffer Cache Analysis - Slot Status (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 on;
set verify off;
set termout on;

ttitle left 'Buffer Cache Analysis - Slot Status' skip 2

spool buffer_cache_analysis_slots.log

select  decode (STATUS, 'free', 'FREE',
                        'xcur', 'INST EXCL',
                        'scur', 'INST SHAR',
                        'cr',   'CONS READ',
                        'read', 'DISK READ',
                        'mrec', 'MED RECO',
                        'irec', 'INS RECO', 'OTHER') "Slot Status",
    count(*) "Counts"
  from  V$CACHE
 group  by
    STATUS
/

spool off;