system表空间不足的问题分析(二) (r8笔记第5天)
公众号：杨建荣的学习笔记 · 作者：杨建荣 · 发布：2016-02-10 23:45:50 · 原文链接
今天收到一条不太起眼的报警邮件，大体内容是某个表空间的空间有些紧张了。大体内容如下：
Tablesapce: CMBI_SNZG_DATA: 92.2% [Warning!]
根据这个信息，很明显是需要添加数据文件了，但是同时还有一个警告就是磁盘空间也告警了，那么这个看起来简单的问题得好好琢磨琢磨了，其实是几件事，一件 是做一些数据清理，释放部分表空间，甚至可以通过释放数据文件的空间来进一步释放磁盘空间，第二件是给表空间告警的表空间添加数据文件。
首先查看数据库中的用户占有的数据量的情况,可以看到占用率比较靠前的几个用户。而这次报警的是第三个用户CMBI_SNZG.
USERNAME Default TBS TEMP TBS CREATED Size (Mb)
—————————— ————— ————— ——————- ————
CMBI_MIN CMBI_MIN_DATA TEMP_NEW 2014-06-03 10:37:16 253,624
BIDATA BIDATA_DATA TEMP_NEW 2012-10-17 11:56:00 226,679
CMBI_SNZG CMBI_SNZG_DATA TEMP_NEW 2014-08-08 18:21:54 213,879
进一步分析发现，这个用户下占用表空间最多的是几个日志表。
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB
—————————— —————————— —————— ———-
CMBI_SNZG M_START_LOG TABLE 160
CMBI_SNZG IND_M_ONLINE_LOG INDEX 168
CMBI_SNZG IND_M_SDKSTART_LOG INDEX 520
CMBI_SNZG M_ONLINE_LOG TABLE 1472
CMBI_SNZG M_SDKSTART_LOG TABLE 3392
CMBI_SNZG IND_M_GAMEEVENT_LOG INDEX 22463
CMBI_SNZG M_GAMEEVENT_LOG TABLE 185619
对于这些日志表在统计系统中还是有一些保留时长，允许删除较早的历史数据，但是比较晕的这几个表都是普通表，没有做分区，那么删除意味值数据空间勉强释放，但是物理空间无法释放。而且删除的代价比较高。在这个时候还是需要和开发的同事做一些确认才可以清理或者整改+清理。
所以这个时候处理问题就看起来比较棘手了。看来原本的两种处理思路都没有奏效。那么还有什么空间呢。
其中一个亮点就是在最开始的时候，SYS用户占用的表空间竟然有40多个G,这个是很不正常的。凭着以往的经验，一般是aud$占用的空间过大导致SYSAUX过大。
表空间的具体使用数据如下：
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
SYSAUX OLN L S 64K 51,030 4,039 46,991 1,005 2147483645 8 *
SYSTEM OLN L S 64K 14,400 565 13,835 498 2147483645 4 *
那么这个场景中是不是aud$导致的呢，结果使用dba_segments过滤查询，发现竟然都是WRH$的一些基表。
OWNER SEGMENT_NAME SIZE_MB
——- ————————————- ———-
SYS WRH$_SQL_PLAN 54
SYS SYS_LOB0000006331C00004$$ 57
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 72
SYS WRH$_SQLSTAT 107
SYS WRM$_SNAPSHOT_DETAILS 234
SYS WRM$_SNAPSHOT_DETAILS_INDEX 280
SYS WRH$_LATCH_CHILDREN_PK 15950
SYS WRH$_LATCH_CHILDREN 28309
可以使用下面的语句进行一个简单的验证，发现大多数数据都来自于awr.
SQL> SELECT occupant_name “Item”,
2 space_usage_kbytes / 1048576 “Space Used (GB)”,
3 schema_name “Schema”,
4 move_procedure “Move Procedure”
5 FROM v$sysaux_occupants
6* ORDER BY 2 desc
SQL> /
Item pace Used (GB) Schema Move Procedure
———————————- ——– —————————————————————-
SM/AWR 45.0463257 SYS
SM/OPTSTAT .176513672 SYS
SM/ADVISOR .16973877 SYS
XDB .153869629 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SDO .07220459 MDSYS MDSYS.MOVE_SDO
EM .04486084 SYSMAN emd_maintenance.move_em_tblspc
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
31
那么占用空间较大的段是哪几个呢？发现都是清一色来自WRH$_LATCH_CHILDREN
SQL> SELECT *
2 FROM (SELECT SEGMENT_NAME,
3 PARTITION_NAME,
4 SEGMENT_TYPE,
5 BYTES/1024/1024
6 FROM DBA_SEGMENTS
7 WHERE TABLESPACE_NAME = ‘SYSAUX’
8 ORDER BY 4 DESC)
9* WHERE ROWNUM <= 10
SQL> /
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
—————————— —————————— —————— —————
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53068 TABLE PARTITION 3343
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53692 TABLE PARTITION 3046
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52588 TABLE PARTITION 3014
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52904 TABLE PARTITION 2895
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_54009 TABLE PARTITION 2891
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53864 TABLE PARTITION 2551
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53260 TABLE PARTITION 2540
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52760 TABLE PARTITION 2535
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53548 TABLE PARTITION 2530
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53404 TABLE PARTITION 2524
对于这部分数据可以做一些简单的解读，它的分区设置还是有一定的规律，分区会根据db_id和snap_id来组合生成对应的分区名称。
比如DB_ID为：
SQL> select DBID from v$database;
DBID
———-
723611991
对应的快照为：
DB_NAME BEGIN_SNAP END_SNAP SNAPDATE LVL DURATION_MINS DBTIME
——— ———- ———- —————————— ———- ————- ———-
BIDB 54009 54010 07 Feb 2016 00:00 2 29 1
54010 54011 07 Feb 2016 00:30 2 30 0
如果对于v$latch_children有一些影响，可以从statspack找到一些信息，statspack的帮助文档中是这么描述设置的收集信息的等级，level 6的部分有这么一段描述。
6.2. Time Units used for Performance Statistics
Oracle now supports capturing certain performance data with millisecond and
microsecond granularity.
Views which include microsecond timing include:
– v$session_wait, v$system_event, v$session_event (time_waited_micro column)
– v$sql, v$sqlarea (cpu_time, elapsed_time columns)
– v$latch, v$latch_parent, v$latch_children (wait_time column)
– v$sql_workarea, v$sql_workarea_active (active_time column)
其中v$latch_children赫然在列。那么这种情况可能是怎么造成的呢，这部分统计信息是不是过于详细了。可以通过参数statistics来做进一步验证。
SQL> show parameter statis
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
奇怪的是这个等级竟然不是默认的TYPICAL而是ALL,难怪这部分的信息占用的数据空间会格外大。
查看备库的设置信息，做一个简单的对比和验证。
SQL> show parameter statistics
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
所以这个问题就很显而易见了，就是收集统计信息的等级为ALL导致生成了大量的统计信息数据。可以把数据库修改为TYPICAL的模式就可以了。
SQL> alter system set statistics_level=typical;
System altered.
好了一个潜在的问题已经解决了，这部分的空间也可以手工删除，更多细节不再赘述。
对于AWR还有一些数据值得参考，那就是快照的生成频率和保留时长。目前的配置为：
SQL>select *from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- —————————— ————————————————————————— ———-
723611991 +00000 00:30:00.0 +00030 00:00:00.0 DEFAULT
可以适当缩短保留时长，生成快照的频率也可以降低。
exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>14*24*60);
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- —————————— ————————————————————————— ———-
723611991 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
所以通过这些设置可以释放出一部分宝贵的空间，当然也就可以转换为新增的数据文件大小了。最后把这些空间释放了之后，再添加一个数据文件，这个问题就暂时告一段落，至少可以在节后再进一步做更多的处理了。
关于第一篇，可以参见
system表空间不足的问题分析