A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.

One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the Force FULL database cache.

In my session, I gave a long explanation about several hybrid solutions (such as the MySQL memory storage engine, and Oracle 12c database In-Memory option) but this post will focus on the Force Full database cache, which will be explained in the second part of the post.

Enjoy!

Database systems internal

When we talk about database management system, one of the basic things we need to understand is the separation between an instance and a database. This separation might be somewhat artificial for some vendors, but the idea behind it is always the same. Instance is the processes and memory structures behind the database management system. It’s the program itself and where all the magic happens. When we say instance, we are actually talking about the various processes and threads that are in charge of managing the data access (both read and write), the concurrency, durability, and so on. This is the software part of our management system.

On the other hand, we have the database. The database is the common name for the entire dataset itself. The actual term “database” is representing the physical storage of the data – the place where we keep our information. When we say “Storing the data”, we can distinguish between keeping it on a persistent storage (usually a disks or storage machines) and non-persistent storage (usually in the memory).

When put together, the instance and the database are two parts of the same thing – without them, we will not have a database management system.

Reading Performance

When we are reading data from persistent databases, we need to read it from physical disks. That means that every read operation we do, has a time cost. Looking at the numbers of HDD vs. SSD, we can clearly see that HDD are much slower than SSD. When regarding to latency, we can expect a seek time of 5-10 milliseconds (and sometimes even more) for a single operation. When comparing this to SSD, we expect a latency of only 1 ms. The IOPS on HDDs are also lower – we can do around 150-250 operations per second in magnetic devices but over 5000 in SSD.

What about memory components? How fast are they?

Accessing a single block of data on RAM can be as fast as 100 nano seconds (which is 0.0001 millisecond). There is no IOPS measurement, but the bandwidth can go up to 10GB/s, which is 20 times higher than SSD. There is a direct communication between our CPU and memory, which makes things very fast when we read random blocks from memory.

Caching Optimization

Some of you might think now – “well, this is much better – why aren’t we using those for our database systems?” and the answer is that we do; this is part of our read optimization using database cache.

Most of the “normal” systems we’re using now use the memory as a caching mechanism to reduce the use of the physical devices. Most database systems build some kind of a memory area (sometimes referred to as buffer or cache) to keep blocks they used before – hoping that these blocks will be used again. If a block is indeed reused, the database doesn’t have to read it from the slow persistent disk again, it can just fetch it from the fast memory. This improves data read performance dramatically but since our memory is limited, these systems also need to maintain an algorithm to dispose unused blocks when we need the space – or just not load blocks in certain situations to save the space for more important blocks.

Caching is not the same as pure IMDBs. IMDB keep the entire dataset in memory beforehand. It does not fetch blocks from disks at any time, and since persistency is often not required, they don’t have to write anything back to disk. Once an IMDB based system starts, it will load everything it needs to the memory and that is how it will stay until ordered otherwise (or until the system stops or crushes). The amount of overhead maintenance is reduced to a minimum in order to preserve performance, and we get a system that is much more consistent with its performance.

Oracle 12c Full Database In Memory Caching

The new 12.1.0.2 Full Database In-memory Caching feature enables an entire database to be cached in memory. This is of course only true when the database size (sum of all segments in data files and SYSTEM tablespace files minus the SYSAUX, TEMP) is smaller than the buffer cache size itself. Since there will be less disk access, the caching leads to huge performance benefits.

There are two modes:

• Full Database Caching: Implicit default and automatic mode in which an internal calculation determines if the database can be fully cached.
• Force Full Database Caching: This mode requires the DBA to execute the ALTER DATABASE FORCE FULL DATABASE CACHING command.

Neither Full Database Caching nor Force Full Database Caching forces or prefetches data into memory. Workload must access the data first for them to be cached. It considers the entire database as eligible to be completely cached in the buffer cache.

In order to turn this feature on, the database must be in mount mode:

shutdown immediate;
startup mount;
alter database force full database caching;
alter database open;
SELECT force_full_db_caching FROM v$database;

What happens if not enough memory in the buffer cache

If we are using Force Full Database Caching and there is not enough memory to fit everything into the buffer cache, the Force setting will automatically be turned off. We will see this in the alert log:

Buffer Cache Force Full DB Caching mode on when DB does not fit in cache. Turning off Force Full DB Caching advisable

Just note that even though the database is now not using the force mode, the indication is not changed in the v$database view:

SQL> select force_full_db_caching from v$database;

FOR
---
YES

Conclusion

Oracle 12c offers us a new way to look at the caching mechanism. DBA’s still need to keep their eye on it (since it might be turned off without any indication) but still, a rather cool feature to work with. I tried to think why this is better than the old caching mechanism and the only idea I had is that with the new feature, we no longer maintain the LRU lists.

If someone has other ideas, I’d be happy to hear them.. 🙂