Our Oracle tools add support for the memoptimized rowstore feature introduced by Oracle 18c. The memoptimized rowstore provides the capability of fast data lookup for tables that are mainly queried based on primary key columns. The option provides a very, fast latency-reduced, data access for high on demand access rates, as needed for example for internet of things (IoT) workloads.
The feature memoptimized rowstore is currently available for Exadata and Oracle database cloud services “Enterprise Edition Extreme Performance”. For more information see the Licensing Information User Manual.
First of all, the memoptimized rowstore must be enabled by setting a database parameter. Before making any change, it is a good idea to back up your database parameters that are stored as a binary SPFile into an editable copy that is called PFile. We will show you later how easy you can get in need of a backup copy.
Hora can create the backup copy for you. Logon to the ROOT database as SYS and open the Database | SGA page. The data grid in the bottom of the dialog shows a subset of database parameters that are related to memory configuration. For a full list of database parameters, go to Hora’s database page.
In order to back up the database parameters, right-click the data grid and choose the menu item ‘Backup SPFile to PFile’. It opens the following dialog:
The SPFile name is read-only. Check the name of the PFile to be created and then click OK. In the case Hora’s SQL-Preview option has been enabled, you can sneak the command before execution:
CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';
Now you have an editable backup copy of the database parameter file that can be used for recovery of the parameter configuration whenever needed.
Now it is time to change the parameter to enable the memoptimized rowstore. Find the entry “memoptimize pool size” and right-click the data grid and choose the context menu “alter system parameter”:
Enter a minimum size of 100M and make sure the scope is set to “spfile”. It is always a good idea to add a comment as well.
After confirming the change, it is required to restart the database to enable the memoptimized rowstore.
In the case your database does not meet the requirements mentioned in the Licensing Information User Manual the database may not be come up right after restarting the service. If you logon to SQL-Plus, you can try to startup the database manually.
SQL> connect SYS as sysdba SQL> startup ORA-12754: Feature 'Memoptimized Rowstore' is disabled due to missing capability 'Runtime Environment'.
Now it is helpful to have a backup copy of the parameter configuration. On the database server, open the PFile using a text edidor, remove the line that contains the ‘memoptimized_pool_size’ parameter and save.
Using the following SQL-Plus command, startup your database using the PFile:
SQL> connect SYS as sysdba SQL> startup pfile=C:\Oracle\ora19c3\database\PFILEORA19C3.ORA ORACLE instance started.
If your database meets the requirements mentioned above, the memotimized rowstore has been enabled now. Run Hora and login as the owner of your database schema and open the tables page. The overview grid shows two new columns “Memoptimize Read” and “Memoptimize Write” to display for each table the status of the memoptimized rowstore.
You can change both flags by right-clicking a table and choosing “Mem-Optimize Table …” from the context menu. The popup allows you to change each of the flags.
After the table has been enabled for using the memoptimized rowstore, you can fill it by calling
DBMS_MEMOPTIMIZE.POPULATE()
BTW, KeepTool Reverse DB adds the appropriate ALTER TABLE statement later in the generated DDL script. That makes sure constraints have been added first, since enabling the option requires an existing primary key.
Explore possibilities of KeepTool.
