The Oracle v$bh view shows the contents of the data buffers as well as the number of blocks for each type of segment in the buffer. This view is primarily useful for indicating the amount of table and index caching in multiple data blocks. 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. This is very important in Oracle10g since the data buffer sizes can be altered dynamically.
There are several data dictionary tricks that can be used 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 such as db_2k_cache_size , etc., the block size for the object must be displayed. This is achieved 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 qualification is required to handle partitions:
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, it needs to be joined 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')
A sample listing from this exciting report is shown below. The report lists the tables and indexes that reside inside the data buffer at the exact moment that the script was executed. 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.
The following is the report from buf_blocks.sql 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 an interesting report because there are three object types: tables; indexes; and partitions. The subsets of the DEFAULT pool for KEEP and RECYCLE are also evident. Also, 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 can be somewhat confusing due to the repeated DEFAULT buffer pool name. In earlier releases of Oracle, the KEEP and RECYCLE buffer pools are subsets of db_cache_size and can ONLY accommodate objects with the DEFAULT db_block_size. In later releases, the KEEP and RECYCLE pools become independent pools.
Conversely, any block sizes that are NOT the default
db_block_size, go into the buffer pool
named DEFAULT. The output listing shows that there are really six
mutually exclusive and independently sized buffer pools, four of them
are called DEFAULT.