Automatic Big Table Caching

Automatic big table caching integrates queries with the buffer cache to enhance the in-memory query capabilities of Oracle Database, in both single instance and Oracle RAC environments.

In Oracle Real Application Clusters (Oracle RAC) environments, this feature is supported only with parallel queries. In single instance environments, this feature is supported with both parallel and serial queries.

The cache section reserved for the big table cache is used for caching data for table scans. While the big table cache is primarily designed to enhance performance for data warehouse workloads, it also improves performance in Oracle Database running mixed workloads.

Automatic big table caching uses temperature and object based algorithms to track medium and big tables. Oracle does cache very small tables, but automatic big table caching does not track these tables.

To enable automatic big table caching for serial queries, you must set a value (percentage) for the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter. Additionally, you must set the PARALLEL_DEGREE_POLICY initialization parameter to AUTO or ADAPTIVE to enable parallel queries to use automatic big table caching. In Oracle RAC environments, automatic big table caching is only supported in parallel queries so both settings are required.

If a large table is approximately the size of the combined size of the big table cache of all instances, then the table is partitioned and cached, or mostly cached, on all instances. An in-memory query could eliminate most disk reads for queries on the table, or the database could intelligently read from disk only for that portion of the table that does not fit in the big table cache. If the big table cache cannot cache all the tables to be scanned, only the most frequently accessed table are cached, and the rest are read through direct read automatically.

The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter determines the percentage of the buffer cache size used for scans. If DB_BIG_TABLE_CACHE_PERCENT_TARGET is set to 80 (%), then 80 (%) of the buffer cache is used for scans and the remaining 20 (%) is used for OLTP workloads.

The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter is only enabled in an Oracle RAC environment if PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE. The default for DB_BIG_TABLE_CACHE_PERCENT_TARGET is 0 (disabled) and the upper limit is 90 (%) reserving at least 10% buffer cache for usage besides table scans. When the value is 0, in-memory queries run with the existing least recently used (LRU) mechanism. You can adjust the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter dynamically.

Use the following guidelines when setting the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter:

  • If you do not enable automatic DOP in an Oracle RAC environment, then you should not set this parameter because the big table cache section is not used in that situation.

  • When setting this parameter, you should consider the workload mix: how much of the workload is for OLTP; insert, update, and random access; and how much of the workload involves table scans. Because data warehouse workloads often perform large table scans, you may consider giving big table cache section a higher percentage of buffer cache space for data warehouses.

  • This parameter can be dynamically changed if the workload changes. The change could take some time depending on the current workload to reach the target, because buffer cache memory might be actively used at the time.

When PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE, additional object-level statistics for a data warehouse load and scan buffers are added to represent the number of parallel queries (PQ) scans on the object on the particular (helper) instance.

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

See Also: