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