Oracle 18c : MEMOPTIMIZE_POOL_SIZE in SGA

Description:-

  • Oracle 18c introduces new feature called MemOptimize pool, a memory area in the system global area (SGA).
  • We can increase the size of the memoptimize pool by manually adjusting the MEMOPTIMIZE_POOL_SIZE initialization parameter.
  • We have use this feature’s to frequently access tables in database level. Those tables are pined into buffer cache using primary key values
  • Tables must have primary key column then only we will achieve the expected performance from the query level.
  • Tables should not be compressed. Compressed tables not suitable for Memoptimized Rowstore option.

To check the value of Memoptimized _Pool_size parameter

To set the size of MEMOPTIMIZE_POOL_SIZE parameter.

Note this parameter is not dynamic and required database restart.

Now the MEMOPTIMIZE_POOL_SIZE parameter value has been changed to 1GB.

Create table with  Memoptimized Rowstore option.

Alter the existing table for memoptimized read.

We can load the table data into pool using DBMS_MEMOPTIMIZEpackage:

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

 

 

 

One thought on “Oracle 18c : MEMOPTIMIZE_POOL_SIZE in SGA”

Leave a Reply

Your email address will not be published. Required fields are marked *