DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle 23ai: Utilizing Memoptimized Rowstore Without Setting a Hint

#oracle #devops #database #linux

In Oracle Database versions 19c and 21c, enabling the Memoptimized Rowstore feature required activating it at the table level and adding a hint to the INSERT statement. Since some applications might already be developed, adding the MEMOPTIMIZE_WRITE hint to INSERT statements could pose a challenge for leveraging this feature.

SQL> ALTER TABLE tbl1 MEMOPTIMIZE FOR WRITE;
Table altered
SQL> INSERT /*+ MEMOPTIMIZE_WRITE */ INTO tbl1 VALUES (1,'usefzadeh.com');
Enter fullscreen mode Exit fullscreen mode

Thus, in the mentioned versions, using the Memoptimized Rowstore feature without the MEMOPTIMIZE_WRITE hint was not possible.

In version 23ai, Oracle introduced a parameter named MEMOPTIMIZE_WRITES, which allows the use of this feature without requiring a hint:

SQL> show parameter memoptimize_writes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memoptimize_writes                   string      HINT
Enter fullscreen mode Exit fullscreen mode

The default value of this parameter is HINT, meaning the MEMOPTIMIZE_WRITE hint is mandatory. By setting this parameter to ON, the hint is no longer necessary:

SQL> alter system set memoptimize_writes=ON scope=spfile;
System altered.

SQL> startup force;
Enter fullscreen mode Exit fullscreen mode

After configuring this parameter, create a table and enable the MEMOPTIMIZE FOR WRITE feature:

Connected to Oracle Database 23c Free, Release 23.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    mydate date
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  partition by range ( mydate )
  9  interval ( numtoyminterval(1, 'MONTH'))
 10  (
 11   PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2015', 'DD-MM-YYYY'))
 12  );
Table created
SQL> ALTER TABLE tbl1 MEMOPTIMIZE FOR WRITE;
Table altered
Enter fullscreen mode Exit fullscreen mode

Insert a record into the table:

SQL> insert into TBL1 values(1, 'www.usefzadeh.com', sysdate);
1 row created.

SQL> commit;
Commit complete.
Enter fullscreen mode Exit fullscreen mode

The INSERT statement executed without errors, but the record still resides in memory and hasn't been written to disk, indicating that the Fast Ingest feature is active. To flush the record to disk, you can wait or execute the following command:

SQL> exec dbms_memoptimize_admin.writes_flush();
PL/SQL procedure successfully completed.

SQL> select * from TBL1;
ID TEXT                 MYDATE
--- -------------------- ---------
  1 www.usefzadeh.com    20-SEP-23
Enter fullscreen mode Exit fullscreen mode

Reverting the parameter to memoptimize_writes=HINT necessitates using the hint for leveraging this feature:

SQL> alter system set memoptimize_writes=HINT scope=spfile;
System altered.
SQL> startup force;

SQL> truncate table tbl1;
Table truncated.

SQL> insert into TBL1 values(1, 'www.usefzadeh.com', sysdate);
1 row created.

SQL> select * from TBL1;
ID TEXT                 MYDATE
--- -------------------- ---------
  1 www.usefzadeh.com    20-SEP-23

SQL> truncate table tbl1;
Table truncated.

SQL> insert /*+ MEMOPTIMIZE_WRITE */ into TBL1 values(1, 'www.usefzadeh.com', sysdate);
1 row created.

SQL> select * from TBL1;
no rows selected
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.