system表空间不足的问题分析(二)-阿里云开发者社区

开发者社区> jeanron100> 正文

system表空间不足的问题分析(二)

简介: 今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下:Tablesapce: CMBI_SNZG_DATA: 92.2%  [Warning!] 根据这个信息,很明显是需要添加数据文件了,但是同时还有一个警告就是磁盘空间也告警了,那么这个看起来简单的问题得好好琢磨琢磨了,其实是几件事,一件是做一些数据清理,释放部分表空间,甚至可以通过释放数据文件的空间来进一步释放磁盘空间,第二件是给表空间告警的表空间添加数据文件。
+关注继续查看
今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下:
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
CMBI_QSMY2                     CMBI_QSMY2_DATA TEMP_NEW        2014-06-27 17:59:21      207,010
CMBI_TEST                     CMBI_TLBB_DATA  TEMP_NEW        2014-06-11 22:54:29      118,653
CMBI_QSMYQZ                    CMBI_QSMYQZ_DAT TEMP_NEW        2014-06-27 18:01:34       82,681
SYS                            SYSTEM          TEMP_NEW        2011-09-17 09:46:22       46,608
CMBI_DTLM                      CMBI_DTLM_DATA  TEMP_NEW        2014-06-11 10:09:10       21,919
进一步分析发现,这个用户下占用表空间最多的是几个日志表。
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表空间不足的问题分析 http://blog.itpub.net/23718752/viewspace-1805645/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9485 0
RocketMQ 消息发送system busy、broker busy原因分析与解决方案
1、现象 最近收到很多RocketMQ使用者,反馈生产环境中在消息发送过程中偶尔会出现如下4个错误信息之一:1)[REJECTREQUEST]system busy, start flow control for a while2)too many requests and system thre.
1652 0
[PeterDLax著泛函分析习题参考解答]第7章 Hilbert 空间结果的应用
1. 对测度是 $\sigma$ 有限的情形证明 Radon-Nikodym 定理.     证明: 设 $\mu,\nu$ 均为 $\sigma$ 有限的非负测度, 则存在分割 $$\bex X=\cup_{i=1}^\infty X_i=\cup_{j=1}^\infty Y_j \eex...
458 0
【巡检问题分析与最佳实践】MongoDB 空间使用问题
阿里云数据库MongoDB的空间使用率是一个非常重要的监控指标,如果实例的存储空间完全打满,将会直接导致实例不可用。一般来说,当一个MongoDB实例的存储空间使用比例达到80-85%以上时,就应及时进行处理,要么降低数据库实际占用空间的大小,要么对存储空间进行扩容,以避免空间打满的风险。 然而,阿里云数据库MongoDB的空间使用情况分析并不简单,本文将由浅入深帮您查看,分析和优化云数据库MongoDB的空间使用。
238 0
[PeterDLax著泛函分析习题参考解答]第6章 Hilbert 空间
1. 证明满足 (6) 的范数可以由一个内积诱导出来. 这个结论属于 von Neumann.   证明: 以实线性空间为例, 取内积 $$\bex \sex{x,y}=\cfrac{1}{4}[\sen{x+y}^2-\sen{x-y}^2], \eex$$ 则 $\sex{x,y}$ 为内积, 且 $\sex{x,x}^\frac{1}{2}=\sen{x}$.
591 0
Observability:使用 Elastic Stack 分析地理空间数据
在今天的文章中,我们将参考之前的文章 “如何使用 Elasticsearch ingest 节点来丰富日志和指标”。我们可以利用 Elasticsearch ingest 节点来更加丰富我们的数据,并对这些数据做更进一步的的分析。
1272 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13167 0
《网络空间欺骗:构筑欺骗防御的科学基石》一3.3.3 现场分析
本文讲的是网络空间欺骗:构筑欺骗防御的科学基石一3.3.3 现场分析,本节书摘来华章计算机《网络空间欺骗:构筑欺骗防御的科学基石》一书中的第3章,第3.3.3节, Cyber Deception: Building the Scientific Foundation 苏西尔·贾乔迪亚(Sushil Jajodia)V. S.苏夫拉曼尼(V. S. Subrahmanian)[美] 维平·斯沃尔(Vipin Swarup) 著 克利夫·王(Cliff Wang) 马多贺 雷程 译 译更多章节内容可以访问云栖社区“华章计算机”公众号查看。
882 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
1180
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载