ever asked yourself what is going on in the buffer cache? e.g., does oracle honour my settings for the different buffer cache types, that is, the default, keep and recycle cache? or, does oracle tune the buffer cache nicely, such that the application important database objects are in fact cached?
the latter is, however, not only oracle’s duty. imagine an application where there is an index missing on a large table. on every unindexed select, oracle will have to full scan the table, flooding the buffer cache with unwanted blocks. but how to trap this scenario?
the solution key is v$bh or, internally, x$bh, a system view that allows a blockwise inspection of the buffer cache. some pretend that v$bh has been introduced with oracle parallel server (ops), others relate it to the real application cluster (rac) environment.
whereas many people use v$bh to track the status of buffer cache blocks, i.e. being read, being shared, under recovery, v$bh may also be employed to simply count the number of blocks being allocated per database object. a database object in that respect is essentially a segment of type table, index or lob, if applicable with partitioning. the resulting view definition reads as follows:
create or replace view v_buffer_cache_eye as select seg_tbs, seg_pool, owner, object_type, object_name, partition_name, buf_blks, round(buf_mb0) as buf_mb, seg_mb, round(buf_mb0/greatest(seg_mb, 0.001)*100) as buf2seg_pct, round(sum(buf_mb0) over (partition by seg_pool)) as all_mb, round(buf_mb0/(sum(buf_mb0) over (partition by seg_pool))*100) as buf2all_pct from ( select owner, object_type, (case when (object_type = 'LOB') then (select table_name || ' . ' || column_name from dba_lobs where segment_name = A.OBJECT_NAME and OWNER = A.OWNER) else (case when (object_type = 'LOB PARTITION') then (select table_name || ' . ' || column_name from dba_lob_partitions where lob_name = a.object_name and owner = a.owner and lob_partition_name = a.subobject_name) else object_name end) end) as object_name ,(case when (object_type = 'LOB PARTITION') then (select partition_name from dba_lob_partitions where lob_name = a.object_name and owner = a.owner and lob_partition_name = A.subobject_name) else subobject_name end) as partition_name ,buf_blks ,buf_mb as buf_mb0 ,(select round(bytes/1024/1024) from dba_segments where segment_name = a.object_name and segment_type like a.object_type || '%' and owner = a.owner and (partition_name is null or partition_name = A.subobject_name)) as seg_mb ,(select buffer_pool from dba_segments where segment_name = a.object_name and segment_type like a.object_type || '%' and owner = a.owner and (partition_name is null or partition_name = a.subobject_name)) as seg_pool ,(select tablespace_name from dba_segments where segment_name = a.object_name and segment_type like a.object_type || '%' and owner = a.owner and (partition_name is null or partition_name = a.subobject_name)) as seg_tbs from ( select o.object_name, o.object_type, o.owner, o.subobject_name, count(*) as buf_blks, (count(*)*8192/1024/1024) as buf_mb from dba_objects o, (select distinct objd, file#, block# from v$bh) bh -- distinct for v$bh.status where o.data_object_id = bh.objd group by o.object_name, o.owner, o.object_type, o.subobject_name having count(*) > (100*1024*1024/8192) -- > 100 mb order by o.object_name, o.owner, o.object_type, o.subobject_name ) a8 ) b order by seg_pool desc, buf_blks desc ;
the several columns may be commented like this:
- SEG_TBS – tablespace definition of the segment
- SEG_POOL – pool location of the segments, value of (DEFAULT, KEEP, RECYCLE)
- OWNER – owner of the database object/segment
- OBJECT_TYPE – object/segment type, value of (TABLE, INDEX, LOB)
- OBJECT_NAME – object/segment name, for lobs, the system names are already translated into a table_name.column_name format for easy readabilty
- PARTITION_NAME – object/segment partition name, if applicable
- BUF_BLKS – amount of blocks allocated to the cache
- BUF_MB – amount of megabytes allocated to the cache
- SEG_MB – total size of the persistent segment in megabytes
- BUF2SEG_PCT – share, in percent, of megabytes allocated, compared to the total size of the segment
- ALL_MB – overall amount of megabytes allocated to the cache
- BUF2ALL_PCT – share, in percent, of megabytes allocated, compared to the total size of the cache
note that ALL_MB and BUF2ALL_PCT may not correctly given iff you apply the
having count(*) > (100*1024*1024/8192) -- > 100 mb restriction in the inner
where clause for efficiency reasons.
most probably, other scripts/tools/application, may show up with different numbers and amounts of buffer cache allocations. one example is enterprise manager in 10g and 11g, which generally delivers anything a bit smaller. who has got time to inspect why … ? however, the overall tendency of buffer cache allocation is correctly represented by the code and you may explore what actually goes on on there.