Serial direct path reads were first introduced in Oracle 11g to prevent wiping out a large number of buffers from the buffer cache by a serial large table scan. As a result, big tables accessed via serial Full Table Scans bypass the Buffer Cache and read data from the Data Files into the users’ Program Global Area (PGA). This prevents flooding of the Buffer Cache, but subsequent SELECTs on big table always access the disk thereby degrading the performance.

If a big table is accessed repeatedly using serial FTS, and it is desirable to avoid repeated disk reads during subsequent serial full table scans, there are a few options according the version of your Oracle instance. Prior to 12.1.0.2c, the data in big tables can be cached in buffer cache using two different methods:

• Use CACHE: If a table has the cache option on it and it is full scanned, the blocks retrieved are placed onto the most recently used end of the Least Recently Used (LRU) list in the default buffer cache. Although it makes them less prone to being aged out, blocks of cached table might be removed from CACHE when space is needed. Since blocks of cached tables are placed in the default buffer cache itself, they can evict/get evicted by the blocks of other tables (without CACHE option) assigned to the default buffer cache.

• Use the KEEP Pool: This causes blocks of certain tables to be placed in a separate memory area allocated in the Buffer Cache called Keep Pool. Since Keep Pool is not a subset of default buffer cache, objects assigned to KEEP Pool do not interfere with the objects in the default buffer cache. However, the LRU behavior of KEEP Pool is same as the default buffer cache, so if the KEEP Pool is smaller than the number of blocks to be kept, some blocks will be forced to age out of the cache.

Both of the above options:

• Are intended to be practiced for small, frequently-accessed objects which are less than 10% of the buffer cache.
• Come into effect only for the individual tables and indexes explicitly specified by the administrator.
• Would cause an aged-out table to be loaded into memory again only after another full table scan.

Hence, prior to 12.1.0.2c, there wasn’t any method that would allow you to automatically cache frequently accessed big tables into memory.

Oracle Database 12.1.0.2c introduces Automatic big table cache (ABTC), which:

• Automatically caches scanned big objects in the Big Table Cache – an optional section of the buffer cache reserved for storing large objects, thereby avoiding direct path reads.
• Tracks only big tables and no small tables where big table is any table larger than _small_table_threshold (= 2% of _db_block_buffers). ABTC uses multiple criteria in addition to the size of the object and the size of the Big Table cache to decide which objects are stored in Big Table Cache.
• Uses a temperature-based, object-level replacement algorithm to manage the big table cache contents instead of the traditional LRU-based, block-level replacement algorithm used by the buffer cache. Temperature of an object is incremented every time it is accessed, and determines whether an object will get loaded into the CACHE and whether it stays there or not. When caching multiple large objects, the database gives preference to hotter (more popular) objects. Moreover, an object with a higher temperature can replace other, already-cached colder objects thereby pushing them fully / partially to disk.
• Prevents objects from dropping out of the Big Table Cache once they have been assigned to it. Objects in Big Table Cache which are pushed to disk by hotter objects are automatically loaded back into memory when their temperature rises or sufficient memory is available.
• Caches partial objects when objects cannot be fully cached. For example, if available memory is sufficient to cache only 90% of a hot table, then instead of cyclically reading blocks into memory and evicting the least recently used ones – a phenomenon known as thrashing – the database caches 90% of the object while remaining 10% of the blocks are left on disk.
• Can be used for Serial as well as parallel full scans of large objects in Single Instance environments.
• Is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.
• Can cache
  • Non-partitioned tables
  • Partitions and sub-partitions of partitioned tables
  • Indexes
  • Partitions and sub-partitions of partitioned indexes
• Is designed primarily to enhance performance for data warehouse workloads, but also improves performance in mixed workloads.

ABTC Configuration:

The percentage of the buffer cache size used for automatic big table caching can be set using the initialization parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET. The default for DB_BIG_TABLE_CACHE_PERCENT_TARGET is 0 (disabled) and the upper limit is 90, so as to reserve at least 10% of the buffer cache for storing objects other than big objects. This parameter can be dynamically changed if the workload changes.

Whereas in single instance configuration, ABTC can be configured only by setting DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter to a non-zero value, in Oracle Real Application Clusters (Oracle RAC) configuration, PARALLEL_DEGREE_POLICY initialization parameter also needs to be set to AUTO or ADAPTIVE.

Dynamic Performance Views associated with ABTC

The V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS views provide information about the big table cache.

• V$BT_SCAN_CACHE Shows the parameters and status of the big table cache.
  • BT_CACHE_ALLOC: Current ratio of the Big Table cache section to the buffer cache
  • BT_CACHE_TARGET: Target ratio of the Big Table cache section to the buffer cache
  • OBJECT_COUNT: Number of objects tracked by the Big Table cache section
  • MEMORY_BUF_ALLOC: Number of memory buffers allocated by the Big Table cache section to objects
  • MIN_CACHED_TEMP: Minimum temperature of the object currently cached by the Big Table cache section
  • CON_ID: The ID of the container to which the data pertains. Possible values include:
    • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
    • 1: This value is used for rows containing data that pertain to only the root
    • n: Where n is the applicable container ID for the rows containing data
• V$BT_SCAN_OBJ_TEMPS Shows the active objects currently tracked by the big table cache.
  • TS#: Tablespace where the object resides
  • DATAOBJ#: Data object number (objd)
  • SIZE_IN_BLKS: Size of the object being scanned on this instance, in blocks
  • TEMPERATURE: Temperature of this object
  • POLICY: Caching policy of this object. Possible values are:
    • MEM_ONLY: This object will be fully cached in memory.
    • MEM_PART: This object will be partially cached in memory and some portion will remain on disk and will not be cached.
    • DISK: This object will not be cached in memory or flash for the scan at all.
  • CACHED_IN_MEM: The number of blocks that are cached/allocated in memory for this object
  • CON_ID: The ID of the container to which the data pertains. Possible values include:
    • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
    • 1: This value is used for rows containing data that pertain to only the root
    • n: Where n is the applicable container ID for the rows containing data

In this article, I will demonstrate ABTC for serial scans in a 12.1.0.2c single instance database.

• Let’s first check the size of the default buffer cache and number of buffers in it.

• Ensure that full database caching is disabled as it is not compatible with Automatic Big Table Caching.

• Let’s check the value of the parameter _small_table_threshold, since an object occupying a number blocks higher than this value is considered big. It can be seen that _small_table_threshold defaults to 2% of _db_block_buffers.

• Verify that Hr.big_table1 has 3928 blocks (>_small_table_threshold) so that it qualifies as a big table and can leverage ABTC.

• Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 (default) so that ABTC is disabled.

• Verify that no space has been allocated to the big table cache:

• Issue a serial scan of HR.BIG_TABLE1 and verify that a direct path reads take place:

• Execute the same query again. The same number of physical reads as earlier again indicates that the results of the last query were not read into the buffer cache and direct reads are taking place.

• Configure Big table Cache to 65%
• The number of buffers reserved in buffer cache for ABTC = 0.65 * number of buffers in buffer cache
  = 0.65 * 18130 = 11784.5 ≈ 11784

• Verify that 65% of buffer cache has been ‘reserved’ for big table caching, and that no objects are
  presently cached in ABTC.

• Perform a serial scan of some small tables and verify that no objects have been loaded into ABTC since the sizes of these tables are smaller than _small_table_threshold.

• Scan the big table HR.BIG_TABLE1 serially and note the time taken. Verify that:
  • Number of buffers allocated to objects in ABTC = Size of the HR.big_table1 in blocks = 3928
  • HR.big_table1 has been completely populated in ABTC (POLICY = MEM_ONLY and SIZE_IN_BLKS = CACHED_IN_MEM)
  • HR.big_table1 has been assigned a temperature of 1000 (TEMPERATURE = 1000)

Hence, out of 11784 buffers allocated to ABTC, 3928 buffers have been occupied by HR.big_table1 so that (11784 – 3928) = 7856 buffers are still free.

• Re-execute the query and verify that the time now taken (0.02s) is less than earlier (0.12s) as required blocks were available in memory (0 physical reads).

• Verify that the temperature of the table HR.big_table1 has increased to 2000 due to repeated access:

• Create another big table, HR.big_table2, a copy of HR.big_table1. Scan it serially. Note that:
  • The number of buffers allocated in ABTC = (2*Size of the HR.big_table1 in blocks) = (2*3928) = 7856.
  • HR.big_table2 gets completely loaded into ABTC (POLICY = MEM_ONLY and SIZE_IN_BLKS = CACHED_IN_MEM) with a temperature of 1000.
  • Temperature of HR.big_table1 has further increased to 3000.
  • Both the tables are totally accommodated in ABTC.

• Create another big table – HR.big_table3 – which is twice the size of HR.big_table1 and scan it serially. Verify that:
  • Temperature of HR.big_table1 has increased to 4000 because it is accessed while creating table HR.big_table3.
  • The temperature of HR.big_table3 is 2000 because it is accessed twice – during insert as well as during select.
  • HR.big_table3 gets partially loaded into ABTC (POLICY = MEM_PART and SIZE_IN_BLKS > CACHED_IN_MEM).
  • HR.big_table3 has evicted HR.big_table2 (POLICY = DISK), since HR.big_table2 has the lowest temperature (1000) of the 3 tables in ABTC.
  • Although HR.big_table2 has been evicted from memory, it is still a candidate of ABTC. As soon as its temperature rises or free buffers are available in ABTC, it will be automatically read from disk into ABTC.

• Perform a full table scan of hr.big_table2 twice to increase its temperature. Verify that:
  • The temperature of hr.big_table2 rises from 1000 to 3000.
  • HR.big_table2 automatically gets fully loaded into memory while pushing more of hr.big_table3, having a lower temperature (2000), to disk.

• Increase the size of ABTC to 90% of the buffer cache verify that:
  • All the three big tables are fully loaded into memory automatically without requiring a full scan.

• Try to increase the size of ABTC to 95% of the buffer cache – this fails as ABTC can be configured to maximum of 90% of buffer cache.

• Shrink ABTC back to 65% of the buffer. Verify that HR.Big_table3 gets partly evicted since it has the lowest temperature:

• Flush the buffer cache and verify that objects still remain loaded in ABTC. The instance needs to be restarted in order to free the buffers in ABTC.

Summary:

Oracle Database 12.1.0.2c introduces Automatic big table cache (ABTC) which:

• Automatically caches scanned big objects in Big table cache – an optional section of the buffer cache reserved for storing large objects, thereby avoiding direct path reads.
• Tracks only big tables and no small tables.
• Uses a temperature-based, object-level replacement algorithm to manage the big table cache contents instead of the traditional LRU-based, block-level replacement algorithm used by the buffer cache.
• An object with a higher temperature can replace other already-cached colder objects thereby pushing them fully / partially to disk.
• Objects in Big Table Cache which are pushed to disk by hotter objects are automatically loaded back into memory when their temperature rises or sufficient memory is available.
• Caches partial objects when objects cannot be fully cached.
• Is designed primarily to enhance performance for data warehouse workloads, but also improves performance in mixed workloads.

References:

• https://docs.oracle.com/database/121/VLDBG/GUID-A553169D-C6CD-443E-88C3-B746D5E32923.htm#VLDBG14145
• http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN00207
• http://progeeking.com/2014/02/25/the-big-q-direct-path-and-cell-offloading/