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 
as 
(
  select
     c.owner#, c.kind, c.name, c.partition_name, 
     c.status, c.file#, 
     count(*) co
  from v$cache c
  group by 
     c.owner#, c.kind, c.name, c.partition_name,
     c.partition_name, file#, c.status
), cache_raw2
as
(
  select 
     du.username, c.kind, 
     case when c.partition_name is null
            then c.name
            else c.name || ' 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,
     c.file#,  
     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
(
  select 
     c.username as owner, c.kind, 
     case 
       when c.kind in ('INDEX', 'INDEX PARTITION')
       then c.name || ' index on '|| ind.table_name
       else c.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,   
     c.status, 
     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
  where 
     seg.owner(+)=c.username
     and 
     case when seg.partition_name(+) is null
            then seg.segment_name(+)
            else seg.segment_name(+) || ' partition ' || seg.partition_name(+)
     end = c.name
     and seg.segment_type(+)=c.kind
  and
     ind.owner(+)=c.username and ind.index_name(+)=nvl(substr(c.name, 1, instr(c.name, ' ', 1) - 1), c.name)
)   
  select 
     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.name, 
     c.percentage,
     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
About these ads

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: http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12606).

    Regards

    Martin

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: