Data Buffer Cache  «Prev  Next»
Lesson 2 Tuning the Oracle data buffer
Objective Describe the components of the Oracle data buffer.

Oracle Buffer Cache

The in-memory area of the (SGA) System Global Area is where incoming Oracle data blocks are kept is named the buffer cache. On standard UNIX databases, the data is read from disk into the UNIX buffer where it is transferred into the Oracle buffer.
The size of the buffer cache can have a huge impact on Oracle system performance. The larger the buffer cache, the greater the likelihood that data from a prior transaction will reside in the buffer, thereby avoiding a physical disk I/O. It would be ideal if you could create one buffer for each database page, ensuring that Oracle would read each block only once. However, the costs of memory in the real world make this prohibitive.
At best, you can only allocate a small number of real-memory buffers, and Oracle will manage this memory for you. Oracle utilizes a (LRU) least-recently-used algorithm to determine which database pages are to be flushed from memory.
The following SlideShow will guide you through the buffer utilization process step-by-step.

Buffer Utilization Process

1) Buffer Utilization 1 2) Buffer Utilization 2 3) Buffer Utilization 3 4) Buffer Utilization 4 5) Buffer Utilization 5 6) Buffer Utilization 6 7) Buffer Utilization 7 8) Buffer Utilization 8 9) Buffer Utilization 9

Program 1 Program 2 Program 3 Program 4 Program 5 Program 6 Program 7 Program 8 Program 9
Oracle Buffer Cache

Cache size Database blocks

Just as the size of the buffer cache can have dramatic impact on your system, so will the database block size.
Roll your cursor over the areas outlined in red to see pop-up explanations of database blocks.

Dirty Blocks
  1. This is the permanent residence for the Oracle data blocks.
  2. These are new data blocks.
  3. This is the user process that is requesting to see a row within an Oracle data block.
  4. This is the Oracle data buffer cache which contains data blocks that have been read from disk into the Oracle data buffer cache.
  5. These are blocks that have been modified but not yet written back to disk
  6. This is the Oracle background database writer process.

DBWR (DataBase Writer) is an Oracle background process created when you start a database instance. The DBWR writes modified data (dirty buffers) from the SGA into the Oracle database files. When the SGA data buffer cache fills, the DBWR process selects buffers using an LRU algorithm and writes them to disk.

Database Blocks
For batch-oriented reporting databases, very large block sizes are always recommended. As a general rule, 8K block sizes (db_block_size) will benefit most Oracle databases.

Control block size

The db_block_size parameter is used to control the physical block size of the data files, and unlike other relational databases, Oracle allocates the data files on your behalf when the CREATE TABLESPACE command is issued. One of the worst things that can happen to a buffer cache is the running of a full-table scan on a large table. In order to optimize performance, Oracle allows for several extensions to the basic parameters, and one of the most important interactions is between the db_block_size parameter and the db_file_multiblock_read_count. We will review this in a later lesson. I/O is the single most important slowdown in a client/server system, and the more relevant the data that can be grabbed in a single I/O, the better the performance. If you make appropriate use of Oracle clusters, you can reap dramatic performance improvements by switching to large block sizes. In the next lesson, you will learn to calculate the buffer hit ratio.