Database Caching Modes - 12c

12.1.0.2 has a new feature called Full database caching.

Caching can be of two types

1 . Default one

2.  Full Database Caching

Default Database Caching

In the default mode if the cache has enough space to accommodate full database then only full database is cached .


Following information has been taken from Oracle Docs

What if we dont have space in the default caching mode?

• 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.

Full Database Caching

Force Full Database Caching  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, are loaded into the buffer cache. This feature can drastically improve database performance when performing full table scans or accessing LOBs.

Consider using force Full Database Caching Mode in the following situations:

• The logical database size (or actual used space) is smaller than the individual buffer cache of each database instance in an Oracle RAC environment. This is applicable for non-Oracle RAC database as well.
• The logical database size is smaller than 80% of the combined buffer cache sizes of all the database instances for well-partitioned workloads (by instance access) in an Oracle RAC environment.
• The database uses SGA_TARGET or MEMORY_TARGET.
• The NOCACHE LOBs need to be cached. The NOCACHE LOBs are never cached unless force Full Database caching is used.

Demo

SGA INFORMATION

Testing:

Lets create a test table and verify how it behaves and what wait events are observed in the trace file

For Each and every run we have enabled captured trace

For the first Run

For the Second and third run

If you see that the wait event on the first run was Db file scattered Read. After the second and the third run it has totally gone and everything is read from cache.