sysaux中HEATMAP 对象较大

在一次数据检查中,发现HEATMAP对象比较大

SQL>  
col BANNER for a30
col BANNER_FULL for a30
col BANNER_LEGACY for a30
select * from v$version;

BANNER                         BANNER_FULL                    BANNER_LEGACY                      CON_ID
------------------------------ ------------------------------ ------------------------------ ----------
Oracle Database 19c Enterprise Oracle Database 19c Enterprise Oracle Database 19c Enterprise          0
 Edition Release 19.0.0.0.0 -   Edition Release 19.0.0.0.0 -   Edition Release 19.0.0.0.0 -
Production                     Production                     Production
                               Version 19.28.0.0.0

col owner for a30
col  segment_name for a30
col segment_type for a20
ines 150
SELECT owner, segment_name, segment_type, sum(bytes/1024/1024/1024)
FROM dba_extents
WHERE tablespace_name='SYSAUX'
and segment_name='HEATMAP'
group by owner, segment_name, segment_type;

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE         SUM(BYTES/1024/1024/1024)
------------------------------ ------------------------------ -------------------- -------------------------
SYS                            HEATMAP                        SYSTEM STATISTICS                   4.62402344

检查Heat Map特性为关闭

SQL> show parameter HEAT_MAP;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF

查询mos发现相关Bug 20678613 – HEATMAP SIZE IS 500 MB, THOUGH HEAT_MAP IS TURNED OFF.通过设置_drop_stat_segment为1来自动减小HEATMAP（其默认值为0）

---默认值
col name for a52
col value for a24
col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
   order by name
/

Enter value for param: _drop_stat_segment
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_drop_stat_segment%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ----------------------------
_drop_stat_segment                                   0                        drop ilm statistics segment

---设置值
ALTER SYSTEM SET "_drop_stat_segment" =1;

如果需要也可以人工进行删除
exec dbms_space_admin.heat_map_segment_drop;

参见:HEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1)
Bug 24704547 : SYS.HEATMAP GROWS IN SIZE IN 12C UNDER SYSAUX TBS