SYSAUX表空間清理之SM/OPTSTAT

linxueguo發表於2021-09-21
UX

sysaux是system的輔助表空間，主要存放AWR基表和審計資訊，一般情況下sysaux的使用率都是正常的，看到sysaux表空間使用過高就有點懷疑了，首先檢視下錶空間。

SQL> select * from 
(select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  from dba_segments where tablespace_name='SYSAUX'
group by owner,segment_name,segment_type
order by 4 desc )
where rownum <10;  2    3    4    5  
 
OWNER			       SEGMENT_NAME									 SEGMENT_TYPE		    GB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
SYS			       I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST							 INDEX		    13.1171875
SYS			       I_WRI$_OPTSTAT_H_ST								 INDEX		    12.6362915
SYS			       WRI$_OPTSTAT_HISTGRM_HISTORY							 TABLE		    10.1123047
SYS			       WRH$_SQL_BIND_METADATA								 TABLE		    7.53729248
SYS			       WRH$_SQL_BIND_METADATA_PK							 INDEX		    5.27453613
SYS			       WRI$_OPTSTAT_HISTHEAD_HISTORY							 TABLE		    2.31567383
SYS			       I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST							 INDEX		    1.94006348
SYS			       I_WRI$_OPTSTAT_HH_ST								 INDEX		    1.75189209
MONITOR 		       LOGIN_LOG									 TABLE			1.5625
 
9 rows selected.
 
Elapsed: 00:02:05.03
 
SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';
 
OWNER			       INDEX_NAME
------------------------------ ------------------------------
SYS			       I_WRI$_OPTSTAT_H_ST
SYS			       I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

所以是不是大概清楚了，WRI$_OPTSTAT_HISTGRM_HISTORY表佔用了35GB，那這是 一個什麼表或者存放的是什麼資訊呢，實際上這個表存放的是歷史統計資訊，預設存放31天，我們透過dbms_stats.get_stats_history_retention可以確認儲存時間，他是透過指令碼$ORACLE_HOME/rdbms/admin/catost.sql建立的。

SQL> col Item for a30
col Schema for a20
set lines 200
SELECT occupant_name"Item",
 round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
 schema_name "Schema",
 move_procedure "MoveProcedure"
 FROM v$sysaux_occupants
 ORDER BY 2 Desc;SQL> SQL> SQL>   2    3    4    5    6  
 
 
 
Item			       Space Used (GB) Schema		    MoveProcedure
------------------------------ --------------- -------------------- ----------------------------------------------------------------
SM/OPTSTAT				42.144 SYS
SM/AWR					 16.19 SYS
SM/ADVISOR				  .468 SYS
EM					   .27 SYSMAN		    emd_maintenance.move_em_tblspc
JOB_SCHEDULER				  .151 SYS
XDB					  .124 XDB		    XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SDO					  .073 MDSYS		    MDSYS.MOVE_SDO
AO					  .037 SYS		    DBMS_AW.MOVE_AWMETA
XSOQHIST				  .037 SYS		    DBMS_XSOQ.OlapiMoveProc
SM/OTHER				   .02 SYS
LOGMNR					  .013 SYSTEM		    SYS.DBMS_LOGMNR_D.SET_TABLESPACE
ORDIM/ORDDATA				  .013 ORDDATA		    ordsys.ord_admin.move_ordim_tblspc
SQL_MANAGEMENT_BASE			  .007 SYS
XSAMD					  .005 OLAPSYS		    DBMS_AMD.Move_OLAP_Catalog
EXPRESSION_FILTER			  .004 EXFSYS
TEXT					  .004 CTXSYS		    DRI_MOVE_CTXSYS
SMON_SCN_TIME				  .003 SYS
WM					  .003 WMSYS		    DBMS_WM.move_proc
PL/SCOPE				  .002 SYS
EM_MONITORING_USER			  .002 DBSNMP
STREAMS 				  .001 SYS
LOGSTDBY				  .001 SYSTEM		    SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM/ORDPLUGINS			     0 ORDPLUGINS	    ordsys.ord_admin.move_ordim_tblspc
ULTRASEARCH				     0 WKSYS		    MOVE_WK
ULTRASEARCH_DEMO_USER			     0 WK_TEST		    MOVE_WK
ORDIM					     0 ORDSYS		    ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA		     0 SI_INFORMTN_SCHEMA   ordsys.ord_admin.move_ordim_tblspc
TSM					     0 TSMSYS
AUDIT_TABLES				     0 SYS		    DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK				     0 SYS
STATSPACK				     0 PERFSTAT
 
31 rows selected.

同樣我們透過v$sysaux_occupants檢視也能確認，SM/OPTSTAT代表的就是歷史統計資訊，可以看到儲存31天的歷史資訊，接下來我們對歷史的刪除。

SQL> select dbms_stats.get_stats_history_availability from dual;
 
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
15-FEB-21 11.13.29.763337000 PM +08:00
 
Elapsed: 00:00:00.06
SQL>  select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
			 31
 
Elapsed: 00:00:00.01
SQL> exec dbms_stats.purge_stats(sysdate-10);
 
PL/SQL procedure successfully completed.
 
Elapsed: 01:42:52.33
SQL>  select dbms_stats.get_stats_history_availability from dual;
 
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
05-MAR-21 09.50.09.000000000 AM +08:00
 
Elapsed: 00:00:00.08

刪除成功！！！！我們檢視段大小。

SQL> select * from 
(select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  from dba_segments where tablespace_name='SYSAUX'
group by owner,segment_name,segment_type
order by 4 desc )
where rownum <10;  2    3    4    5  
 
OWNER			       SEGMENT_NAME									 SEGMENT_TYPE		    GB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
SYS			       I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST							 INDEX		    13.1171875
SYS			       I_WRI$_OPTSTAT_H_ST								 INDEX		    12.6362915
SYS			       WRI$_OPTSTAT_HISTGRM_HISTORY							 TABLE		    10.1123047
SYS			       WRH$_SQL_BIND_METADATA								 TABLE		    7.53729248
SYS			       WRH$_SQL_BIND_METADATA_PK							 INDEX		    5.27453613
SYS			       WRI$_OPTSTAT_HISTHEAD_HISTORY							 TABLE		    2.31567383
SYS			       I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST							 INDEX		    1.94006348
SYS			       I_WRI$_OPTSTAT_HH_ST								 INDEX		    1.75189209
MONITOR 		       LOGIN_LOG									 TABLE			1.5625
 
9 rows selected.
 
Elapsed: 00:02:05.03

並沒有釋放，原因就是dbms_stats.purge_stats(sysdate-10)操作實際的操作內容是delete。

所以.......... 是不是明白一些坑了。。

①delete操作過程中有大量的undo佔用和redo產生，關注歸檔使用量

②delete之後不會釋放HWM

使用MOVE方式釋放HWM

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
 
Table altered.
 
Elapsed: 00:00:30.58
SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;
 
Index altered.
 
Elapsed: 00:02:08.88
SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online;
 
Index altered.
 
Elapsed: 00:00:55.97

選擇業務空閒時間做

在看段大小，已經大大縮小了

(select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  from dba_segments where tablespace_name='SYSAUX'
group by owner,segment_name,segment_type
order by 4 desc )
where rownum <10;  2    3    4    5  
 
 
 
OWNER			       SEGMENT_NAME									 SEGMENT_TYPE		    GB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
SYS			       WRH$_SQL_BIND_METADATA								 TABLE		    7.53729248
SYS			       WRH$_SQL_BIND_METADATA_PK							 INDEX		    5.27453613
SYS			       WRI$_OPTSTAT_HISTHEAD_HISTORY							 TABLE		    2.31567383
SYS			       I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST							 INDEX		    1.94006348
SYS			       I_WRI$_OPTSTAT_HH_ST								 INDEX		    1.75189209
MONITOR 		       LOGIN_LOG									 TABLE			1.5625
SYS			       WRH$_ACTIVE_SESSION_HISTORY							 TABLE PARTITION    1.21685791
SYS			       WRI$_OPTSTAT_HISTGRM_HISTORY							 TABLE		    1.06835938
SYS			       I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST							 INDEX		     .76953125
 
9 rows selected.
 
Elapsed: 00:02:43.54


————————————————

版權宣告：本文為CSDN博主「Small-A」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處連結及本宣告。

原文連結：https://blog.csdn.net/renyanjie123/article/details/114832842


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31374736/viewspace-2792918/，如需轉載，請註明出處，否則將追究法律責任。

相關文章