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