Oracle DBMS_MEMOPTIMIZE
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose The memoptimize pool caches data to optimizes read operations for key based SELECT statements from uncompressed heap tables created or altered with the MEMOPTIMIZE FOR READ clause. With the memoptimize pool populated significant performance improvements can be achieved for statements such as:

SELECT <column_name_list>
FROM <table_name>
WHERE <primary_key_column_name> = <value>;


The memoptimize pool does this by pulling requested buffers directly from the SGA over the network, minimizing or eliminating CPU and operating system overhead. Application changes are not required to leverage the memoptimize pool except for CREATE TABLE DDL statements.

The memoptimize pool uses essentially the same structure as the BUFFER CACHE but does not utilize any of the memory allocated for the BUFFER CACHE.

The memoptimize pool is disabled by default. To enable its use a DBA must explicitly set a non-zero pool size as demonstrated at the bottom of this page. To calculate the pool size first determine the size required for the pool required to cache the data then add an additional 25% for the required hash index.

The DBMS_MEMOPTIMIZE package is the interface for managing data in the memoptimize pool.

This package provides the following functionality:
 - Populate the Memoptimized Rowstore hash index with the data related to a specific table.
 - Remove the data from the Memoptimized Rowstore hash index related to a specific table.
AUTHID CURRENT_USER
Dependencies
ALL_TAB_PARTITIONS DBMS_MEMOPTIMIZE_LIB V$MEMORY_DYNAMIC_COMPONENTS
ALL_TAB_SUB_PARTITIONS V$MEMOPTIMIZE_WRITE_AREA  
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-62135 Invalid schema name
ORA-62136 Invalid table name
First Available 18c
Initialization (Startup) Parameters MEMOPTIMIZE_POOL_SIZE
ALTER SYSTEM SET memoptimize_pool_size = 60G sid='*' scope=SPFILE;
   
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsmemoptimize.sql
Subprograms
 
DROP_OBJECT
Removes the in-memory hash index for a table dbms_memoptimize.drop_object(
schema_name    IN VARCHAR2,
table_name     IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL);
exec dbms_memooptimize.drop_object('UWCLASS', 'SERVERS');
 
GET_APPLY_HWM_SEQID
Returns the global low HWM of sequence numbers that have successfully been flushed to disk dbms_memoptimize.get_apply_hwm_seqid RETURN BINARY_DOUBLE;
exec dbms_memoptimize.get_apply_hwm_seqid
FROM dual;
 
GET_WRITE_HWM_SEQID
Returns the HWM of sequence numbers written by this session; it is the sequence id of the last write made by this session dbms_memoptimize.get_write_hwm_seqid RETURN BINARY_DOUBLE;
SELECT dbms_memoptimize.get_write_hwm_seqid
FROM dual;
 
POPULATE
Populates the in-memory hash index for a table dbms_memoptimize.populate(
schema_name    IN VARCHAR2,
table_name     IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL);
exec dbms_memooptimize.populate('UWCLASS', 'SERVERS');
 
WRITE_END
Signals IGA teardown for the current session; does cleanup, currently limited flushing all data from the IGA for the most recently written table dbms_memoptimize.write_end;
exec dbms_memoptimize.write_end;
 
Demo
Pin an existing table in the memoptimize pool ALTER TABLE uwclass.servers MEMOPTIMIZE FOR READ;

exec dbms_memoptimize.populate('UWCLASS', 'SERVERS');
Create a new table pinned in the memoptimize pool: DBA actions conn / as sysdba

show parameter memopt

NAME                  TYPE        VALUE
--------------------- ----------- -----
memoptimize_pool_size big integer     0


col value format 9999999999
SELECT * FROM v$sga;

NAME               VALUE      CON_ID
------------------ ---------- ------
Fixed Size            8907792      0
Variable Size      1040187392      0
Database Buffers   4429185024      0
Redo Buffers         74977280      0



ALTER SYSTEM SET memoptimize_pool_size = 1G sid='*' scope=SPFILE;

System altered.

SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.


STARTUP;
ORACLE instance started.

Total System Global Area 5553257488 bytes
Fixed Size 8907792 bytes
Variable Size 2030043136 bytes
Database Buffers 3439329280 bytes
Redo Buffers 74977280 bytes
Database mounted.
Database opened.


show parameter memopt

NAME                  TYPE        VALUE
--------------------- ----------- -----
memoptimize_pool_size big integer 1G


col value format 9999999999

SELECT * FROM v$sga;

NAME               VALUE      CON_ID
------------------ ---------- ------
Fixed Size            8907792      0
Variable Size      1912602624      0
Database Buffers   3556769792      0
Redo Buffers         74977280      0


-- compare the yellow and green highlighted rows
-- before and after enabling the memoptimize pool


col component format a30

SELECT component, current_size, min_size, max_size, user_specified_size
FROM v$memory_dynamic_components
ORDER BY 1;

COMPONENT                      CURRENT_SIZE MIN_SIZE   MAX_SIZE   USER_SPECIFIED_SIZE
------------------------------ ------------ ---------- ---------- -------------------
ASM Buffer Cache                          0          0          0                   0
DEFAULT 16K buffer cache                  0          0          0                   0
DEFAULT 2K buffer cache                   0          0          0                   0
DEFAULT 32K buffer cache                  0          0          0                   0
DEFAULT 4K buffer cache                   0          0          0                   0
DEFAULT 8K buffer cache                   0          0          0                   0
DEFAULT buffer cache             3321888768 3321888768 3556769792                   0
Data Transfer Cache                       0          0          0                   0
In Memory RO Extension Area               0          0          0                   0
In Memory RW Extension Area               0          0          0                   0
In-Memory Area                            0          0          0                   0
KEEP buffer cache                         0          0          0                   0
PGA Target                       3707764736 3707764736 3707764736          3707764736
RECYCLE buffer cache                      0          0          0                   0
SGA Target                       5553258496 5553258496 5553258496          5553258496
Shared IO Pool                    234881024          0  234881024                   0
java pool                          16777216   16777216   16777216                   0
large pool                         33554432   33554432  150994944                   0
memoptimize buffer cache         1073741824 1073741824 1073741824          1073741824
shared pool                       788529152  788529152  788529152                   0
streams pool     
                         0          0          0                   0

conn sys@pdbdev as sysdba
Enter password:
Connected.

SELECT * FROM v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE NUM_WRITES NUM_WRITERS CON_ID
---------- ---------- ---------- ---------- ----------- ------
         0          0          0          0           0      3


GRANT execute ON dbms_memoptimize TO uwclass;

Grant succeeded.
Create a new table pinned in the memoptimize pool: DBA actions SQL> conn uwclass/uwclass@pdbdev

-- first let's try what doesn't work to learn why

CREATE TABLE uwclass.servers(
srvr_id    NUMBER(10),
network_id NUMBER(10),
status     VARCHAR2(1),
latitude   FLOAT(20),
longitude  FLOAT(20),
netaddress VARCHAR2(15))
SEGMENT CREATION IMMEDIATE
MEMOPTIMIZE FOR READ;
CREATE TABLE uwclass.servers(
*
ERROR at line 1:
ORA-62142: MEMOPTIMIZE FOR READ feature requires NOT DEFERRABLE PRIMARY KEY constraint on the table


-- the table must have a defined uniqueness, for example a primary key,
-- to use the MEMOPTIMIZE FOR READ clause now let's do it correctly
-- ... first create the table, then the primary key, then alter the table


CREATE TABLE uwclass.servers(
srvr_id    NUMBER(10),
network_id NUMBER(10),
status     VARCHAR2(1),
latitude   FLOAT(20),
longitude  FLOAT(20),
netaddress VARCHAR2(15))
SEGMENT CREATION IMMEDIATE;

Table created.

ALTER TABLE uwclass.servers
ADD CONSTRAINT pk_servers PRIMARY KEY (srvr_id)
USING INDEX
PCTFREE 0;

Table altered.

ALTER TABLE servers MEMOPTIMIZE FOR READ;

Table altered.

BEGIN
  INSERT INTO uwclass.servers VALUES (1,1028,'Y',32.9806,-117.2567,'172.020.130.002');
  INSERT INTO uwclass.servers VALUES (2,1028,'Y',32.6956,-117.1261,'172.020.130.018');
  INSERT INTO uwclass.servers VALUES (3,1028,'Y',32.5525,-117.0461,'172.020.130.026');
  INSERT INTO uwclass.servers VALUES (5,1028,'N',32.7297,-117.1722,'172.020.130.010');
  INSERT INTO uwclass.servers VALUES (6,1028,'Y',32.7501,-117.1603,'172.020.130.006');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

-- explicitly populate the memoptimize pool with rows from the table

exec dbms_memoptimize.populate('UWCLASS', 'SERVERS');

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
DBMS_INMEMORY
DBMS_INMEMORY_ADMIN
DBMS_MEMOPTIMIZE_ADMIN
Full Database Caching
In Memory Database
Memory Management
Startup Parameters
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx