Monday, March 26, 2018

Oracle Force Full Database Cache Mode

Oracle Force Full Database Cache Mode



One of the most expensive operations in Oracle Database is to read data from disk. Most of our performance tuning goal is to reduce IO and read block from memory.

Oracle’s db_buffer_cache is smart enough to decide what to cache and when to edge out the data blocks when not frequently used or no room for new data. Sometime edging out of data become very frequently and we face performance degradation and very high IO read from disk.

Beginning with 12cR1 (12.1.0.2) Oracle introduces a new Feature called FORCE FULL DATABASE CACHING. This Feature allows DBA to cache complete database in db buffer cache.

Starting with Oracle Database 12c Release 1 (12.1.0.2), there are two Database Caching Modes:

  • Default Database Caching Mode.
  • Force Full Database Caching Mode.
Default Database Caching Mode
If Database instance determines that there is enough space to cache the Full Database in the buffer cache and that it would be beneficial to gain performance, and then the instance automatically caches the Full Database in the buffer cache. This is a new feature in 12.1.0.2.

If the Oracle Database instance determines that there is not enough space to cache the Full Database in the buffer cache, then:

From Oracle Docs, Doc ID 1941494.1
Smaller tables are loaded into memory only when the table size is less than 2 percent of the buffer cache size.

For medium tables, Oracle Database analyzes the interval between the last table scan and the aging timestamp of the buffer cache.
If the size of the table reused in the last table scan is greater than the remaining buffer cache size, then the table is cached.

Large tables are typically not loaded into memory, unless if you explicitly declare the table for the KEEP buffer pool.

Force Full Database Caching Mode

In force Full Database Caching Mode, Oracle Database caches the entire database in memory when the size of the database is smaller than the database buffer cache size.
All data files, including NOCACHE LOBs, will be loaded into the buffer cache. This feature can improve database performance drastically for the queries which performing full table scans and IO intensive queries.

In a multitenant environment, force Full Database Caching Mode applies to the entire multitenant container database (CDB), including all of its pluggable databases (PDBs).

Must note below things before activating Force Full DB Cache Mode

  • The database size must be smaller than the BUFFER CACHE size. It is recommended that SGA should be 20% more than DB 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 are considered for Caching Data

FAQs of Force Full DB Cache Mode

Q. What type of performance benefit we get from Force full database caching mode?
Ans. Performance benefit can be seen on IO bound and higher read intensive databases and queries which are doing full table scan.

Q. What if buffer cache is not sufficient to hold my database in cache?
Ans. If this is the case Force Full Caching setting automatically be turned off. This can be seen in alert log file.

Q. In a Multitenant environment, Is Force Full Database Caching applies to All PDBs?
Ans. Yes, It applies to entire multitenant Container Database (CDB) including all of its pluggable database (PDBs).

Q. How objects are going to be loaded/cached in Buffers?
Ans. Objects will be cached when they are accessed. Objects were not cached or loaded in buffer immediately after instance started.

Q. Where can i find information about Force Full Database Caching Mode.
Ans. You can find information in control file.

Q. What if my control file is replaced or lost?
Ans. If the control file is replace or recreated then the information about the force full database caching mode is lost. Restored control file might have information depending on when the control file was backed up.

Q. How to estimate the buffer cache size for Force Full DB Cache Mode when the instance is under normal workload:
Ans. Use below query,
SELECT NAME, BYTES FROM V$SGAINFO WHERE NAME = 'Buffer Cache Size';

Q. How to check if database is in Force Full DB Cache Mode?
Ans. Use below query to check. if FORCE_FULL_DB_CACHING showing yes than DB is in Force Full Caching Mode
SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

Q. Is it recommended for OLTP Database?
Ans. No, my recommendation is for data warehouse workloads. This is because we cannot calculate OLTP database size due to frequent data grows. Hence if it crosses to your allocated buffer cache size than force full caching mode converts in default caching mode and you will not get benefit of this.

How to enable Force Full DB Cache Mode

Only Three Steps to enable force full db cache mode.

1) Start database should be in mount state
Sql> Startup mount

2) Alter database with force full caching
Sql> alter database force full database caching;

3) Now Open database normally
Sql> alter database open;

How to Disable Force Full DB Cache Mode

Only Three Steps to disable force full db cache mode.

1) Start database should be in mount state
Sql> Startup mount

2) Alter database with force full caching
Sql> alter database NO force full database caching;

3) Now Open database normally
Sql> alter database open;


I am very glad to share information on Oracle Force Full Database Cache Mode. Recently I have presented session Oracle Force Full Database Cache Mode in @AIOUG Gujarat Chapter and @AIOUG Mumbai Chapter.

Please find attachment of my Presentation for the same.
My presentation is more sort of on FAQ to understand Force Full Database Cache Mode.



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
Post a Comment