Oracle Database, Back to Basics – SGA, Buffer Cache & DBWR

The last installment to this series will be huge! I will be sharing about System Global Area (SGA), Database Buffer Cache and Database Write (DBWR) process. I am constantly amazed by the group of people who designed Oracle database. Without these 3  critical components in Oracle Database, your database will be bounded by the limitations of 15k RPM spinning disks (Before your SSDs!) and the performance will be crawling like tortoise!

SGA is a group of shared memory structures. It contains several memory pool like;

  1. Buffer Cache – Holds copy of data blocks read from datafiles
  2. Redo Buffer – Holds changes to the database
  3. Shared Pool – Holds parsed SQL or PL/SQL query to be shared among all the sessions for resource sharing, 2 user running the same SQL query will use the same shared SQL area!
  4. Large Pool – Optional, for your RMAN operations and parallelism operations
  5. Etc…

Buffer Cache

Buffer Cache are organized into 2 lists;

  1. Write List => Dirty buffer waiting to be written to the data files by DBWR
  2. Least Recent Used List, LRU List.
      • Free Buffer => Not in used
      • Pinned Buffer => Currently being accessed
      • Dirty Buffer => Dirty buffers that are not moved to write list yet

The LRU list acts like a FIFO algorithm, imagine like a pipe. A buffer that is recently accessed will be thrown into the top of LRU (also known as the MRU, most recently used). As more buffers are accessed and moved to the MRU, the dirty buffers will age out towards the bottom of the LRU list.

When the Oracle user process requires a data block, it searches for it in the buffer cache. If it finds the data in the cache, it’s a cache hit and the data is being read directly from memory. If the data is not found in the cache, it’s a cache miss and the data block must be loaded from the data file to a free buffer in the buffer cache. Obviously, reading the data from memory is much faster that reading it from disks.

The process searches the LRU list, starting at the bottom of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the top of the LRU list. If an Oracle Database user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBWR to write some of the dirty buffers to disk.

Database Write, DBWR

DBWR job is to write data from the database buffer cache to the data files. The DBWR writes the data based on the write list in the buffer cache. When does the flushing of dirty buffer occurs?

  1. Unable to find free buffer after scanning a threshold number of buggers in the buffer cache.
  2. Will periodically writes buffer to advance in checkpoint which will be be in-sync between the redo theads and datafile headers.

Important Concepts!

Dirty buffer does NOT mean that it is committed or uncommitted data. It merely means that the data block in the data file is not consistent with the data in buffer cache, it has been modified by the database instance. When the uncommitted data is being flushed by the DBWR to the data file, a checkpoint SCN will be updated in the control file but not to the data file headers. At this point of time, if another process tries to access the same piece of data block, it will read the SCN from the data file header and knows that it does not have the consistent data (control file SCN does not tally with data file SCN) and reads the consistent data from the UNDO segment.

I think it’s great to re-look at database concepts from time to time. Sometimes people just forget stuff, especially when you got too much on your plate.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s