Oracle 12c Database Heat Maps and Automatic Data Optimization

Today’s economy is driven by information. So an organization’s data is essentially the main source of all information taking the highest precedence. Not only is the data critical but is also growing at an exceptional pace. Managing this huge amount of data is thus becoming the biggest challenge for any organization. DBA’s continuously face this pressure of properly managing the data and at the same time ensuring that the proper access to the data. This part of IT strategy which deals with the life cycle of information, is known as the Information Life Cycle Management (ILM). In this article we will look at Oracle 12c Database Heat Maps and Automatic Data Optimization, which are designed to automate ILM implementation.


At the heart of the ILM strategy, lies the importance of categorizing the data according to its importance. There is data which is very crucial for company and then there is data which is important but not accessed frequently. So normally the DBA’s move this type of data which is not accessed frequently onto a slower, less expensive storage tier. On the other hand data accessed frequently is moved onto a faster and more expensive storage tier. This helps in both ways by utilizing faster storage for better system performance (for data accessed often) and keeping the storage costs in check. Partitioning is the primary mechanism used to manage this. DBA’s create partitions and separate the data into these partitions, moving the partitions which need to placed on faster storage while the older partitions are moved onto slower storage. Further the data on slower tiers are also compressed to save further space.


Both Partitions and Compression worked great but there was a problem with this. There was a lot of manual work required by the DBA. The DBA had to check every now and then which data is movabale to a slower storage tier and which can be compressed and at which ratio. Then they had to perform these operations by themselves.

The two new features in Oracle database 12c, Heat Map and Automatic Data Optimization (ADO) automate most of these manual tasks, involved in implementing the ILM strategy.

Heat Map


Heat Map addresses the first issue, identifying and managing the data which should be moved or compressed. It keeps track of data being accessed at the segment and row level, dividing it into three different categories as below. The Heat Map can be generated against the table, partition or row and can provide specific information.


Hot: Data has been actively accessed and the last activity date is less than a week.
Warm: Data has been moderately accessed and the last activity data is less greater than a week but less than an year.
Cold: Data has not been actively accessed and the last activity date is more than an year ago.

Enabling Heat Map

The HEAT_MAP initialization parameter can be used to enable the Heat Map feature. Heat Map can be enabled at session and system level. The default value for HEAT_MAP is OFF.


SQL> alter session set HEAT_MAP=ON;
Session altered.

SQL> alter system set HEAT_MAP=ON;
System altered.


The same HEAT_MAP parameter is also used to enable Automatic Data Optimization (ADO). However HEAT_MAP should be enabled at the system level in order for ADO to be enabled. Once enabled all data will be tracked, except for the objects residing in the SYSTEM and SYSAUX tablespaces.

Viewing Heat Map Information

There are five views which show the segment level Heat Map information. The V$HEAT_MAP_SEGMENT dynamic view shows the run time information about object access. The DBA_HEAT_MAP_SEGMENT and DBA_HEAT_MAP_SEQ_HISTOGRAM show the segment level access information with some slight differences. The first one shows the timing information like when an object was accessed and second one shows the operation information like Read and write operations. The first one will show when a Full table scan occurred and second one will also tell you if a certain access was Full table scan or not.


The last two views are very useful as they will provide the DBAs an holistic overview of what objects and tablespaces are being accessed the most. DBA_HEATMAP_TOP_OBJECTS will list the top 1000 objects which are being accessed most frequently. And the DBA_HEATMAP_TOP_TABLESPACES will list the top 100 tablespaces which contain very hot data.

Managing Heat Map Data

The views above provide segment level information. If you want to dig deep into individual segment, extents, rows etc. then you can use the DBMS_HEAT_MAP PL/SQL package. The package has two sets of procedures which you can use. The first set deals with objects. You can use these procedure to get extra insight into different objects, segments, extents etc. The other set of procedures allow you to view more detailed information about tablespaces.

Automatic Data Optimization (ADO)

Automatic Data Optimization addresses the second issue related to the legacy approach of implementing ILM strategies. Once you have identified the objects or tablespaces which are hotter and which are colder you can now define policies using ADO to move or compress this automatically. You define policies and attach them to objects and when policy condition is met then action specified in policy description will be performed automatically. Typical action would be to move data or part of data to a different storage tier or to use different compression.


To enable ADO and start defining policies you need to set the HEAT_MAP parameter to value ON on system level.

Managing ADO Policies

You can specify, enable, disable and delete ADO policies at segment and row level. Essentially any ADO policy specified will either compress the row/segment or it will move the target row/segment to new storage tier. When an ADO policy performs these steps can be controlled via condition which is specified when an ADO policy is specified. The condition can be simple pre-defined by Oracle or it can be custom and complex to suite your specific requirements.


To specify an ADO policy you can use the ILM clause of Create table or Alter table commands. You can specify an ADO policy at the time of table creation and add more using the Alter table command. The policies are named automatically following the P1, P2, P3… pattern.

SQL> alter table sales ilm add policy
2 compress for archive high segment
3 after 12 months of no access;
Table altered.


The above command has added a new ILM policy to an already exiting SALES table in SH schema. The first line tells Oracle that an ILM policy is required to be added. The second line tells what this new policy will do. It will high Compress the table. The keyword segment tells that policy is at segment level. The last line is the condition which will trigger the ILM policy when met. The policy will be triggered if SALES table has no access in past 12 months.


There are two other types of compression which can be provided. Below is the brief description of all three compression choices.


• COMPRESS ADVANCED maps to standard compression for indexes and low compression for LOBs.
• COMPRESS FOR QUERY LOW/QUERY HIGH maps to standard compression for indexes and medium for LOBs.
• COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH maps to standard compression for indexes and high for LOBs.


The policy can be at segment or row level policy. In case you want to specify ROW level policy the “ROW” keyword will be used instead of “SEGMENT” in above command.


The condition can be pre-defined one like above. Normally these are based on Modification time or Access time. The time can be provided in Days, Months, Years etc.
The condition can also be custom code. You can create a custom PL/SQL function and attach it to ILM policy using the following syntax.


ALTER TABLE sales ILM ADD POLICY COMPRESS ADVANCED SEGMENT
ON <function_name>;

SQL> alter table sales ilm add policy
2 tier to users;
Table altered.


The above query added another ILM policy to SALES table. This policy will move SALES table to USERS tablespace. We assume that USERS tablespace has a different storage tier then to that of current tablespace. You can also use the Alter table command to disable/enable or delete any existing policies. First lets check what ILM policies are currently in place.


SQL> select policy_name,policy_type,enabled from user_ilmpolicies;
POLICY_NAME POLICY_TYPE ENABLED
-------------------- ------------- -------
P1 DATA MOVEMENT YES
P21 DATA MOVEMENT YES


You can disable/enable or delete them one by one or all of them at once.


SQL> alter table sales ilm disable policy P1;
Table altered.

SQL> alter table sales ilm enable policy P1;
Table altered.

SQL> alter table sales ilm disable_all;
Table altered.

SQL> alter table sales ilm delete policy P1;
Table altered.

SQL> alter table sales ilm delete_all;
Table altered.

Advance Policy Management using PL/SQL functions

The examples above provide some basic operations which can be performed as part of table creation and alteration. For more advance management of ADO policies Oracle provides two new PL/SQL packages. The packages are:


• DBMS_ILM
• DBMS_ILM_ADMIN


The procedure inside these packages provide many powerful capabilities to control the movement and compression of data. For example the EXECUTE_ILM() procedure lets you immediately execute the desired ILM policy. The procedure creates a Scheduler job which is immediately run if there is no other ILM policy in queue.

Monitoring Policies

There are many data dictionary views which provide useful information about the current ILM policies and when they are scheduled to run. Here is a list of most important ones.


• DBA/USER_ILMPOLICIES – ILM policies in the database.
• DBA/USER_ILMRESULTS – Data movement related jobs for ADO policies.
• DBA_ILMPARAMETERS – Parameters related to ILM policies.
• DBA/USER_ILMDATAMOVEMENTPOLICIES – All policies related to data movement.
• DBA/USER_ILMTASKS – All tasks created as resulted of EXECUTE_ILM() procedure call.
• DBA/USER_ILMEVALUATIONDETAILS – Policies created for some particular task.
• DBA_USER_ILMOBJECTS – Relationships between objects and ILM policies. Objects can inherit policies from parent objects like for example tablespace in which they are created. This view displays information about those relations as well.

Restrictions on Heat Map and ADO

The biggest concern for many would be that heat maps and ADO are not yet available for new Multitenant architecture. Means if you have CDB/PDB architecture for your database deployment then heat maps and ADO will not be supported. They are only supported for databases running using old architecture. However database version should be at least 12.0. Here is a list of all restrictions on heat maps and ADO.


• Heat Maps and ADO are not supported in multitenant container database (CDB).
• Row Level policies are not supported for temporal validity.
• Row level policies are supported for in-database archiving.
• Custom Policies (User defined functions) are not supported if policies are default to tablespace level.
• When using storage tier policies the ADO does not check for space in target tablespace.
• ADO is not supported on tables with objects types or materialized views.
• ADO concurrency – the number of policies active at the same time – is dependent on concurrency of Oracle Scheduler. If ADO job fails more then twice then it is disabled and must be run manually.
• Policies for ADO are only run in maintenance window. ADO policies for offline index rebuild can be run outside maintenance window though.
• ADO also has some restrictions on moving tables and table partitions.


Read more on Oracle 12c Articles.

Leave a Reply

Your email address will not be published.