背景
??經歷了一次“Oracle SYSAUX表空間異常爆滿”,當時查資料幾種解決思路,在此做個匯總方便后續查看。不嚴謹的地方歡迎大家指正
問題介紹
- sysaux表空間中占用空間的組件
set line 800
col OCCUPANT_NAME for a30
col OCCUPANT_DESC for a60
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS order by SPACE_USAGE_KBYTES desc;
OCCUPANT_NAME OCCUPANT_DESC USAGE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/OPTSTAT -->優化器統計信息 Server Manageability - Optimizer Statistics History 23922.25
- SYSAUX 表空間
表空間名 表空間大小(M) 已使用空間(M) 使用比 空閑空間(M) 最大塊(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX 32720 32673.31 99.86% 46.69
由此可以看到SM/OPTSTAT組件占比 SYSAUX 空間:73%
SM/OPTSTAT簡介
SM/OPTSTAT是用于存儲老的統計信息。
(1)10G之前,當對表/字段/索引做了相應的統計信息之后,新的統計信息就會覆蓋老的統計信息,也就是說的無法直接找回統計信息,要找回,只能事先通過dbms_stats導出來。
(2)10G之后,就不必了,它會自動的存到相應的表里,而這些表是存在sysaux的,但這也引出了一個問題:如果這些表的數據不斷的增長,而不把老的數據刪除的話,sysaux遲早會被撐爆。
默認的情況下,系統會為SM/OPTSTAT保留31天的記錄,可以通過dbms_stats.get_stats_history_retention 來確定。
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SM/OPTSTAT保留的時間可以通過dbms_stats.alter_stats_history_retention來控制。
-- 將歷史統計信息保留時間設為無限:
SQL> exec dbms_stats.alter_stats_history_retention(-1);
-- 將歷史統計信息保留時間設為7天
SQL> exec dbms_stats.alter_stats_history_retention(7);
-- 查看歷史統計信息保留時間
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
04-NOV-22 10.00.07.354209000 PM +08:00
如果SM/OPTSTAT確實占用了比較多的空間,要刪除某個時間前的記錄可以用:dbms_stats.purge_stats
-- 清理3天前的歷史統計信息
SQL> exec dbms_stats.purge_stats(sysdate-3);
PL/SQL procedure successfully completed.
這個SP其實只從存儲歷史統計信息的表里刪除記錄,這樣的話,就會出現一種情況,刪除了大量的數據,但這些表占用的空間并沒有釋放,也就是HWM不會降下來的。這時要手工處理
處理方案
方案一:來自(文檔 ID 1055547.1)的方案:
- 涉及的表

- 原因

- 解決


方案二:直接truncate 表
- 1、將歷史統計信息保留時間設為無限:
exec dbms_stats.alter_stats_history_retention(-1);
- 2、truncate TABLE:
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;
- 3、清理歷史統計信息(清理7天前的信息)
exec dbms_stats.purge_stats(sysdate-7);
- 4、將歷史統計信息保留時間設為7天(默認31天)
exec dbms_stats.alter_stats_history_retention(7);
- 5、避免有其它問題,執行一次數據庫的收集任務:
SQL> exec dbms_stats.gather_database_stats_job_proc();
PL/SQL procedure successfully completed.
方案三:move table 達到清理碎片的目地
- 將表move 到users表空間
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users;
-- LOB 字段字段需要單獨move
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users LOB (expression) STORE AS lobsegment (TABLESPACE users);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;
- 重新將表move 回原空間:SYSAUX
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX LOB (expression) STORE AS lobsegment (TABLESPACE SYSAUX);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;
方案四:重創建表
- 根據create table as 創建一張指定范圍內的表備做備份,truncate 表后再重新導入
SQL> CREATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY_B AS SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY where to_char(savtime,'yyyy-mm-dd')>'2022-11-09';
SQL> TRUNCATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY ;
SQL> INSERT INTO WRI$_OPTSTAT_HISTGRM_HISTORY SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY_B;
SQL> COMMIT;
SQL> drop table WRI$_OPTSTAT_HISTGRM_HISTORY_B purge;
總結:
- 不一定是全的,但應該有一款適合你!
文章推薦
– 故障
《Oracle_索引重建—優化索引碎片》
《Oracle 自動收集統計信息機制》
《DBA_TAB_MODIFICATIONS表的刷新策略測試》
《FY_Recover_Data.dbf》
《Oracle RAC 集群遷移文件操作.pdf》
《Oracle Date 字段索引使用測試.dbf》
《Oracle 診斷案例 :因應用死循環導致的CPU過高》
《記錄一起索引rebuild與收集統計信息的事故》
《RAC DG刪除備庫redo時報ORA-01623》
《問答榜上引發的Oracle并行的探究(一)》
《問答榜上引發的Oracle并行的探究(二)》
《DG 同步延遲之奇怪的經典報錯:ORA-16191》
– 等待事件
《log file sync》 等待事件問題分析匯總
《ASH報告發現:os thread startup 等待事件分析》
– 監控&腳本
《DG standby time 監控腳本部署》
《Oracle 慢SQL監控腳本》
《Oracle 慢SQL監控測試及監控腳本.pdf》
《oracle 監控表空間腳本 每月10號0點至06點不報警》
《Oracle 腳本實現簡單的審計功能》
– 安裝系列
《ORACLE_19C_linux安裝.pdf》
《Oracle 19c-手工建庫.pdf》
《19c單庫升級19.11補丁.pdf》
《19c_rac補丁《19.11-p32841500》.pdf 》
《oracle_圖形-單實例11.2.0.4升級19.3.pdf》
《oracle_11.2.0.3升級11.2.0.4–單實例升級.pdf》
《oracle_靜默-單實例 11.2.0.4升級19.3.pdf》
《CentOS_6.7系統一步一步 RAC 11.2.0.4升級19.3.pdf》
《整理后_RAC_11.2.0.4升級19c.pdf》
歡迎贊賞支持或留言指正




