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
