In Oracle Database 21c the Database In-Memory feature Automatic In-Memory (AIM) has been significantly enhanced. I wrote a blog post about AIM when it first came out in Oracle Database 18c. Oracle Database 21c 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 21c 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 21c 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.
This is just one of several features that we've introduced for Database In-Memory in Oracle Database 21c, but I think it is the most exciting one. I will describe the other features in future blog posts, but I think AIM offers the ability to make Database In-Memory a fully automatic performance feature.
There are plenty of other new features in Oracle Database 21c to learn about as well.
Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.