背景
??晚上突然收到數據庫ORA日志報警：ORA-1653: unable to extend table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY by 128 in tablespace SYSAUX 這個報錯需要關注一下。SYSAUX 數據庫的系統表空間，平時都是數據庫自己維護的一般不需要單獨注意，怎么就空間空間爆滿了呢？
處理過程：
1、首先還是去查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
SM/AWR -->AWR信息 Server Manageability - Automatic Workload Repository 8165.0625
XDB XDB 126.9375
SDO Oracle Spatial 74.25
SM/ADVISOR Server Manageability - Advisor Framework 66
JOB_SCHEDULER Unified Job Scheduler 50.1875
EM Enterprise Manager Repository 46.0625
AO Analytical Workspace Object Table 38.1875
-- 與其它庫對比：
OCCUPANT_NAME OCCUPANT_DESC USAGE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/AWR Server Manageability - Automatic Workload Repository 3042.8125
SM/ADVISOR Server Manageability - Advisor Framework 528.9375
SM/OPTSTAT Server Manageability - Optimizer Statistics History 452.75
XDB XDB 157.5625
JOB_SCHEDULER Unified Job Scheduler 102.3125
發現：SM/OPTSTAT組件（優化器統計信息）使用了：24G，其它庫才：452.75MB,差距很大
2、查看下表空間：SYSAUX
表空間名 表空間大小(M) 已使用空間(M) 使用比 空閑空間(M) 最大塊(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX 32720 32673.31 99.86% 46.69 46
SYSAUX 使用了32GB,由此可以確認SM/OPTSTAT組件（優化器統計信息）空間使用異常導致的。
3、查看占用SYSAUX 表空間前10的對象
SQL> select * from
2 (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX'
3 group by owner,segment_name,segment_type
4 order by 4 desc )
5 where rownum <10;
OWNER SEGMENT_NAME SEGMENT_TYPE GB
------------------------------ ---------------------------------------- ------------------ ----------
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 8.70898438
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 8.41699219
SYS I_WRI$_OPTSTAT_HH_ST INDEX 4.81835938
SYS WRH$_LATCH TABLE PARTITION .812561035
SYS WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION .664123535
SYS WRH$_EVENT_HISTOGRAM TABLE PARTITION .640686035
SYS WRH$_SYSSTAT_PK INDEX PARTITION .562561035
SYS WRH$_SQLSTAT TABLE PARTITION .546936035
SYS WRH$_SYSSTAT TABLE PARTITION .531311035
4、查看WRI$_OPTSTAT_HISTHEAD_HISTORY表的關聯索引
SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
OWNER INDEX_NAME
------------------------------ ------------------------------
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
SYS SYS_IL0000000494C00016$$
SYS I_WRI$_OPTSTAT_HH_ST
5、查看相關聯對像大小
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_MB
FROM DBA_SEGMENTS D
WHERE D.TABLESPACE_NAME = 'SYSAUX'
GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE
having D.SEGMENT_NAME in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','SYS_IL0000000494C00016$$','I_WRI$_OPTSTAT_HH_ST');
SEGMENT_NAME SEGMENT_TYPE SIZE_MB
---------------------------------------- ------------------ ----------
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 8918
SYS_IL0000000494C00016$$ LOBINDEX .0625
I_WRI$_OPTSTAT_HH_ST INDEX 4934
WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 8619
6、與（23922.25）基本吻合
SQL> select (8918+.0625+4934+8619)/1024 from dual;
(8918+.0625+4934+8619)/1024
---------------------------
21.944397
由此確認了占比空間最大的對象：WRI$_OPTSTAT_HISTHEAD_HISTORY
7、分析原因
百度查“SYSAUX表空間清理 WRI$_OPTSTAT_HISTHEAD_HISTORY”，結果都說是:
Bug 12540172 : SYSAUX CONTINUES TO GROW MMON NOT CLEANING UP
SYSAUX Grows Because Optimizer Stats History is Not Purged(文檔 ID 1055547.1)
不能什么都扔給BUG，自己往深的挖挖，看看能不能挖出點東西來。
- 1）與另一個生產數據庫的數據量做下對比：
SQL> select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
COUNT(*)
----------
141033942
-- 另一個庫：
SQL> select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
COUNT(*)
----------
151012
正常庫記錄數才10萬+，這個庫達到了1.4億，屬實不正常，比對一下每天的量，是不是某一天記錄突增：
-- 本庫
SQL> select to_char(savtime,'yyyy-mm-dd') as savtime,count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by to_char(savtime,'yyyy-mm-dd') order by 1;
SAVTIME COUNT(*)
---------- ----------
2022-11-04 1768420
2022-11-05 21849053
2022-11-06 21311262
2022-11-07 20613680
2022-11-08 20666343
2022-11-09 20078776
2022-11-10 19649162
2022-11-11 15097246
-- 另一個庫
SQL> select to_char(savtime,'yyyy-mm-dd') as savtime,count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by to_char(savtime,'yyyy-mm-dd') order by 1;
SAVTIME COUNT(*)
---------- ----------
......省略
2022-11-04 4456
2022-11-05 10657
2022-11-06 8967
2022-11-07 2029
2022-11-08 4508
2022-11-09 4200
2022-11-10 4777
2022-11-11 4315
31 rows selected
每天平均：1千萬以上的記錄。正常情況下也就4千左右。
那么查一下都是哪個對象在頻繁刷統計信息：
SQL> select obj#, count(*)
from WRI$_OPTSTAT_HISTHEAD_HISTORY
where to_char(savtime, 'yyyy-mm-dd') = '2022-11-09' -
group by obj#--, to_char(savtime, 'yyyy-mm-dd hh24:mi') order by 1;
OBJ# COUNT(*)
---------- ----------
147988 8000 -- 8000條屬實異常
147989 8000
141836 8000
484 7
5304 5
5325 12
6243 7
6661 3
468 10
6731 13
在這里就發現問題了，147988、147989、141836 這幾個對象一天刷新了8000次，其它也就10個左右。
查下 OBJ#對應的對象：
select owner,object_name,subobject_name from dba_objects where object_id in ('147988','147989','141836 ');
因為此庫為綜合庫上面放著好多應用，一個應用對應一個用戶。于是登到對應用戶，查看里面的存儲過程，發現有一個存儲過程代碼中有對其表進行統計：
后與開發人員溝通確認，此存儲過程為每天一次執行頻率，但在2022-11-04程序執行報錯，于是程序又重新調用，調用又報錯，于是又調用（死循環了，又是一個程序死循環的坑）。開發表示后期進行優化調整。至此問題分析完成。
8、解決
因此庫為生產數據庫，但不是核心數據庫，所以郵件申請了2個小時的維護窗口。
網上查到有好多的解決方法，后續再進行總結，這里先寫我的處理過程。
- 1、將歷史統計信息保留時間設為無限:
exec dbms_stats.alter_stats_history_retention(-1);
- 2、先對表move:僅釋放出：6GB空間
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;
并沒有解決多少空間，因為此庫但不是核心數據庫，直接truncate 得了，此次不建議，一定要根據自己庫的實際情況進行風險評估。
- 3、truncate TABLE:
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;
- 4、清理歷史統計信息
exec dbms_stats.purge_stats(sysdate-3); --保留3天
- 5、將歷史統計信息保留時間設為31天
exec dbms_stats.alter_stats_history_retention(31);
- 6、重新將表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;
- 7、收集’WRI_OPTSTAT_HISTHEAD_HISTORY、'WRI_OPTSTAT_HISTGRM_HISTORY統計信息
SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);
SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
- 8、避免有其它問題，執行一次數據庫的收集任務：
SQL> exec dbms_stats.gather_database_stats_job_proc();
PL/SQL procedure successfully completed.
- 9、驗證空間：釋放空間：22GB
表空間名 表空間大小(M) 已使用空間(M) 使用比 空閑空間(M) 最大塊(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX 32720 9506.69 29.05% 23213.31 1096
自我總結
- 一定要深挖一下問題，BUG不是所有問題的答案。這次如果不挖一下，程序的一個BUG(死循環的大坑)就發現不了，過一段時間就又會出現SYSAUX爆滿的報警。
- 表空間監控還需要再優化，系統自增表空間監控還有遺漏，有時間改進一下：Oracle 表空間監控腳本優化
- 做一次SYSAUX 爆滿的方案總結，以應對不同的數據庫環境。SYSAUX 表空間基于SM/OPTSTAT組件爆滿—解決方案匯總
文章推薦
– 故障
《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》
歡迎點贊支持或留言指正
