SYSAUX表空間占用過大情況下的處理（AWR信息過多）

真題1、  SYSTEM和SYSAUX表空間存儲的內容有哪些區別？若SYSAUX表空間占用過大則應該如何處理？

答案：在一般情況下，企業產生的業務數據應該存放在單獨的數據表空間，而不應該使用系統已存在的表空間，尤其不能將業務數據保存到SYSTEM和SYSAUX表空間中，所以，DBA需要著重關注SYSTEM和SYSAUX表空間的占用情況。

Oracle服務器使用SYSTEM表空間管理整個數據庫。這個表空間包含系統的數據字典和關于數據庫的管理信息，這些信息均包含在SYS方案中，只有SYS用戶或者擁有所需權限的其它管理用戶才可訪問這些信息。SYSTEM表空間用于核心功能（例如數據字典表）。

SYSAUX是SYSTEM表空間的輔助表空間。Oracle DB早期版本中某些使用SYSTEM表空間或其本身表空間的組件和產品現在改為使用SYSAUX表空間。每個Oracle Database 10g（或更高版本）數據庫都必須擁有SYSAUX表空間。輔助表空間SYSAUX用于附加的數據庫組件，例如，OEM庫（Oracle Enterprise Manager Repository）、AWR快照信息庫、統計信息、審計信息等。

SYSTEM和SYSAUX表空間是在創建數據庫時創建的必需存在的表空間。這些表空間必須聯機。在OPEN狀態下，SYSAUX表空間可以脫機以執行表空間恢復，而SYSTEM表空間則不能，這兩種表空間都不能設置為只讀狀態。在MOUNT狀態下，任何表空間都可以脫機。

SYSTEM表空間的大小一般變化不大，而SYSAUX表空間在默認條件下如果不做任何配置，那么隨著時間的推移，會越來越大。所以，如果SYSAUX表空間過大，那么應該及時診斷清理該表空間。

對于SYSTEM表空間而言，如果占用過大，那么一般情況下是由于審計表（SYS.AUD$）過大引起的。需要將審計表移動到其它表空間中，然后再清理審計表（TRUNCATE TABLE SYS.AUD$）即可。需要注意的是，如果審計表過大，那么應該分部去清理審計表，詳細步驟可以參考審計部分。

對于SYSAUX表空間而言，如果占用過大，那么一般情況下是由于AWR信息或對象統計信息沒有及時清理引起的，具體原因可以通過如下的SQL語句查詢：

SELECT OCCUPANT_NAME "Item",

       SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",

       SCHEMA_NAME "Schema",

       MOVE_PROCEDURE "Move Procedure"

  FROM V$SYSAUX_OCCUPANTS

 WHERE SPACE_USAGE_KBYTES > 1048576

 ORDER BY "Space Used (GB)" DESC;

如果OCCUPANT_NAME列為SM/AWR（Server Manageability - Automatic Workload Repository），那么表示AWR信息占用過大；如果該列為SM/OPTSTAT（Server Manageability - Optimizer Statistics History），那么表示優化器統計信息占用過大。

也可以直接查詢DBA_SEGMENTS視圖獲取信息：

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024  SIZE_M

  FROM DBA_SEGMENTS D

 WHERE D.TABLESPACE_NAME = 'SYSAUX'

 GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE

 ORDER BY SIZE_M DESC;

然后查詢占用空間較大的表，即可得到占用空間較大的原因，下面分別討論。

（一）AWR信息占用過大

如果確認是AWR信息占用空間過大，那么還可以使用如下的SQL腳本獲取AWR占用信息的詳細信息：

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

如果AWR信息占用過大，那么可以通過設置AWR的保留時間來減小AWR信息的存儲空間。通過如下的SQL語句可以獲取AWR的保留時間：

SELECT * FROM DBA_HIST_WR_CONTROL;

通過如下的SQL語句可以設置AWR信息的保留時間為7天（7*24*60），每隔1小時收集一次AWR信息：

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);

需要注意的是，在Oracle 10g中，AWR默認保留7天，在Oracle 11g中，AWR默認保留8天。

在以上設置完成后，可以刪除不需要的AWR快照信息，從而釋放SYSAUX表空間，相關SQL語句如下所示：

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

BEGIN

     DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

      LOW_SNAP_ID => 1,

      HIGH_SNAP_ID => 36768,

      DBID => 1148453265);

END;

如果DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE執行太慢，那么可以先執行TRUNCATE操作：

select distinct 'truncate  table  '||segment_name||';',s.bytes/1024/1024

  from dba_segments s

 where s.segment_name like 'WRH$%'

   and segment_type in ('TABLE PARTITION', 'TABLE')

   and s.bytes/1024/1024>100

   order by s.bytes/1024/1024/1024 desc;

執行完TRUNCATE操作后，再執行DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE。需要注意的是，以上TRUNCATE操作會將AWR中的所有信息全部清除。所以，需要先確認釋放需要這些AWR信息，當然也可以先把需要的AWR信息做導出操作，然后再清空以上AWR信息。

需要注意的是，DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通過DELETE操作來完全清理工作的。所以，執行完成后，并不會真正的釋放SYSAUX表空間。此時，應該對相關的表執行MOVE或TRUNCATE操作。在執行MOVE操作時，由于AWR信息的表都是分區表，不能對分區表全表執行MOVE操作，所以需要單獨對分區執行MOVE操作，例如：

ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION 分區名稱;

執行完MOVE操作后，需要對索引進行重建。同理，對于分區索引，只能對分區的單個索引進行重建，而不能總體重建：

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分區名稱;

需要注意的是，可以在以上SQL后加上“UPDATE GLOBAL INDEXES”子句讓全局索引不失效。

（二）統計信息占用過大

如果統計信息占用空間過大，那么可以修改統計信息的保留時間。統計信息默認保留31天，過期的統計信息會自動被刪除。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查詢統計信息的保留時間

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --設置統計信息的保留時間

若發現統計信息占用了SYSAUX上的大量空間，則可以考慮使用DBMS_STATS.PURGE_STATS過程實施清理。

以下的SQL語句對于診斷SYSAUX表空間的占用情況非常有用：

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

以下SQL可以查詢到無效的ASH信息：

SELECT COUNT(*)

  FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A

 WHERE NOT EXISTS (SELECT 1

          FROM SYS.WRM$_SNAPSHOT B

         WHERE A.SNAP_ID = B.SNAP_ID

           AND A.DBID = B.DBID

           AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);

最后需要說明的一點是，負責收集和清理AWR信息的后臺進程為MMON，而隱含參數“_swrf_test_action”可以調試MMON的行為，可以和10046事件結合使用。MMON進程每分鐘都會自動刷新一定的AWR數據到磁盤上，默認情況下，MMON每30分鐘做一次AWR信息的清理工作。在trace文件中可以看到“MMON Auto-Purge cycle”字樣。

& 說明：

有關SYSTEM和SYSAUX的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152868/

有關審計的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/

有關數據庫操作導致索引失效的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/