Oracle Force Full Database Caching (FFDC)

This blog post describes an Oracle feature called Force Full Database Caching.  It was first introduced in Oracle 12c R1 Patchset 12.1.0.2 back in July 2014.  There have been no major changes since then, so this post is relevant to Oracle 12c R2, 18c, 19c, and 21c.

What is Force Full Database Caching?

Oracle's Force Full Database Caching solution, or simply FFDC, accelerates all workloads by caching the entire database in the database buffer cache and managing it with a special set of algorithms.  The initial read of each block requires physical I/O from storage into buffer cache.  Subsequent reads occur in memory.

When I say FFDC caches the entire database, I mean it caches all data blocks from all user-defined permanent tablespaces as well as the SYSTEM tablespace.  It does not cache temp, undo, redo, or the SYSAUX tablespace.

The feature pretty awesome, if you can get it to turn on.  Once enabled Oracle is really fast because all of your tables and indexes are fully cached in DRAM with its low latency and high bandwidth.  But, that means you need enough DRAM to cache all of your tables and indexes.

I have seen performance gains up to 20X with this feature enabled, so I think it is worth a look.  I have also seen cases with very minor performance gains, and even some cases where performance dropped after enabling FFDC.  But, considering this is a free feature, what do you have to lose by testing it?

There's one big problem with FFDC: most servers have insufficient RAM to turn it on. 

From my 25 years of experience as an Oracle DBA, a typical production database is 7 times larger than server memory.  I commonly see servers with 768GB DRAM hosting databases from 10TB to 50TB.  It is physically impossible, even with compression, to cache all of the data in memory.  For these typical Oracle customers, the FFDC just isn’t an option.  For the smaller databases there's still hope, and the potential for great rewards.

A quick rule-of-thumb is you server must have 3X the DRAM as all user-defined tablespaces.  Recall that the parameter db_cache_size is generally set to 40% of physical memory so right there you need 2.5X as much DRAM as data.  And, as I'll explain later, FFDC requires 20% extra memory or it shuts itself off, so that's how I got to 3X total.

Oracle disables FFDC if db_cache_size is less than 120% the sum of all high-water marks in user-defined permanent tablespaces.  It isn't enough to have 100%, you need at least 120% as much memory as disk.  I remember running a database with FFDC working beautifully until we ingested new data that pushed us over the threshold and Oracle disabled the feature.  The server couldn't hold any more memory, so we never got FFDC working again on that server.

There's a hidden problem with this 120% rule.  If your tablespace is fragmented and there are big gaps between segments, Oracle still counts the space.  It looks at the tablespace's high-water mark.

Let's assume you are not a typical Oracle customer and your database is relatively small.  Great, you can enable FFDC and enjoy the ride.  Moving on …

Enabling FFDC

FFDC is enabled at the CDB level and applies automatically to all PDB.  You cannot enable or disable this feature at the PDB level.

First, before you can enable FFDC, you must ensure parameter db_cache_size is at least 20% larger than the sum of your user-defined permanent tablespaces plus tablespace SYSTEM.  If you are using Automatic Shared Memory Management (i.e., parameter sga_target), then you don't need to manually set db_cache_size, but you do need to ensure there's enough SGA memory for Oracle to adequately grow the buffer cache.

Second, run the below sequence of statements in SQL*Plus to enable FFDC.  This requires a full outage because it stops and restarts the database.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FORCE FULL DATABASE CACHING;
ALTER DATABASE OPEN;
SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

You'll all done.  Maybe.

Next, check the database alert log for a message indicating it isn't working.  If it is working, then there's no message.  If it isn't working, then you'll see a message like this:

grep -i "force full" alert*.log

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

Please note, Oracle does not auto-populate the database buffer cache on instance startup even when FFDC is enabled.  The cache gets populated with data blocks during normal DML and certain queries (not table scans).  It could take several hours to fully populate the cache and start seeing the effects of FFDC.

FFDC Performance

A paper published by SanDisk several years ago showed the gains one might obtain by implementing FFDC.  They had two identical databases, one stored on a 8-disk array and the other stored on a single flash drive.  The paper shows us FFDC's ability to help certain workloads depended on how much help the storage layer needed.  Source: https://www.sandisk.com/content/dam/sandisk-main/en_us/assets/resources/enterprise/white-papers/maximizing-oracle-db-12c-performance-with-fusion-iomemory.pdf.

  • For latency sensitive OLTP workloads the HDD-based database improved by 35% but the database on flash actually slowed down 1%. 

  • For DSS workloads the database on disk improved by 2381% and the database on flash improved by 751%.

Another chart in the same paper directly compared the OLTP performance with both types of storage, with and without FFDC.  They normalized the disk-based database's score to 100 and made all other scores are relative to that.  So, for example, the OLTP workload on the disk-based database improved from 100 to 135 when FFDC was enabled, but the flash-based database's score dropped from 1837 to 1819.

These numbers mean the OLTP workload was 13X faster on flash storage without FFDC than the same database stored on disk with FFDC enabled.  In other words, it was faster to perform small block random reads from flash than it was to cache all of the blocks in memory and incur the wrath of memory management algorithms.

But that's just for transactional workloads.  Remember, they got very different results for DSS workloads.

I had a chance to talk to the SanDisk team at Oracle OpenWorld that year, including the paper's author.  FFDC improved DSS run times so much that the 8-disk array beat a single flash drive by nearly 3X.  It was basic math: sequential reads are a lot faster from DRAM than NAND.  Both are memory, but DRAM is exponentially faster.  Of course, however, when they enabled FFDC on the flash-based database it too was greatly accelerated and came out on top.  In their testing, the database on flash finished a TPC-H-like multi-user test in one-eighth of the time compared to before enabling FFDC.

An all flash database running 8X faster using a free Oracle feature!

Your performance results will vary by platform and workload.  OLTP performance should rise for databases stored on disk arrays.  DSS, Warehouse, and Analytics performance should rise for databases regardless of storage.  But, as noted earlier, getting FFDC to turn on is the biggest challenge.

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more