The v$bh view is especially useful for showing the amount of table and index caching in databases with multiple data buffer pools. For example, researchers have shown that creating a separate index buffer with a very large blocksize can improve Oracle index structures. The v$bh view also provides important information for the proper setting of the optimizer_index_caching parameter, an important cost-based optimizer parameter that tells the CBO how much of the databases index reside in the buffer cache.

Today, many Oracle professionals are segmenting their db_cache_size, using the new db_2k_cache_size, db_keep_cache_size, db_4k_cache_size, db_8k_cache_size, db_16k_cache_size and db_32k_cache_size to create separate data buffers to isolate the caching of data blocks to improve manageability and caching.

Combining the v$bh view with dba_objects and dba_segments provides a block-by-block listing of the data buffer contents and indicates how well the buffers are caching tables and indexes. Of course, this is very important in Oracle9i, since the data buffer sizes can be altered dynamically.

There are several data dictionary tricks required when writing a script for mapping data objects to RAM buffers:

• Duplicate object names - When joining dba_objects to dba_segments, the name, type, and owner are all required to distinguish the object sufficiently.
  
• Multiple blocksizes - To show objects in the separate instantiated buffers (db_2k_cache_size, etc.), we need to display the block size for the object. We do this by computing the block size from dba_segments, dividing bytes by blocks.
  
• Partitions - With a standard equi-join, every object partition joins to every segment partition for a particular object. Hence, the following query qualification is required to handle partitioned objects:

  and nvl(t1.subobject_name,'*') = nvl(s.partition_name,'*')
  

• Clusters - Clusters present a challenge when joining the v$bh row with its corresponding database object. Instead of joining the bh.objd to object_id, we need to join into data_object_id.
  
• Multiple caches - There are situations where a particular block may be cached more than once in the buffer cache. This is a mystifying concept, but it is easily overcome by creating the following in-line view:

  (select distinct objd, file#, block# from v$bh where status != 'free')
  

This is the most important script in this text because it provides a detailed analysis of those objects in the data buffers. This information is critical when considering an alteration to the data buffer sizes.

set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   See code depot download for full script 
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;

column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999

select
   t1.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
   buffer_pool                                       c5,
   sum(bytes)/sum(blocks)                            c6
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   s.owner = t1.owner
and
   s.segment_type = t1.object_type
and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   t1.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc
;

A sample listing from this exciting report is shown below. We can see that the report lists the tables and indexes that reside inside the data buffer. This is important information for the Oracle professional who needs to know how many blocks for each object reside in the RAM buffer. To effectively manage the limited RAM resources, the Oracle DBA must be able to know the ramifications of decreasing the size of the data buffer caches.

Here is the report from this script when run against a large Oracle data warehouse.

                           Contents of Data Buffers

                                             Number of Percentage
                                             Blocks in of object
             Object            Object        Buffer    Buffer  Buffer    Block
Owner        Name              Type          Cache     Blocks  Pool       Size
------------ -------------------------- ----------- ---------- ------- -------
DW01         WORKORDER         TAB PART      94,856          6 DEFAULT   8,192
DW01         HOUSE             TAB PART      50,674          7 DEFAULT  16,384
ODSA         WORKORDER         TABLE         28,481          2 DEFAULT  16,384
DW01         SUBSCRIBER        TAB PART      23,237          3 DEFAULT   4,096
ODS          WORKORDER         TABLE         19,926          1 DEFAULT   8,192
DW01         WRKR_ACCT_IDX     INDEX          8,525          5 DEFAULT  16,384
DW01         SUSC_SVCC_IDX     INDEX          8,453         38 KEEP     32,768
DW02         WRKR_DTEN_IDX     IDX PART       6,035          6 KEEP     32,768
DW02         SUSC_SVCC_IDX     INDEX          5,485         25 DEFAULT  16,384
DW02         WRKR_LCDT_IDX     IDX PART       5,149          5 DEFAULT  16,384
DW01         WORKORDER_CODE    TABLE          5,000          0 RECYCLE  32,768
DW01         WRKR_LCDT_IDX     IDX PART       4,929          4 KEEP     32,768
DW02         WOSC_SCDE_IDX     INDEX          4,479          6 KEEP     32,768
DW01         SBSC_ACCT_IDX     INDEX          4,439          8 DEFAULT  32,768
DW02         WRKR_WKTP_IDX     IDX PART       3,825          7 KEEP     32,768
DB_AUDIT     CUSTOMER_AUDIT    TABLE          3,301         99 DEFAULT   4,096
DW01         WRKR_CLSS_IDX     IDX PART       2,984          5 KEEP     32,768
DW01         WRKR_AHWO_IDX     INDEX          2,838          2 DEFAULT  32,768
DW01         WRKR_DTEN_IDX     IDX PART       2,801          5 KEEP     32,768

This is a very important report because we see three object types (tables, indexes, and partitions), and we also see the sub-sets of the DEFAULT pool for KEEP and RECYCLE. Also, note that all indexes are defined in the largest supported block size (db_32k_cache_size), and multiple buffer pools of 4K, 8K, 16K and 32K sizes are defined.

The output of this script is somewhat confusing because of the repeated DEFAULT buffer pool name. This is misleading because the KEEP and RECYCLE buffer pools are sub-sets of db_cache_size and can ONLY accommodate objects with the DEFAULT db_block_size.

Conversely, any block sizes that are NOT the default db_block_size, go into the buffer pool named DEFAULT. As you can see from the output listing, there are really 6 mutually exclusive and independently-sized buffer pools, and four of them are called "DEFAULT."

It is valuable to run this report repeatedly because the Oracle data buffers are dynamic and constantly changing. Running this script frequently allows us to view the blocks entering and leaving the data buffer. We can see the midpoint insertion method in action and the hot and cold regions as they update. Each time a block is re-referenced it moves to the head of the MRU chain on the hot side of the data buffer. Blocks that are accessed less frequently will age-out, first moving into the cold region and eventually being paged-out to make room for new incoming blocks.

This approach is even more important when considering a decrease to a cache size. When you issue an alter system command to decrease the cache size, Oracle will grab pages from the least recently used (LRU) end of the buffer. Depending on the amount of RAM removed, an alter system command will un-cache data blocks that may be needed by upcoming SQL statements.

For more information on SGA internals and data buffer management, see my book, "Creating a Self-Tuning Database". If you want details on the new Oracle10g automation features, see Mike Ault's latest book Oracle Database 10g New Features.