Skip to content
Oracle DBA Scripts & Database Utilities

Buffer Pool Summary by File (vbhstat.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes the distribution of database blocks in the Oracle buffer cache across different datafiles. It provides insights into buffer allocation patterns, identifies files with high buffer cache usage, and shows the distribution of dirty blocks that need to be written to disk.

Script

Section titled “Script”
rem  vbhstat.sql
rem
ttitle 'Buffer Pool Summary by File'
rem
col file# format 999 heading 'FILE'
col buffer_count format b999999 heading 'BUFFERS'
col distinct_block format b999999 heading 'BLOCKS'
col extra_block format b999999 heading 'EXTRA|BLOCKS'
col dirty_block format b99999 heading 'DIRTY|BLOCKS'
col temp_block format b99999 heading 'TEMP|BLOCKS'
rem
break on report
compute sum -
     of buffer_count distinct_block extra_block dirty_block temp_block -
     on report
rem
select file#,
       count(*) buffer_count,
       count( distinct block# ) distinct_block,
       count(*) - count( distinct block# ) extra_block,
       count( decode( dirty, 'Y', 1 ) ) dirty_block,
       count( decode( temp, 'Y', 1 ) ) temp_block
  from v$bh
 group by file#
/

Usage

Section titled “Usage”
-- Run the script in SQL*Plus or SQLcl
@vbhstat.sql


-- No parameters required
-- Shows current buffer cache state

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$BH (buffer header view)
  • Typically requires DBA role due to V$BH access

Sample Output

Section titled “Sample Output”
Buffer Pool Summary by File


FILE  BUFFERS  BLOCKS EXTRA DIRTY  TEMP
                      BLOCKS BLOCKS BLOCKS
---- -------- ------- ------ ------ ------
   1    45678   45234    444   2345      0
   2    23456   23123    333   1234      0
   3    12345   12234    111    567      0
   4     8901    8845     56    234      0
   5     5678    5634     44    123      0
  10      234     234      0     45     45
---- -------- ------- ------ ------ ------
sum   96292   95304    988   4548     45

Key Output Columns

Section titled “Key Output Columns”
  • FILE: Datafile number
  • BUFFERS: Total number of buffers from this file in cache
  • BLOCKS: Number of distinct blocks cached (unique blocks)
  • EXTRA BLOCKS: Duplicate blocks in cache (BUFFERS - BLOCKS)
  • DIRTY BLOCKS: Modified blocks not yet written to disk
  • TEMP BLOCKS: Temporary tablespace blocks in cache

Understanding the Metrics

Section titled “Understanding the Metrics”

Buffer Distribution Analysis

Section titled “Buffer Distribution Analysis”
  • High BUFFERS: Files with heavy read activity or large objects
  • BLOCKS vs BUFFERS: Indicates block access patterns
  • EXTRA BLOCKS: Multiple copies of same block (normal for busy blocks)

Dirty Block Patterns

Section titled “Dirty Block Patterns”
  • High DIRTY BLOCKS: Files with heavy write activity
  • Dirty Block Ratio: DIRTY/BUFFERS shows write intensity
  • Temp Files: Usually show in temporary tablespace files

File Activity Indicators

Section titled “File Activity Indicators”
  • Large Buffer Counts: Heavily accessed datafiles
  • High Dirty Ratios: Write-intensive tablespaces
  • Low Buffer Counts: Infrequently accessed files

Performance Analysis

Section titled “Performance Analysis”

Identifying Hot Files

Section titled “Identifying Hot Files”
  1. High Buffer Counts: Most actively read files
  2. High Dirty Block Counts: Write-intensive files
  3. High Extra Block Ratios: Frequently re-read blocks

Buffer Cache Efficiency

Section titled “Buffer Cache Efficiency”
  • Extra Blocks > 10%: May indicate cache pressure
  • Even Distribution: Good for balanced I/O
  • Concentrated Activity: May indicate hot spots

I/O Patterns

Section titled “I/O Patterns”
  • Read-Heavy Files: High buffers, low dirty blocks
  • Write-Heavy Files: High dirty block ratios
  • Balanced Files: Moderate buffers and dirty blocks

Common Use Cases

Section titled “Common Use Cases”

Performance Troubleshooting

Section titled “Performance Troubleshooting”
-- During I/O performance issues
@vbhstat.sql
-- Identify files consuming most buffer cache

Capacity Planning

Section titled “Capacity Planning”
-- Analyze buffer cache distribution
@vbhstat.sql
-- Plan datafile placement and buffer cache sizing

Storage Analysis

Section titled “Storage Analysis”
-- Understand file access patterns
@vbhstat.sql
-- Optimize storage layout and I/O distribution

Troubleshooting Insights

Section titled “Troubleshooting Insights”

High Buffer Usage Files

Section titled “High Buffer Usage Files”
  1. Large Table Scans: Consider indexing or partitioning
  2. Hot Tables: Consider spreading across multiple files
  3. Poor SQL: Optimize queries accessing these files

High Dirty Block Counts

Section titled “High Dirty Block Counts”
  1. DBWR Performance: Check database writer efficiency
  2. Checkpoint Frequency: Review checkpoint intervals
  3. Redo Log Sizing: Ensure adequate redo log capacity

Uneven Distribution

Section titled “Uneven Distribution”
  1. File Placement: Consider spreading hot objects
  2. Tablespace Design: Review tablespace file allocation
  3. Parallel Processing: May cause uneven access patterns

Advanced Analysis

Section titled “Advanced Analysis”
Section titled “Related Queries”
-- Map file numbers to actual files
SELECT file_id, file_name, tablespace_name
FROM dba_data_files
WHERE file_id IN (/* high buffer count files */);


-- Check datafile I/O statistics
SELECT name, phyblkrd, phyblkwrt, readtim, writetim
FROM v$filestat f, v$datafile d
WHERE f.file# = d.file#
ORDER BY phyblkrd DESC;


-- Buffer pool configuration
SELECT name, block_size, current_size, buffers
FROM v$buffer_pool;
Section titled “Related Scripts”