MOVE HEAT MAP SEGMENT FROM SYSAUX TO ANOTHER TABLESPACE.

EricKR-Oracle

Heat Map is an Oracle Database 12c feature that stores system-generated data usage statistics at the row and segment levels.

However, Some of customers asked me that is there a way to move historical heatmap segment from sysaux to another tablespace to reduce the big sysaux size.

The following is a monitoring data of the sysaux size everyday. We can see the size increased as "GB" per day.

LOG_DT TS_NAME TOTL_SIZE_GB FREE_SIZE_GB USED_SIZE_GB USED_PCT
20180301 SYSAUX 80  17.2  62.8 78.54
20180302 SYSAUX 80  16.7  63.3 79.11
20180303 SYSAUX 80  16.2  63.8 79.79
20180304 SYSAUX 80  17.0  62.9 78.71
20180305 SYSAUX 80  16.6  63.4 79.26
20180306 SYSAUX 80  16.1  63.8 79.86
20180307 SYSAUX 80  17.5  62.5 78.12 <------  HEAT_MAP = ON
20180308 SYSAUX 80  12.0  67.9 84.95
20180309 SYSAUX 80   1.4  78.5 98.21
20180310 SYSAUX 80    .7  79.3 99.07
20180311 SYSAUX 80    .5  79.5 99.43
20180312 SYSAUX 80   1.7  78.2 97.85
20180313 SYSAUX 80    .6  79.4 99.26
20180314 SYSAUX 80    .5  79.5 99.43
20180315 SYSAUX 110  27.9  82.0 74.61 <----- add 30G size of Datafile of sysaux and then set HEAT_MAP = OFF
20180316 SYSAUX 110  28.2  81.8 74.38
20180317 SYSAUX 110  27.8  82.2 74.77
20180318 SYSAUX 110  27.4  82.6 75.09
20180319 SYSAUX 110  28.6  81.3 73.96
20180320 SYSAUX 110  28.2  81.7 74.34

Also, the implementation of moving heatmap segment would be benefit to whom want to shrink sysaux datafile but heatmap is at the end of datafile.

user11988073

Is there an answer to this??

tbela

Hi,

There is an interesting feature/bug which could help on you (I haven't tested it yet), but my problem is same.

HEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1)

In my db (19.14) heatmap is off (we have never used it), but the heatmap system statistic segments size is near 5GB.

Good luck!

tbela

I have run the following procedure:

select owner,segment_name,segment_type, sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX  group by owner,segment_name,segment_type  order by 4 desc;

-- heatmap size was an 5GB segment object type: system statistics 

This was our settings:

 show parameter HEAT_MAP; 

 --OFF

 It will clean heatmap after run this command:

 ALTER SYSTEM SET "_drop_stat_segment"=1 scope=memory;

Or you have to install a patch 22624709.

https://rayfoxblog.wordpress.com/category/oracle-technical/ or Doc ID 2024036.1

I hope I could help a little bit...