This content is part of the Essential Guide: Guide to Oracle 12c database features and upgrades

Oracle 12c's heat map tool and data optimization can improve storage

The heat map tool and Automatic Data Optimization features in Oracle 12c should help Oracle database shops improve data storage and compression.

Oracle Database 12c includes Heat Map and Automatic Data Optimization, two new features to better manage data storage and compression. The Heat Map tool tracks data use at row and segment levels to establish patterns. Automatic Data Optimization takes this information and automatically moves or compresses data based on user-defined policies.

The Heat Map tool and Automatic Data Optimization (ADO) feature are part of Oracle's strategy for managing enterprise data from cradle to grave. Part of that strategy includes storage tiering, which stores data on different tiers depending on access and performance needs.

Heat Map and ADO make storage tiering possible so that the most active data is stored on the highest-performing tier and the least active data is on a less-critical tier. By using Heat Map and ADO, you can automate the often-cumbersome process of moving the right data to the right place at the right time without impacting operations.

Oracle 12c Heat Map tool

When gathering data use, Heat Map tracks write and read times at the row level and aggregates these statistics up to the block level. At the segment level, Heat Map also tracks write and read times, along with index lookup and full table scan times.

You can enable the Heat Map tool at the system or session level. However, you must enable it at the system level to support ADO. Once enabled, Heat Map collects usage information and makes it available to ADO. You can also access the information through a set of built-in views that provide details about the usage data collected at the segment level. These views include:

  • V$HEAT_MAP_SEGMENT: Displays real-time access information, including object name, object number and container ID.
  • DBA_HEAT_MAP_SEGMENT: Displays the latest segment access time for all segments visible to the specified user. (Supports ALL or USER in place of DBA.)
  • DBA_HEAT_MAP_SEG_HISTOGRAM: Displays access information for all segments visible to the specific user. (Supports ALL or USER in place of DBA.)
  • DBA_HEATMAP_TOP_OBJECTS: Displays access information for the top 1,000 objects.
  • DBA_HEATMAP_TOP_TABLESPACES: Displays access information for the top 100 tablespaces.

To dive deeper into the information that Heat Map creates, you can use the DBMS_HEAT_MAP PL/SQL package, which provides an interface to the Heat Map data at various levels, including blocks, extents, segments, objects and tablespaces. The package contains a set of procedures that give you details about how data is accessed in your database.

Automatic Data Optimization

Although it can be useful to access the Heat Map tool manually, the more important function is its ability to automatically supply ADO with information it needs to move and compress data. Automatic Data Optimization provides the mechanisms necessary for applying the Heat Map information to the stored data.

At the heart of ADO is a set of user-defined policies that specify your storage tiering preferences. Oracle Database evaluates the policies periodically to determine whether any data should be moved or compressed and when to do it. If an action needs to be taken, ADO carries it out automatically during the database maintenance window. A database administrator can also execute the policies manually.

You define one or more ADO policies on the table that contain the data you want to manage. A policy can specify whether to implement compression or move the data.

When configuring a compression policy, you must choose the compression type and whether to implement compression at the row or segment level, and identify when the compression is to be carried out. For example, suppose you're configuring a policy on a table that contains sales data. For the compression type, you choose advanced row compression. You then determine compression should occur at the segment level. Finally, you specify that the data should be compressed after 60 days without any modifications.

Automatic Data Optimization policies support a number of compression types:

  • COMPRESS: Enables basic table compression. This is the same as specifying ROW STORE COMPRESS BASIC.
  • ROW STORE COMPRESS BASIC: Enables basic table compression.
  • ROW STORE COMPRESS ADVANCED: Enables advanced row compression. Oracle Database compresses the data during data manipulation language, or DML, operations. This is recommended for online transaction processing applications.
  • COLUMNSTORE COMPRESS FOR QUERY: Enables Hybrid Columnar Compression, in which data is transformed into a column-oriented format and then compressed. This compression type is useful for data warehouses.
  • COLUMNSTORE COMPRESS FOR ARCHIVE: Enables Hybrid Columnar Compression, but the data is compressed at a higher level than COLUMNSTORE COMPRESS FOR QUERY does. This compression type is useful when archiving data.
  • NOCOMPRESS: Disables table compression. This is the default.

When configuring a policy to move data, you must specify the destination tablespace where you want the data moved.

You can define an ADO policy within a CREATE TABLE statement or ALTER TABLE statement. Be sure to view Oracle Database documentation for more specifics about the mechanics of adding a policy to a table, as well as disabling, enabling, deleting and viewing ADO policies.

Oracle also provides a number of data dictionary views that return details about ADO policies. In addition, Oracle offers two PL/SQL packages for carrying out advanced management tasks on ADO policies. The DBMS_ILM package provides tools for evaluating ADO policy operations and managing ADO-related tasks. The DBMS_ILM_ADMIN package provides tools for customizing ADO policy execution. Once again, be sure to refer to Oracle Database documentation.

About the author
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

Dig Deeper on Oracle data warehousing