oracle表空间暴涨,表空间暴涨原因核查

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点监控不到这个临时段了，可能表已经建成或者建表语句报错后临时段释放了。

大段的监控历史截图：

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

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

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

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

createtableG_TX_DB_LABEL_base_4 NOLOGGINGAS

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

FROMG_TX_DB_LABEL_1 a

LEFTJOING_TX_DB_LABEL_2_comp b

ONa.单位名称= a.单位名称

LEFTJOING_TX_DB_LABEL_2_comp_1 C

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

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

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

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

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

加入笛卡儿积的监控，每隔20分钟监控一次

增加对执行了5个小时以上的sql的监控

增加对执行计划中预估的行数以及cost花费超大的sql的监控(例如本例中的sql语句)

对统计信息有误的表的监控(如表实际有200W行，但是统计信息中的num_rows为0 ，这种可能会出现笛卡儿积的连接)

对数据库中的分区表全分区扫描的sql监控