With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete.
This is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized tables) to improve significantly the performance of queries having filter on PKs.

This new MEMPTIMIZE POOL memory area is split in 2 parts:

  • Memoptimize buffer area: 75% of the space reserved to store table buffers the same way as they are store in the so-called buffer cache
  • Hash index: 25% of the space reserved to store the hash index of primary key from table in the Memoptimize buffer area

To manage this space a new parameter MEMOPTIMIZE_POOL_SIZE is available, unfortunately not dynamic. This parameter is fixed at run time and it is not managed with the database automatic memory management. This parameter takes space from the SGA_TARGET so be careful when dimensioning it.

Before this new memory structure, clients who want to query a standard table with a filter on its PK (e.g: where COL_PK = X ) have to wait on I/Os coming from the disk to the memory until reach the X value from the index. Then I/Os again from disk to memory to fetch the table block containing the row from the table where COL_PK = X. This mechanism consumes I/Os of course and also CPU cycles because it involves other processes of the instance who need to perform some tasks.

Now thanks to this new memory space, when a client does the exact same query where COL_PK = X, it can directly hash the value and walk through the Hash Index to find the row location in the Memoptimize buffer area. Then the result is directly picked up by the client process. It results in less CPU consumption and less I/Os disk in most of case at the cost of memory space.

When to used?

It is only useful in case when queries are done on table with an equality filter on the PK. You can balance the need with the size of the requested table and the frequency of usage of such queries.

4 steps activation

  1. Check that the COMPATIBLE parameter is set to 18.0.0 or higher
  2. Set the parameter MEMOPTIMIZE_POOL_SIZE to the desired value (restart required)
  3. Alter (or create) target table with the “MEMOPTIMIZE FOR READ” clause
  4. Then execute the procedure “DBMS_MEMOPTIMIZE.POPULATE( )” to populate the MEMOPTIMIZE POOL with the target table

How to remove a table from the MEMOPTIMIZE POOL ?

With the procedure DROP_OBJECT() from the DBMS_MEMOPTIMIZE package.

You can disable the access to this new MEMPTIMIZE POOL by using the clause “NO MEMOPTIMIZE FOR READ”.

 

I hope this helps and please do not hesitates to contact us should you want more details.

Nicolas

Post Views: 352
Thumbnail [60x60]
by
Nicolas Penot