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

Allocating Oracle Data Buffer Caches

Let’s take a look at how multiple data buffers actually work. As an example, we might define the following buffer cache allocations in our initialization parameters.

db_block_size=32768 -- This is the system-wide
-- default block size

db_cache_size=3G -- This allocates a total of 3
-- gigabytes for all of the 32K
-- data buffers

db_keep_cache_size=1G -- Use 1 gigabyte for the KEEP pool

db_recycle_cache_size=500M -- Here is 500 meg for the RECYCLE pool
-- Hence, the DEFAULT pool is 1,500 meg

-- ***************************************************************
-- The caches below are all additional RAM memory (total=3.1 gig)
-- that are above and beyond the allocation from db_cache_size
-- ***************************************************************

db_2k_cache_size=200M -- This cache is reserved for random
-- block retrieval on tables that
-- have small rows.

db_4k_cache_size=500M -- This 4K buffer will be reserved
-- exclusively for tables with a small
-- average row length and random access

db_8k_cache_size=800M -- This is a separate cache for
-- segregating I/O for specific tables

db_16k_cache_size=1600M -- This is a separate cache for
-- segregating I/O for specific tables

What is the total RAM allocated to the data buffer caches in the example above? The total RAM is the sum of all the named buffer caches, plus db_cache_size. Hence, the total RAM in the example is 6,100 megabytes, or 6.1 gigabytes.

Remember, as subsets of the DEFAULT pool, the db_keep_cache_size and db_recycle_cache_size are subtracted from the db_cache_size. After subtracting the allocation for the KEEP and RECYCLE pools, the DEFAULT pool is 1.5 gigabytes in our example. Of course, the total size must be less than the value of sga_max_size.

Also, bear in mind that the assigned value of db_block_size is the default block size and cannot be used for another buffer. The db_block_size is 32K in our example, so we cannot allocate a db_32k_cache_size.

We have now defined seven totally separate data buffers. Table 3.1 lists each buffer, its total size, its defined block size, and the number of data blocks each buffer can hold.

Table 3.1 - Computing the block space for multiple data buffer caches

The next step is to create tablespaces for each of the block sizes. Oracle will then automatically load the tablespace blocks into the data buffer of the appropriate block size.

To illustrate, we mentioned that the db_2k_cache_size is suitable for tables comprised of small row sizes that are always accessed randomly. Small block sizes are also effective in preventing DML locking problems with bitmap indexes. Hence, we could define a 2K tablespace as follows:

create tablespace

Once the db_2k_cache_size data buffer is defined, Oracle will always load the 2k_tablespace blocks into it. We can use the Create Table As Select (CTAS) command to move all the appropriate tables into the new tablespace as shown:

-- ****************************************
-- First, disable all RI constraints
-- ****************************************
alter table customer disable constrant fk_cust_name;

-- ****************************************
-- Copy the table into a new tablespace
-- ****************************************
create table
as select

-- ****************************************
-- Rename the tables
-- ****************************************
rename customer to old_customer;
rename new_customer to customer;

-- ****************************************
-- Lastly, you must now transfer all RI constraints and indexes
-- ****************************************
create index pk_cust_idx on customer . . . ;
alter table customer add constraint fk_cust . . . ;

Now that we understand the basic concepts behind the data buffers, let's go deeper into the internals and see how STATSPACK data can allow us to monitor and self-tune the data buffers.


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.