Skip to content
Oracle DBA Scripts & Database Utilities

Buffer Pool Contents by Segment (vbhseg.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes the Oracle buffer cache to show:

  • Which segments (tables, indexes) are currently cached in memory
  • How many buffers each segment is using
  • The actual number of distinct blocks vs total buffers
  • Memory efficiency by showing duplicate blocks in cache

Script

Section titled “Script”
rem  vbhseg.sql
rem
ttitle 'Buffer Pool by Segment'
rem
col tablespace_name format a15 heading 'TABLESPACE'
col file_id format 999 heading 'FILE'
col buffer_count format b99999 heading 'BUFFERS'
col distinct_block format b99999 heading 'BLOCKS'
col owner# format 999 heading 'OWNER'
col name format a30 heading 'SEGMENT NAME'
col net_diff format b99999 heading 'NET|DIFF'
rem
break on report on tablespace_name skip 1
compute sum of buffer_count distinct_block net_diff on report tablespace_name
rem
select tablespace_name,
       owner#,
       name,
/*
       file_id,
*/
       count(block#) buffer_count,
       count( distinct block# ) distinct_block,
       count(block#) - count( distinct block# ) net_diff
  from v$bh s,
       sys.dba_data_files f,
       sys.ext_to_obj e
 where f.file_id = s.file#(+)
   and s.file# = e.file#(+)
   and s.block# >= e.lowb(+)
   and s.block# <= e.highb(+)
   and tablespace_name like upper('&tablespace')
   and name like upper('&object_name')
 group by tablespace_name, owner#, name;

Usage

Section titled “Usage”
SQL> @vbhseg.sql
Enter value for tablespace: USERS
Enter value for object_name: %

Parameters

Section titled “Parameters”
  • tablespace: Tablespace name to filter (use % for all)
  • object_name: Object name pattern to filter (use % for all)

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$BH
  • SELECT on SYS.DBA_DATA_FILES
  • SELECT on SYS.EXT_TO_OBJ
  • Elevated privileges may be required for SYS objects

Sample Output

Section titled “Sample Output”
Buffer Pool by Segment


TABLESPACE      OWNER SEGMENT NAME                   BUFFERS  BLOCKS    NET
                                                                        DIFF
--------------- ----- ------------------------------ ------- ------- -------
USERS              84 CUSTOMER_DATA                    12,456  11,234   1,222
                   84 CUSTOMER_IDX1                     8,765   8,234     531
                   84 ORDER_ITEMS                       5,432   5,123     309
***                                                    ------  ------  ------
sum                                                    26,653  24,591   2,062


SYSTEM              0 AUD$                              2,345   2,100     245
                    0 IDL_UB1$                          1,234   1,200      34
***                                                    ------  ------  ------
sum                                                     3,579   3,300     279

Key Output Columns

Section titled “Key Output Columns”
  • TABLESPACE: Tablespace containing the segment
  • OWNER: Owner ID of the segment (numeric)
  • SEGMENT NAME: Name of the table, index, or other segment
  • BUFFERS: Total number of buffers in cache for this segment
  • BLOCKS: Number of distinct blocks cached
  • NET DIFF: Difference between buffers and blocks (duplicate blocks)

Understanding the Metrics

Section titled “Understanding the Metrics”

Buffer Usage

Section titled “Buffer Usage”
  • High buffer counts indicate frequently accessed objects
  • These objects benefit most from buffer cache memory
  • Consider pinning critical objects with high usage

Duplicate Blocks

Section titled “Duplicate Blocks”
  • NET DIFF shows blocks cached multiple times
  • High values may indicate:
    • Multiple sessions reading same blocks
    • Inefficient access patterns
    • Potential for buffer cache contention

Memory Efficiency

Section titled “Memory Efficiency”
  • Compare BUFFERS to BLOCKS ratio
  • Ratio close to 1.0 indicates efficient usage
  • High ratios suggest redundant caching

Common Use Cases

Section titled “Common Use Cases”

  1. Performance Tuning

    • Identify hot objects consuming buffer cache
    • Find candidates for partitioning
    • Validate caching strategies

  2. Memory Management

    • Right-size buffer cache based on usage
    • Identify objects for KEEP/RECYCLE pools
    • Monitor cache efficiency

  3. Troubleshooting

    • Find objects causing cache contention
    • Identify full table scans filling cache
    • Validate index usage patterns

Performance Considerations

Section titled “Performance Considerations”
  • This query can be resource intensive on large buffer caches
  • Best run during off-peak hours for analysis
  • Consider filtering by tablespace or object name
Section titled “Related Scripts”