Oracle 23c New Features for DBA'sStarting 18c, A new pool is introduced in SGA called MemOptimize pool to store the frequently access tables (in other words pin your tables in buffer cache) via primary key values. The Memoptimized Rowstore (pool) uses a memory area in the system global area (SGA) called the memoptimize pool that stores the hash indexes of the tables when enabled for fast lookup.
For Fast look up of the tables you must,
- Have a primary key on the table
- No compression is used on the table
When you set Memoptimize_pool_size to a non zero value that much size is allocated to memoptimize store from SGA. So you must be careful while setting this as it takes away the memory from SGA.
You can use alter system set memoptimize_pool_size=n scope=spfile to set the parameter. Note this parameter is not dynamic and required database restart.
To enable memoptimize on tables , you can use create/alter table statement with optimize for read option.
- create table test (id number primary key, name varchar2(10)) mem optimize for read segment creation immediate;
- Alter table test mem optimize for read
-Thanks
Follow Me!!!