Lightning-Fast Queries with Oracle’s Memoptimized Rowstore

Updated October 2025

Introduction

With this release, our Oracle tools now support Oracle’s memoptimized rowstore feature (introduced in 18c). The memoptimized rowstore enables ultra-low latency lookups on tables that are primarily queried by their primary keys. This is especially useful for high-throughput scenarios like IoT workloads.

Currently, this feature is available only in Exadata and Oracle Cloud’s Enterprise Edition Extreme Performance. For licensing details, see the Oracle documentation.

---

Enabling the Feature: Backup First

Before you turn anything on, always back up your database parameters:

• SPFile parameters are stored in a binary SPFile

• To edit them safely, create a PFile (text version) as a backup

Hora makes this process easy. Log in as SYS to the ROOT container, then open the Database | SGA page. The lower grid shows memory-related parameters.

To back up:

1. Right-click that parameter grid → “Backup SPFile to PFile”

2. A dialog appears (SPFile name read-only; PFile target editable)

3. Confirm the creation

  CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Now you have an editable PFile you can roll back to in emergencies.

---

Activating Memoptimized Rowstore

Next, find the memoptimize_pool_size parameter in the same grid:

1. Right-click → “Alter System Parameter”

2. Enter a size (e.g. 100M)

3. Set the scope to spfile

4. Optionally include a comment

5. Confirm the change

If the database doesn’t meet requirements (as stated in Oracle’s licensing guide), the startup may fail. You might see an error like:

In that case:

• Use your saved PFile to remove the memoptimized_pool_size line

• Start up the database using the PFile:

---

Verifying Column Status in Hora

Once the database is up:

1. Log in as the owner schema

2. Open the Tables page

3. The data grid now shows two new columns: Memoptimize Read and Memoptimize Write
   These indicate the status for each table.

You can enable or disable these flags:

• Right-click a table

• Choose “Mem-Optimize Table …”

• The popup lets you toggle Read or Write setting

Once enabled, you must populate the table via:

Note: In Reverse DB, KeepTool will automatically include the ALTER TABLE statements in the generated DDL script after constraints, so that the table’s primary key is already in place.

---

Final Thoughts

The memoptimized rowstore provides a powerful way to speed up primary-key lookups with minimal latency overhead.
Via Hora and KeepTool tooling, enabling, monitoring, and managing this feature becomes intuitive.

If you work with high-frequency access patterns or microservice architectures, this feature could be a game-changer.

---