Skip Headers
Oracle® Database VLDB and Partitioning Guide
12c Release 1 (12.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

Automatic Big Table Caching


This feature is available starting with Oracle Database 12c Release 1 (

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 existing the 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:

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: