Oracle 11g SYSAUX 和 SYSTEM 表空间回收相关知识点
作者 | JiekeXu
来源 | JiekeXu之路(ID: JiekeXu_IT)
转载请联系授权 | (微信ID:xxq1426321293)
大家好,我是 JiekeXu,很高兴又和大家见面了,今天分享下SYSAUX 和 SYSTEM 表空间回收的相关知识点。本文发布于微信公众号【JiekeXu之路】,欢迎点击上方蓝字关注我,标星或置顶,更多干货第一时间到达!
01--表空间使用率现状
通过查询可得知目前表空间使用情况如下图:可看到 SYSAUX 表空间和 SYSTEM 表空间使用率都已经高达99%,SYSAUX 表空间甚至只剩下 6.19MB 的空间。
可看出两张系统关键表空间的使用率已经不容乐观。急需找出占用空间的幕后凶手,并将之绳之以法。
02--AWRINFO脚本初步诊断
通过 Oracle 数据库自带的 awrinfo 脚本进行初步诊断,下边截取关键内容,可得到如下信息,快照过期信息是占用 SYSAUX 的元凶.
注:awrinfo.sql 是个很不错的工具,第一次使用,这里重点记录一下,使用 ?/rdbms/admin/awrinfo.sql 可以查看 awr 的数据使用情况。
开头显示 AWR 的设置信息,快照保留 40 天,每隔 30 分钟生成一个快照。后边便是一些对象占用的大小以及明细。
03--v$sysaux_occupants视图查询明细
进一步查询 SYSAUX 表空间使用详细情况:
SQL> select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc where rownum <=20;
OWNER SEGMENT_NAME PARTITION_NAME BYTES/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ---------------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1404287032_0 7907
SYS WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__1404287032_0 3627
SYS WRH$_EVENT_HISTOGRAM WRH$_EVENT__1404287032_0 2560
SYS WRH$_LATCH WRH$_LATCH_1404287032_0 1600
SYS WRH$_SYSSTAT_PK WRH$_SYSSTA_1404287032_0 1472
SYS WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__1404287032_0 1408
SYS WRH$_SQLSTAT WRH$_SQLSTA_1404287032_0 1344
SYS WRH$_LATCH_PK WRH$_LATCH_1404287032_0 1216
SYS WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1404287032_0 1152
SYS WRH$_SYSSTAT WRH$_SYSSTA_1404287032_0 1088
SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1404287032_0 935
SYS WRH$_PARAMETER_PK WRH$_PARAME_1404287032_0 896
SYS WRH$_PARAMETER WRH$_PARAME_1404287032_0 759
SYS WRH$_SEG_STAT WRH$_SEG_ST_1404287032_0 712
SYS WRH$_SYSTEM_EVENT WRH$_SYSTEM_1404287032_0 688
SYS WRH$_SYSTEM_EVENT_PK WRH$_SYSTEM_1404287032_0 504
SYS WRH$_SERVICE_STAT_PK WRH$_SERVIC_1404287032_0 408
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_1404287032_0 352
SYS WRH$_DLM_MISC WRH$_DLM_MI_1404287032_0 320
SYS WRH$_DLM_MISC_PK WRH$_DLM_MI_1404287032_0 280
SYS WRH$_SEG_STAT_PK WRH$_SEG_ST_1404287032_0 256
查询 v$sysaux_occupants 视图,我们可以确定占用 SYSAUX 表空间过多的大部分都是AWR 的基表,所以,根据实际业务需求,删除部分 AWR 数据理论上就可以回收一部分 SYSAUX 表空间,在 Oracle 中,通常 AWR 信息都会设置保留期限,Oracle 10g 版本默认保留7天,Oracle 11g 版本默认保留 8 天,可以通过 dba_hist_wr_control 视图来查看保留期限。也可以手动更改 AWR 保留时间,设置 AWR 基线。我们生产环境通常保留 40 天,每半个小时生成一份快照。
select * from dba_hist_wr_control;
这里看全是 awr 的相关内容,需要重建 awr 来释放空间,具体重建步骤,请参考博文:http://blog.itpub.net/20893244/viewspace-2146541/
~~~~~~~~~~~~~~临时删除的办法~~~~~~~~~~~~~~~··
truncate table WRH$_ACTIVE_SESSION_HISTORY;
truncate table WRH$_EVENT_HISTOGRAM;
truncate table WRH$_SQLSTAT;
truncate table WRH$_LATCH_MISSES_SUMMARY;
truncate table WRH$_LATCH;
truncate table WRH$_SYSSTAT;
truncate table WRH$_SEG_STAT;
truncate table WRH$_PARAMETER;
truncate table WRH$_SYSTEM_EVENT;
truncate table WRH$_SQL_PLAN;
truncate table WRH$_DLM_MISC;
truncate table WRH$_SERVICE_STAT;
truncate table WRH$_TABLESPACE_STAT;
truncate table WRH$_ROWCACHE_SUMMARY;
truncate table WRH$_MVPARAMETER;
根据脚本清除 WRH$% 相关表:
col "'truncate table '||segment_name||';'" for a66
set line 345 pages 345
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024 ToTAL_MB from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
04-顺带AWR讲解
既然说到这里,就顺带一下 AWR 的日常操作:
AWR (Automatic Workload Repository)一堆历史性能数据,放在 SYSAUX 表空间上, AWR 和 SYSAUX 都是10g 出现的,是 Oracle 调优的关键特性;大约 1999 年左右开始开发,已经有约 20 年历史.主要是 MMON(Manageability Monitor Process) 和它的小工进程 (m00x) 来维护 AWR。
##手动执行一个快照:
Exec dbms_workload_repository.create_snapshot;
##创建一个AWR基线
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id,end_snap_id ,baseline_name);
##单节点AWR报告
@?/rdbms/awrrpt.sql
##AWR比对报告
@?/rdbms/admin/awrddrpt
##RAC 全局AWR
@?/rdbms/admin/awrgrpt
##RAC中可选择节点生成AWR报告
@?/rdbms/admin/awrrpti
05--AWR过期快照信息清理
在本案例中,Oracle 版本为 11.2.0.4.0,AWR 默认保留期限 8 天。但是为什么会占用这么多SYSAUX 表空间呢?首先,要明确 AWR 快照信息的删除方式:AWR 报告默认是采取DELETE 的方式进行过期信息删除的,相比 TRUNCATE 而言,就会产生大量的碎片,对于开启了自动扩展数据文件的表空间而言,碎片的现会象更加严重。再有一点,ASH 的信息在有可能不受 AWR 快照保留策略的控制。从如下 SQL 查询可得知,从 SNAP_ID 为 1 的快照到目前为止的所有快照都还在数据库中保存着,导致 WRH$_ACTIVE_SESSION_HISTORY 表很大,使用 DBMS_WORKLOAD_REPOSITORY 包清理过期或者不需要的 AWR 数据,可以回收这部分空间。
SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 25444
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 18000);
PL/SQL procedure successfully completed.
但是,通过这种方式清理的 AWR 信息,再次查看 SYSAUX 表空间的空间,发现空间并没有被回收,使用率还和之前一样,这是因为清理 AWR 操作是通过 DELETE 操作实现的,表的水位线并没有下降导致的。但是通过再次查询 WRH$_ACTIVE_SESSION_HISTORY 可发现表记录已经少了。但是表大小还是没有变化。
06--对分区进行 MOVE 操作,回收表空间
通过上边查询出来的 SYSAUX 表空间占用分布情况,将占用空间较大的对象进行MOVE操作,回收表空间。
①首先查看表的分区情况以及大小
select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments
where segment_name='WRH$_EVENT_HISTOGRAM';
②对分区表进行 MOVE 操作,回收空间
alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1404287032_0;
alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;
③MOVE后,重建分区表索引
##查看分区表索引信息
select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
##重建分区表索引
SQL> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
INDEX_NAME
------------------------------
WRH$_EVENT_HISTOGRAM_PK
SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1404287032_0;
SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;
以下是相关操作参考:
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_EVENT_HISTOGRAM WRH$_EVENT__1404287032_0 2.5
WRH$_EVENT_HISTOGRAM WRH$_EVENT_HISTO_MXDB_MXSN .000061035
SQL> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1404287032_0;
Table altered.
SQL> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;
Table altered.
SQL> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
INDEX_NAME
------------------------------
WRH$_EVENT_HISTOGRAM_PK
SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1404287032_0;
Index altered.
SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;
Index altered.
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';
SUM(BYTES)/1024/1024
--------------------
.125
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH WRH$_LATCH_MXDB_MXSN .000061035
WRH$_LATCH WRH$_LATCH_1404287032_0 1.5625
SQL> alter table WRH$_LATCH move partition WRH$_LATCH_1404287032_0;
Table altered.
SQL> alter table WRH$_LATCH move partition WRH$_LATCH_MXDB_MXSN;
Table altered.
SQL> select index_name from dba_indexes where table_name='WRH$_LATCH';
INDEX_NAME
------------------------------
WRH$_LATCH_PK
SQL> alter index WRH$_LATCH_PK rebuild partition WRH$_LATCH_1404287032_0;
Index altered.
SQL> alter index WRH$_LATCH_PK rebuild partition WRH$_LATCH_MXDB_MXSN;
Index altered.
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH WRH$_LATCH_MXDB_MXSN .000061035
WRH$_LATCH WRH$_LATCH_1404287032_0 .000061035
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_SYSSTAT';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_SYSSTAT WRH$_SYSSTA_1404287032_0 1.0625
WRH$_SYSSTAT WRH$_SYSSTAT_MXDB_MXSN .000061035
SQL> alter table WRH$_SYSSTAT move partition WRH$_SYSSTA_1404287032_0;
Table altered.
SQL> alter table WRH$_SYSSTAT move partition WRH$_SYSSTAT_MXDB_MXSN;
Table altered.
SQL> select index_name from dba_indexes where table_name='WRH$_SYSSTAT';
INDEX_NAME
------------------------------
WRH$_SYSSTAT_PK
SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_LATCH_1404287032_0;
alter index WRH$_SYSSTAT_PK rebuild partition WRH$_LATCH_1404287032_0
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTA_1404287032_0;
Index altered.
SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTAT_MXDB_MXSN;
Index altered.
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_SYSSTAT';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_SYSSTAT WRH$_SYSSTA_1404287032_0 .000061035
WRH$_SYSSTAT WRH$_SYSSTAT_MXDB_MXSN .000061035
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1404287032_0 1.125
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH_MISSE_MXDB_MXSN .000061035
SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH__1404287032_0;
Index altered.
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1404287032_0 1.125
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH_MISSE_MXDB_MXSN .000061035
SQL> alter table WRH$_LATCH_MISSES_SUMMARY move partition WRH$_LATCH__1404287032_0;
Table altered.
SQL> alter table WRH$_LATCH_MISSES_SUMMARY move partition WRH$_LATCH_MISSE_MXDB_MXSN;
Table altered.
SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH__1404287032_0;
Index altered.
SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH_MISSE_MXDB_MXSN;
Index altered.
SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
SEGMENT_NAME PARTITION_NAME GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1404287032_0 .000061035
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH_MISSE_MXDB_MXSN .000061035
07--SYSTEM表空间回收
查询得知,SYSTEM 表空间大多被 AUD$ 占用,即 Oracle 数据库审计信息,对于审计信息,如无特殊需求,可以直接通过 truncate 即可回收 SYSTEM 表空间。相比 SYSAUX 而言,是不是简单很多。要注意,SYSTEM 表空间的日常备份,以及空间预警。
TRUNCATE TABLE AUD$;
08--扩展点--High Water Mark 水位线
在清理 SYSAUX 表空间时,发现通过 DBMS_WORKLOAD_REPOSITORY 包清理快照信息时,SYSAUX 表空间并未释放,这里涉及到一个 Oracle 数据库中一个关于 Segment(段)的关键概念---HWM(High Water Mark),段中用于表示已使用和未使用空间的边界。
##Oracle官方文档给出的简要解释
high water mark (HWM)
The boundary between used and unused space in a segment.
那么,在 Oracle 数据库中,Segment 是什么呢?Segment 是占用磁盘空间的一个对象,比如我们常见的Table(表)、表分区、Cluster(聚簇)、Index索引、索引分区、LOB分区、嵌套表、回滚段等,都是不同类型的Segment(段)。
##Oracle官方定义的Segment概念
segment
A set of extents allocated for a specific database object such as a table, index, or table cluster. User segments, undo segments, and temporary segments are all types of segments.
从Oracle9i开始,Oracle数据库推出了ASSM,详见ASSM官网说明,automatic segment space management,自动段空间管理。ASSM,链接列表freelist被位图所取代,它是一个二进制的数组,能够效地管理存储扩展和剩余区块(free block)。
高水位线就是数据块在一个段内历史周期内达到的最大的位置,高水位线只增不减,只升不降,直到这个segment对象被truncate,才会降低。这里这个高水位线对数据库性能的影响还是有的。为什么不会释放呢?
①空间利用率:
上述文中,如果不对表分区进行 MOVE,那个高水位线是不会降低的,删除的数据本质上是通过DELETE语句去删除的,但是这部分的空间(EXTEND)还是属于它原有的段(Segment),不能被其他对象所使用,在表空间无法自动扩展或者未开启自动扩展的情况下,这些空间(EXTEND)只能是原来所属表新增数据进行使用;如果在开启了自动扩展的表空间下,且未达到上限的表空间中,这部分的空间将被“遗忘”,无法被再次利用,这就会导致碎片化,DELETE后的空间是无法被其他对象使用的。
②性能影响:
在Oracle数据库中,全表扫描时,会对 HWM 高水位线以下的所有数据块进行扫描,虽然数据块中没有任何数据,但是也会一一进行扫描,这对资源是一种不必要的消耗,而且会对数据库的性能产生影响。
参考链接:
https://www.linuxidc.com/Linux/2017-12/149979.htm
https://www.cnblogs.com/acdante/archive/2018/07/04/9228457.html
每天进步一点,一年后的进步将会很大,远远大于 “1”;每天退步,即使退步一点点,一年后几乎退步为 "0"。如果本文对你有帮助,请多支持“在看”与转发。
Oracle 12c 及以上版本补丁更新说明及下载方法(收藏版)
Oracle 11.2.0.4 RAC 最新补丁下载(11.2.0.4.200714)
11g RAC 在线存储迁移实现 OCR 磁盘组完美替换
我的 OCM 之路|书写无悔青春,追梦永不止步
Oracle 19c 之多租户 PDB 连接与访问(三)
案例:RMAN 备份控制文件报错 ORA-00230
Oracle 12C 最新补丁下载与安装操作指北
DBA 常用的软件工具有哪些(分享篇)?
Oracle 相关认证证书查询及真伪辨别
Oracle 每日一题系列合集
点亮在看,你最好看!