Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Display Oracle data buffer contents

This is the most important script in this text because it provides a detailed analysis of those objects in the data buffers. This information is critical when considering an alteration to the data buffer sizes.

Many thanks to Randy Cunningham for the developing this sophisticated and powerful script. Please note that the script below only works with Oracle.

See code depot for full scripts

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
. . .
dba_objects o,
v$bh bh
. . .
order by
count(distinct file# || block#) desc

t1.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
. . .
dba_segments s
. . .

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

Listing 3.2 – Specific objects in the Oracle RAM data buffers

This is an interesting report because we see three object types (tables, indexes, and partitions), and we also see the sub-sets of the DEFAULT pool for KEEP and RECYCLE. Also, note that all indexes are defined in the largest supported block size (db_32k_cache_size), and multiple buffer pools of 4K, 8K, 16K and 32K sizes are defined.

The output of this script is somewhat confusing because of the repeated DEFAULT buffer pool name. This is misleading because the KEEP and RECYCLE buffer pools are sub-sets of db_cache_size and can ONLY accommodate objects with the DEFAULT db_block_size.

Conversely, any block sizes that are NOT the default db_block_size, go into the buffer pool named DEFAULT. As you can see from the output listing, there are really 6 mutually exclusive and independently-sized buffer pools, and four of them are called "DEFAULT."

It is interesting to run this report repeatedly because the Oracle data buffers are so dynamic. Running the script frequently allows us to view the blocks entering and leaving the data buffer. We can see the midpoint insertion method in action and the hot and cold regions as they update. Each time a block is re-referenced it moves to the head of the MRU chain on the hot side of the data buffer. Blocks that are accessed less frequently will age-out, first moving into the cold region and eventually being paged-out to make room for new incoming blocks.

This buf_blocks.sql script is even more important when considering a decrease to a cache size. When you issue an alter system command to decrease the cache size, Oracle will grab pages from the least recently used (LRU) end of the buffer. Depending on the amount of RAM removed, an alter system command will un-cache data blocks that may be needed by upcoming SQL statements.


The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Complete Oracle Script Collection Available

Burleson Consulting shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, BC experts share their secrets for navigating the Oracle data dictionary.

Packed with 681 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

BC has priced this collection of 681 scripts at $79.95.  You can download them immediately at this link:




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.