In an earlier article, I discussed Automatic big table cache (ABTC) which is a new feature introduced in Oracle Database 12.1.0.2c. Automatic big table cache is an optional section of the buffer cache reserved for storing large objects. It tracks only big tables and automatically caches scanned big objects in big table cache thereby avoiding direct path reads. It caches partial objects when objects cannot be fully cached. It is primarily designed to enhance performance for data warehouse workloads, but also improves performance in mixed workloads. Though it can be used for Serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.

In my last article, I demonstrated ABTC for serial scans in a 12.1.0.2c single instance database. In this article, I will demonstrate ABTC for parallel scans in a 12.1.0.2c RAC database.

Current scenario:

  • Name of cluster: Cluster01
  • Number of nodes : 3
  • Name of RAC database (Non-CDB): orcl
  • Number of RAC database instances : 3

Overview:

This article will cover the steps needed to test ABTC for parallel scans.

  • Check the size of the default buffer cache and number of buffers in it for all the instances.
  • Ensure that full database caching is disabled on all the instances.
  • Check the value of the parameter _SMALL_TABLE_THRESHOLD.
  • Verify that Hr.big_table1 qualifies as a big table and can leverage ABTC.
  • Set PARALLEL_DEGREE_POLICY = AUTO on all the instances
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 (default) on all the instances so that ABTC is disabled on all the instances.
  • Verify that serial scan of HR.BIG_TABLE1 results in direct path reads.
  • Configure Big table Cache to 25% on all the instances.
  • Query the big table in serial to verify that direct reads take place and that it does not get cached in big table cache as serial scans are not supported in RAC.
  • Query the big table in parallel and verify that its fragments get distributed in big table cache across instances.
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 on instance 3 and verify that in that instance, the fragment of table which was cached in big table cache is no longer cached.
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET back to 25 on instance 3 and verify that the table data which was allocated to big table cache on instance 3, has again been read from disk into the big table cache.
  • Drop table HR.BIG_TABLE1 and verify that another big table HR.BIG_TABLE_PART has 3 partitions, each of which qualify as a big segment.
  • Scan partitions p1 and p2 of the table HR.BIG_TABLE1 – Data from both the partitions p1 and p2 is distributed across instances.
  • To disable caching the data from a partition in all instance except one, set parameter PARALLEL_LOCAL_FORCE = TRUE on instance 1.
  • Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.

Demonstration:

  • Check the size of the default buffer cache and number of buffers in it for all the instances:
SQL> SELECT inst_id, name,block_size,buffers FROM gv$buffer_pool order by 1;

   INST_ID NAME                                     BLOCK_SIZE    BUFFERS
---------- ---------------------------------------- ---------- ----------
         1 DEFAULT                                        8192      18760
         2 DEFAULT                                        8192      18760
         3 DEFAULT                                        8192      18760

SQL>   select inst_id, COMPONENT, CURRENT_SIZE/1024/1024 SIZE_MB
       from gv$sga_dynamic_components
        where component = 'DEFAULT buffer cache'
        order by 1;

   INST_ID COMPONENT                         SIZE_MB
---------- ------------------------------ ----------
         1 DEFAULT buffer cache                  160
         2 DEFAULT buffer cache                  160
         3 DEFAULT buffer cache                  160
  • Ensure that full database caching is disabled on all the instances.
SQL>select inst_id, force_full_db_caching 
    from gv$database order by 1;
    
   INST_ID FORCE_FULL_DB_CACHING
---------- ------------------------------
         1 NO
         2 NO
         3 NO
  • Check the value of the parameter _SMALL_TABLE_THRESHOLD, since an object occupying number blocks higher than this value is considered big.
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 18760
                               ed in memory: hidden parameter

_small_table_threshold         lower threshold level of table 375
                                size for direct reads
  • Verify that Hr.big_table1 qualifies as a big table and can leverage ABTC.
SQL> Select blocks from dba_tables where table_name = 'BIG_TABLE1';
     
    BLOCKS
----------
      3928
  • Set PARALLEL_DEGREE_POLICY = AUTO on all the instances
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 (default) on all the instances so that ABTC is disabled on all the instances.
SQL>Alter system set PARALLEL_DEGREE_POLICY = AUTO  ; 
    alter system set db_big_table_cache_percent_target=0 scope=both; 
    select inst_id, name, value 
    from gv$parameter
    where name in ('parallel_degree_policy','db_big_table_cache_percent_target')
    order by 2,1;

 INST_ID NAME                                     VALUE
---------- ---------------------------------------- ---------------
         1 db_big_table_cache_percent_target        0
         2 db_big_table_cache_percent_target        0
         3 db_big_table_cache_percent_target        0
         1 parallel_degree_policy                   AUTO
         2 parallel_degree_policy                   AUTO
         3 parallel_degree_policy                   AUTO
  • Verify that serial scan of HR.BIG_TABLE1 results in direct path reads.
SQL> select value from v$mystat where statistic#=
        (select statistic# from v$statname
          where name='table scans (direct read)');
VALUE
-----
    0
SQL>set autot traceonly statistics 
    select count(*) from hr.big_table1;
    set autot off

Statistics
----------------------------------------------------------
         91  recursive calls
          0  db block gets
       3937  consistent gets
       3861  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
          5  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
-----
    1

If we re-execute the query, almost the same number of physical reads appearing again confirm that a direct read was performed.

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 25% on all the instances.
SQL>alter system set db_big_table_cache_percent_target = 25;
     select inst_id, name, value from gv$parameter 
     where name in    ('parallel_degree_policy','db_big_table_cache_percent_target')
     order by 2,1;

   INST_ID NAME                                     VALUE
---------- ---------------------------------------- --------------------
         1 db_big_table_cache_percent_target        25
         2 db_big_table_cache_percent_target        25
         3 db_big_table_cache_percent_target        25
         1 parallel_degree_policy                   AUTO
         2 parallel_degree_policy                   AUTO
         3 parallel_degree_policy                   AUTO

We can verify that 25% of the buffer cache has been ‘reserved’ for big table caching on all the instances and no objects are cached in ABTC presently.

SQL> select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP 
     from gv$bt_scan_cache
      order by 1;

   INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
---------- --------------- ------------ ---------------- ---------------
         1              25            0                0            1000
         2              25            0                0            1000
         3              25            0                0            1000

SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from 
gv$bt_scan_obj_temps  b, dba_objects o 
where b.dataobj#  = o.data_object_id
order by 1,2;
    
no rows selected
  • Query the big table in serial and verify that direct reads take place and it does not get cached in big table cache as serial scans are not supported in RAC.
SQL>select value from v$mystat where statistic#=
       (select statistic# from v$statname
        where name='table scans (direct read)');
VALUE
-----
    2
SQL> set autot traceonly statistics
     set timing on;
     select count(*) from hr.big_table1;
     set timing off;
     set autot off

Elapsed: 00:00:00.63

Statistics
----------------------------------------------------------
          1  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>col value for 999
     select value from v$mystat where statistic#=
       (select statistic# from v$statname
        where name='table scans (direct read)');
  
VALUE
-----
    3

SQL>select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP 
     from gv$bt_scan_cache
     order by 1; 
  
   INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
---------- --------------- ------------ ---------------- ---------------
         1              25            0                0            1000
         2              25            0                0            1000
         3              25            0                0            1000
  • Query the big table in parallel to verify that direct path reads are not performed and that fragments of the whole of the table are distributed in big table cache across all three instances.
SQL>select value from v$mystat where statistic#=
       (select statistic# from v$statname
        where name='table scans (direct read)');
VALUE
-----
    3

SQL> set autot traceonly statistics
     set timing on;
     select /*+ full(big) parallel(big) */ count(*) from hr.big_table1 big;
     set timing off;
     set autot off

Elapsed: 00:00:01.80

Statistics
----------------------------------------------------------
        232  recursive calls
          0  db block gets
       4708  consistent gets
       3869  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
         10  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
-----
    3  

SQL>select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP 
     from gv$bt_scan_cache
     order by 1;  

    INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
---------- --------------- ------------ ---------------- ---------------
         1              25            1             1479            1000
         2              25            1             1170            1000
         3              25            1             1209            1000

SQL> Select inst_id, object_name, size_in_blks, temperature,
            policy, cached_in_mem 
     from  gv$bt_scan_obj_temps  b, dba_objects o
     where b.dataobj#  = o.data_object_id
     order by 1;

   INST_ID OBJECT_NAME     SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM
---------- --------------- ------------ ----------- ---------- -------------
         1 BIG_TABLE1              1479        1000 MEM_ONLY            1479
         2 BIG_TABLE1              1170        1000 MEM_ONLY            1170
         3 BIG_TABLE1              1209        1000 MEM_ONLY            1209

If we re-execute the query you can see that the time taken has reduced considerably, from 00:00:01.80 to 00:00:00.08, and that physical reads have also reduced from 3869 to 0 as all the data is read from big table cache.

SQL> set timing on;
     select /*+ full(big) parallel(big) */ count(*) from hr.big_table1 big;
     set timing off;
     set autot off
 
Elapsed: 00:00:00.08

Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
       4556  consistent gets
          0  physical reads
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET to 0 on instance 3 and verify that the part of the table which was cached in big table cache of that instance is no longer cached.
SQL>alter system set db_big_table_cache_percent_target=0 sid = 'orcl3'; 
    select inst_id, name, value 
    from gv$parameter 
    where name in ('parallel_degree_policy','db_big_table_cache_percent_target') 
    order by 2,1;

   INST_ID NAME                                     VALUE
---------- ---------------------------------------- --------------------
         1 db_big_table_cache_percent_target        25
         2 db_big_table_cache_percent_target        25
         3 db_big_table_cache_percent_target        0
         1 parallel_degree_policy                   AUTO
         2 parallel_degree_policy                   AUTO
         3 parallel_degree_policy                   AUTO

SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem 
     from gv$bt_scan_obj_temps  b, dba_objects o 
     where b.dataobj#  = o.data_object_id order by 1;  

   INST_ID OBJECT_NAME       SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
---------- ------------ ----------------- ----------- --------------- -------------
         1 BIG_TABLE1                1479        2000 MEM_ONLY                1479
         2 BIG_TABLE1                1170        2000 MEM_ONLY                1170
         3 BIG_TABLE1                1209        2000 DISK                    1209
  • Set DB_BIG_TABLE_CACHE_PERCENT_TARGET back to 25 on instance 3 and verify that the table data which was allocated to big table cache on instance 3, has again been read from disk into the big table cache.
SQL> alter system set db_big_table_cache_percent_target=25 sid = 'orcl3'; 
     select inst_id, name, value from gv$parameter 
     where name in ('parallel_degree_policy','db_big_table_cache_percent_target') 
     order by 2,1;

   INST_ID NAME                                     VALUE
---------- ---------------------------------------- --------------------
         1 db_big_table_cache_percent_target        25
         2 db_big_table_cache_percent_target        25
         3 db_big_table_cache_percent_target        25
         1 parallel_degree_policy                   AUTO
         2 parallel_degree_policy                   AUTO
         3 parallel_degree_policy                   AUTO

SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem 
      from gv$bt_scan_obj_temps  b, dba_objects o 
      where b.dataobj#  = o.data_object_id order by 1;  

   INST_ID OBJECT_NAME       SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM
---------- ------------ ----------------- ----------- --------------- -------------
         1 BIG_TABLE1                1479        2000 MEM_ONLY                1479
         2 BIG_TABLE1                1170        2000 MEM_ONLY                1170
         3 BIG_TABLE1                1209        2000 MEM_ONLY                1209
  • Drop table HR.BIG_TABLE1 so that its blocks are no longer cached in ABTC. Verify that the table HR.BIG_TABLE_PART has 3 partitions, each of which qualifies as a big segment.
SQL>drop table hr.big_table1 purge;
    Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem 
from gv$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id order by 1;  

no rows selected

SQL> Select table_name, partition_name, blocks from dba_tab_partitions
     where table_name = 'BIG_TABLE_PART';

TABLE_NAME      PARTITION_NAME      BLOCKS
--------------- --------------- ----------
BIG_TABLE_PART  P3                    1370
BIG_TABLE_PART  P2                     502
BIG_TABLE_PART  P1                    2085
  • Scan partitions p1 and p2 of the partitioned table in parallel. Data from both the partitions p1 and p2 is distributed across all three instances.
sql> select /*+ parallel */ count(*) from hr.big_table_part partition (p1);

SQL> Select inst_id, object_name Table_name , subobject_name Partition_name,
            size_in_blks, temperature, policy, cached_in_mem 
     from gv$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id order by 2, 3, 1;      

   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM
---------- --------------- --------------- ----------------- ----------- --------------- -------------
         1 BIG_TABLE_PART  P1                            635        1000 MEM_ONLY                  635
         2 BIG_TABLE_PART  P1                            663        1000 MEM_ONLY                  663
         3 BIG_TABLE_PART  P1                            741        1000 MEM_ONLY                  741
 
SQL> select /*+ parallel */ count(*) from hr.big_table_part partition (p2);

SQL> Select inst_id, object_name Table_name , subobject_name Partition_name, 
            size_in_blks, temperature, policy, cached_in_mem 
         from gv$bt_scan_obj_temps  b, dba_objects o 
         where b.dataobj#  = o.data_object_id order by 2, 3, 1; 

   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM
---------- --------------- --------------- ----------------- ----------- --------------- -------------
         1 BIG_TABLE_PART  P1                            635        1000 MEM_ONLY                  635
         2 BIG_TABLE_PART  P1                            663        1000 MEM_ONLY                  663
         3 BIG_TABLE_PART  P1                            741        1000 MEM_ONLY                  741
         1 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156
         2 BIG_TABLE_PART  P2                            172        1000 MEM_ONLY                  172
         3 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156
  • To disable caching the data from a partition in all the instances except one, set parameter PARALLEL_LOCAL_FORCE = TRUE on instance 1.
ORCL1>show parameter parallel_force_local
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local                 boolean     FALSE

ORCL1>ALter system set parallel_force_local=true;
      show parameter parallel_force_local
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local                 boolean     TRUE

ORCL1> sho parameter instance_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl1
  • Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.
ORCL1> select /*+ parallel */ count(*) from hr.big_table_part partition (p3);

ORCL1>Select inst_id, object_name Table_name , subobject_name Partition_name, 
       size_in_blks, temperature, policy, cached_in_mem 
      from gv$bt_scan_obj_temps  b, dba_objects o 
      where b.dataobj#  = o.data_object_id order by 2, 3, 1; 

   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM
---------- --------------- --------------- ----------------- ----------- --------------- -------------
         1 BIG_TABLE_PART  P1                            635        1000 MEM_ONLY                  635
         2 BIG_TABLE_PART  P1                            663        1000 MEM_ONLY                  663
         3 BIG_TABLE_PART  P1                            741        1000 MEM_ONLY                  741
         1 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156
         2 BIG_TABLE_PART  P2                            172        1000 MEM_ONLY                  172
         3 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156
         1 BIG_TABLE_PART  P3                           1336        1000 MEM_ONLY                 1336

Summary:

  • Automatic Big Table Caching can be used for serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.
  • When parameter PARALLEL_LOCAL_FORCE = FALSE(default), querying a big object in parallel causes its fragments to be cached across the big table caches of all the instances.
  • When parameter PARALLEL_LOCAL_FORCE = TRUE on an instance, querying a big object from that instance causes that object to be cached in the big table cache of that instance only. This can be employed to implement application partitioning in RAC.
  • Partitioning can be combined with “ABTC” to considerably reduce the amount of data to be cached.