system表空间不足的问题分析(二) (r8笔记第5天)

今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下: 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表空间不足的问题分析

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-02-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏学习有记

包含列的索引:SQL Server索引进阶 Level 5

1502
来自专栏漫漫全栈路

Oracle数据库项目——MC服务器管理系统

数据库设计内容,分为:系统功能说明,数据库分析与设计,数据库实现。本数据库系统提供的功能用于项目MC-Servers-Web,详情内容参见MC-Service...

7374
来自专栏杨建荣的学习笔记

一条看似平常的报警邮件所做的分析(r8笔记第9天)

今天留意到一封报警邮件。内容如下: ZABBIX-监控系统: ------------------------------------ 报警内容: CPU u...

2584
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(71)-微信公众平台开发-公众号管理

思维导图 下面我们来看一个思维导图,这样就可以更快了解所需要的功能: ? 上一节我们利用了一个简单的代码例子,完成了与微信公众号的对话(给公众号发一条信息...

4377
来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

3958
来自专栏乐沙弥的世界

启用 Oracle 10046 调试事件

    Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_t...

542
来自专栏漫漫全栈路

Oracle 学习笔记

前言 本贴内容纪录Oracle课程中的学习笔记,和Oracle的课后作业,以及数据库相关课程的学习笔记,笔记部分使用实例代码记录,不记详细语法。 用户管理...

3555
来自专栏杨建荣的学习笔记

物化视图刷新的问题及分析(61天)

最近现场需要搭建一套全新的环境,对于数据字典的管理采用了物化视图,因为数据量不大,采用了全量刷新的方式。因为有好几套环境,有几套环境是通过db link和主节点...

4287
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1333
来自专栏杨建荣的学习笔记

手工创建/删除数据库的步骤

今天和大家分享下数据库的创建和删除的步骤,里面有很多细节需要大家考虑。创建数据库不只是一个create database语句。删除数据库 drop databa...

3256

扫码关注云+社区

领取腾讯云代金券