One of the cool new things in the Oracle database is this new cache technology called True Cache in 23ai.  I just set it up on one of my compute instances and ran some initial tests, which I’ll talk about here. Because the posting is quite lengthy, let’s divide it into the following 4 parts:

Overview

First, let’s explain what this new cache technology is all about. Oracle True Cache is an in-memory, consistent, and automatically managed cache for Oracle Database. It caches all Oracle Database objects and data types. True Cache is a fully functional, read-only replica of the primary database, except that it’s mostly diskless. Technically speaking it’s a diskless Active Data Guard (ADG) and uses physical redo to apply updates. Unlike conventional caches, True Cache automatically keeps the most frequently accessed data in the cache, and it keeps the cache consistent with the primary database, other objects in the same cache, and other caches.  

That means, it can be used as a supplementary read-only Database Cache in front of an Oracle Database in addition to the standard Oracle Database Buffer Cache. All queries that can use cached data can also use True Cache. A query to True Cache returns only committed data, and the data is always consistent. Like all caches, the True Cache data might not be the most current data as it exists in the primary database.

Oracle True Cache provides several benefits such as  

  • Improves scalability and performance by offloading queries from the primary database.
  • Reduces application response time and network latency by deploying True Cache closer to the application. This especially benefits situations where a database is in a different location than the application due to data residency requirements.
  • Creates a large, in-memory storage area by dividing data across multiple True Caches. The total size of the cached data across all True Caches can be much larger than it would be for a single primary database or cache.
  • Automatically maintains the cache contents.
  • Simplifies development and maintenance by being transparent to the application.

Therefore, it can be used to improve performance and simplify application development. Applications can manually switch connections from True Cache to the primary database when needed. However, to take full advantage of True Cache, Java application developers who are using the latest JDBC Thin driver can set the primary database connection as read-only so the connection is directed automatically to True Cache.

To get an overview of the possible use case scenarios, check out the interactive “Oracle True Cache Technical Architecture diagrams“. The diagrams illustrate the following scenarios:

  • A general True Cache configuration with four mid-tier sessions using the JDBC Thin driver.
  • A uniform configuration with multiple, identical True Caches that use the same True Cache database application service. Client sessions are evenly distributed among True Caches, which all cache the same set of data.
  • A partitioned configuration with data divided across multiple True Caches, which each cache a different subset of the data. The total size of the cached data across all True Caches can be much larger than it would be for a single primary database or a single cache in a uniform configuration.
  • True Cache Partitioned Configuration with Multiple Services.

How can you use it today? In general, the technology is available with Oracle Database 23ai. You can use it with all cloud database editions such as Base EE, BaseDB EE-HP, BaseDB EE-EP, and ExaDB and also with the updated Oracle Database 23ai FREE edition, which is available for download. Before you start, check out the Database Licensing Information User Manual.
Please keep in mind the following when using the FREE database software:

  • You can deploy at most one Oracle Database Free True Cache for an Oracle Database Free primary database. Otherwise, the editions of True Cache and the primary database must match. For example, an Enterprise Edition True Cache can only be deployed with an Enterprise Edition primary database.

  • A Free True Cache must comply with all Oracle Database Free licensing restrictions for memory, CPUs, and user data.

Database & Installation and Configuration

To get an idea of how it works, I installed and configured it for one of my Oracle Database 23ai free installations. I used two compute instances: one for the free True Cache (here “instance-tc.xxxx.vcnus.oraclevcn.com”) and one for Oracle Database 23ai free (here “instance-23ai.xxxx.vcnus.oraclevcn.com”). You can install and configure True Cache using different methods. I followed the steps described in this documentation (see Configuring True Cache on Oracle Database Free) using Oracle DBCA.
Before I started, I stopped and disabled the firewalls on my two cloud Linux compute instances. Then I set up both instances with the following steps. 

1) Set Up Oracle Database Free Primary Database (instance-23ai.xxxx.vcnus.oraclevcn.com)
Before you configure Oracle Database Free True Cache, install and set up the Oracle Database Free primary database on the primary node. 

  • Install Oracle Database Free using RPM packages and then create and configure the primary database as described in Installation Guide for Linux x86-64.
  • Enable archive logging (ARCHIVELOG) mode and check the mode. 
    SQL> select log_mode from V$DATABASE;
LOG_MODE
------------
ARCHIVELOG

2) Configure Oracle Database Free True Cache (instance-tc.xxxx.vcnus.oraclevcn.com)
After setting up the primary database, configure Oracle Database Free True Cache on the True Cache node.

  • Install only the Oracle Database Free software using the RPM packages (see Installing Oracle Database Free RPM).
    Important hint: Please do NOT create and configure the database on the True Cache node !
  • Login as user oracle and create an env.sh script such as  
    export ORACLE_SID=FREE 
export ORACLE_BASE=/opt/oracle 
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree 
export PATH=/home/oracle/.local/bin:/home/oracle/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:
/opt/oracle/product/23ai/dbhomeFree/bin
  • Copy the primary password file located in /opt/oracle/product/23ai/dbhomeFree/dbs/orapwFREE to /opt/oracle/tmp on the true cache node.
  • The simplest way to configure a True Cache environment is to use Oracle Database Configuration Assistant (Oracle DBCA). Run the following Oracle DBCA command to complete the True Cache configuration. You can find more information on that in the chapter Oracle DBCA Commands and Parameters for True Cache.
    Before you start make sure that the easy connect string (sourceDBConnectionString) for the primary database works. You will then be asked to enter the SYS password.  
    ./dbca -silent -createTrueCache -gdbName FREE -sourceDBConnectionString instance-23ai.xxxx.vcnus.oraclevcn.com:1521/FREE -passwordFileFromSourceDB /opt/oracle/tmp/orapwFREE

    The output is the following:

    Enter Remote DB SYS user password:

Session ID of the current execution is: 1
Log file location: /opt/oracle/cfgtoollogs/dbca/FREE/trace.log_2024-05-21_12-15-20PM_58538
-----------------
Running Extract_password_file_from_blob_file job
Completed Extract_password_file_from_blob_file job
25% complete
-----------------
Running Create_static_listener job
Completed Create_static_listener job
38% complete
-----------------
Running Register_listener job
Completed Register_listener job
50% complete
-----------------
Running Extract_tde_wallet_from_blob_file job
Skipping. Job is detected as not applicable.
54% complete
-----------------
Running Setup_required_directories job
Completed Setup_required_directories job
57% complete
-----------------
Running Create_pfile job
Completed Create_pfile job
61% complete
-----------------
Running Start_nomount_instance job
Completed Start_nomount_instance job
64% complete
-----------------
Running Create_TDE_wallet job
Skipping. Job is detected as not applicable.
68% complete
-----------------
Running Create_truecache_instance job

Completed Create_truecache_instance job
71% complete
-----------------
Running Add_oratab_entry job
Completed Add_oratab_entry job
75% complete
-----------------
Running Reopen_wallet job
Skipping. Job is detected as not applicable.
100% complete
---------- PLUGIN NOTES ----------
Successfully created True Cache.
In order to complete the operation,configure the True Cache database application services from the primary database.
---------- END OF PLUGIN NOTES ----------
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.

In the documentation, you will find the additional steps on how to configure database application services for True Cache. Let’s leave this out for now and test it with this basic setup.

Verification Steps

Now let’s verify the setup on the true cache instance. The True Cache instance is already running and the init.ora is already created. 

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
FREE      READ ONLY WITH APPLY TRUE CACHE

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ ONLY  NO

SQL> select controlfile_type from v$database;
CONTROLFIL
----------
TRUE CACHE

Now let’s query the new view V$TRUE_CACHE to find out more about the True Cache.

SQL> col TRUE_CACHE_NAME format a15
SQL> col status format a10 
SQL> col primary_name format a15
SQL> select TRUE_CACHE_NAME, PRIMARY_NAME, STATUS, REMOTE_VERSION, CON_ID from v$true_cache;

TRUE_CACHE_NAME PRIMARY_NAME    STATUS          REMOTE_VERSION         CON_ID
--------------- --------------- --------------- ------------------ ----------
FREE            FREE            HEALTHY         23.0.0.0.0                  0

Now let’s investigate the parameter file: View $ORACLE_HOME/dbs/init_FREE.ora.

[oracle@instance-tc-1 dbs]$ more  init_FREE.ora
##############################################################################
# Copyright (c) 1991, 2024 by Oracle Corporation
##############################################################################
###########################################
# Database Identification
###########################################
db_name="FREE"
###########################################
# File Configuration
###########################################
db_create_file_dest="/opt/oracle/oradata"
db_files=200
###########################################
# Miscellaneous
###########################################
enable_pluggable_database=true
true_cache=true
###########################################
# Network Registration
###########################################
remote_listener=instance-23ai.xxxx.vcnus.oraclevcn.com:1521
local_listener=LISTENER
###########################################
# SGA Memory
###########################################
sga_target=1536m
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=512m
###########################################
# Standby Database
###########################################
fal_client=instance-tc.xxxx.vcnus.oraclevcn.com:1521/FREE
fal_server=instance-23ai.xxxx.vcnus.oraclevcn.com:1521/FREE
db_unique_name="FREE"

For simplicity, I added the following two entries in the tnsnames.ora on the True Cache and the primary instance.

freepdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = instance-23ai.xxxx.vcnus.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = freepdb1)
    )
  )

free_tc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = instance-tc.xxxx.vcnus.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = freepdb1)
    )
  )

The service name is freepdb1 on both hosts and it will be used now in the tnsnames entry freepdb1 for the primary host connection and in free_tc for the true cache service. So we will use both of them to connect either to the primary or to the True Cache instance. 

In general, you can shut down the True Cache instance using the SHUTDOWN command. You can start an existing True Cache using the STARTUP command. In the following example we did startup the True Cache instance again. 

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1599564808 bytes
Fixed Size                  5360648 bytes
Variable Size             402653184 bytes
Database Buffers         1191182336 bytes
Redo Buffers                 368640 bytes
Database mounted.
Database opened.

Now let’s have fun and query data – once using the primary database cache and then using True Cache.

Basic Tests

To have some data for queries, I install now the database schema SH from GitHub. You will find a short description how to do a quick install in the post Sample data for Oracle Database.

Let’s use the table CUSTOMERS; it’s not partitioned and has enough blocks to work with.
On the primary host with the default service freepdb1, check the table CUSTOMERS.

SQL> select num_rows, blocks, partitioned from all_tables 
     where owner='SH' and table_name='CUSTOMERS';
  NUM_ROWS     BLOCKS PAR
---------- ---------- ---
     55500       1630 NO

So let’s pre-load the table CUSTOMERS into the buffer cache on the primary host with the following commands.
Note: Change “_small_table_threshold” manually, to avoid direct path reads for big tables. “_small_table_threshold” defines the lower threshold level of table size for direct reads.

-- "_small_table_threshold" defines the lower threshold level of table size for direct reads and may be increased manually 
-- alter session set "_small_table_threshold"=3000;

SQL> select /*+FULL(c)*/ count(*) from sh.customers c;
  COUNT(*)
----------
     55500

In the next step, we check the cache status with V$BH. As a reminder: V$BH lists those blocks with DATA_OBJECT_ID that are currently in the buffer cache.

SQL> set trimspool on
SQL> set linesize 180
SQL> col object_name format a30
SQL> col object_type format a20
SQL> col owner format a25
SQL> col number_of_blocks format 9999999999999

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS,   
  2  substr(d.database_role,1,10) role   
  3  FROM DBA_OBJECTS o, V$BH bh,  v$database d   
  4  WHERE o.DATA_OBJECT_ID = bh.OBJD   
  5  AND o.OWNER in ('SH') and o.object_name like 'CUSTOMERS%'   
  6  GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE,d.database_role;

OBJECT_NAME                    OBJECT_TYPE          OWNER                     NUMBER_OF_BLOCKS ROLE
------------------------------ -------------------- ------------------------- ---------------- ----------------------------------------
CUSTOMERS                      TABLE                SH                                    1541 PRIMARY
CUSTOMERS_GENDER_BIX           INDEX                SH                                       5 PRIMARY
CUSTOMERS_MARITAL_BIX          INDEX                SH                                       1 PRIMARY
CUSTOMERS_PK                   INDEX                SH                                       1 PRIMARY
CUSTOMERS_YOB_BIX              INDEX                SH                                       1 PRIMARY

So let’s query the True Cache instance using the tnsname free_tc.

SQL> connect system/password@free_tc
SQL> set trimspool on
SQL> set linesize 180
SQL> col object_name format a30
SQL> col object_type format a20
SQL> col owner format a25
SQL> col number_of_blocks format 9999999999999

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS,
  2  substr(d.database_role,1,10) role
  3  FROM DBA_OBJECTS o, V$BH bh,  v$database d
  4  WHERE o.DATA_OBJECT_ID = bh.OBJD
  5  AND o.OWNER in ('SH') and o.object_name like 'CUSTOMERS%'
  6  GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE,d.database_role;
no rows selected

Now let’s connect as SH with the True Cache instance. Let’s query one row of the table CUSTOMERS. It will use the primary key for this.

SQL> connect sh/sh@free_tc
SQL> set lines 200
SQL> select * from sh.customers where cust_id=1;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           C CUST_YEAR_OF_BIRTH CUST_MARITAL_STATUS  CUST_STREET_ADDRESS                      CUST_POSTA CUST_CITY
---------- -------------------- ---------------------------------------- - ------------------ -------------------- ---------------------------------------- ---------- ------------------------------
CUST_CITY_ID CUST_STATE_PROVINCE                      CUST_STATE_PROVINCE_ID COUNTRY_ID CUST_MAIN_PHONE_NUMBER    CUST_INCOME_LEVEL              CUST_CREDIT_LIMIT
------------ ---------------------------------------- ---------------------- ---------- ------------------------- ------------------------------ -----------------
CUST_EMAIL                                         CUST_TOTAL     CUST_TOTAL_ID CUST_SRC_ID CUST_EFF_ CUST_EFF_ C
-------------------------------------------------- -------------- ------------- ----------- --------- --------- -
         1 Abigail              Kessel                                   M               1957                      7 South 3rd Circle                       30828      Downham Market
       51396 England - Norfolk                                         52591      52789 127-379-8954              G: 130,000 - 149,999                        9000
Kessel@company.example.com                         Customer total         52772             01-JAN-19           I

Let’s check the True Cache. The index and table blocks are in the true cache now.

SQL> set trimspool on
SQL> set linesize 180
SQL> col object_name format a30
SQL> col object_type format a20
SQL> col owner format a25
SQL> col number_of_blocks format 9999999999999

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS,
  2  substr(d.database_role,1,10) role
  3  FROM DBA_OBJECTS o, V$BH bh,  v$database d
  4  WHERE o.DATA_OBJECT_ID = bh.OBJD
  5  AND o.OWNER in ('SH') and o.object_name like 'CUSTOMERS%'
  6  GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE,d.database_role;

OBJECT_NAME                    OBJECT_TYPE          OWNER                     NUMBER_OF_BLOCKS ROLE
------------------------------ -------------------- ------------------------- ---------------- ----------------------------------------
CUSTOMERS_PK                   INDEX                SH                                       2 TRUE CACHE
CUSTOMERS                      TABLE                SH                                       2 TRUE CACHE

So let’s load more blocks with the following query.

-- SQL> alter session set "_small_table_threshold"=3000;

SQL> select /*+FULL(c)*/ count(*) from sh.customers c;
  COUNT(*)
----------
     55500

As we can see in our monitoring session: The table blocks are in the True Cache now, as we expected.

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, substr(d.database_role,1,10) role
  2  FROM DBA_OBJECTS o, V$BH bh, v$database d
  3  WHERE o.DATA_OBJECT_ID = bh.OBJD
  4  AND o.OWNER in ('SH') and o.object_name like 'CUSTOMERS%'
  5  GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, d.database_role;

OBJECT_NAME                    OBJECT_TYPE          OWNER                     NUMBER_OF_BLOCKS ROLE
------------------------------ -------------------- ------------------------- ---------------- ----------------------------------------
CUSTOMERS                      TABLE                SH                                    1541 TRUE CACHE
CUSTOMERS_PK                   INDEX                SH                                       2 TRUE CACHE

This concludes my first test.
The queries and the setup used in this post were intentionally chosen very basic so that you can get an understanding of how this functionality can be set up and used. For real-world scenarios, you may extend this setup with additional application services (see Configuring Database Application Services for True Cache). You can also use complementary caching features with True Cache, such as KEEP Buffer and server-side result set cache.

The best is to try it out yourself. 

Further readings