Your eye into the buffer cache


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.

regards

Advertisements

3 comments

    1. hi, you mean the different keep/recycle strategy i mentioned after your talk?
      well, although already in production on a large site, i did not yet blog about it because it will not only take a write-up of some parameter reconfigurations but also a discussion of some preconditions, of a switch in thinking about the rationale of keep/recycle cache types iff you like, whereby the same old goal of optimization persists: a healthy buffer cache. (you tellingly said “den buffer cache abräumen” to describe the bad case.)
      this is why i defered writing the post from day to day and week to week… so far.
      maybe summer will find some time slot to get the text in façon and published.
      i like your riddle-type blog, have never seen something like this on oracle before.
      regards peter

      Liked by 1 person

Leave a Reply

Please log in using one of these methods to post your comment:

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