For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
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:
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.
Invalid schema name
Invalid table name
Initialization (Startup) Parameters
ALTER SYSTEM SET memoptimize_pool_size = 60G sid='*' scope=SPFILE;
SQL> CREATE TABLE 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 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> ALTER TABLE uwclass.servers
2 ADD CONSTRAINT pk_servers PRIMARY KEY (srvr_id)
3 USING INDEX
4* PCTFREE 0;
SQL> ALTER TABLE servers MEMOPTIMIZE FOR READ;
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');
PL/SQL procedure successfully completed.
-- explicitly populate the memoptimize pool with rows from the table