Oracle Automatic Data Optimisation 12c

One of the seriously cool new features in the Oracle database 12c is the automated lifecycle management option. I freely admit you can get the same with other storage vendors (EMC FAST VP is the one I know first hand, but 3PAR and others have similar technology) but this is not really an option for the Oracle Database and Exadata. A quick word of warning: I have not even opened the licensing guide for 12c yet, this may well be a cost option so as always please ensure you are appropriately licensed before using this feature.

Why this post

The official documentation-VLDB and Partitioning Guide, chapter 5-is a bit lacking, as is the SQL Language Reference, hence this post.

Oracle allows you to automate movement of “cold” data from tier 1 storage to lower tiers completely automatic based on so-called ILM policies. These policies can be assigned to a table at creation time or alternatively retrofitted. It is possible to define the policies on the row, segment or a group. Two distinct policies are possible: either to compress the data or to move it to a different tablespace.


Compression is simple and the documentation is adequate. Consider this example:

SQL> alter table t1
  2  ilm add policy compress basic segment
  3  after 3 months of no modification;

Table altered

Table t1 is of course range partitioned on a date-the only sensible use case for ILM in my opinion anyway. If you are lucky enough to be on Exadata you can use the ILM policy to request Hybrid Columnar Compression too by the way. At the time of writing I could request “compress for archive high” on my laptop as well but that of course is a bug and will lead to problems later. The policy states that a segment which isn’t modified in 3 months is to be compressed with BASIC compression.

Where to find information about ILM policies?

Dictionary views related to the ILM management all start with DBA_ILM:


If you get the DDL for a table/partition using DBMS_METADATA.GET_DDL you will also see the ILM policies.

Heat Map Tracking

NOTE: for the ADO option to work you need to set heat_map=ON system wide. Be careful: I haven’t tested if there is an overhead doing so. Once the heat map tracking is enabled you can query v$heat_map_segment and various DBA% views to report reads, writes, index lookups and full scans on the segments under surveillance. In my testing it took a little while for the segments to appear in the views so be patient and keep accessing the data. I used a few scheduler jobs to do so.


Tiering is different and here the documentation set is missing detail. Despite the syntax diagram suggesting that you can define a tiering policy based on access/modification/creation as with the compression policies you are mistaken.

By pure chance I found the Oracle By Example for tiering which is so much easier to understand than the official documentation. To enable tiering-which is completely independent of the compression-I used this sample:

SQL> alter table t1
  2  ilm add policy
  3  tier to ARCH segment;

Table altered.

And nothing happened. You cannot specify a condition for tiering yet, i.e. “ilm add policy tier to ARCH segment after …” will cause an error, which is actually expected behaviour.

Enter DBA_ILMPARAMETERS. In this view you see the TBS_PERCENT_USED and TBS_PERCENT_FREE columns which indicate when a segment is to be moved. Again-nothing to do with the access or modification time. As soon as the tablespace containing the segment is full, a job will be started moving the partition in question to the lower tier tablespace.

It is possible to mix and match the compression and tiering with tables, i.e. tables can have multiple ILM policies.

When does it happen?

This was again not too clear in the documentation but during testing it appears as if the evaluation of the ILM policies take place when the default maintenance window opens (10pm to 2 AM if memory serves me right). The ILM policies are implemented as PL/SQL blocks. You can see the actual code executed in sys.ilmresults$ in the PAYLOAD field. Limit your query to the current TASK_ID and/or JOB_NAME as in DBA_ILMEVALUATIONDETAILS.


A good walk-though, easy to follow example is on “Oracle by Example”:


6 thoughts on “Oracle Automatic Data Optimisation 12c

  1. remi

    Good article, but I have to point out Automatic Data Optimization is not a “feature” of Oracle 12c Database. It is part of the extra cost Advanced Compression Option. Worse, it is only available to Enterprise Edition customers. Those customers running Standard or Standard One Editions cannot purchase Advanced Compression Option and therefore cannot use the new Automatic Data Optimization.

  2. Pingback: Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée

  3. Guy Harrison

    Thanks for this article. I’ve been driving myself mad trying to get the TIER TO command to behave as I expected. As it is, it seems hardly useful. But can you tell me when a TBS_PERCENT_USED threshold is hit, do all segments in that tablespace move? Can row level tiering be implemented?

    1. Martin Bach Post author

      Hi Guy,

      I haven’t tested it to that extent yet. I now have a 12c quarter rack available for testing and have it on my to-do list. Watch this for an update :)


      1. Ganesh

        When the threshold is hit, the chosen segment moves (Ex. table, partition) to new tablespace. ROW level and User Defined condition (function) based – tiering is not working at this point. I’ve checked the later with Oracle support as well. Not sure if those will be made available in the future (R2?).

        Ganesh Balabharathi

Comments are closed.