Automatic big table caching for full table scans
For "Full Table Scan" operations, the Oracle database uses an internal algorithm for use via the buffer cache. If the table is small (SMALL), the buffer cache is used for access. If the table is large (LARGE), however, "direct path reads" are used. This means that the data is read directly from the data files into the PGA. The parameter _small_table_threshold sets the lower limit for the size of small (SMALL) tables. In Oracle Database 12c, further internal criteria are also used to determine which tables are temporarily stored via the cache. In order to be independent of the standard behavior of the buffer cache for parallel and serial "Full Table Scans" described above, a new cache area in the buffer cache has been introduced in Oracle Database 12c - the so-called Automatic Big Table Caching (ABTC for short).
a certain part of the buffer cache is reserved for storing large objects or parts of them, so that queries can benefit from caching. Instead of "direct path reads", the big table cache is then used. ABTC uses several criteria to decide which objects can use the big bolivia telegram screening table cache. The decisive factors for use are not only the size of the object and the size of the big table cache. In contrast to the standard buffer cache behavior, which is based on the block level oriented LRU algorithm, the "temperature" of the objects (not blocks) plays the main role when using the big table cache. It is important to know that ABTC is only supported with parallel query in Oracle Real Application Clusters (Oracle RAC) environments; in single instance environments, ABTC can also be used with serial queries.
The setup is simple and can be done during operation. ABTC is activated in a single instance environment using the dynamic initialization parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET. This parameter reserves a dedicated portion of the buffer cache (in percent). The process can be controlled using the two special V$ views V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS.