Skip to content
Oracle DBA Scripts & Database Utilities

Buffer Pool Cache Contents Analysis (vbpcache.sql)

What This Script Does

Section titled “What This Script Does”

This script provides detailed analysis of buffer pool cache contents by:

  • Showing which objects are currently cached in memory
  • Breaking down usage by owner, object name, and type
  • Displaying which buffer pool (DEFAULT, KEEP, RECYCLE) contains each object
  • Calculating total buffers used by each object

Script

Section titled “Script”
rem
ttitle 'Buffer Pool Cache Contents'
rem
col owner format a15 heading 'Owner'
col name     format a30 heading 'Name'
col type     format a13 heading 'Type'
col pool     format a8  heading 'Buffer|Pool'
col buffers  format 999,999 heading 'Buffers'
rem
break on report
compute sum of buffers on report
rem
select user$.name owner,
       obj$.name,
       DECODE(obj$.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',
              9,'PACKAGE',10,'NON-EXISTENT',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',
              20,'INDEX PARTITION',21,'LOB',22,'LIBRARY','UNKNOWN') TYPE,
       bp_name POOL,count(*) buffers
  from sys.x$bh , sys.obj$, sys.user$,
       (select distinct bp_name,set_id,START_BUF#,END_BUF#
          from X$kcbwds , X$KCBWBPD, v$buffer_pool
         where SET_ID between BP_LO_SID and BP_HI_SID
           and v$buffer_pool.name = bp_name
           and v$buffer_pool.buffers >0)
 where buf# between START_BUF#
   and END_BUF# and obj= obj$.dataobj#
   and obj$.owner# = user$.user#
   and user$.name like upper(nvl('&Owner','%'))
   and obj$.name like upper(nvl('&Name','%'))
   and DECODE(obj$.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',
       9,'PACKAGE',10,'NON-EXISTENT',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',
       20,'INDEX PARTITION',21,'LOB',22,'LIBRARY','UNKNOWN') like upper(nvl('&Type','%'))
   and bp_name like upper(nvl('&BufferPool','%'))
 group by user$.name,obj$.name,obj$.type#,bp_name
/

Usage

Section titled “Usage”
SQL> @vbpcache.sql
Enter value for Owner: SCOTT
Enter value for Name: %
Enter value for Type: TABLE
Enter value for BufferPool: DEFAULT

Parameters

Section titled “Parameters”
  • Owner: Schema owner to filter (use % for all)
  • Name: Object name pattern (use % for all)
  • Type: Object type (TABLE, INDEX, etc., use % for all)
  • BufferPool: Buffer pool name (DEFAULT, KEEP, RECYCLE, use % for all)

Required Privileges

Section titled “Required Privileges”
  • SELECT on SYS.X$BH
  • SELECT on SYS.OBJ$
  • SELECT on SYS.USER$
  • SELECT on X$KCBWDS
  • SELECT on X$KCBWBPD
  • SELECT on V$BUFFER_POOL
  • SYSDBA or elevated privileges required

Sample Output

Section titled “Sample Output”
Buffer Pool Cache Contents


Owner           Name                           Type          Buffer  Buffers
                                                            Pool
--------------- ------------------------------ ------------- -------- --------
SCOTT           EMPLOYEES                      TABLE         DEFAULT   12,456
SCOTT           EMP_IDX1                       INDEX         DEFAULT    8,234
SCOTT           DEPARTMENTS                    TABLE         DEFAULT    3,456
HR              SALARY_HISTORY                 TABLE         KEEP       5,678
HR              SALARY_HIST_IDX                INDEX         KEEP       2,345
SYSTEM          AUD$                           TABLE         DEFAULT    1,234
                                                                      --------
sum                                                                    33,403

Key Output Columns

Section titled “Key Output Columns”
  • Owner: Schema that owns the object
  • Name: Object name
  • Type: Database object type
  • Buffer Pool: Which buffer pool contains the object
  • Buffers: Number of database blocks cached

Understanding Buffer Pools

Section titled “Understanding Buffer Pools”

Buffer Pool Types

Section titled “Buffer Pool Types”
  1. DEFAULT: Standard buffer pool for most objects
  2. KEEP: For frequently accessed objects to minimize aging
  3. RECYCLE: For large scans to prevent cache pollution

Memory Usage Calculation

Section titled “Memory Usage Calculation”
  • Each buffer represents one database block
  • Memory used = Buffers × DB_BLOCK_SIZE
  • Example: 1,000 buffers × 8KB = 8MB

Common Use Cases

Section titled “Common Use Cases”

  1. Performance Analysis

    • Identify hot objects in cache
    • Validate buffer pool assignments
    • Find cache pollution sources

  2. Memory Optimization

    • Right-size buffer pools
    • Move objects between pools
    • Reduce memory wastage

  3. Troubleshooting

    • Investigate cache contention
    • Find oversized objects in cache
    • Validate caching strategy

Buffer Pool Best Practices

Section titled “Buffer Pool Best Practices”

  1. KEEP Pool Candidates

    • Small, frequently accessed tables
    • Critical lookup tables
    • Hot indexes

  2. RECYCLE Pool Candidates

    • Large tables with full scans
    • Temporary reporting tables
    • Batch processing objects

  3. Monitoring Guidelines

    • Regular cache content analysis
    • Track hit ratios by pool
    • Adjust pool sizes based on usage

Performance Considerations

Section titled “Performance Considerations”
  • This query accesses X$ tables (expensive)
  • Best run during low-activity periods
  • Consider filtering to reduce overhead
  • Results show current snapshot only
Section titled “Related Scripts”