Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Script for Oracle data buffer contents

 Oracle Tips by Burleson

August 28, 2014
One of the most important areas of Oracle tuning is the management of the RAM data buffers. Oracle performance will be dramatically improved if you can avoid a physical disk I/O by storing a data block inside the RAM memory of the data buffer caches. Historically, an Oracle professional would allocate an area of RAM cache with a size defined by the db_block_buffers (db_cache_size in Oracle 9i) parameter, and leave it to Oracle to manage the data blocks within this RAM area. But with the advent of support for multiple block sizes in Oracle 9i, you can manage each individual data buffer pool and segregate tables and indexes into separate data buffers.

Oracle caching in a nutshell

When a SQL statement requests a row from a table, Oracle first checks the internal memory structures to see if the data is already in a data buffer. If the requested data is there, it's returned, saving a physical IO operation. With the very large SGAs in some 64-bit releases of Oracle, small databases can be entirely cached.

For very large databases, however, the RAM data buffers cannot hold all of the database blocks.

Oracle has a scheme for keeping frequently used blocks in RAM. When there isn't enough room in the data buffer for the whole database, Oracle utilizes a least-recently-used algorithm to determine which database pages are to be flushed from memory. Oracle keeps an in-memory control structure for each block in the data buffer: New data blocks are inserted at the middle of the data buffer, and every time a block is requested, it is moved to the front of the list. Data blocks that aren't frequently referenced will wind up at the end of the data buffer where they will eventually be erased to make room for a new data block.

Starting in Oracle 8, Oracle provides three separate pools of RAM to hold incoming Oracle data blocks:

  • KEEP pool is used to hold tables that are frequently referenced by the application, such as small tables that have frequent full table scans and reference tables for the application.
  • RECYCLE pool is reserved for large tables that experience full table scans that are unlikely to be reread. The RECYCLE pool is used so that the incoming data blocks don't flush out data blocks from more frequently used tables and indexes.
  • DEFAULT pool is used for all table and index accesses that aren't appropriate for the KEEP or RECYCLE pools.

Remember, the KEEP and RECYCLE pools are subsets of the DEFAULT pool. Now that I've explained the basic mechanisms of the Oracle data buffers, let's look at how data dictionary queries can help you view the internal contents of the buffers.

Dictionary queries for data buffers

Oracle provides the v$bh view to allow you to view the contents of the data buffers, along with the number of blocks for each segment type in the buffer. This view is especially useful when you are using multiple data buffers and you want to know the amount of caching used for tables and indexes. Joining the v$bh view with dba_objects gives you a block-by-block listing of your data buffer contents and shows you how well your data buffers are caching table and index contents.

The script in Listing A formats the data buffer information into a great report you can use to monitor the data buffer activity in your database. Figure A shows the output from this report.

Figure A
Data buffer activity report


Listing A

set pages 999
set lines 80
spool blocks.lst
ttitle 'Contents of Data Buffers'
drop table t1;
create table t1 as
   o.object_name    object_name,
   o.object_type    object_type,
   count(1)         num_blocks
   dba_objects  o,
   v$bh         bh
   o.object_id  = bh.objd
   o.owner not in ('SYS','SYSTEM')
group by
order by
   count(1) desc
column c1 heading "Object|Name"                 format a30
column c2 heading "Object|Type"                 format a12
column c3 heading "Number of|Blocks"            format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999
   object_name       c1,
   object_type       c2,
   num_blocks        c3,
   (num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4
   dba_segments s
   s.segment_name = t1.object_name
   num_blocks > 10
group by
order by
   num_blocks desc

A full-featured script (written by Randy Cunningham) to display details about the buffer contents is available in the online code depot in the book Oracle Tuning: the Definitive Reference.  Here is the script, reproduced with permission:



--   Contents of Data Buffers
set pages 999
set lines 92
ttitle 'Contents of Data Buffers'
drop table t1;
create table t1 as
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
   dba_objects  o,
   v$bh         bh
   o.data_object_id  = bh.objd
   o.owner not in ('SYS','SYSTEM')
   bh.status != 'free'
group by
order by
   count(distinct file# || block#) desc
column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999
   t1.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
   buffer_pool                                       c5,
   sum(bytes)/sum(blocks)                            c6
   dba_segments s
   s.segment_name = t1.object_name
   s.owner = t1.owner
   s.segment_type = t1.object_type
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc

A sample listing from this exciting report is shown below.  We can see that the report lists the tables and indexes that reside inside the data buffer.  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.

Here is the report from buf_blocks.sql when run against a large Oracle data warehouse (Listing 3.2).


                           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

As you can see from Listing A, this report can give you some valuable insight into the tables and indexes that reside inside the data buffer. If you happen to have limited RAM resources for the data buffer caches, this report can show you the number of blocks that currently reside in the buffer for each object.

Oracle's midpoint insertion algorithm tends to segregate each buffer into "hot" and "cold" areas, depending on the frequency with which each data block is read. Each time a data block is re-referenced, it moves to the head of the data block chain on the "hot" side of the data buffer. I find it interesting to run the report repeatedly to watch the data blocks move from cold to hot and back again.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.




Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational