ADO Storage Tiering with customized function: Example

An exciting feature of 12c is Automatic Data Optimization (ADO). It can automate Compression Tiering and Storage Tiering. The documentation mentions that Storage Tiering can be based on a customized function, but there is no example for it. Therefore this posting.

The name of the policy is provided automatically. ADO Tiering requires the following initialization parameter setting, regardless that the customized policy is not based on access respectively modification frequency of the partition p:

SQL> alter system set heat_map=on;
System altered.

Attention: Setting this already means you need the Advanced Compression Option! And to me it seems funny that this parameter can be modified on the session level, but that’s how it is. I want to cause now the Storage Tiering, so I update the flag to 1, but that would lead to automatic action only when the next Auto Task maintenance window opens. Therefore I trigger the ADO Tiering manually.

It is possible to add a Storage Tiering policy to a non-partitioned table, but that is probably not a good idea. My observation at least with the current 12.1.0.1 is that open transactions on the non-partitioned table will cause the Storage Tiering to fail silently. Usually, the Storage Tiering will be a unique action on the segment. For testing purposes, though, you may want to trigger it multiple times. But the policy gets silently disabled after the first tiering although dba_ilmpolicies shows it as enabled: