Automatic Big Table Caching in RAC

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

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:

  • Ensure that full database caching is disabled on all the instances.

  • Check the value of the parameter _SMALL_TABLE_THRESHOLD, since an object occupying number blocks higher than this value is considered big.

  • 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.

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

  • Configure Big Table Cache to 25% on all the instances.

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.

  • 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.

  • 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.

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.

  • 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.

  • 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 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.

  • 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.

  • To disable caching the data from a partition in all the instances 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.

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.