SYSAUX表空间清理之SM/OPTSTAT

最新推荐文章于 2023-06-04 10:33:13 发布

c.a.p

最新推荐文章于 2023-06-04 10:33:13 发布

阅读量1.6k

点赞数 3

分类专栏： Oracle

本文链接：https://blog.csdn.net/renyanjie123/article/details/114832842

版权

Oracle 专栏收录该内容

31 篇文章 4 订阅

订阅专栏

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