SYSAUX tablespace shows segment HEATMAP consuming a lot of space
SYSAUX tablespace shows segment HEATMAP consuming a lot of space, but usage of HEATMAP has been disabled
You may face issues as SYSAUX tablespace consuming lot of space in Oracle database. If you encounter such situation please follow the below notes.
Symptom: Heat Map has been disabled in the database.
SQL> show parameter HEAT_MAP;
NAME TYPE VALUE
———————————— ———– ——————————
heat_map string OFF
But, SYSAUX tablespace shows segment HEATMAP consuming space.
SELECT owner, segment_name, segment_type, SUM(bytes) seg_size, COUNT(extent_id)
GROUP BY owner,segment_name , segment_type
ORDER BY seg_size DESC;
OWNER SEGMENT_NAME SEGMENT_TYPE SEG_SIZE COUNT(EXTENT_ID)
—– ————- ————- ——– —————-
SYS HEATMAP SYSTEM STATISTICS 520093696 62
Heat Map is an Oracle Database 12c feature that stores system-generated data usage statistics at the row and segment levels – information that can be used to automate the compression and movement of data. Heat Map, once enabled, automatically collects segment and row level statistics that can be used to define compression and storage policies which will be automatically maintained throughout the lifecycle of the data.
Other Terms: oracle 12c, Oracle heatmap, Oracle heat_map, Oracle sysaux Tablespace, space
Reason and Prerequisites
Oracle Database – Enterprise Edition – Version 220.127.116.11 and later
Using space for HEATMAP table in SYSAUX is an expected behaviour in oracle 12c.
Even though heat map is off, the default autotask behavior that is defined on every database runs dbms_heat_map.auto_advisor_heatmap_job as part of the automatic segment adviser, which is invoked in the predefined maintenance windows. This information is kept in system segments and used to feed views such as BA_HEATMAP_TOP_TABLESPACES, DBA_HEATMAP_TOP_OBJECTS, etc. That is why HEATMAP segment is seen even though heat_map=OFF.
If the size of the HEATMAP segment is large due to the workload of the database (i.e. if database has many ‘hot’ objects), then below command can be executed to help get the stat segment dropped :
ALTER SYSTEM SET “_drop_stat_segment” =1;
After this, the HEATMAP segment size is gradually reduced.