Basics of the Database Buffer Cache in Oracle 12c - dummies

Basics of the Database Buffer Cache in Oracle 12c

By Chris Ruel, Michael Wessler

Oracle 12c’s database buffer cache is typically the largest portion of the SGA. It has data that comes from the files on disk. Because accessing data from disk is slower than from memory, the database buffer cache’s sole purpose is to cache the data in memory for quicker access.

The database buffer cache can contain data from all types of objects:

  • Tables

  • Indexes

  • Materialized views

  • System data

In the phrase database buffer cache, the term buffer refers to database blocks. A database block is the minimum amount of storage that Oracle reads or writes. All storage segments that contain data are made up of blocks. When you request data from disk, at minimum Oracle reads one block.

Even if you request only one row, many rows in the same table are likely to be retrieved. The same goes if you request one column in one row. Oracle reads the entire block, which most likely has many rows, and all columns for that row.

It’s feasible to think that if your departments table has only ten rows, the entire thing can be read into memory even if you’re requesting the name of only one department.

Buffer cache state in Oracle 12c

The buffer cache controls what blocks get to stay depending on available space and the block state (similar to how the shared pool decides what SQL gets to stay). The buffer cache uses its own version of the LRU algorithm.

A block in the buffer cache can be in one of three states:

  • Free: Not currently being used for anything

  • Pinned: Currently being accessed

  • Dirty: Block has been modified but not yet written to disk

Free blocks

Ideally, free blocks are available whenever you need them. However, that probably isn’t the case unless your database is so small that the whole thing can fit in memory.

The LRU algorithm works a little differently in the buffer cache than it does in the shared pool. It scores each block and then times how long it has been since it was accessed. For example, a block gets a point each time it’s touched.

The higher the points, the less likely the block will be flushed from memory. However, it must be accessed frequently or the score decreases. A block has to work hard to stay in memory if the competition for memory resources is high.

Giving each block a score and time prevents this type of situation from arising: A block is accessed heavily at the end of the month for reports. Its score is higher than any other block in the system. That block is never accessed again.

It sits there wasting memory until the database is restarted or another block finally scores enough points to beat it out. The time component ages it out very quickly after you no longer access it.

Pinned blocks

A block currently being accessed is a pinned block. The block is locked (or pinned) into the buffer cache so it cannot be aged out of the buffer cache while the Oracle process (often representing a user) is accessing it.

Dirty blocks

A modified block is a dirty block. To make sure your changes are kept across database shutdowns, these dirty blocks must be written from the buffer cache to disk. The database names dirty blocks in a dirty list or write queue.

You might think that every time a block is modified, it should be written to disk to minimize lost data. This isn’t the case — not even when there’s a commit (when you save your changes permanently)! Several structures help prevent lost data.

Furthermore, Oracle has a gambling problem. System performance would crawl if you wrote blocks to disk for every modification. To combat this, Oracle plays the odds that the database is unlikely to fail and writes blocks to disk only in larger groups.

Don’t worry; it’s not even a risk against lost data. Oracle is getting performance out of the database right now at the possible expense of a recovery taking longer later. Because failures on properly managed systems rarely occur, it’s a cheap way to gain some performance. However, it’s not as if Oracle leaves dirty blocks all over without cleaning up after itself.

Block write triggers in Oracle 12c

What triggers a block write and therefore a dirty block?

  • The database is issued a shutdown command.

  • A full or partial checkpoint occurs — that’s when the system periodically dumps all the dirty buffers to disk.

  • A recovery time threshold, set by you, is met; the total number of dirty blocks causes an unacceptable recovery time.

  • A free block is needed and none are found after a given amount of searching.

  • Certain data definition language (DDL) commands. (DDL commands are SQL statements that define objects in a database.)

  • Every three seconds.

  • Other reasons. The algorithm is complex, and you can’t be certain with all the changes that occur with each software release.

The fact is the database stays pretty busy writing blocks in an environment where there are a lot changes.