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 the bottom. 

Caching Feature or Option	Introduced	Free or Cost	Target Workload
Automatic Big Table Caching (ABTC)	12.1.0.2	Free	Query and Analytics
Force Full Database Caching (FFDC)	12.1.0.2	Free	All
In-Memory Column Store (IMCS)	12.1.0.2	Extra Cost	Query and Analytics
Oracle Database Smart Flash Cache (DSFC)	11g R2	Free	OLTP
Client Result Cache	11g R1	Free	OLTP
Server Result Cache	11g R1	Free	OLTP

Table of Oracle Caching Solutions

All of the listed caching solutions are free with Enterprise Edition except the In-Memory Column Store which requires a license for the Oracle Database In-Memory option.  Well, the Oracle Database Smart Flash Cache requires an Oracle branded operating system, but other than that it's free.

Oracle has not introduced a new caching feature since the July 2014 patchset 12.1.0.2.  Maybe that's because there aren't any more caching solutions waiting to be implemented, or none with a projected customer base that justifies the cost of research, development, and long-term support.

Or, maybe because persistent memory crushes everything.  See my blog posts about using persistent memory with Oracle.  (I'm using the term "persistent memory" generically here and not referring a specific implementation).  And, with computational storage on the horizon we're hearing about a new generation of acceleration technologies embedded in storage devices.

Notice a pattern in the above table?  The first two solutions released in Oracle 11gR1 use system memory, then comes a solution in 11g R2 that leverages flash storage devices, and then we're back to using system memory for all caching solutions introduced in 12c.  There's a good reason for this.

By the time 12c was released Oracle's customers were already migrating large databases from spinning disk to flash, and storage array vendors like EMC, IBM, and NetApp were rolling out all-flash arrays.  There's no advantage to putting a flash-based cache ahead of flash-based storage.  In fact, putting a flash cache in front of flash storage gets in the way and slows things down.  This implies the DSFC is pretty much dead now.  A victim of its own success.

What might be killing all traditional caching solutions is non-volatile memory like the HPE NVDIMM and the Intel Optane Persistent Memory (PMEM) caching devices.  Non-volatile memory is much more scalable than system memory at a fraction of the cost and with blazing fast performance.  But, that's not an Oracle solution and can be implemented on any DBMS like PostgreSQL or Microsoft SQL Server.  I have another blog about that technology.

Without question, the most desirable of Oracle's caching solutions today is the In-Memory Column Store (IMCS).  It's also Oracle's only extra-cost caching solution, enabled by licensing the Oracle Database In-Memory Option.  In fact, it's so awesome I cover the IMCS in a separate dedicated blog.  So, for now, let's talk about the freely available Oracle-supplied caching solutions.

The remainder of this blog post describes, in a fair amount of detail, each of Oracle's caching solutions.  And, for most of these I have a deep dive blog post with additional information.

Oracle Automatic Big Table Caching (ABTC)

Here's a quick review of the feature.  A deep dive is provided in another blog post.

ABTC accelerates reporting workloads like DSS, Warehouse, and Analytics by allowing data fetched via table scan to be cached in the SGA where it can be shared by other users.  Without ABTC, the shared cache is not used and each user who needs the same data must perform physical I/O -- every query that scans the same table must repeat the physical I/O and fetch the data from storage.

All currently shipping releases of Oracle Enterprise Edition support ABTC.  No additional hardware or software is required to use it, and there are no licensing restrictions beyond the requirement to use Enterprise Edition.

The Oracle SGA has a Big Table Cache sub-area within the Oracle Database Buffer Cache's default pool.  The area is not initialized (has no memory) by default.  Once you allocate memory to it Oracle auto-manages it.  Hence the name "Automatic" Big Table Caching.  Oracle manages the Big Table Cache using an algorithm specifically designed for large tables based on object type and temperature, rather than traditional Oracle LRU algorithms.

Enabling ABTC is a simple two-step process:

1. Set database initialization parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET to a non-zero value (it is 0 by default).  The parameter specifies the percentage of the database buffer cache’s default pool to be sectioned off for use by the Big Table Cache -- do not enter an absolute memory size
2. Set parameter PARALLEL_DEGREE_POLICY to either AUTO or ADAPTIVE.  This parameter is required when using RAC, but is optional for non-RAC databases.

Performance gains from ABTC depend greatly on your storage.  The slower your storage layer, the more room there is to gain performance.  My database on flash went 4X faster after enabling ABTC.  An identical database on an array of 8 internal hard drives went nearly 20X faster after enabling ABTC.  These results were observed using the HammerDB implementation of a TPC-H-like Power User Test.

Monitoring ABTC basically involves querying two views:

• V$BT_SCAN_CACHE shows the parameters and status of the Big Table Cache.
• V$BT_SCAN_OBJ_TEMPS lists the active objects currently tracked by the Big Table Cache.

Oracle Force Full Database Caching (FFDC)

Here's a quick review of the feature.  A deep dive is provided in another blog post.

FFDC accelerates all workloads by caching all data 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, but all 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 in the database.  It does cache the SYSTEM tablespace, but it does not cache any temp or undo tablespaces, or the SYSAUX tablespace.

All currently shipping releases of Oracle Enterprise Edition support FFDC.  No additional hardware or software is required to use FFDC, and there are no licensing restrictions beyond the requirement to use Enterprise Edition.  

Enabling FFDC is a three-step process:

• Ensure parameter db_cache_size is at least 20% larger than the sum of your user tablespaces.
• Alter the database as shown here:

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

• Check the database alert log for any messages.  If it is working, then you won't get any messages.  If it fails, then you'll see a message like this:

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

Oracle does not auto-populate the buffer cache on instance startup.  This is true whether or not FFDC is enabled. The buffer cache gets populated with data blocks during normal DML and queries (but not table scans).  It could take several hours to fully populate the cache and start seeing the effects of FFDC.

My performance test results varied by type of storage and workload.  In all of these tests the database buffer cache was 20% larger than the sum of all user-defined permanent tablespaces.  I ran the tests using HammerDB, a free open source benchmarking utility by Steve Shaw.

• TPC-H-like single user test of Oracle on flash: 4X faster.
• TPC-H-like single user test of Oracle on disk: 25X faster. 
• TPC-H-like test with 8-users of Oracle on flash: 8X faster.
• TPC-H-like test with 8-users of Oracle on disk: 24X faster. 
• TPC-C-like test of Oracle on flash: 1% slower
• TPC-C-like test of Oracle on disk: 35% faster.

FFDC significantly narrowed the performance gap between Oracle on flash and disk.  But, in all cases the database on flash was fastest.

Oracle Database Smart Flash Cache (DSFC)

Here's a quick review of the feature.  A deep dive is provided in another blog post.

DSFC accelerates primarily OLTP workloads by caching frequently accessed blocks in a hybrid memory area that spans from the Oracle database buffer cache to one or more flash memory storage devices.  The feature is recommended for any Oracle database stored entirely on disk, which is well known for higher wait times and lower throughput than flash.

Implementation rates have fallen sharply over the past decade.  More and more Oracle databases are run entirely on flash storage, which is faster than using the same flash technology as a cache.  If your database is stored on flash, then you cannot make it faster by putting a flash-based cache in front of it.  In fact, it will be slower due to the added overhead of managing the cache.

DSFC requires an Oracle branded operating systems, but it does not require an Oracle branded kernel like UEK.  If you don't have access to an Oracle operating system, consider moving specific tablespaces to flash storage and skip the caching solution.  Flash economics have changes significantly and many Oracle customers now use flash as primary storage, having repurposed their disk drives for RMAN backups.

The maximum DSFC size is approximately 10X your database buffer cache.  For example: suppose you have a 10TB database on a server with 512GB RAM, and to boost performance you implement a 2TB flash cache.  In this case the flash cache is about 10X the size of your database buffer cache, but only 20% of the database size.

Oracle only stores clean data blocks in the DSFC.  DML reads blocks from the DSFC into the database buffer cache -- these reads are technically physical I/O from flash, but reading from flash is many times faster than reading from disk-based datafiles.  Once a checkpoint has persisted the buffer cache's dirty blocks to the database files, the blocks are marked clean and aged out of the buffer cache back to the flash cache.

Real world performance gains using DSFC have been extraordinary: up to 40x more transactions per hour simply by adding one flash storage device to your server and enabling DSFC.  Case studies published circa 2010 showed a 24x7 period might run 3X faster.  That's because it does not accelerate database maintenance operations like auditing, indexing, or stats collection.  Just imagine a $5,000 flash hardware investment making your million dollar Oracle system run 3 times faster.

The cache can have from 1 to 16 flash devices, which can be locally installed or externally attached.  They do not need to be the same size, make, or model.  You do not need to create a RAID group since DSFC accepts a comma-separated list of devices and then stripes the cache across all devices.

Enabling DSFC requires setting two database initialization parameters: DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE.  Both parameters support an ordered list of values.  The below example allocates 400 GB from devices fioa and fiob, and 1200 GB from device fioc, to create a 2TB cache:

alter system set db_flash_cache_file='/dev/fioa,/dev/fiob,/dev/fioc'
  scope=spfile;
alter system set db_flash_cache_size='400g,400g,1200g'
  scope=spfile;
shutdown immediate;
startup;

The first time you set these parameters you must use scope=spfile and then bounce the instance to pick up both changes simultaneously.  Subsequent changes may be made on-line, within restrictions.

Oracle Server Result Cache

Here's a quick review of the feature.  A deep dive is provided in another blog post.

Oracle Server Result Cache is a free feature of Enterprise Edition that allows you to cache the results of queries and PL/SQL function calls in database server memory.  Future executions of an identical query or function can immediately obtain results from the cache instead of re-executing and re-fetching the data. 

Oracle has a similar feature available for client-side caching called the Oracle Client Result Cache.  The recommendation is to use one or the other, not both at the same time.  The Server Result Cache supports all clients.  The Client Result Cache only supports OCI clients.

The Server Result Cache is physically located in a sub-region of the Oracle Shared Pool called the “Result Cache”, which is sized using the initialization parameter RESULT_CACHE_MAX_SIZE.  Memory consumed by the Result Cache takes away from the Shared Pool memory, so you might want to increase the overall size of the Shared Pool before giving memory to the Result Cache. 

The feature is disabled by default on some platforms because it has no memory allocation.  On other platforms the feature is enabled by default because the cache receives a default allocation of memory.  Check the status like so:

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
-----------------------------------------------------------------
DISABLED

To enable the Oracle Server Result Cache, just allocate it some memory and bounce the instance.  However, if the Shared Pool is not explicitly sized then an error can occur.  Therefore, in the below example I first ensure the Shared Pool is set to a real value and then I size the Server Result Cache:

ALTER SYSTEM SET SHARED_POOL_SIZE=4G SCOPE=SPFILE;
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE=1024M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Bouncing the instance is only required when enabling or disabling the feature.  You can resize it without a bounce.

ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE=1500M;

You can verify the feature is enabled with the following query:

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
-----------------------------------------------------------------
ENABLED

Now, you are ready to run queries and cache their results in the Oracle Server Result Cache. 

Oracle only caches results from queries that have the hint /*+ RESULT_CACHE */ as illustrated below:

SQL> select /*+ RESULT_CACHE */ id, fname, lname from emp order by 3,2,1;

If you want to cache all query result sets, whether or not they include the hint, then you need to reconfigure Oracle by setting the initialization parameter RESULT_CACHE_MODE=FORCE.  The allowable values are noted below:

• RESULT_CACHE_MODE=FORCE means all query results are subject to caching.
• RESULT_CACHE_MODE=MANUAL means query results are only cached when they include the optimizer hint /*+ RESULT_CACHE */. 

Please note that the init.ora parameter RESULT_CACHE_MODE has no effect on caching PL/SQL function results.  To enable caching of PL/SQL function results you must include the RESULT_CACHE clause in the function's definition.

You can also override the init.ora parameter and set the caching mode per table, using an ALTER TABLE statement with the MODE clause as shown below.  Any table where you don't do this will inherit the mode from the init.ora parameter.

ALTER TABLE FOO RESULT_CACHE (MODE FORCE);

To verify a query use the Server Result Cache, use EXPLAIN.  The explain plan will clearly indicate the Server Result Cache is part of the query execution plan.

You can also monitor memory utilization with the following command:

SET SERVEROUTPUT ON
EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

There are quite a number of views available, so refer to the Oracle Docs (ref).

Oracle Client Result Cache

Here's a quick review of the feature.  A deep dive is provided in another blog post.

Earlier I talked about the Oracle Server Result Cache.  It can cache the resulting rowsets from queries and function calls in a central location to be reused by all clients.  Here, the Oracle Client Result Cache stores the resulting rowsets locally on the client or app server for re-use by that client.  Every client maintains its own local cache.  All database sessions managed by the given client share the local cache.

A benefit of the Client Result Cache over the Server Result Cache is it avoids network calls.  The data is cached locally.  When using the Server Result Cache the client must issue a call over the network and wait for the data to be returned over the network.  The Server Result Cache is available for all clients, but the Client Result Cache is only available to OCI clients.

Tip: The Server Result Cache and the Client Result Cache are enabled by setting two different init.ora parameters, and you should only set one or the other.  Their behaviors are controlled by a shared init.ora parameter RESULT_CACHE_MODE and this parameter will control the behavior of whichever of the two features you enabled.

Before using the Oracle Client Result Cache you must satisfy three prerequisites: the database must be Enterprise Edition, the client must use Oracle Call Interface (OCI), and you must enable caching in your OCI application. 

Enabling the Oracle Client Result Cache requires just one database initialization parameter:   CLIENT_RESULT_CACHE_SIZE.  This mandatory parameter enables / disables the feature and specifies a maximum cache size.

• The default value 0 means there is no cache memory so caching is disabled.  
• A non-zero value like 1G enables caching, and this will be the default cache size used by all OCI clients.  
• Each client can override this by setting OCI_RESULT_CACHE_MAX_SIZE in file sqlnet.ora.

Therefore, to enable the Oracle Client Result Cache, just allocate it some memory and bounce the instance:

ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE=1024M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

This parameter is static, which means you cannot change it at run time.  You must set it to the SPFILE and bounce the instance.

You can set the parameter at the PDB level.  That makes sense, because most PDB map to different application clients.

There are two optional database initialization parameters you should know about.  They are tuning parameters and have default values, so they are not required for enabling or using the feature.

• CLIENT_RESULT_CACHE_LAG - defines the maximum time in milliseconds since the last round trip to the server before the client makes another round trip to pick-up changes.  The default value 3000 means "every 3 seconds".
• RESULT_CACHE_MODE - defines which query result sets are cached.  The default value MANUAL tells all OCI clients to cache the results of any query that includes hint /*+ RESULT_CACHE */.  To prevent caching results, just don't add the hint to your queries.  Setting this parameter to FORCE tells OCI clients to cache all query results whether or not the hint was included.

Pleases note parameter RESULT_CACHE_MODE is used by both the Client Result Cache and Server Result Cache features, so be careful when setting it as it effects the behavior of both caching solutions.

Optionally, the client administrator can set overrides in files sqlnet.ora and oraaccess.xml (both files are in directory $ORACLE_HOME/network/admin/ on your client computer).  An OCI client always overrides the database parameter settings with those in sqlnet.ora, and then overrides those with settings in oraaccess.xml.  I discuss these overrides in my deep dive blog.

That's basically it for enabling and configuring the Oracle Client Result Cache.

Using the Client Result Cache requires specifying the cache hint in your query, or changing the cache mode as discussed earlier.  And as noted earlier, your OCI application must have caching enabled.

Cache maintenance is automatic.  At run time Oracle will automatically invalidate stale result sets in the cache.  If the database data changes, then the next query execution will fetch the data and create a new result set in the client cache.

To monitor the Oracle Client Result Cache, use the Oracle view CLIENT_RESULT_CACHE_STATS$.  There are quite a number of views available, so refer to the Oracle Docs (ref).