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.
SQL> select COMPONENT, CURRENT_SIZE/1024/1024 SIZE_MB 
     from v$sga_dynamic_components 
     where component = 'DEFAULT buffer cache';     

COMPONENT                                                           SIZE_MB
---------------------------------------------------------------- ----------
DEFAULT buffer cache                                                    148

SQL> SELECT name,block_size,buffers FROM v$buffer_pool;

NAME                 BLOCK_SIZE    BUFFERS
-------------------- ---------- ----------
DEFAULT                    8192      18130
  • Ensure that full database caching is disabled as it is not compatible with Automatic Big Table Caching.
SQL> select force_full_db_caching from v$database;

FORCE_FULL_DB_CACHING
------------------------------
NO
  • 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.
SQL>  SELECT a.ksppinm  "Parameter", a.ksppdesc "Description", c.ksppstvl "Value"
      FROM   x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
      WHERE  a.indx = b.indx AND a.indx = c.indx
       AND   p.name(+) = a.ksppinm
       AND   UPPER(a.ksppinm) IN ( UPPER('_db_block_buffers'), 
                                   UPPER('_small_table_threshold'));

Parameter                      Description                    Value
------------------------------ ------------------------------ ---------------
_db_block_buffers              Number of database blocks cach 19600
                               ed in memory: hidden parameter

_small_table_threshold         lower threshold level of table 392
                                size for direct reads
  • Verify that Hr.big_table1 has 3928 blocks (>_small_table_threshold) so that it qualifies as a big table and can leverage ABTC.
SQL> Select blocks from dba_tables where table_name = 'BIG_TABLE1';

    BLOCKS
----------
      3928
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 (default) so that ABTC is disabled.
SQL>alter system set db_big_table_cache_percent_target=0 scope=both; 
    Show parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_big_table_cache_percent_target    string      0
  • Verify that no space has been allocated to the big table cache:
SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
              0            0                0            1000
  • Issue a serial scan of HR.BIG_TABLE1 and verify that a direct path reads take place:
SQL> select value from v$mystat 
     where statistic#= (select statistic# from v$statname
                         where name='table scans (direct read)');
VALUE
-----
    1
SQL>set autot traceonly statistics 
    select count(*) from hr.big_table1;
    set autot off
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3861  consistent gets
       3858  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select value from v$mystat 
     where statistic#= (select statistic# from v$statname
                         where name='table scans (direct read)');
VALUE
-----
    2
  • 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.
SQL>set autot traceonly statistics 
    select count(*) from hr.big_table1;
    set autot off

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3861  consistent gets
       3858  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
  • 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
SQL>alter system set db_big_table_cache_percent_target = 65;
    sho parameter db_big_table_cache_percent_target 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_big_table_cache_percent_target    string      65
  • Verify that 65% of buffer cache has been ‘reserved’ for big table caching, and that no objects are
    presently cached in ABTC.
SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             65            0                0            1000
SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem 
     from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;
    
no rows selected
  • 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.
SQL>select count(*) from hr.departments;
SQL> select count(*) from hr.employees;
SQL>select count(*) from hr.locations;
SQL>select count(*) from hr.countries;
SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             65            0                0            1000
  • 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.

SQL> set timing on;
     select count(*) from hr.big_table1;
     set timing off;  
Elapsed: 00:00:00.12

SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP 
     from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             65            1             3928            1000
SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem from
   v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;     

OBJECT_NAME          SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM
-------------------- ------------ ----------- ---------- -------------
BIG_TABLE1                   3928        1000 MEM_ONLY            3928
  • 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).
  SQL>   set autot traceonly statistics
         set timing on;
         select count(*) from hr.big_table1;
         set timing off;
         set autot off
 
Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3865  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
  • Verify that the temperature of the table HR.big_table1 has increased to 2000 due to repeated access:
SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;  

OBJECT_NAME          SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM
-------------------- ------------ ----------- ---------- -------------
BIG_TABLE1                   3928        2000 MEM_ONLY            3928
  • 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.
SQL>create table hr.big_table2 as select * from hr.big_table1;
    Select count(*) from hr.big_table2;

SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from 
v$bt_scan_cache;   

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             65            2             7856            1000

SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;   

OBJECT_NAME          SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM
-------------------- ------------ ----------- ---------- -------------
BIG_TABLE1                   3928        3000 MEM_ONLY            3928
BIG_TABLE2                   3928        1000 MEM_ONLY            3928
  • 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.
  SQL>create table hr.big_table3 as select * from hr.big_table1;
      Insert into hr.big_table3 select * from hr.big_table3;
      Select count(*) from hr.big_table3;

SQL> select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from
     v$bt_scan_cache;
BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             65            3            11784            1000

SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;   
  
OBJECT_NAME               SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
-------------------- ----------------- ----------- --------------- -------------
BIG_TABLE1                        3928        4000 MEM_ONLY                 3928
BIG_TABLE2                        3928        1000 DISK                     3928
BIG_TABLE3                        7874        2000 MEM_PART                 7856
  • 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.
SQL> select count(*) from hr.big_table2;
SQL> select count(*) from hr.big_table2;
SQL>  Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;    
OBJECT_NAME               SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
-------------------- ----------------- ----------- --------------- -------------
BIG_TABLE1                        3928        4000 MEM_ONLY                 3928
BIG_TABLE2                        3928        3000 MEM_ONLY                 3928
BIG_TABLE3                        7874        2000 MEM_PART                 3928
  • 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.
SQL>alter system set db_big_table_cache_percent_target = 90;

   select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from
     v$bt_scan_cache;  

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             90            3            11784            1000
         
SQL> Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;   
OBJECT_NAME               SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
-------------------- ----------------- ----------- --------------- -------------
BIG_TABLE1                        3928        4000 MEM_ONLY                 3928
BIG_TABLE2                        3928        3000 MEM_ONLY                 3928
BIG_TABLE3                        7874        2000 MEM_ONLY                 7874
  • 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.
SQL> alter system set db_big_table_cache_percent_target = 95;
alter system set db_big_table_cache_percent_target = 95
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 95 for parameter db_big_table_cache_percent_target, must be between 0 and 90
  • Shrink ABTC back to 65% of the buffer. Verify that HR.Big_table3 gets partly evicted since it has the lowest temperature:
SQL>alter system set db_big_table_cache_percent_target =65;

 Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;

OBJECT_NAME               SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
-------------------- ----------------- ----------- --------------- -------------
BIG_TABLE1                        3928        4000 MEM_ONLY                 3928
BIG_TABLE2                        3928        3000 MEM_ONLY                 3928
BIG_TABLE3                        7874        2000 MEM_PART                 3928
  • 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.
SQL> alter system flush buffer_cache;

     Select object_name, size_in_blks, temperature, policy, cached_in_mem 
from v$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id;
   OBJECT_NAME               SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
-------------------- ----------------- ----------- --------------- -------------
BIG_TABLE1                        3928        4000 MEM_ONLY                 3928
BIG_TABLE2                        3928        3000 MEM_ONLY                 3928
BIG_TABLE3                        7874        2000 MEM_PART                 3928

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:

Tags: ,