Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

Heat Map – Vital to Automating Information Lifecycle Management (ILM)

Gregg Christman
Product Manager

In the next few blogs we’re going to explore the benefits of automating Information Lifecycle Management as well as the specific Oracle Database features needed to enable both database compression tiering and storage tiering.

But first, why should you care about database compression tiering and storage tiering?

An organization (or even a single application) does not access all its data equally: the most critical or frequently accessed data needs the best available performance and availability – this data is typically best suited for Tier 1 storage. But to provide this best access quality to all the data is costly, inefficient and is often architecturally impossible. Ideally, organizations need to implement storage tiering, deploying their data on different tiers of storage so that less-accessed (“colder”) data is migrated away from the costliest and fastest storage. This “colder” data remains online and available, but is stored on Tier 2 storage, which is typically lower cost and slower speed, but whose effect on the overall application performance is minimal, due to the rarity of accessing this “colder” data as this data is typically only used occasionally or for reporting purposes.

In addition to storage tiering, it is also possible to use different types of compression to suit different access patterns. For example, colder data may be compressed more at the cost of slower access. As we have briefly discussed in earlier blogs, Oracle Database provides several types of compression (Advanced Compression and HCC) for use with an organizations data as that data moves through its lifecycle - from hot to active to less active to historical/cold.

But how do organizations identify which tables/partitions, across the database, are best suited for compression or storage tiering? To do so requires that the organization have the ability to easily determine which of their tables/partitions are “hot” (the most active data) and which have “cooled” (less active historic/reporting data) down.

Heat Map -- fine grained data usage tracking.

Heat Map is a feature of the Advanced Compression option, with 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.

Enabling Heat Map

You can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP clause (parameter). For example, the following SQL statement enables Heat Map tracking for the database instance.


When Heat Map is enabled, all accesses are tracked by the in-memory activity tracking module. The default value for the HEAT_MAP initialization parameter is OFF.

So I’ve turned on Heat Map, now what?

Patience will be needed here, it will take some time (could be days or weeks) before enough activity has occurred for Heat Map to track and gather the information needed to enable organization to understand the individual usage levels of their tables and partitions.

Heat map tracking data is viewed with V$*, ALL*, DBA*, and USER* heat map views – we’ll talk more about these views in future blogs, but for now, please see the Oracle Database documentation for more information.

Now no longer solely dependent upon the collective knowledge of the organization, Heat Map enables organizations to understand where their segments are (Hot Warm or Cold) in terms of the tables/partitions movement through its lifecycle. Using the usage information being automatically gathered and managed by Heat Map, organizations can create the ILM policies (enforced by ADO) which will automate database compression tiering and storage tiering, allowing them to better utilize the organizations existing storage, better manage their storage growth and help with database performance.

But for now the database storage optimization adventure continues in the next blog, in which we will discuss how Automatic Data Optimization works with Heat Map and the vital role this feature also plays in terms of automating Information Lifecycle Management, specifically, database compression and storage tiering with Oracle Database.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.