This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,699 Users
  • 2,269,776 Discussions
  • 7,916,823 Comments

Discussions

MOVE HEAT MAP SEGMENT FROM SYSAUX TO ANOTHER TABLESPACE.

EricKR-Oracle
EricKR-Oracle Senior Technical Support EngineerMember Posts: 5 Employee
edited Apr 5, 2018 3:50AM in Database Ideas - Ideas

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.

EricKR-OracleBPeaslandDBASven W.Rami_robotuser11987622mmhanaoka
8 votes

Active · Last Updated

Comments

  • user11988073
    user11988073 Member Posts: 1 Blue Ribbon

    Is there an answer to this??

  • tbela
    tbela Member Posts: 9 Red Ribbon

    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
    tbela Member Posts: 9 Red Ribbon

    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...