Simon Krenger

As with all the new features that Oracle 12c brings us, we want to test them out! So one of the lesser known features in the new release is the Automatic Data Optimization with the Heat Map mechanism. Oracle describes the feature in a White Paper:

Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.

Sounds great, how can we use it? It turns out, that is relatively easy, all you have to do is to set the HEAT_MAP parameter to “ON” (source) and add an ILM policy to your table:

SQL> show parameter heat_map

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON

But when I tried to add the policy to the table, the following ORA-38342 appeared:

SQL> ALTER TABLE simontest ILM ADD POLICY TIER TO ILM_KDB2 SEGMENT;
ALTER TABLE simontest ILM ADD POLICY TIER TO ILM_KDB2 SEGMENT
*
ERROR at line 1:
ORA-38342: heat map not enabled

Huh? How can this be? I am pretty sure I changed the parameter HEAT_MAP to “ON”! It turns out, that I was testing this feature in a CDB and the documentation states that Heat Map only works in a non-CDB environment:

ADO and Heat Map are not supported with a multitenant container database (CDB)

Let’s hope that in the future, Oracle integrates this feature in the multitenant environment as well, it would be sad not to have this mechanism in a container database!