ITPub博客

首页 > 数据库 > Oracle > SYSAUX表空间清理之SM/OPTSTAT

SYSAUX表空间清理之SM/OPTSTAT

Oracle 作者:linxueguo 时间:2021-09-21 12:07:21 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: mysql锁表查询
请登录后发表评论 登录
全部评论

注册时间:2016-07-16

  • 博文量
    10
  • 访问量
    3357