Oracle Database 20c Automatic In-Memory Enhancements

In Oracle Database 20c the Database In-Memory feature Automatic In-Memory (AIM) has been significantly enhanced. I wrote about AIM when it first came out in Oracle Database 18c here. Oracle Database 20c adds a new HIGH option to the INMEMORY_AUTOMATIC_LEVEL initialization parameter. With this setting all objects that do not have a pre-existing INMEMORY setting are automatically set to INMEMORY MEMCOMPRESS AUTO by default. AIM then automatically manages objects populated into the In-Memory (IM) column store using access tracking and column statistics. This is a big change in behavior and addresses one of the most frequent questions that customers have had, which is "How do I determine which objects to populate into the IM column store?"

The previous parameter options of LOW and MEDIUM still exist and function the same as they did when introduced in Oracle Database 18c. However, with the new HIGH option in Oracle Database 20c the database automatically manages the contents of the IM column store. It monitors segment activity using an access tracking and column statistic infrastructure similar to Heat Map data which was introduced as part of Automatic Data Optimization (ADO). With AIM set to HIGH segments are automatically evicted and populated based on usage. You do not have to pick and choose which objects to enable for in-memory. In addition, individual columns may be automatically compressed as well by AIM. All of this has been done to make the most optimal use of the IM column store and provide the best performance possible, automatically!

When the INMEMORY_AUTOMATIC_LEVEL initialization parameter has been set to HIGH all objects that do not have a pre-existing INMEMORY setting are automatically set to INMEMORY MEMCOMPRESS AUTO. This is a new option of the INMEMORY MEMCOMPRESS subclause in 20c that is part of the AIM feature. If you do not want specific objects to be populated in the IM column store then you can still manually set them to NO INMEMORY. Also, segments marked with an INMEMORY PRIORITY setting other than NONE are excluded from automatic eviction.

If you decide that you no longer want to use the HIGH setting of the INMEMORY_AUTOMATIC_LEVEL parameter then when you change the parameter value or unset it all segments with MEMCOMPRESS AUTO will be set to NO INMEMORY.

One other interesting feature of AIM is that you can adjust the window that AIM considers for segment usage. In other words, AIM can be adjusted to only consider the object usage statistics that coincide with your active workload window. That way those statistics won't be skewed by periods of inactivity, other application usage or perhaps even maintenance activities.

How do you tell what AIM has done? AIM runs tasks, similar to ADO, and those tasks are exposed in two data dictionary views. The views DBA_INMEMORY_AIMTASKS and DBA_INMEMORY_AIMTASKDETAILS can be queried to see what actions AIM has performed. Of course you can still query the contents of the IM column store using the view v$im_segments and see how much memory has been used using the view v$inmemory_area.