Exploring Oracle Internals -
Database Buffer Cache
The database buffer cache is an integral part of the Oracle 11g
SGA that provides the following tasks:
- Storage for data blocks that have been retrieved from data
files
- Provides optimization boost for DML operations (UPDATES)
- Managed via the LRU algorithm
Whenever a query is processed by Oracle 11g, the server process
looks in the database buffer cache to locate any blocks that it
needs. In the event that the block is not found in the database
buffer cache, Oracle reads the block from the datafiles and then
places a copy into the database buffer cache. The purpose of the
LRU (Least Recently Used) algorithm is to age out buffers that are
not accessed recently in order to free up space for new blocks in
the database buffer cache.
To manage the database buffer cache, use Automatic Memory
Management with Oracle 11g. Then the two database initialization
parameters, memory_target and memory_max_target, will perform the
automatic tuning sizing for the database buffer cache along with
the other components of the SGA for Oracle 11g.
The database cache is composed of three different independent
subcaches:
- db_cache_size is the default buffer cache for Oracle 11g
- db_keep_cache_size is for the KEEP buffer cache
- db_recycle_ cache_size is for the RECYCLE buffer cache
- db_nk_ cache_size
These subcaches can be dynamically resized using the ALTER
SYSTEM commands. Each buffer in the database buffer cache is equal
in size to the size of an Oracle block which is determined by the
db_block_sizedatabase
initialization parameter.
In order to obtain the optimum size for each of these subcaches
as well as the database buffer cache, the performance advisors in
Oracle 11g Enterprise Manager can be used as well as the
v$db_cache_advice dynamic performance view.
SQL> desc v$db_cache_advice
Name Null? Type
----------------------------------------- --------
----------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
ADVICE_STATUS VARCHAR2(3)
SIZE_FOR_ESTIMATE NUMBER
SIZE_FACTOR NUMBER
BUFFERS_FOR_ESTIMATE NUMBER
ESTD_PHYSICAL_READ_FACTOR NUMBER
ESTD_PHYSICAL_READS NUMBER
ESTD_PHYSICAL_READ_TIME NUMBER
ESTD_PCT_OF_DB_TIME_FOR_READS NUMBER
ESTD_CLUSTER_READS NUMBER
ESTD_CLUSTER_READ_TIME NUMBER
SQL> alter system set db_cache_advice=on;
System altered.
SQL>
alter system set db_cache_advice=READY;
column size_for_estimate format 999,999,999,999
column buffers_for_estimate format 999,999,999
column estd_physical_read_factor format 999.90
column estd_physical_reads format 999,999,999
SELECT size_for_estimate, buffers_for_estimate
, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';
Estd
Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 10% of
Current
Size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475
Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 200%
Size
The above example shows that at nearly twice the size of
current database buffer cache, one can obtain substantial
performance improvement over the number of physical reads that
occur with the Oracle 11g database.
