前邊兩篇文章我們測試了修改保留快照的時間、收縮索引空間等方法，但是效果不是太明顯，檢視、索引還是佔用著大部分空間，

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

SQL> select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ------------------------------ ------------------ ---------------

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 23150

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 10105.5625

WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10008

I_WRI$_OPTSTAT_H_ST INDEX 7424.75

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627 INDEX PARTITION 3030

WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2135

WRH$_SYSMETRIC_HISTORY TABLE 1732

I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1685

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

檢視了一下與統計資訊相關的幾個檢視，發現記錄還有一年之前的

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_TAB_HISTORY;

MIN(SAVTIME)

---------------------------------------------------------------------------

MAX(SAVTIME)

---------------------------------------------------------------------------

26-MAY-13 09.01.04.793825 PM +08:00

25-DEC-13 10.42.55.676557 AM +08:00

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_HISTGRM_HISTORY;

MIN(SAVTIME)

---------------------------------------------------------------------------

MAX(SAVTIME)

---------------------------------------------------------------------------

16-MAR-12 09.28.07.004180 PM +08:00

25-DEC-13 10.42.55.750147 AM +08:00

SQL> select min(savtime),max(savtime) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

MIN(SAVTIME)

---------------------------------------------------------------------------

MAX(SAVTIME)

---------------------------------------------------------------------------

16-NOV-12 12.00.27.899822 AM +08:00

25-DEC-13 10.42.55.750147 AM +08:00

執行清除統計資訊命令

SQL> exec dbms_stats.purge_stats(systimestamp - 500);

PL/SQL procedure successfully completed.

SQL> SQL> SQL>

SQL>

SQL> exec dbms_stats.purge_stats(systimestamp -300);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.purge_stats(systimestamp - 100);

。。。。。。。一直等啊等

透過上述方式呢，對於資料量較大的檢視執行效果太緩慢，經過短暫掙扎，決定truncate

SQL> exec dbms_stats.alter_stats_history_retention(-1);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

-1

SQL> truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;

Table truncated.

SQL> truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

Table truncated.

再次執行DBMS_STATS.PURGE_STATS清理就快多了

SQL> exec dbms_stats.purge_stats(sysdate-200);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.purge_stats(sysdate-100);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.purge_stats(sysdate-3);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.alter_stats_history_retention(3);

PL/SQL procedure successfully completed.

下面呢，就將跟統計相關的檢視、索引進行整理，也就是move、rebuild，在分析一下

SQL> alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;

Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;

Index altered.

SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;

Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild online;

Index altered.

SQL> alter table sys.WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;

Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;

Index altered.

SQL> alter table sys.WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;

Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild online;

Index altered.

SQL> ALTER TABLE SYS.WRI$_OPTSTAT_OPR MOVE TABLESPACE SYSAUX;

Table altered.

SQL> ALTER TABLE SYS.WRI$_OPTSTAT_AUX_HISTORY MOVE TABLESPACE SYSAUX;

Table altered.

SQL> ALTER INDEX SYS.I_WRI$_OPTSTAT_AUX_ST REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX SYS.I_WRI$_OPTSTAT_OPR_STIME REBUILD ONLINE;

Index altered.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_IND_HISTORY',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_TAB_HISTORY',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_OPR',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_AUX_HISTORY',cascade => TRUE);

PL/SQL procedure successfully completed.

待命令執行完成後，再次檢視SYSAUX表空間空間詳細使用狀況，OPTSTAT表基本“消失”了，

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

SQL> select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ------------------------------ ------------------ ---------------

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 23150

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627 INDEX PARTITION 3030

WRH$_SYSMETRIC_HISTORY TABLE 1732

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

WRH$_EVENT_HISTOGRAM WRH$_EVENT__2065804525_25594 TABLE PARTITION 1099

SYS_LOB0000006213C00038$$ LOBSEGMENT 750

WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__2065804525_26275 INDEX PARTITION 544

WRH$_SQL_PLAN TABLE 482

WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__2065804525_26275 TABLE PARTITION 481

乘勝追擊，檢視ASH相關檢視中無效的資料，並delete掉

SQL> select count(*)

2 from sys.wrh$_active_session_history a

3 where not exists (select 1

4 from sys.wrm$_snapshot b

5 where a.snap_id = b.snap_id

6 and a.dbid = b.dbid

7 and a.instance_number = b.instance_number);

COUNT(*)

----------

663769

SQL> delete

2 from sys.wrh$_active_session_history a

3 where not exists (select 1

4 from sys.wrm$_snapshot b

5 where a.snap_id = b.snap_id

6 and a.dbid = b.dbid

7 and a.instance_number = b.instance_number);

663769 rows deleted.

檢視wrh$_active_session_history分割槽情況時發現，佔用資料空間最大的那個分割槽的資料應該為空才對啊，現在的快照只保留三天

DBID SNAP_INTERVAL

---------- ---------------------------------------------------------------------------

RETENTION TOPNSQL

--------------------------------------------------------------------------- ----------

2065804525 +00000 01:00:00.0

+00003 00:00:00.0 DEFAULT

好吧，直接將該分割槽truncate掉，其實該分割槽已沒用，所有又drop了一下

SQL> alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_2065804525_25627 update global indexes;

Table truncated.

SQL> alter table sys.wrh$_active_session_history drop partition WRH$_ACTIVE_2065804525_25627 update global indexes;

Table altered.

再次檢視SYSAUX表空間中資料分佈情況

SQL> col SEGMENT_NAME for a30

SQL> set lines 999

SQL> select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ------------------------------ ------------------ ---------------

WRH$_SYSMETRIC_HISTORY TABLE 1732

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

WRH$_EVENT_HISTOGRAM WRH$_EVENT__2065804525_25594 TABLE PARTITION 1099

SYS_LOB0000006213C00038$$ LOBSEGMENT 750

WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__2065804525_26275 INDEX PARTITION 544

WRH$_SQL_PLAN TABLE 482

WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__2065804525_26275 TABLE PARTITION 481

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_31206 TABLE PARTITION 299

WRH$_SYSSTAT_PK WRH$_SYSSTA_2065804525_26275 INDEX PARTITION 251

檢視錶空間資訊，SYSAUX已經倒數了

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

TABLESPACE_NAME TOTAL FREE % Free

------------------------- ---------- ---------- ----------

……………………..

SYSAUX 81920 69898 85

………

再檢視一下有沒有無效物件什麼的：

SQL> select * from dba_indexes where status<>'VALID' AND STATUS<>'N/A';

no rows selected

SQL> SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>'USABLE' AND STATUS<>'N/A';

no rows selected

SQL> SELECT * FROM DBA_IND_SUBPARTITIONS WHERE STATUS<>'USABLE';

no rows selected

SQL> select * from dba_indexes where degree not in ('1','0','DEFAULT');

no rows selected

後記：由於SYSAUX表空間下幾個檢視、索引資料量有點大，對於常規清除方法過於緩慢、效果更不明顯，至於ASH中表WRH$_ACTIVE_SESSION_HISTORY怎麼分割槽的，為什麼過期資料沒有自動清除，還需要觀察一下，再跟蹤幾天看看。

【AWR】清理SYSAUX表空間資料（第三季非暴力不合作）

相關文章