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)
FROM dba_extents
WHERE tablespace_name=’SYSAUX’
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 12.1.0.2 and later

Using space for HEATMAP table in SYSAUX is an expected behaviour in oracle 12c.

Solution

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.

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *