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 |