Database Buffer Cache
The database buffer cache is a memory structure in SGA. Buffers in the DB cache are used to hold blocks of data read from the data files.
Different buffer caches
The buffer cache can be split in 5 different caches to allow for different block sizes:
- DEFAULT CACHE : To hold blocks of size DB_BLOCK_SIZE. Its size is determined by the parameter DB_CACHE_SIZE
- DB NK CACHE : To hold blocks of size = nK where n = 2,4,8,16,32 where n is not equal the size of the default block (DB_BLOCK_SIZE). Its size is determined by the parameter DB_NK_CACHE_SIZE
The buffer cache can be split in 3 different caches for different behaviour:
- DEFAULT CACHE : Cache where blocks of those objects are stored which are not assigned to keep or recycle cache or assigned default cache.
- KEEP CACHE : Cache to hold blocks of small sized objects for a longer time.Its size is determined by the parameter DB_KEEP_CACHE_SIZE.
- RECYCLE CACHE : Cache designed to quickly age out blocks of rarely accessed large sized objects. Its size is determined by the parameter DB_RECYCLE_CACHE_SIZE.
Each buffer in any of the above caches can be in any of the following states :
- FREE : Free buffers can be further of two kinds:
- Empty or unused
- Buffers which are identical to the blocks on disk because
- they have not been dirtied or
- they were dirtied and have been written to disk
- PINNED : 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 : 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
Oracle checkpointing: writing dirty blocks to disk
A checkpoint performs the following three operations:
- Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.
- The latest SCN is written (updated) into the datafile header.
- The latest SCN is also written to the controlfiles. The update of the datafile headers and the control files is done by the LGWR(CKPT if CKPT is enabled). As of version 8.0, CKPT is enabled by default.
A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) fromthe RAM buffer cache to the database datafiles. This happens when one of the following conditions appear:
- The database is issued a shutdown command.
- A manual "alter system checkpoint" is issued
- 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.
- After a redo log switch
- Other reasons. The algorithm is complex, and you can’t be certain with all the changes that occur with each software release.
The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header
The SCN of the last checkpoint can be found in v$database.checkpoint_change#.
Oracle Read Consistency
To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this through undo data.
Whenever a user modifies data, Oracle Database creates undo entries, which it writes to undo segments ("Undo Segments"). The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.