Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow

AWR数据增长较快，导致sysaux表空间使用较高

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> select f.tablespace_name,   2         a.total,   3         f.free,   4         round((f.free / a.total) * 100) "% Free"   5    from (select tablespace_name, sum(bytes / (1024 * 1024)) total   6            from dba_data_files   7           group by tablespace_name) a,   8         (select tablespace_name, round(sum(bytes / (1024 * 1024))) free   9            from dba_free_space  10           group by tablespace_name) f  11   WHERE a.tablespace_name = f.tablespace_name(+)  12   order by "% Free"  13  /   TABLESPACE_NAME                     TOTAL       FREE     % Free ------------------------------ ---------- ---------- ---------- UNDOTBS1                             5185          6          0 SYSAUX                               8660        413          5 USERS                                   5          4         80 SYSTEM                               4640       3798         82 UNDOTBS2                              365        335         92 X_DATA                               1000        990         99 X_INDEX                              1000        986         99

 

查看是哪些数据库对象占用了sysaux表空间

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL> select *   2    from (select segment_name,   3                 PARTITION_NAME,   4                 segment_type,   5                 bytes / 1024 / 1024   6            from dba_segments   7           where tablespace_name = 'SYSAUX'   8           order by 4 desc )   9   where rownum <= 20;   SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       BYTES/1024/1024 ------------------------------ ------------------------------ ------------------ --------------- WRH$_EVENT_HISTOGRAM_PK        WRH$_EVENT__1402125233_0       INDEX PARTITION                736 WRH$_EVENT_HISTOGRAM           WRH$_EVENT__1402125233_0       TABLE PARTITION                658 WRH$_LATCH                     WRH$_LATCH_1402125233_0        TABLE PARTITION                588 WRH$_SQLSTAT                   WRH$_SQLSTA_1402125233_0       TABLE PARTITION                560 WRH$_SYSSTAT_PK                WRH$_SYSSTA_1402125233_0       INDEX PARTITION                504 WRH$_LATCH_PK                  WRH$_LATCH_1402125233_0        INDEX PARTITION                440 WRH$_SYSSTAT                   WRH$_SYSSTA_1402125233_0       TABLE PARTITION                368 WRH$_PARAMETER_PK              WRH$_PARAME_1402125233_0       INDEX PARTITION                336 WRH$_PARAMETER                 WRH$_PARAME_1402125233_0       TABLE PARTITION                280 WRH$_LATCH_MISSES_SUMMARY_PK   WRH$_LATCH__1402125233_0       INDEX PARTITION                280 WRH$_SEG_STAT                  WRH$_SEG_ST_1402125233_0       TABLE PARTITION                256 WRH$_SYSTEM_EVENT              WRH$_SYSTEM_1402125233_0       TABLE PARTITION                232 WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH__1402125233_0       TABLE PARTITION                224 WRH$_SYSTEM_EVENT_PK           WRH$_SYSTEM_1402125233_0       INDEX PARTITION                216 WRH$_SQLSTAT_PK                WRH$_SQLSTA_1402125233_0       INDEX PARTITION                160 WRH$_SERVICE_STAT_PK           WRH$_SERVIC_1402125233_0       INDEX PARTITION                152 WRH$_DLM_MISC                  WRH$_DLM_MI_1402125233_0       TABLE PARTITION                120 WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_1402125233_0       TABLE PARTITION                120 WRH$_SQLSTAT_INDEX             WRH$_SQLSTA_1402125233_0       INDEX PARTITION                112 WRH$_SEG_STAT_PK               WRH$_SEG_ST_1402125233_0       INDEX PARTITION                104

 

查看awr快照保留期限。设置的期限是8天，但是真正保留了258天

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select sysdate - a.sample_time ash,   2         sysdate - s.begin_interval_time snap,   3         c.RETENTION   4    from sys.wrm$_wr_control c,   5         (select db.dbid, min(w.sample_time) sample_time   6            from sys.v_$database db, sys.Wrh$_active_session_history w   7           where w.dbid = db.dbid   8           group by db.dbid) a,   9         (select db.dbid, min(r.begin_interval_time) begin_interval_time  10            from sys.v_$database db, sys.wrm$_snapshot r  11           where r.dbid = db.dbid  12           group by db.dbid) s  13   where a.dbid = s.dbid  14     and c.dbid = a.dbid;   ASH                                      SNAP                                     RETENTION ---------------------------------------- ---------------------------------------- ------------------------------------- +000000258 08:55:03.824                  +000000008 08:55:30.192                  +00008 00:00:00.0

 

也可以通过dba_hist_snapshot和WRH$_EVENT_HISTOGRAM进行比较

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;   MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------         5489         6350   SQL> select min(snap_id),MAX(snap_id) from WRH$_EVENT_HISTOGRAM;   MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------          168         6350   SQL>

 

通过MOS查看，这是数据库的一个bug：

　　Document 14084247.8 Bug 14084247 - Failed AWR purge can lead to continued SYSAUX space use

因为purge操作失败，split分区表没有成功。所以导致分区大小不断增加，进而影响性能（purge job超时，导致AWR过期数据没有被清空）。

 

但是，即使是安装了补丁后，也不是立即生效，可能需要等一段时间才能生效。

安装了补丁后，还要按照以下步骤执行purge操作：

1) drop expired partitions (DDL)

2) purge by callback (anything can happen here, but mostly no-ops or regular DELETE operations)

3) purge regular non-partitioned tables (i.e, DELETE)

4) split partitions (DDL)