ANBOB™

AWR负载库的ASH信息记录在AWR的基础表wrh$_active_session_history中,wrh$_active_session_history记录的这些历史信息，可以通过dba_hist_active_sess_history视图进行聚合查询，wrh$_active_session_history是一个分区表，Oracle会自动进行数据清理。Oracle根据保留策略确定需要清除哪些,对于大型AWR表，数据是根据DBID,SNAPID存储在分区中。 如果分区中的所有记录都已过期，才会在自动清理数据时使用drop partition. 如果分区中有任何一条记录该分区都不会清除， 这是一个比较普遍的问题，当SYSAUX表空间一直增长，如超过20Gb 时需要检查一下是否有AWR 的表一直未清理，如wrh$_active_session_history， 最近遇到的较多简单记录一下处理方法.

SQL> @df

TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
 
SYSAUX                              33277      31558       1719    95% NO  |################### |  <<<<< too big
SYSTEM                              17918       2242      15676    13% NO  |###                 |
...

SQL> col topseg_segment_name head SEGMENT_NAME for a30 
SQL> col topseg_seg_owner HEAD OWNER FOR A30 
SQL>  select  tablespace_name, 
  2                   topseg_seg_owner, 
  3                   topseg_segment_name, 
  4                  --partition_name, 
  5                  segment_type, 
  6                    MB,partitions, round((RATIO_TO_REPORT (MB) OVER ()) *100,2)||'%' pct from ( 
  7          select 
  8                  tablespace_name, 
  9                  owner topseg_seg_owner, 
 10                  segment_name topseg_segment_name, 
 11                  --partition_name, 
 12                  segment_type, 
 13                  round(SUM(bytes/1048576)) MB, 
 14      case when count(*) >= 1 then count(*) else null end partitions 
 15          from dba_segments 
 16          where upper(tablespace_name) like upper('&TBSNA')  -- tablespace name   
 17    group by 
 18                  tablespace_name, 
 19                  owner, 
 20                  segment_name, 
 21                  segment_type 
 22          order by MB desc 
 23  ) 
 24  where rownum <= 50; 
Enter value for tbsna: SYSAUX 
TABLESPACE_NAME    OWNER      SEGMENT_NAME                   SEGMENT_TYPE               MB PARTITIONS PCT
------------------ ---------- ------------------------------ ------------------ ---------- ---------- -----------------------------------------
SYSAUX             SYS        WRH$_ACTIVE_SESSION_HISTORY    TABLE PARTITION          8866          2 29.46%
SYSAUX             SYS        WRH$_EVENT_HISTOGRAM_PK        INDEX PARTITION          2756          1 9.16%
SYSAUX             SYS        WRH$_EVENT_HISTOGRAM           TABLE PARTITION          1920          1 6.38%
SYSAUX             SYS        WRH$_FILESTATXS                TABLE PARTITION          1606          2 5.34%
SYSAUX             SYS        WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION          1347          2 4.48%
SYSAUX             SYS        WRH$_LATCH_MISSES_SUMMARY_PK   INDEX PARTITION          1222          2 4.06%
SYSAUX             SYS        WRH$_LATCH_MISSES_SUMMARY      TABLE PARTITION           905          2 3.01%
SYSAUX             SYS        WRH$_LATCH                     TABLE PARTITION           892          2 2.96%
SYSAUX             SYS        WRH$_SYSSTAT_PK                INDEX PARTITION           820          2 2.72%
SYSAUX             SYS        WRH$_LATCH_PK                  INDEX PARTITION           768          2 2.55%

SQL> @seg WRH$_ACTIVE_SESSION_HISTORY

    SEG_MB OWNER     SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESP 
---------- --------- ------------------------------ ------------------------------ -------------------- ------------
         0 SYS       WRH$_ACTIVE_SESSION_HISTORY_BL                                TABLE                SYSAUX      
         0 SYS       WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN      TABLE PARTITION      SYSAUX      
      8866 SYS       WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2197585535_57370   TABLE PARTITION      SYSAUX     <<<<< no purge  
         0 SYS       WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN      INDEX PARTITION      SYSAUX      
      1347 SYS       WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_57370   INDEX PARTITION      SYSAUX   
	  
SQL> select min(SAMPLE_TIME) from WRH$_ACTIVE_SESSION_HISTORY  partition(WRH$_ACTIVE_2197585535_57370);

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
17-DEC-18 01.00.47.297 AM     <<<<<

SQL> select max(snap_id),to_char(sample_time,'yyyymm') from WRH$_ACTIVE_SESSION_HISTORY partition(WRH$_ACTIVE_2197585535_57370)
  2  group by to_char(sample_time,'yyyymm') order by 2;

MAX(SNAP_ID) TO_CHA
------------ ------
       58084 201812
       59573 201901
       60916 201902
       62404 201903
       63844 201904
       65332 201905
       66773 201906
       68260 201907
       69748 201908
       71188 201909
       71699 201910

11 rows selected.

SQL> @st dba_hist_wr_control;

      DBID SNAP_INTERVAL            RETENTION     TOPNSQL
---------- ------------------------ ------------- ----------
2197585535 +00000 00:30:00.0        <<<<< retention 30 days


# Try to force the execution of the purge operations 
SQL> alter session set "_swrf_test_action"=72;

Session altered.

SQL> @seg WRH$_ACTIVE_SESSION_HISTORY

    SEG_MB OWNER  SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE
---------- ------ ------------------------------ ------------------------------ -------------------- --------------
         0 SYS    WRH$_ACTIVE_SESSION_HISTORY_BL                                TABLE                SYSAUX        
         0 SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN      TABLE PARTITION      SYSAUX        
         0 SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2197585535_71702   TABLE PARTITION      SYSAUX      <<<<< new partition    
      8866 SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2197585535_57370   TABLE PARTITION      SYSAUX        
         0 SYS    WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN      INDEX PARTITION      SYSAUX        
      1347 SYS    WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_57370   INDEX PARTITION      SYSAUX        
         0 SYS    WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_71702   INDEX PARTITION      SYSAUX        

7 rows selected.

NOTE:
如果上面的命令失败，可以手动执行报错中的分区脚本，直到上面的event成功

# Perform a manual purge of the snapshots older then 30 day’s:

SQL> select max(snap_id) from WRH$_ACTIVE_SESSION_HISTORY partition(WRH$_ACTIVE_2197585535_57370)
where sample_time<sysdate-31;

MAX(SNAP_ID)
------------
69732

SQL> begin
2 dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id=>69732);
3 end;
4 /

PL/SQL procedure successfully completed.

Note:
保留周期是30天， 但是目前分区里的数据已接近11个月， 手动清理过期的数据，这一过程会级连清除很多表的过期数据，所有时间可能较久。 如果表段的分区大小没有回收需要手动降一下高水位，如下:

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY enable row movement;

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY modify partition WRH$_ACTIVE_2197585535_57370 shrink space compact;;

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY modify partition WRH$_ACTIVE_2197585535_57370 shrink space

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY disable row movement;

Note:
COMPACT选项允许将收缩操作分为两个阶段。 首先，使用COMPACT选项移动行，但不调整高水位线（HWM），因此不会使解析的SQL语句无效。 可以在以后通过重新发出不带COMPACT选项的语句来调整HWM。 此时，任何依赖的SQL语句都需要重新解析。

打赏

微信扫一扫，打赏作者吧～