The Buffer Cache

What is it?

The Buffer Cache is an area of memory set aside to store images of data blocks read from disk.

By far, the buffer cache comprises the largest portion of memory used by an Oracle instance. The data used by any application is first read into the buffer cache and then served to the process which needs it.

The most interesting and useful parts of Oracle—features such as read consistency, row level locking, among many others—are implemented at the basic level by managing blocks in the buffer cache in various ways, and presenting the correct block in the correct state to the process which needs it.

In a very real way, the buffer cache is the database, and everything else just supports it.

How does the Buffer Cache Improve Performance?

The main reason for having a buffer cache is to reduce the amount of time required to access a block of data.

The disk drive is a rapidly spinning platter coated with a magnetic medium, with regions magnetize in ways that encode information.

The greatest length of time spent reading information from the disk is the time required for two things to happen: First, for the read/write head pivots toward or away from the center of the disk and positions itself over the correct track. Second, the platter spins so that the sector where the data has been written passes by the read/write head. The first time interval is called seek time. The second time interval is called latency.

The best mechanical systems such as disk drives are inherently slower than the best electronic systems such as random access memory. Roughly speaking it can take 1000 times as long to read a block from disk as it does to read it from memory. Reading a block from disk is an expensive operation.

This length of processing time can be avoided if the block is read just once, and then saved for later access, which is the basic purpose for the buffer cache.

A simple way to demonstrate this is with autotrace. This provides very basic performance measurements available in an oracle system, but it is still useful.

Here is an example:

First set up a test table TEST01

SQL> create table test01 as 
     select * from dba_objects 
     where rownum >= 10000

Next, we will query the count of the test table. This query will visit every block of the test table, since there is no index on the table, and return the value of 10,000.

SQL> set autotrace on
SQL> select count(*) from test01;

COUNT(*)
----------
10000

With autotrace on, Oracle follows up the query result with a report of the execution plan, and basic performance statistics:

Execution Plan
----------------------------------------------------------
Plan hash value: 1460474239

---------------------------------------------------------------------
| Id  | Operation        | Name   |  Rows | Cost (%CPU) | Time     |
---------------------------------------------------------------------
|  0  | SELECT STATEMENT |        |     1 |      40 (0) | 00:00:01 |
|  1  | SORT AGGREGATE   |        |     1 |             |          |
|  2  | TABLE ACCESS FULL| TEST01 | 11180 |      40 (0) | 00:00:01 |
---------------------------------------------------------------------

Note
—–
– dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
9 recursive calls
0 db block gets
205 consistent gets
130 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now, run the same query again.
SQL> /

COUNT(*)
———-
10000

For simplicity I’m only showing the statistics. Nothing else is of interest here.>

Statistics
———————————————————-
0 recursive calls
0 db block gets
134 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
[/php]

Observe that the physical reads has dropped from 130 to 0. In other words, on the second execution, Oracle found all the physical blocks it needed already in the buffer cache and therefore did not need to read any from disk.

This point can be driven home. We can flush the buffer cache, and then re-run the same query.

Now, run the same query again.

SQL> / 

SQL> create table test01 as
     select * from dba_objects where rownum >= 10000;

Table created.

SQL> set autotrace on

SQL> select count(*) from test01;

  COUNT(*)
----------
     10000

Execution Plan
----------------------------------------------------------
Plan hash value: 1460474239

---------------------------------------------------------------------
| Id | Operation         | Name   |  Rows | Cost (%CPU) | Time      |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |     1 |      40 (0) |  00:00:01 |
|  1 | SORT AGGREGATE    |        |     1 |             |           |
|  2 | TABLE ACCESS FULL | TEST01 | 11180 |      40 (0) |  00:00:01 |
---------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
   9  recursive calls
   0  db block gets
 205  consistent gets
 130  physical reads
   0  redo size
 526  bytes sent via SQL*Net to client
 524  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

SQL> /

COUNT(*)
----------
10000

Execution Plan
----------------------------------------------------------
Plan hash value: 1460474239

---------------------------------------------------------------------
| Id | Operation         | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT  | | 1 | 40 (0)| 00:00:01 |
|  1 | SORT AGGREGATE    | | 1 | | |
|  2 | TABLE ACCESS FULL | TEST01 | 11180 | 40 (0)| 00:00:01 |
---------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
 134  consistent gets
   0  physical reads
   0  redo size
 526  bytes sent via SQL*Net to client
 524  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed 

  COUNT(*)
----------
     10000

Execution Plan
----------------------------------------------------------
Plan hash value: 1460474239

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST01 | 11180 | 40 (0)| 00:00:01 |
---------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
 134 consistent gets
 131 physical reads
   0 redo size
 526 bytes sent via SQL*Net to client
 524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   1 rows processed
About the author

1 Response

Leave a Reply