6.5 Using Force Full Database Caching Mode

«« Previous
Next »»

An Oracle Database instance can cache the full database in the buffer cache.

➤ About Force Full Database Caching Mode

In default caching mode, Oracle Database does not always cache the underlying data when a user queries a large table because doing so might remove more useful data from the buffer cache. Starting with Oracle Database 12c Release 1 (12.1.0.2), if the Oracle Database instance determines that there is enough space to cache the full database in the buffer cache and that it would be beneficial to do so, then the instance automatically caches the full database in the buffer cache.

Caching the full database in the buffer cache might result in performance improvements. You can force an instance to cache the database in the buffer cache using an ALTER DATABASE FORCE FULL DATABASE CACHING statement. This statement puts the instance in force full database caching mode. In this mode, Oracle Database assumes that the buffer cache is large enough to cache the full database and tries to cache all blocks that are accessed subsequently.

When an Oracle Database instance is in force full database caching mode, the following query returns YES:

SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

When an instance is in default caching mode, NOCACHE LOBs are not cached in the buffer cache. However, when an instance is in force full database caching mode, NOCACHE LOBs can be cached in the buffer cache. Also, both LOBs that use SecureFiles LOB storage and LOBs that use BasicFiles LOB storage can be cached in the buffer cache in force full database caching mode only.

Note:
When an instance is put in force full database caching mode, database objects are not loaded into the buffer cache immediately. Instead, they are cached in the buffer cache when they are accessed.

In a multitenant environment, force full database caching mode applies to the entire multitenant container database (CDB), including all of its pluggable databases (PDBs).

Information about force full database caching mode is stored in the control file. If the control file is replaced or recreated, then the information about the force full database caching mode is lost. A restored control file might or might not include this information, depending on when the control file was backed up.

➤ Before Enabling Force Full Database Caching Mode

The database must be at 12.0.0 or higher compatibility level to enable force full database caching mode for the database instance. In addition, ensure that the buffer cache is large enough to cache the entire database.

When a database is configured to use the SGA_TARGET or MEMORY_TARGET initialization parameter for automatic memory management, the size of the buffer cache might change depending on the workload. Run the following query to estimate the buffer cache size when the instance is under normal workload:

SELECT NAME, BYTES FROM V$SGAINFO WHERE NAME='Buffer Cache Size';

This query returns the buffer cache size for all possible block sizes. If your database uses multiple block sizes, then it is best to ensure that the buffer cache size for each possible block size is bigger than the total database size for that block size.

You can determine the buffer cache size for non-default block sizes with the DB_nK_CACHE_SIZE initialization parameter. With SGA_TARGET or MEMORY_TARGET, the buffer cache size for the default block size in the default pool might change depending on the workload. The following query returns the current buffer cache size for the default block size in the default pool:

SELECT COMPONENT, CURRENT_SIZE FROM V$SGA_DYNAMIC_COMPONENTS
   WHERE COMPONENT LIKE 'DEFAULT buffer cache';
  • If you are estimating memory requirements for running a database fully in the buffer cache, then you can estimate the size of the buffer cache as one of the following:
  • If you plan to use SGA_TARGET, then you can estimate the buffer cache size as 60% of SGA_TARGET.
If you plan to use MEMORY_TARGET, then you can estimate the SGA size as 60% of MEMORY_TARGET, and buffer cache size as 60% of SGA size. That is, you can estimate the buffer cache size as 36% of MEMORY_TARGET.

➤ Enabling Force Full Database Caching Mode

You can enable force full database caching mode for a database.
  • Connect to the instance as a user with ALTER DATABASE system privilege.
  • Ensure that the database is mounted but not open.
  • Issue the following SQL statement:
ALTER DATABASE FORCE FULL DATABASE CACHING;
  • (Optional) Open the database:
ALTER DATABASE OPEN;

➤ Disabling Force Full Database Caching Mode

You can disable force full database caching mode for a database.
  • Connect to the instance as a user with ALTER DATABASE system privilege.
  • Ensure that the database is mounted but not open.
  • Issue the following SQL statement:
ALTER DATABASE NO FORCE FULL DATABASE CACHING;
  • (Optional) Open the database:
ALTER DATABASE OPEN;

«« Previous
Next »»