Sokrates on Oracle

what’s in my buffer cache ?

Posted by Matthias Rogel on 4. February 2013

The following SQL shows me what is currently in my buffer cache and runs in a reasonable amount of time ( never longer than 30 seconds with some dozens of GB buffer cache and around 5 million entries in v$cache ), it also shows me the cached percentage of each segment which is currently part of the cache.
Version is 11.2.

with cache_raw 
     c.owner#, c.kind,, c.partition_name, 
     c.status, c.file#, 
     count(*) co
  from v$cache c
  group by 
     c.owner#, c.kind,, c.partition_name,
     c.partition_name, file#, c.status
), cache_raw2
     du.username, c.kind, 
     case when c.partition_name is null
            else || ' partition ' || c.partition_name
     end as name,
     case c.status
        when 'free' then 'not currently in use'
        when 'xcur' then 'exclusive'
        when 'scur' then 'shared current'
        when 'cr' then 'consistent read'
        when 'read' then 'being read from disk'
        when 'mrec' then 'in media recovery mode'
        when 'irec' then 'in instance recovery mode'
     end as status,
     co * (select value from v$parameter where name='db_block_size') as anzb
  from cache_raw c, dba_users du
  where du.user_id(+)=c.owner#
), cache_segm as
     c.username as owner, c.kind, 
       when c.kind in ('INDEX', 'INDEX PARTITION')
       then || ' index on '|| ind.table_name
     end as name, 
     round(100 * c.anzb / sum(c.anzb) over (), 2) percentage,
     round(c.anzb / (1024 * 1024 * 1024), 2) as gbytes_in_cache, 
     round(100 * c.anzb / seg.bytes, 2) perc_of_segment_in_cache, 
     round(seg.bytes / (1024 * 1024 * 1024), 2) as gbytes_in_segment,   
     round(c.anzb / (1024 * 1024), 2) as mbytes_in_cache,
     round(seg.bytes / (1024 * 1024), 2) as mbytes_in_segment,   
     round(c.anzb / (1024), 2) as kbytes_in_cache,   
     round(seg.bytes / (1024), 2) as kbytes_in_segment,            
     c.anzb as bytes_in_cache, 
     seg.bytes as bytes_in_segment,   
     seg.segment_subtype, seg.tablespace_name
  from cache_raw2 c, dba_segments seg, dba_indexes ind
     case when seg.partition_name(+) is null
            then seg.segment_name(+)
            else seg.segment_name(+) || ' partition ' || seg.partition_name(+)
     end =
     and seg.segment_type(+)=c.kind
     ind.owner(+)=c.username and ind.index_name(+)=nvl(substr(, 1, instr(, ' ', 1) - 1),
     case row_number() over(order by c.bytes_in_cache desc) 
       when 1 then round(sum(c.bytes_in_cache) over () / (1024 * 1024 * 1024), 2) 
     end as gb_total,
     c.owner, c.kind,, 
     c.gbytes_in_cache, c.perc_of_segment_in_cache, 
     c.gbytes_in_segment, c.status, 
     c.mbytes_in_cache, c.mbytes_in_segment,   
     c.kbytes_in_cache, c.kbytes_in_segment,            
     c.bytes_in_cache, c.bytes_in_segment,   
     c.segment_subtype, c.tablespace_name
  from cache_segm c
  order by 
     c.bytes_in_cache desc

2 Responses to “what’s in my buffer cache ?”

  1. Hi Matthias,

    a very interesting script. Perhaps it’s worth noting, that v$cache is not created by default in current releases and has to be build with the DDL in catclust.sql (and the documentation even calls it deprecated:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: