12c New Feature: Force Full Database Caching

One of the most expensive operation a Oracle Database can do, is to read the data from the disk. On the other side, read a block from the memory is one of the goals in Database Performance Tuning. In the past Oracle has introduced some feature that can help the DBA to prevent Disk Reads (for example Smart Flash Cache).

Normally a Oracle database decides what data to cache in the buffer cache. For example when there is not enough room the database age out blocks from the cache. Other operations like a Direct Path Read complete bypass the SGA and loads the blocks into the PGA. Prior to Oracle 12c a DBA can use the KEEP Option to prevent the aging blocks from the buffer cache.

Beginning with 12cR1 (12.1.0.2) Oracle introduces a new Feature called FORCE FULL DATABASE CACHING. This Feature allows that the DBA can tell the database, that the SGA is big enough to hold the complete database.

Considerations Before activating

• The database size must be smaller then the BUFFER CACHE size
  • The TEMP and the SYSAUX Tablespace can be excluded
• NOCACHE Securefile will be also CACHED
• In a multitenant configuration this feature works at CDB level and all PDBs
• This is not a „WarmUp“ Cache

Setup

Check your database and memory size. Like in the example below, the SYSAUX Tablespace can be excluded.

SQL> col size_mb format 9999
SQL> SELECT sum(bytes)/1024/1024 seg_size_mb
     FROM dba_segments where tablespace_name != 'SYSAUX';
SEG_SIZE_MB
-----------
4971

Check the size of your BUFFER CACHE

SQL> SELECT round(sum(cnum_set * blk_size)/1024/1024) size_mb
     FROM X$KCBWDS;
SIZE_MB
-------
5283

Configure your database for FORCE FULL DATABASE CACHING

SQL> startup
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 5304248 bytes
Variable Size 3539992648 bytes
Database Buffers 5033164800 bytes
Redo Buffers 11472896 bytes
Database mounted.
SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;
Database altered.
SQL> SELECT force_full_db_caching FROM v$database;
FOR
---
YES
SQL> alter database open;
Database altered.

Loading blocks for Force Full Database Caching

Once enabled you will see slow response for the first data block reads (from the disk) this is because it is not a warmup cache. However, all subsequent reads are done from the Buffer Cache and should be run faster the the disk reads.

Disbale Force Full Database Caching

SQL> ALTER DATABASE NO FORCE FULL DATABASE CACHING;
Database altered

As always comments are welcome.