Oracle DBMS_MEMOPTIMIZE
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
Exceptions
Error Code Reason
ORA-62135 Invalid schema name
ORA-62136 Invalid table name
First Available 18.0.0.0
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 (new 18.1)
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('C##UWCLASS', 'SERVERS');
 
POPULATE (new 18.1)
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('C##UWCLASS', 'SERVERS');
 
Demo
Pin an existing table in the memoptimize pool ALTER TABLE c##uwclass.servers MEMOPTIMIZE FOR READ;

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

SQL> show parameter memopt

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

SQL> col value format 9999999999
SQL> SELECT * FROM v$sga;

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


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

System altered.

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

SQL> 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.

SQL> show parameter memopt

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

SQL> col value format 9999999999

SQL> 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.

SQL> col component format a30

SQL> SELECT component, current_size, min_size, max_size, user_specified_size
  2  FROM v$memory_dynamic_components
  3  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

SQL> conn sys@pdbdev as sysdba
Enter password:
Connected.

SQL> SELECT * FROM v$memoptimize_write_area;

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

SQL> GRANT execute ON dbms_memoptimize TO c##uwclass;

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

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

SQL> CREATE TABLE c##uwclass.servers(
  2  srvr_id NUMBER(10),
  3  network_id NUMBER(10),
  4  status VARCHAR2(1),
  5  latitude FLOAT(20),
  6  longitude FLOAT(20),
  7  netaddress VARCHAR2(15))
  8  SEGMENT CREATION IMMEDIATE
  9* MEMOPTIMIZE FOR READ;
CREATE TABLE c##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

SQL> CREATE TABLE c##uwclass.servers(
  2  srvr_id NUMBER(10),
  3  network_id NUMBER(10),
  4  status VARCHAR2(1),
  5  latitude FLOAT(20),
  6  longitude FLOAT(20),
  7  netaddress VARCHAR2(15))
  8* SEGMENT CREATION IMMEDIATE;

Table created.

SQL> ALTER TABLE c##uwclass.servers
  2  ADD CONSTRAINT pk_servers PRIMARY KEY (srvr_id)
  3  USING INDEX
  4* PCTFREE 0;

Table altered.

SQL> ALTER TABLE servers MEMOPTIMIZE FOR READ;

Table altered.

BEGIN
  INSERT INTO c##uwclass.servers VALUES (1,1028,'Y',32.9806,-117.2567,'172.020.130.002');
  INSERT INTO c##uwclass.servers VALUES (2,1028,'Y',32.6956,-117.1261,'172.020.130.018');
  INSERT INTO c##uwclass.servers VALUES (3,1028,'Y',32.5525,-117.0461,'172.020.130.026');
  INSERT INTO c##uwclass.servers VALUES (5,1028,'N',32.7297,-117.1722,'172.020.130.010');
  INSERT INTO c##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

SQL> exec dbms_memoptimize.populate('C##UWCLASS', 'SERVERS');

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
DBMS_INMEMORY
DBMS_INMEMORY_ADMIN
Full Database Caching
In Memory Database
Memory Management
Startup Parameters
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved