oracle10g的sysaux空間暴增與空間回收-轉載

author：skate
time：2012/02/22

出處：http://blog.csdn.net/wyzxg/article/details/7280955

oracle10g的sysaux空間暴增與空間回收

在Oracle10中表空間SYSAUX引入，oracle把統計資訊儲存在這裡，這也是為了更好的優化system表空間，我們可以用檢視V$SYSAUX_OCCUPANTS 檢視，oracle有哪些資料存貯在SYSAUX中。

SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;

oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的統計資訊都儲存在SYSAUX中，這裡重點介紹SM/OPTSTAT。

SM/OPTSTAT：用於儲存老版本的優化統計資訊，在oracle10g中，在我們手動或自動更新統計資訊使oracle選擇了錯誤的執行計劃。oracle10g是可以恢復舊版本的統計資訊，這個統計資訊預設儲存31天

查詢當前SM/OPTSTAT的統計資訊的儲存時間
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31

修改SM/OPTSTAT的統計資訊的儲存時間為10天
SQL> exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
10

SQL>

刪除16天前的統計資料
SQL> exec dbms_stats.purge_stats(sysdate-16);

PL/SQL procedure successfully completed

SQL>

檢視當前有效的統計資料是到什麼時間的
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
12-2月 -12 07.15.49.000000000 下午 +08:00

再刪除7天前的統計資料
SQL> exec dbms_stats.purge_stats(sysdate-7);

PL/SQL procedure successfully completed

這個時候發現有效的統計資訊時間已經變了
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
14-2月 -12 07.15.57.000000000 下午 +08:00

SQL>

這個時候雖然刪除了資料，但空間還沒有回收，如何回收空間呢？

沒有釋放空間是因為“purge_stats”用delete的方式刪除資料，雖然資料沒了，但是HWM還沒降下來，檢視OPTSTAT使用哪些表，然後降低其高水位即可。
SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE'
2 ;

OBJECT_NAME
--------------------------------------------------------------------------------
WRI$_OPTSTAT_TAB_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_OPR
OPTSTAT_HIST_CONTROL$

7 rows selected

SQL>

再結合如下sql判斷哪個表大，然後就move哪個表
SQL> select a.table_name,a.num_rows from dba_tables a where a.tablespace_name='SYSAUX' and a.table_name like '%OPTSTAT%'
2 ;

TABLE_NAME NUM_ROWS
------------------------------ ----------
WRI$_OPTSTAT_OPR 151
WRI$_OPTSTAT_AUX_HISTORY 0
WRI$_OPTSTAT_HISTGRM_HISTORY 139933
WRI$_OPTSTAT_HISTHEAD_HISTORY 14406
WRI$_OPTSTAT_IND_HISTORY 1196
WRI$_OPTSTAT_TAB_HISTORY 1323

6 rows selected

SQL>

再用如下語句查出相關表的索引，因為move表，索引會失效，需要重建索引
SQL> select i.index_name,i.table_name,i.status,i.table_owner
2 from dba_indexes i,dba_objects s where i.table_name=s.object_name and s.object_name like '%OPTSTAT%' and s.object_type='TABLE'
3 ;

INDEX_NAME TABLE_NAME STATUS TABLE_OWNER
------------------------------ ------------------------------ -------- ------------------------------
I_WRI$_OPTSTAT_TAB_OBJ#_ST WRI$_OPTSTAT_TAB_HISTORY VALID SYS
I_WRI$_OPTSTAT_TAB_ST WRI$_OPTSTAT_TAB_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_OBJ#_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_AUX_ST WRI$_OPTSTAT_AUX_HISTORY VALID SYS
I_WRI$_OPTSTAT_OPR_STIME WRI$_OPTSTAT_OPR VALID SYS

10 rows selected

SQL>

降低HWM
sql> alter table WRI$_OPTSTAT_TAB_HISTORY move;
sql> alter table WRI$_OPTSTAT_OPR move;
sql> alter table WRI$_OPTSTAT_IND_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
sql> alter table WRI$_OPTSTAT_AUX_HISTORY move;
sql> alter table OPTSTAT_HIST_CONTROL$ move;

重建索引
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online;
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index I_WRI$_OPTSTAT_IND_ST rebuild online;
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index I_WRI$_OPTSTAT_HH_ST rebuild online;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;
alter index I_WRI$_OPTSTAT_H_ST rebuild online;
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online;
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online;

如果索引編譯不成功，就要create indexe

用如下語句生成DDL語句
SQL> set long 4000
SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_IND_OBJ#_ST','SYS') from dual;
SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_TAB_ST','SYS') from dual;

如何恢復統計資訊

用如下語句查到統計資訊的時間點
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history；

可以按需要根據時間點恢復統計資訊
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)

例如：
SQL> execute dbms_stats.restore_table_stats ('SKATE','BK_ADMIN',sysdate -1);

PL/SQL procedure successfully completed

SQL>

參考文件：[ID 329984.1], [ID 452011.1],[ID 454678.1]

----end----