欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

由Oracle 11g SYSAUX 和 SYSTEM 表空间回收引发的联想

程序员文章站 2022-06-29 16:49:53
0x00--目的 整理一下以前一个SYSTEM表空间和SYSAUX表空间使用率达到99%上限的处理思路和相关知识点,好记性不如烂笔头 0x01--表空间使用率现状 通过查询可得知目前表空间使用情况如下图:可看到SYSAUX表空间和SYSTEM表空间使用率都已经高达99%,SYSAUX表空间甚至只剩下 ......

0x00--目的

整理一下以前一个SYSTEM表空间和SYSAUX表空间使用率达到99%上限的处理思路和相关知识点,好记性不如烂笔头

0x01--表空间使用率现状

通过查询可得知目前表空间使用情况如下图:可看到SYSAUX表空间和SYSTEM表空间使用率都已经高达99%,SYSAUX表空间甚至只剩下6.19MB的空间空间。

可看出两张系统关键表空间的使用率已经不容乐观。急需找出占用空间的幕后凶手!并将之绳之以法

由Oracle 11g SYSAUX 和 SYSTEM 表空间回收引发的联想

0x02--AWRINFO脚本初步诊断

通过Oracle数据库自带的awrinfo脚本进行初步诊断,下边截取关键内容,可得到如下信息,快照过期信息是占用SYSAUX的元凶

由Oracle 11g SYSAUX 和 SYSTEM 表空间回收引发的联想

0x03--v$sysaux_occupants视图查询明细

进一步查询SYSAUX表空间使用详细情况:

 1 SQL> select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc where rownum <=20;
 2 
 3 OWNER                   SEGMENT_NAME                                     PARTITION_NAME         BYTES/1024/1024
 4 ------------------------------ --------------------------------------------------------------------------------- ------------------------------ ---------------
 5 SYS                   WRH$_ACTIVE_SESSION_HISTORY                             WRH$_ACTIVE_1404287032_0           7907
 6 SYS                   WRH$_EVENT_HISTOGRAM_PK                                 WRH$_EVENT__1404287032_0           3627
 7 SYS                   WRH$_EVENT_HISTOGRAM                                 WRH$_EVENT__1404287032_0           2560
 8 SYS                   WRH$_LATCH                                     WRH$_LATCH_1404287032_0           1600
 9 SYS                   WRH$_SYSSTAT_PK                                     WRH$_SYSSTA_1404287032_0           1472
10 SYS                   WRH$_LATCH_MISSES_SUMMARY_PK                             WRH$_LATCH__1404287032_0           1408
11 SYS                   WRH$_SQLSTAT                                     WRH$_SQLSTA_1404287032_0           1344
12 SYS                   WRH$_LATCH_PK                                     WRH$_LATCH_1404287032_0           1216
13 SYS                   WRH$_LATCH_MISSES_SUMMARY                             WRH$_LATCH__1404287032_0           1152
14 SYS                   WRH$_SYSSTAT                                     WRH$_SYSSTA_1404287032_0           1088
15 SYS                   WRH$_ACTIVE_SESSION_HISTORY_PK                             WRH$_ACTIVE_1404287032_0            935
16 SYS                   WRH$_PARAMETER_PK                                 WRH$_PARAME_1404287032_0            896
17 SYS                   WRH$_PARAMETER                                     WRH$_PARAME_1404287032_0            759
18 SYS                   WRH$_SEG_STAT                                     WRH$_SEG_ST_1404287032_0            712
19 SYS                   WRH$_SYSTEM_EVENT                                 WRH$_SYSTEM_1404287032_0            688
20 SYS                   WRH$_SYSTEM_EVENT_PK                                 WRH$_SYSTEM_1404287032_0            504
21 SYS                   WRH$_SERVICE_STAT_PK                                 WRH$_SERVIC_1404287032_0            408
22 SYS                   WRH$_SQLSTAT_PK                                     WRH$_SQLSTA_1404287032_0            352
23 SYS                   WRH$_DLM_MISC                                     WRH$_DLM_MI_1404287032_0            320
24 SYS                   WRH$_DLM_MISC_PK                                  WRH$_DLM_MI_1404287032_0            280
25 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基线。

1 select * from dba_hist_wr_control;

0x04-顺带AWR讲解

既然说到这里,就顺带一下AWR的日常操作:

AWR (Automatic Workload Repository)
  一堆历史性能数据,放在SYSAUX表空间上, AWR和SYSAUX都是10g出现的,是Oracle调优的关键特性; 大约1999年左右开始开发,已经有约20年历史
 主要是MMON(Manageability Monitor Process)和它的小工进程(m00x)来维护AWR。

 1 ##手动执行一个快照:
 2 Exec dbms_workload_repository.create_snapshot; 
 3 ##创建一个AWR基线 
 4 Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id,end_snap_id ,baseline_name);
 5 ##单节点AWR报告
 6 @?/rdbms/awrrpt.sql
 7 ##AWR比对报告 
 8 @?/rdbms/admin/awrddrpt 
 9 ##RAC 全局AWR
10 @?/rdbms/admin/awrgrpt
11 ##RAC中可选择节点生成AWR报告
12 @?/rdbms/admin/awrrpti

0x05--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数据,可以回收这部分空间。

1 SQL> select min(snap_id),max(snap_id) from  wrh$_active_session_history;
2 
3 MIN(SNAP_ID) MAX(SNAP_ID)
4 ------------ ------------
5        1        25444

通过DBMS_WORKLOAD_REPOSITORY包清理快照信息:

1 exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 18000);

2 PL/SQL procedure successfully completed.

但是,通过这种方式清理的AWR信息,再次查看SYSAUX表空间的空间,发现空间并没有被回收,使用率还和之前一样,这是因为清理AWR操作是通过DELETE操作实现的,表的水位线并没有下降导致的。但是通过再次查询WRH$_ACTIVE_SESSION_HISTORY可发现表记录已经少了。但是表大小还是没有变化。

由Oracle 11g SYSAUX 和 SYSTEM 表空间回收引发的联想

0x06--对分区进行MOVE操作,回收表空间

通过上边查询出来的SYSAUX表空间占用分布情况,将占用空间较大的对象进行MOVE操作,回收表空间。

   ①首先查看表的分区情况以及大小

1  select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';

  ②对分区表进行MOVE操作,回收空间

1 alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1404287032_0;
2 alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;

   ③MOVE后,重建分区表索引

1 ##查看分区表索引信息
2 select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
3 ##重建分区表索引
4 SQL> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
5 INDEX_NAME
6 ------------------------------
7 WRH$_EVENT_HISTOGRAM_PK
8 SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1404287032_0; 9 SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;

 

以下是操作参考:

  1 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';
  2 SEGMENT_NAME                                      PARTITION_NAME             GB
  3 --------------------------------------------------------------------------------- ------------------------------ ----------
  4 WRH$_EVENT_HISTOGRAM                                  WRH$_EVENT__1404287032_0        2.5
  5 WRH$_EVENT_HISTOGRAM                                  WRH$_EVENT_HISTO_MXDB_MXSN     .000061035
  6 SQL> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1404287032_0;
  7 
  8 Table altered.
  9 
 10 SQL> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;
 11 
 12 Table altered.
 13 SQL> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
 14 
 15 INDEX_NAME
 16 ------------------------------
 17 WRH$_EVENT_HISTOGRAM_PK
 18 
 19 SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1404287032_0;
 20 
 21 Index altered.
 22 
 23 SQL> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;
 24 
 25 Index altered.
 26 
 27 SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';
 28 
 29 SUM(BYTES)/1024/1024
 30 --------------------
 31         .125
 32 
 33 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH';
 34 
 35 SEGMENT_NAME                                      PARTITION_NAME             GB
 36 --------------------------------------------------------------------------------- ------------------------------ ----------
 37 WRH$_LATCH                                      WRH$_LATCH_MXDB_MXSN         .000061035
 38 WRH$_LATCH                                      WRH$_LATCH_1404287032_0         1.5625
 39 
 40 SQL> alter table WRH$_LATCH move partition WRH$_LATCH_1404287032_0;
 41 
 42 Table altered.
 43 
 44 SQL> alter table WRH$_LATCH move partition WRH$_LATCH_MXDB_MXSN;
 45 
 46 Table altered.
 47 
 48 SQL> select index_name from dba_indexes where table_name='WRH$_LATCH';
 49 
 50 INDEX_NAME
 51 ------------------------------
 52 WRH$_LATCH_PK
 53 
 54 SQL> alter index WRH$_LATCH_PK rebuild partition WRH$_LATCH_1404287032_0;
 55 
 56 Index altered.
 57 
 58 SQL> alter index WRH$_LATCH_PK rebuild partition WRH$_LATCH_MXDB_MXSN;
 59 
 60 Index altered.
 61 
 62 SQL>  select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH';
 63 
 64 SEGMENT_NAME                                      PARTITION_NAME             GB
 65 --------------------------------------------------------------------------------- ------------------------------ ----------
 66 WRH$_LATCH                                      WRH$_LATCH_MXDB_MXSN         .000061035
 67 WRH$_LATCH                                      WRH$_LATCH_1404287032_0     .000061035
 68 
 69 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_SYSSTAT';
 70 
 71 SEGMENT_NAME                                      PARTITION_NAME             GB
 72 --------------------------------------------------------------------------------- ------------------------------ ----------
 73 WRH$_SYSSTAT                                      WRH$_SYSSTA_1404287032_0         1.0625
 74 WRH$_SYSSTAT                                      WRH$_SYSSTAT_MXDB_MXSN     .000061035
 75 
 76 SQL> alter table WRH$_SYSSTAT move partition WRH$_SYSSTA_1404287032_0;
 77 
 78 Table altered.
 79 
 80 SQL> alter table WRH$_SYSSTAT move partition WRH$_SYSSTAT_MXDB_MXSN;
 81 
 82 Table altered.
 83 
 84 SQL> select index_name from dba_indexes where table_name='WRH$_SYSSTAT';
 85 
 86 INDEX_NAME
 87 ------------------------------
 88 WRH$_SYSSTAT_PK
 89 
 90 SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_LATCH_1404287032_0;
 91 alter index WRH$_SYSSTAT_PK rebuild partition WRH$_LATCH_1404287032_0
 92                                               *
 93 ERROR at line 1:
 94 ORA-02149: Specified partition does not exist
 95 
 96 
 97 SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTA_1404287032_0;
 98 
 99 Index altered.
100 
101 SQL> alter index WRH$_SYSSTAT_PK rebuild partition WRH$_SYSSTAT_MXDB_MXSN;
102 
103 Index altered.
104 
105 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_SYSSTAT';
106 
107 SEGMENT_NAME                                      PARTITION_NAME             GB
108 --------------------------------------------------------------------------------- ------------------------------ ----------
109 WRH$_SYSSTAT                                      WRH$_SYSSTA_1404287032_0     .000061035
110 WRH$_SYSSTAT                                      WRH$_SYSSTAT_MXDB_MXSN     .000061035
111 
112 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
113 
114 SEGMENT_NAME                                      PARTITION_NAME             GB
115 --------------------------------------------------------------------------------- ------------------------------ ----------
116 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH__1404287032_0          1.125
117 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH_MISSE_MXDB_MXSN     .000061035
118 
119 SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH__1404287032_0;
120 
121 Index altered.
122 
123 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
124 
125 SEGMENT_NAME                                      PARTITION_NAME             GB
126 --------------------------------------------------------------------------------- ------------------------------ ----------
127 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH__1404287032_0          1.125
128 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH_MISSE_MXDB_MXSN     .000061035
129 
130 SQL> alter table WRH$_LATCH_MISSES_SUMMARY move partition WRH$_LATCH__1404287032_0;
131 
132 Table altered.
133 
134 SQL> alter table WRH$_LATCH_MISSES_SUMMARY move partition WRH$_LATCH_MISSE_MXDB_MXSN;
135 
136 Table altered.
137 
138 SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH__1404287032_0;
139 
140 Index altered.
141 
142 SQL> alter index WRH$_LATCH_MISSES_SUMMARY_PK rebuild partition WRH$_LATCH_MISSE_MXDB_MXSN;
143 
144 Index altered.
145 
146 SQL> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_LATCH_MISSES_SUMMARY';
147 
148 SEGMENT_NAME                                      PARTITION_NAME             GB
149 --------------------------------------------------------------------------------- ------------------------------ ----------
150 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH__1404287032_0     .000061035
151 WRH$_LATCH_MISSES_SUMMARY                              WRH$_LATCH_MISSE_MXDB_MXSN     .000061035

0x07--SYSTEM表空间回收

查询得知,SYSTEM表空间大多被AUD$占用,即Oracle数据库审计信息,对于审计信息,如无特殊需求,可以直接通过truncate即可回收SYSTEM表空间。相比SYSAUX而言,是不是简单很多。要注意,SYSTEM表空间的日常备份,以及空间预警。

1 TRUNCATE TABLE AUD$;

0x08--扩展点--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数据库中,段空间的管理方式是MSSM--manual segment space management,手动段空间管理,段空间的观念里和分配需要调整很多参数进行管理,非常之繁琐。比如FREELISTS等参数,关键是这些参数配置正确合理与否,对Oracle数据库的性能影响非常之大。表的剩余空间的管理与分配都是由链接列表freelist来完成的,因为freelist存在串行的问题因此容易引起往往容易引起段头的争用与空间的浪费,最主要的还是因为需要人为的 花费大量的精力和时间去管理这些争用并监控表的空间利用。

    从Oracle9i开始,Oracle数据库推出了ASSM,详见,automatic segment space management,自动段空间管理。ASSM,链接列表freelist被位图所取代,它是一个二进制的数组,能够效地管理存储扩展和剩余区块(free block)。

   高水位线就是数据块在一个段内历史周期内达到的最大的位置,高水位线只增不减,只升不降,直到这个segment对象被truncate,才会降低。这里这个高水位线对数据库性能的影响还是有的。为什么不会释放呢?

     ①空间利用率:

上述文中,如果不对表分区进行MOVE,那个高水位线是不会降低的,删除的数据本质上是通过DELETE语句去删除的,但是这部分的空间(EXTEND)还是属于它原有的段(Segment),不能被其他对象所使用,在表空间无法自动扩展或者未开启自动扩展的情况下,这些空间(EXTEND)只能是原来所属表新增数据进行使用;如果在开启了自动扩展的表空间下,且未达到上限的表空间中,这部分的空间将被“遗忘”,无法被再次利用,这就会导致碎片化,DELETE后的空间是无法被其他对象使用的。

     ②性能影响:

在Oracle数据库中,全表扫描时,会对HWM高水位线以下的所有数据块进行扫描,虽然数据块中没有任何数据,但是也会一一进行扫描,这对资源是一种不必要的消耗,而且会对数据库的性能产生影响。

再下去就要说说行迁移、行链接以及ASSM相关参数,PCTFREE,PCTUSED,BUFFER_POOL等等,之后再详细记录吧。