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:
Heat Map PL/SQL packages:
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.
Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.