Database Buffer Hit Ratio (vdbbufratio.sql)

What This Script Does

This script calculates the database buffer cache hit ratio, one of the most fundamental Oracle performance metrics. It shows the calculation methodology step-by-step, displaying consistent gets, db block gets, total logical reads, physical reads, and the resulting hit ratio percentage.

Script

rem
rem vdbbufratio.sql
rem
tti 'DB Buffer Hit Ratio'
rem
set lines 80
rem
clear break
rem
col consistent_gets   format 9,999,999,990 heading 'Consistent Gets'
col db_block_gets     format 9,999,999,990 heading 'DB Block Gets'
col logical_reads     format 9,999,999,990 heading 'Logical Reads'
col physical_reads    format 999,999,999,999,990 heading 'Physical Reads'
col db_buf_hit_ratio  format 9,990.0 heading 'DB Buffer|Hit Ratio'
rem
select
      sum(decode(name,'consistent gets', value, 0)) consistent_gets,
      '+',
      sum(decode(name,'db block gets', value, 0)) db_block_gets,
      '=',
      sum(decode(name,'consistent gets', value, 0)
          + decode(name,'db block gets', value, 0)) logical_reads,
      sum(decode(name,'physical reads', value, 0)) physical_reads,
      round(sum(decode(name,'consistent gets', value, 0)
                + decode(name,'db block gets', value, 0)
                - decode(name,'physical reads', value, 0))
            / sum(decode(name,'consistent gets', value, 0)
                + decode(name,'db block gets', value, 0))
           * 100,1) db_buf_hit_ratio
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads')
/

Usage

-- Run the script in SQL*Plus or SQLcl
@vdbbufratio.sql

-- No parameters required
-- Shows cumulative statistics since instance startup

Required Privileges

SELECT on V$SYSSTAT
Generally available to most database users

Sample Output

DB Buffer Hit Ratio

Consistent Gets DB Block Gets   Logical Reads        Physical Reads DB Buffer
                                                                    Hit Ratio
----------- -----------   --------------- ----------------- ---------
 45,678,901  12,345,678 = 58,024,579        2,345,678      95.9

Key Output Columns

Consistent Gets: Logical reads for SELECT statements (shared locks)
DB Block Gets: Logical reads for DML statements (exclusive locks)
Logical Reads: Total buffer cache reads (Consistent Gets + DB Block Gets)
Physical Reads: Actual disk reads when data not in cache
DB Buffer Hit Ratio: Percentage of logical reads satisfied from cache

Understanding Buffer Hit Ratio

Calculation Formula

Hit Ratio = ((Logical Reads - Physical Reads) / Logical Reads) * 100

Performance Indicators

>95%: Generally acceptable for OLTP systems
>90%: Acceptable for mixed workloads
<90%: May indicate insufficient buffer cache or inefficient SQL
>99%: Very good, but investigate if approaching 100%

Types of Logical Reads

Consistent Gets: Read-consistent images for queries
DB Block Gets: Current mode blocks for DML operations

Performance Analysis

Interpreting Results

High Hit Ratio (>98%)

Good: Efficient buffer cache usage
Caution: May indicate oversized cache or limited data access patterns
Action: Verify cache is appropriately sized, not over-allocated

Moderate Hit Ratio (90-95%)

Typical: Normal for systems with mix of OLTP and batch processing
Investigation: Look for patterns in physical reads
Optimization: Consider increasing buffer cache if memory available

Low Hit Ratio (<90%)

Concern: Potential performance issues
Causes: Undersized buffer cache, large table scans, inefficient SQL
Action: Investigate SQL patterns and consider cache sizing

Factors Affecting Hit Ratio

Buffer Cache Size: Primary factor in hit ratio
Application Design: OLTP vs DSS workloads
SQL Efficiency: Full table scans reduce hit ratio
Data Access Patterns: Random vs sequential access
Database Size: Large databases may have naturally lower ratios

Common Use Cases

Daily Performance Check

-- Monitor buffer performance
@vdbbufratio.sql
-- Track hit ratio trends over time

Buffer Cache Sizing

-- Evaluate current cache effectiveness
@vdbbufratio.sql
-- Use with memory advisors for optimal sizing

Performance Troubleshooting

-- During performance issues
@vdbbufratio.sql
-- Compare with historical baselines

Troubleshooting Low Hit Ratios

Immediate Analysis

Check recent activity:

-- Look for large scan operations
SELECT sql_text, disk_reads, executions
FROM v$sqlarea
WHERE disk_reads > 100000
ORDER BY disk_reads DESC;

Review buffer cache size:

SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components
WHERE component = 'DEFAULT buffer cache';

Buffer Cache Optimization

Use Buffer Cache Advisor:

SELECT size_for_estimate/1024/1024 cache_size_mb,
       estd_physical_read_factor,
       estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
ORDER BY size_for_estimate;

Increase buffer cache (if memory available):
```
ALTER SYSTEM SET db_cache_size = 2G;
```

SQL-Level Investigation

Find high physical read SQL:

SELECT sql_id, child_number,
       disk_reads, buffer_gets,
       ROUND(disk_reads/buffer_gets*100, 2) phys_read_pct
FROM v$sql
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

Analyze execution plans:

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Advanced Analysis

-- Calculate hit ratio from AWR
SELECT snap_id,
       ROUND(((logical_reads - physical_reads) / logical_reads) * 100, 2) hit_ratio
FROM (
  SELECT snap_id,
         SUM(CASE WHEN stat_name = 'consistent gets' THEN value END) +
         SUM(CASE WHEN stat_name = 'db block gets' THEN value END) logical_reads,
         SUM(CASE WHEN stat_name = 'physical reads' THEN value END) physical_reads
  FROM dba_hist_sysstat
  WHERE stat_name IN ('consistent gets', 'db block gets', 'physical reads')
  GROUP BY snap_id
)
WHERE logical_reads > 0
ORDER BY snap_id DESC;

Memory Component Analysis

-- Compare buffer pools
SELECT name, current_size/1024/1024 size_mb,
       buffers, hit_ratio
FROM v$buffer_pool_statistics;

Session-Level Buffer Usage

-- Top sessions by logical reads
SELECT s.sid, s.username, s.program,
       i.consistent_gets, i.block_gets,
       i.consistent_gets + i.block_gets logical_reads
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
ORDER BY logical_reads DESC;

Best Practices

Monitoring Guidelines

Baseline establishment: Record normal hit ratios for comparison
Regular monitoring: Track trends, not just point-in-time values
Context consideration: Evaluate with workload patterns
Combined metrics: Use with other performance indicators

Optimization Strategy

Memory tuning: Right-size buffer cache using advisors
SQL optimization: Focus on high physical read statements
Application design: Minimize full table scans where possible
Storage optimization: Ensure fast disk subsystem

Buffer Pool Statistics (gvbp.sql) - Detailed buffer pool analysis
SGA Memory Breakdown (vsgastat.sql) - Memory allocation details
Buffer Pool by File (vbhfile.sql) - File-level buffer distribution
Session I/O Statistics (vsessio.md) - Session-level I/O analysis