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

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Oracle’s Seven Data Buffer Hit Ratios

The DBHR is a common metric used by Oracle tuning experts to measure the propensity of a row to be in the data buffer.  For example, a hit ratio of 95 percent means that 95 percent of row requests were already present in the data buffer, thereby avoiding an expensive disk I/O.  In general, as the size of the data buffers increases, the DBHR will also increase and approach 100 percent.

 

Oracle10g has a separate DBHR for all seven data buffer caches. For optimum performance, the Oracle DBA might consider a manual approach, such as turning off AMM, and monitor all seven data buffers and adjust their sizes based on each DBHR.  Oracle10g provides the exciting feature of allowing the number of RAM buffers within any of the data buffer caches to be changed dynamically. 

 

This is done through ALTER SYSTEM commands that allow the size of the buffers to be changed while Oracle remains available.  This means that the DBA can maximize performance in response to current statistics by manually de-allocating RAM from one data buffer and shifting it to another buffer cache.

 

The general rule is that the more data that can be retrieved from a single I/O, the better the overall hit ratio.  However, it is important to delve a little deeper to get a more complete understanding of how multiple data buffers operate.

Allocating Oracle10g Data Buffer Caches

It is important to know how multiple data buffers actually work.  As an example, the following buffer cache allocations might be defined in the 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

SEE CODE DEPOT FOR FULL SCRIPTS

 

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. 

 

Before Oracle9i, these were subsets of the DEFAULT pool, and 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 in the example is 1.5 gigabytes.  Of course, the total size must be less than the value of sga_max_size. In Oracle9i and beyond, the KEEP and RECYCLE became separate RAM areas.

 

At this point, the basic concepts behind the data buffers should be a little clearer, so it is an appropriate time to go deeper into the internals and see how AWR data can allow the monitoring and self-tuning of the data buffers.  Remember, AMM is reactive and only changes the pool sizes after performance has degraded.  By identifying trends in buffer utilization, the DBA can use the dbms_scheduler package to anticipate and self-tune the data buffers, thereby supplementing and improving AMM.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


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

Oracle® is the registered trademark of Oracle Corporation.