Automatic In-Memory (AIM) was introduced as part of Database In-Memory in Oracle Database 18c and allows automatic management of the contents of the IM column store. This feature builds on the underlying data of Heat Map, a feature introduced with Automatic Data Optimization (ADO) in Oracle Database 12.1. Support for Database In-Memory was added in Oracle Database 12.2 and now AIM takes the concepts of ADO even further.
When AIM is enabled, if the size of the objects that have been enabled for in-memory exceeds the size of the IM column store then the least active populated object(s) will be evicted to make room for more frequently accessed in-memory enabled objects. The really cool part of this is that AIM uses access tracking, column and other relevant statistics to determine which are the least active objects. No guessing required!
AIM requires that the initialization parameter INMEMORY_AUTOMATIC_LEVEL be set. There are three values for the parameter:
• OFF (default)
Key to the function of AIM is the concept of "memory pressure". AIM will only kick in when there is not enough space to populate the object. Prior to AIM, it is possible to run out of IM column store space when populating an object. The great thing about Database In-Memory is that even if an object is only partially populated, queries that access the object in the IM column store won't fail. They will access the portion of data that is populated and then get the rest of the column data from the row-store. Obviously, this is not going to provide the best performance and that's where AIM kicks in.
With AIM enabled that object can be fully populated because one or more objects with the least activity will be evicted to make room. Let's take a look at AIM in action. First let's verify that we have enabled AIM:
Next let's look at the tables involved in our SSB schema with INMEMORY status and priority listed:
Here is the current population of objects in the IM column store:
And the total space consumed in the IM column store:
Now we will access TABLE3 which is enabled for INMEMORY with a priority of NONE:
This results in an alert that there is not enough memory to populate the table:
Let's take a look at V$IM_SEGMENTS and see how much of TABLE3 was populated:
Surprise! We see that TABLE3 has been fully populated, but now TABLE1 is missing. It is no longer populated in the IM column store. Let's see if we can figure out what happened.
With the introduction of AIM two new views have been added. These views provide information about the tasks performed by AIM and the details associated with each task. The following shows the DBA_INMEMORY_AIMTASKS view. This view provides information about AIM management tasks:
The task that we're interested in is the last one, task_id 192.We can take a look at this task's details by querying the view DBA_INMEMORY_AIMTASKDETAILS:
You can see that TABLE1 was evicted as part of the AIM task which freed up room for TABLE3. The LOW value for parameter INMEMORY_AUTOMATIC_LEVEL specifies that when the IM column store is under memory pressure that the least active segment(s) will be evicted. As I said earlier, the determination is made based on access tracking, column and other relevant statistics, and since we never accessed TABLE1 in this example it was obviously a candidate to be evicted. Only objects with an INMEMORY PRIORITY of NONE are eligible to be evicted so if you have objects that you want to remain populated just set a priority for them.
AIM provides a powerful tool to automate the population of objects in the IM column store that will get the most benefit from Database In-Memory. Obviously if you have enough memory in the IM column store for all of your in-memory candidate objects then you don't need AIM. However, for most customer this is not the case and AIM makes great sense for those customers. The other thing to remember is that AIM only takes action when there are more objects enabled for INMEMORY than can fit in the IM column store, and the default is that the feature is disabled. You have to explicitly enable it to take advantage of AIM.