sysaux中HEATMAP 对象较大

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

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0


SQL> col owner for a30
SQL> col  segment_name for a30
SQL> col segment_type for a20
set lines 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;SQL> SQL>   2    3    4    5  

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

检查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）

---默认值
SQL> col name for a52
SQL> col value for a24
SQL> 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%')
SQL> SQL>   2    3    4    5    6    7  order by name
/  8  
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