在檢查資料庫時發現表空間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 1662 2

…………………………………………………………………….後邊省略

檢視快照資訊，發現快照最小值為31074

Listing the last 8 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

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

eicdb1 EICDB 31074 18 Dec 2013 01:00 1

31075 18 Dec 2013 02:00 1

31076 18 Dec 2013 03:00 1

31077 18 Dec 2013 04:00 1

透過以下語句檢視，發現下面幾個表佔用空間較大

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 22881

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 16125

I_WRI$_OPTSTAT_H_ST INDEX 12002

WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10008

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

10 rows selected.

檢視分割槽表snap_id資訊，發現其最小值為25627，也就是說裡邊有許多過期資料存放）

SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) order by SNAP_ID) where rownum<=10;

SNAP_ID DBID

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

25627 2065804525

25628 2065804525

25629 2065804525

25630 2065804525

25631 2065804525

25632 2065804525

25633 2065804525

25634 2065804525

25635 2065804525

25636 2065804525

10 rows selected

檢視錶wrh$_active_session_history資訊

SQL> select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' and segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

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

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 22881

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .0625

檢查其他資料庫中sys.wrh$_active_session_history 表，發現該表每天都會建立一個分割槽表，末尾並以起始snap_id命名（很奇怪，為啥前一個資料庫就一個分割槽表）。

SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history order by SNAP_ID) where rownum<=10;

SNAP_ID DBID

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

1008 1358035033

1009 1358035033

1010 1358035033

1011 1358035033

1012 1358035033

1013 1358035033

1014 1358035033

1015 1358035033

1016 1358035033

1017 1358035033

10 rows selected.

SQL> set lines 999

SQL> col SEGMENT_NAME for a30

SQL> select OBJECT_NAME,SUBOBJECT_NAME,object_type,CREATED from dba_objects where OBJECT_NAME like 'WRH$_ACTIVE%' order by object_type;

OBJECT_NAME UBOBJECT_NAME OBJECT_TYPE CREATED

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

WRH$_ACTIVE_SESSION_HISTORY_PK INDEX 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1119 INDEX PARTITION 17-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1095 INDEX PARTITION 16-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN INDEX PARTITION 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1047 INDEX PARTITION 14-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1191 INDEX PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1071 INDEX PARTITION 15-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1143 INDEX PARTITION 18-DEC-13

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1167 INDEX PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY TABLE 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY_BL TABLE 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1167 TABLE PARTITION 18-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1143 TABLE PARTITION 17-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1119 TABLE PARTITION 16-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1095 TABLE PARTITION 15-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1047 TABLE PARTITION 13-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1191 TABLE PARTITION 19-DEC-13

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION 20-APR-10

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1071 TABLE PARTITION 14-DEC-13

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

Table truncated.

SQL>

SQL> col SNAP_INTERVAL for a18

SQL> col RETENTION for a18

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

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

1358035033 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 – Production

透過上述，檢視oracle10g資料庫資訊，發現，有8天的分割槽表資料，而資料庫AWR保留策略為7天，也就是當清除snap_id資訊時並沒有完全清除表中資料資訊。

暫時沒想到好的解決辦法，就先delete部分資料（該11g生產庫執行期間cpu大多在百分之80-90甚至更高，所以只能一點點的先delete部分資料（貨架上的東西是沒了，但貨架還在，暫時也就這樣了，再找找其他方法，其中有兩個索引資料量也不小，可以分析一下）。語句如下：

delete from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) p where p.snap_id<=25850;

曾想試試一下命令，刪除之前統計資訊（這個無法確定執行時長，當時沒用）：

exec dbms_stats.purge_stats(systimestamp - 200);

引數說明：

Parameters

Table 141-78 PURGE_STATS Procedure Parameters

Parameter

Description

before_timestamp

Versions of statistics saved before this timestamp are purged. If NULL, it uses the purging policy used by automatic purge. The automatic purge deletes all history older than the older of (current time - statistics history retention) and (time of recent analyze in the system - 1). The statistics history retention value can be changed using ALTER_STATS_HISTORY_RETENTION Procedure.The default is 31 days.

附：

刪除快照命令：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>777,high_snap_id=>800,dbid=>2530619508)

清除資料庫中其他資料庫AWR資訊可以使用下面的語句：

exec dbms_swrf_internal.unregister_database(2065804525);

遺留問題：

1、 delete分割槽表中部分資料後並沒有完全收回空間，高水位問題（move佔用資源，可避開高峰期），有其他什麼方法可以解決；

2、 oracle11g庫中（其他oracle11g庫中也是每天生成一個分割槽表）就一個分割槽表儲存資料，影響資料管理，暫時未找出原因；

3、對刪除快照、AWR過期資料清理流程不太清楚；

4、為什麼表中還保留一些過期資料，MMON後臺程式怎麼收集和清理AWR資訊

由於對AWR不是很瞭解，有太多業餘的地方，如哪位同志瞭解，請告知在下，不勝感激。

還在繼續分析中……

【AWR】該怎樣清理SYSAUX表空間相關資料

相關文章