由Oracle 11g SYSAUX 和 SYSTEM 表空间回收引发的联想
0x00--目的
整理一下以前一个SYSTEM表空间和SYSAUX表空间使用率达到99%上限的处理思路和相关知识点，好记性不如烂笔头
0x01--表空间使用率现状
通过查询可得知目前表空间使用情况如下图：可看到SYSAUX表空间和SYSTEM表空间使用率都已经高达99%，SYSAUX表空间甚至只剩下6.19MB的空间空间。
可看出两张系统关键表空间的使用率已经不容乐观。急需找出占用空间的幕后凶手！并将之绳之以法
0x02--AWRINFO脚本初步诊断
通过Oracle数据库自带的awrinfo脚本进行初步诊断，下边截取关键内容，可得到如下信息，快照过期信息是占用SYSAUX的元凶
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可发现表记录已经少了。但是表大小还是没有变化。
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等等，之后再详细记录吧。
下一篇： 以小打大，知乎是如何站在“问答”之巅