表空间暴涨原因核查

 

2014年6月25号客户的users表空间暴涨了900G,经过查询系统监控记录,找到了相关的sql语句和责任人,具体过程如下:

这里需要先说明一个情况,由于之前users表空间使用率达到了99%后,由于使用的是bigfile,无法添加文件,只好把自动扩展参数打开,并且设置了每次扩展20G,这里注意一下,如果设置过小会使很多会话发生buffer busy waits 等待事件,但是设置这么大有个缺点就是如果sql语句出现笛卡儿积的话就会是表空间迅速暴涨,这里的这个例子就是这种情况下的一种。

 

 

第一步,首先查看了下Users  表空间增长历史记录,具体截图如下,确定了users表空间增长的时间范围是在 6月25号下午14点到6月25号晚上23点:

表空间暴涨原因核查_表空间

第二,从6月25号下午14点到 晚上23点开始,查看了下具体段的增长情况,发现users表空间有一个XXXXXX(这里屏蔽掉)用户下的临时段持续增长,涨了859G,由临时段的名称看以看出都是一个段,且位于4号文件的705052090块,可以推断出是由于某一个错误sql导致的,而4号文件刚好就是users表空间,而临时段主要是由2种方式来生成:① 重建索引生成 ② 通过CTAS方式建表形成  ,重建索引不可能,因为没有哪个索引的大小达到800G,所以只可能是哪个用户通过CTAS的方式建表导致的,而且在23点监控不到这个临时段了,可能表已经建成或者建表语句报错后临时段释放了。

大段的监控历史截图:

表空间暴涨原因核查_表空间_02

 

第三,仔细分析了下出现问题的时间段内DDL语句的监控,发现了一个错误记录,如下图,由此说明了是临时段达到了最大值sql语句报错了,所以空间释放了,这里我们可以看出当时的会话的sid是1567,登录的terminal的ip地址为10.31.6.61,具体同事是  XXXXXX (这里屏蔽掉)

表空间暴涨原因核查_表空间_03

表空间暴涨原因核查_表空间_04

 

第四,通过sid和serial#查看当时具体的sql监控,截图如下,由图看出该sql是从25号中午11点35分30秒开始运行,一直运行了12小时17分钟后报错,这个也和users表空间增长的时间范围相符

表空间暴涨原因核查_表空间_05

表空间暴涨原因核查_表空间_06

 

第五,把该sql拿出来看了下执行计划和sql语句,发现该执行计划的cost花费和预估的返回行数都超级大:

 

Sql语句(这里只列出出现问题的地方):

create table G_TX_DB_LABEL_base_4 NOLOGGING AS

SELECT 。。。。。。。。。。。。。

FROM   G_TX_DB_LABEL_1 a

LEFT   JOIN G_TX_DB_LABEL_2_comp b

ON     a.单位名称 = a.单位名称

LEFT   JOIN G_TX_DB_LABEL_2_comp_1 C

ON     a.单位名称 = a.单位名称 ;

 

很显然,,,,,,,, 连接条件写错了

Sql执行计划,cost和rows都非常的恐怖呀。。。。。。。。:

表空间暴涨原因核查_表空间_07

 表空间暴涨原因核查_表空间_08

 

由此可以看出空间暴涨的原因是该sql最后的3张表的连接条件无效导致的,我把该sql拿出来重新执行了下发现短短1分钟内临时段涨了2G多,至此可以肯定导致6月25号空间暴涨的sql就是这个了

 

表空间暴涨原因核查_表空间_09

 

 

最后,我给出的一些建议,建议充分利用一下我们的监控系统:

  1. 加入笛卡儿积的监控,每隔20分钟监控一次
  2. 增加对执行了5个小时以上的sql的监控
  3. 增加对执行计划中预估的行数以及cost花费超大的sql的监控(例如本例中的sql语句)
  4. 对统计信息有误的表的监控(如表实际有200W行,但是统计信息中的num_rows为0 ,这种可能会出现笛卡儿积的连接)
  5. 对数据库中的分区表全分区扫描的sql监控