Heat Map

Heat Map was introduced in Oracle Database Release 12c as part of Automatic Data Optimization (ADO) which is a feature of Advanced Compression. When Heat Map is enabled it automatically provides data access tracking at the segment-level and data modification tracking at the segment and row level. In Oracle Database Release 12.2 ADO was enhanced to support Database In-Memory and this resulted in changes for Heat Map. The ability to track access counts was added for full scans, lookup scans and segment writes. This was an important addition for managing the In-Memory column store (IM column store).

ADO policies are based on days, months and years and evaluated on a daily basis. This makes sense for on-disk segments and long term data life cycle management. With Database In-Memory however, more information about the frequency of access was needed. This necessitated adding access counts to Heat Map to enable ranking objects by their popularity. This is particularly important for Automatic In-Memory (AIM) which was first introduced in Oracle Database 18c and can manage which objects are populated or evicted based on their usage. AIM uses the underlying Heat Map statistics and does not require that Heat Map be enabled.

Great, now that we’ve covered the background let’s take a look at how this works in practice. Heat Map is enabled by setting the initialization parameter HEAT_MAP to ON. Heat Map statistic tracking is not transactional in nature and was designed with a "best effort" approach to minimize overhead. Heat Map data is kept initially in session level memory and is flushed to segment level memory at session exit or roughly every 10 minutes. This detail is only important when querying Heat Map data in real time, but also explains why data capture is on a best effort basis.

There are several different views and PL/SQL APIs that can be used to query Heat Map information. Here’s a short summary that I showed in an Ask TOM Office Hours session that we did on Heat Map.

Heat Map based views:

• DBA_HEAT_MAP_SEGMENT (requires heat_map = on)
  •    Displays one line summary per object with timestamp information (no counts)
• _SYS_HEAT_MAP_SEG_HISTOGRAM (requires heat_map = on)
  •    Displays daily object history with count information
• V$HEAT_MAP_SEGMENT
  •    Includes counts
• _SYS_AIM_SEG_HISTOGRAM (requires inmemory_size > 0)
  •    Displays AIM eligible object history

Heat Map PL/SQL packages:

• DBMS_HEAT_MAP (requires heat_map = on)
  •   Displays one line summary per object with timestamp information
• DBMS_ILM
  •   Various flush procedures, for example: FLUSH_ALL_SEGMENTS
• Managing usage statistics
  •    DBMS_ILM_ADMIN.clear_heat_map_all
  •    DBMS_ILM_ADMIN.clear_heat_map_table

The following is a query that shows segment usage statistics:

select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
       to_char(TRACK_TIME,'MM/DD/YYYY HH24:MI') track_time,
       SEGMENT_WRITE, SEGMENT_READ, FULL_SCAN, LOOKUP_SCAN,
       N_FTS, N_LOOKUP, N_WRITE
 from sys."_SYS_HEAT_MAP_SEG_HISTOGRAM" h, dba_objects o
 where o.object_id = h.obj# and o.owner = 'SSB' and track_time >= sysdate-1
 order by track_time, OWNER, OBJECT_NAME, SUBOBJECT_NAME;

And then the output:

SEG        SEG                                                                         FULL       LOOKUP      NUM FULL NUM LOOKUP   NUM SEG
OWNER      OBJECT_NAME          SUBOBJECT_NAME  TRACK_TIME       WRITE      READ       SCAN       SCAN            SCAN       SCAN     WRITE
---------- -------------------- --------------- ---------------- ---------- ---------- ---------- ---------- --------- ---------- ---------
SSB        LINEORDER            PART_1995       06/21/2022 11:18 NO         NO         YES        NO                 1          0         0
SSB        LINEORDER            PART_1996       06/21/2022 11:18 NO         NO         YES        NO                 1          0         0
SSB        LINEORDER            PART_1997       06/21/2022 11:18 NO         NO         YES        NO                 1          0         0
SSB        LINEORDER            PART_1998       06/21/2022 11:18 NO         NO         YES        NO                 1          0         0
SSB        LINEORDER            PART_1995       06/21/2022 11:21 NO         YES        YES        NO                 4          0         0
SSB        LINEORDER            PART_1996       06/21/2022 11:21 NO         YES        YES        NO                 9          0         0
SSB        LINEORDER            PART_1997       06/21/2022 11:21 NO         YES        YES        NO                14          0         0
SSB        LINEORDER            PART_1998       06/21/2022 11:21 NO         YES        YES        NO                19          0         0

Note the tracking time and access counts. From this view we can see that the partition PART_1998 is the most active for this time period in terms of full scans.

We might also be interested in access over a longer period of time. For instance, we might want to create an ADO policy that evicts from the IM column store partitions that have not been accessed in the last 30 days. Is 30 days the right timeframe? We can use Heat Map data to find out. This time we will use one of the PL/SQL APIs to find out the access usage for the LINEORDER partitions. Here’s the query:

SELECT owner, segment_name, partition_name, segment_type, 
  min_writetime, max_writetime, 
  min_ftstime, max_ftstime 
FROM TABLE(DBMS_HEAT_MAP.OBJECT_HEAT_MAP('SSB','LINEORDER'))
WHERE segment_type = 'TABLE PARTITION';

And now the output:

OWNER      SEGMENT_NAME    PARTITION_NAME  SEGMENT_TYPE         MIN_WRITE MAX_WRITE MIN_FTSTI MAX_FTSTI
---------- --------------- --------------- -------------------- --------- --------- --------- ---------
SSB        LINEORDER       PART_1994       TABLE PARTITION      27-JUN-22 27-JUN-22 27-JUN-22 05-JUL-22
SSB        LINEORDER       PART_1995       TABLE PARTITION      27-JUN-22 27-JUN-22 27-JUN-22 05-JUL-22
SSB        LINEORDER       PART_1996       TABLE PARTITION      27-JUN-22 27-JUN-22 27-JUN-22 05-JUL-22
SSB        LINEORDER       PART_1997       TABLE PARTITION      27-JUN-22 27-JUN-22 27-JUN-22 05-JUL-22
SSB        LINEORDER       PART_1998       TABLE PARTITION      27-JUN-22 27-JUN-22 27-JUN-22 05-JUL-22

The partitions were written to on June 27th when I created them, and first accessed on the same day and then last accessed as I was creating this blog post on July 5th. Obviously my data set isn’t going to provide the kind of information an active system will, but hopefully you get the point.

The bottom line is that you can use Heat Map information independently of its use by ADO or AIM and this can be a big help in distinguishing the most active objects from the least active ones.