Automatic Data Optimization (ADO) and Heat Map were first introduced in Oracle Database 12.1. Both Gregg Christman (@aco_gregg) and I have blogged about Heat Map and ADO as far back as 2014 when the features first appeared. That seems like ancient history now!
In Oracle Database 12.2 we introduced support for ADO and Heat Map with Database In-Memory. The same basic infrastructure was used for both versions of ADO, that is on-disk segments and in-memory segments. With in-memory though, we were more concerned with managing the column store rather than strictly compressing segments or tiering them to secondary storage.
ADO for Database In-Memory introduced three new policies that only apply to in-memory enabled objects:
Each of these policies acts on one segment and only runs once (i.e. they are segment level policies).
ADO for Database In-Memory still requires that the initialization parameter HEAT_MAP be enabled (i.e. set to ON), that Database In-Memory is enabled (i.e. INMEMORY_SIZE >= 100MB ) and that the COMPATIBLE parameter be set to 12.2.0 or higher.
Let's take a look at an example. We're going to cheat a bit and change how often ADO evaluates policies. By default, it evaluates policies on a daily basis during the maintenance window, but just for this example we're going to change it so that it evaluates policies in seconds. This will allow us to see the effects of our policies without having to wait for a full day. You can determine whether policies are being evaluated in days or seconds (and seconds should really only be used for testing purposes) by querying the view DBA_ILMPARAMETERS and looking at the value for POLICY_TIME (i.e. 0 equals days and 1 equals seconds). As part of the DBMS_ILM_ADMIN package there is the constant ILM_POLICY_IN_SECONDS that can be passed to the CUSTOMIZE_ILM procedure to change policy evaluation from days to seconds. This allows us to set the policy evaluation to seconds with the following:
With that changed we can create a policy that will evict the SUPPLIER table after 14 days (seconds in our example) of no access. Does this make sense? Well maybe not for the SUPPLIER table since it is an important dimension table in our SSB schema, but in practice it may be very appropriate to evict a table from the IM column store after 14 days of not being accessed. Just think, if your critical business cycle is one week then having objects "linger" in the column store occupying valuable DRAM memory is a luxury that you may not be able to afford. Especially if the object hasn't been accessed in two weeks.
But wait, let's get a little more sophisticated. First, we'll compress the object to a higher compression level once it's no longer being modified and then we'll evict it once it is no longer being accessed. This will require two policies, one for compression to a higher level and then one to evict it when it has not been accessed.
Initially our IM column store is populated with the following objects:
To create a compression policy that will compress the SUPPLIER table to a higher compression level (i.e. MEMCOMPRESS FOR CAPACITY HIGH) after 3 days of no modification we need to run the following SQL:
The policy will run automatically during the maintenance window, but let's force a run to see what the results will be:
To run the policy, I invoked the EXECUTE_ILM procedure which is part of the DBMS_ILM package. I then queried the USER_ILMEVALUATIONDETAILS view and we see that policy P1 was selected for execution. Now let's take a look at the results of running the policy:
We see that the INMEMORY_SIZE for the SUPPLIER table has gone from 164,429,824 bytes (see the initial size in the query of initially populated objects at the beginning of the post) to just 55,377,920 bytes and that the INMEMORY_COMPRESS attribute has been changed from "FOR QUERY LOW" to "FOR CAPACITY HIGH".
Next, I will create a policy to evict the object when it has not been accessed in 14 days:
Running this SQL results in a second policy called P2:
Notice that our first policy, P1, has been disabled (i.e. ENABLED = NO) since it has already been run. Recall that segment level policies only run once. Now let's run our second policy and see what happens. Remember that we're invoking the policies manually, but normally these policies would be run automatically during the maintenance window.
You can see that policy P2 has been selected for execution. Let's take a look at the results:
We can see that the SUPPLIER table has been evicted from the IM column store since it is no longer populated and that the INMEMORY attribute has been set to DISABLED. What really happens behind the scene is that the table is altered by the policy invocation and then the normal Database In-Memory processing occurs to remove the SUPPLIER table from in-memory, just like any ALTER TABLE … NO INMEMORY command would.
And there you have it. An example of using ADO policies on an in-memory enabled segment to manage that object's in-memory life cycle automatically based on Heat Map heuristics. If you're interested in more possibilities for automating the contents of the IM column store then check out our post on Automatic In-Memory (AIM) which is available in Oracle Database 18c.