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

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

SQL> conn / as sysdba

SQL> @?/rdbms/admin/awrinfo.sql

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

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

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

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

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

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分区

SQL> alter session set "_swrf_test_action" = 72;

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

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)

	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);
即：

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

9.再次查看

SQL> conn / as sysdba

SQL> @?/rdbms/admin/awrinfo.sql

手动purge优化器的统计信息与AWR快照，减少对sysaux表空间的占用的相关教程结束。