Changes in Oracle Database 12c Release

This optional, configurable portion of the database buffer cache uses an algorithm for large tables based on object type and temperature. In single-instance and Oracle RAC databases, parallel queries can use the big table cache when the

DB_BIG_TABLE_CACHE_PERCENT_TARGET 

initialization parameter is set to a nonzero value, and PARALLEL_DEGREE_POLICY is set to auto or adaptive. In a single-instance configuration only, serial queries can use the big table cache when DB_BIG_TABLE_CACHE_PERCENT_TARGET is set.

A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs a physical I/O to copy the buffer from either the flash cache or disk into memory, and then a logical I/O to read the cached buffer.

To make buffer access efficient, the database must decide which buffers to cache in memory, and which to access from disk. The database uses the following algorithms:

1. LRU-based, block-level replacement algorithm: This sophisticated algorithm, which is the default, uses a least recently used (LRU) list that contains pointers to dirty and nondirty buffers. The LRU list has a hot end and cold end. A cold buffer is a buffer that has not been recently used. A hot buffer is frequently accessed and has been recently used. Conceptually, there is only one LRU, but for data concurrency the database actually uses several LRUs.
2. Temperature-based, object-level replacement algorithm: Starting in Oracle Database 12c Release 1 (12.1.0.2), the automatic big table caching feature enables table scans to use a different algorithm in the following scenarios:
   1. Parallel queries: In single-instance and Oracle Real Applications Cluster (Oracle RAC) databases, parallel queries can use the big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a nonzero value, and PARALLEL_DEGREE_POLICY is set to auto or adaptive.
   2. Serial queries: In a single-instance configuration only, serial queries can use the big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a nonzero value.

When a table does not fit in memory, the database decides which buffers to cache based on access patterns. For example, if only 95% of a popular table fits in memory, then the database may choose to leave 5% of the blocks on disk rather than cyclically reading blocks into memory and writing blocks to disk. A phenomenon known as thrashing. When caching multiple large objects, the database considers more popular tables hotter and less popular tables cooler, which influences which blocks are cached.
The DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter sets the percentage of the buffer cache that uses this algorithm.