手动purge优化器的统计信息与AWR快照，减少对sysaux表空间的占用

1.运行以下脚本，计算当前优化器统计信息和AWR快照表占用sysaux的空间

?

1 2	SQL> conn / as sysdba SQL> @?/rdbms/admin/awrinfo.sql

2.检查优化器统计信息直方图表中的信息的有效天数

?

1	SQL>  select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history;

3.purge统计信息(修改有效天数)

?

1	SQL>  exec dbms_stats.purge_stats(sysdate - <no of days>);

4.在split表WRH$_ACTIVE_SESSION_HISTORY之前，检查其相关信息

?

1 2 3 4 5 6 7 8 9 10

SQL> set lines 150 SQL> col SEGMENT_NAME for a30 SQL> col PARTITION_NAME for a50   SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';   OWNER                          SEGMENT_NAME                   PARTITION_NAME                                     SEGMENT_TYPE          SIZE_GB ------------------------------ ------------------------------ -------------------------------------------------- ------------------ ---------- SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT_HISTO_MXDB_MXSN                         TABLE PARTITION    .000061035 SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_0                           TABLE PARTITION    .642578125

5.split awr分区，以便于有更多机会purge小的分区
这个命令会对所有的awr分区对象进行split，并初始化一个split分区

?

1	SQL> alter session set "_swrf_test_action" = 72;

6.split之后，再次检查分区

?

1 2 3 4 5 6 7 8 9 10 11

SQL> set lines 150 SQL> col SEGMENT_NAME for a30 SQL> col PARTITION_NAME for a50   SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';   OWNER                          SEGMENT_NAME                   PARTITION_NAME                                     SEGMENT_TYPE          SIZE_GB ------------------------------ ------------------------------ -------------------------------------------------- ------------------ ---------- SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT_HISTO_MXDB_MXSN                         TABLE PARTITION    .000061035 SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_0                           TABLE PARTITION    .642578125 SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_9290                        TABLE PARTITION    .000061035

7.查看每个分区表中的快照id(最小、最大快照id)

?

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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

set serveroutput on     declare     CURSOR cur_part IS     SELECT partition_name from dba_tab_partitions     WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';       query1 varchar2(200);     query2 varchar2(200);       TYPE partrec IS RECORD (snapid number, dbid number);     TYPE partlist IS TABLE OF partrec;       Outlist partlist;     begin     dbms_output.put_line('PARTITION NAME SNAP_ID DBID');     dbms_output.put_line('--------------------------- ------- ----------');       for part in cur_part loop     query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')     group by dbid';     execute immediate query1 bulk collect into OutList;       if OutList.count > 0 then     for i in OutList.first..OutList.last loop     dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);     end loop;     end if;       query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')     group by dbid';     execute immediate query2 bulk collect into OutList;       if OutList.count > 0 then     for i in OutList.first..OutList.last loop     dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);     dbms_output.put_line('---');     end loop;     end if;       end loop;     end;     / 结果： PARTITION NAME SNAP_ID DBID --------------------------- ------- ---------- WRH$_ACTIVE_1402125233_0 Min 9042 1402125233 WRH$_ACTIVE_1402125233_0 Max 9287 1402125233

8.根据上面的查询结果删除快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id IN NUMBER,high_snap_id IN NUMBER, dbid IN NUMBER DEFAULT NULL);
即：

?

1	SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9042,9287,1402125233);

9.再次查看

?

1 2	SQL> conn / as sysdba SQL> @?/rdbms/admin/awrinfo.sql