HEATMAP – Database SOS

标题：sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY

一个老生常谈的问题遇到oracle sysaux表空间不足,通过awrinfo 分析,初步判断很可能是ash相关表没有正常清理分区导致

进一步分析确认异常对象

通过分析基本上可以确认是由于WRH$_ACTIVE_SESSION_HISTORY没有创建新分析,数据未被及时清理.根据官方WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged (Doc ID 387914.1)处理建议
alter session set “_swrf_test_action” = 72;
增加新分区,过一段时间之后,自动清理WRH$_ACTIVE_SESSION_HISTORY表历史数据.

对于sysaux表空间不足的情况,还遇到过类似情况:
sysaux中HEATMAP 对象较大
WRI$_ADV_OBJECTS表过大,导致SYSAUX表空间不足

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

标签归档：HEATMAP