切换temp表空间引起大量enqueue

下面这个案例告诉我们,在一般的情况下,不要轻易的尝试在生产系统上切换temp表空间。如果真的要切换,还是找一个停机时间,关掉侦听,安安静静的做切换。

事情的起因是这样的,某地的数据库由于业务异常,temp表空间从67G一下子增长到142G,造成在文件系统的空间使用紧张,因此我们决定用切换temp表空间的方式新建一个temp表空间。具体的操作步骤大致为:
(1)新建temp2临时表空间,将系统的默认临时表空间切换到temp2;
(2)等待temp上所有的事务完成;
(3)删除temp表空间及其tempfile,释放文件系统空间
(4)重新再建一个67G的临时表空间temp
(5)将系统默认temp2表空间切换到temp;
(6)待temp2上完成事务后,删除temp2表空间和tempfile。
上述操作在进行到第三步的时候,由于事务太多,temp表空间一直在被使用中,drop tablespace的操作一直无法完成,处于的等待事件一直是smon timer,等了差不多到了晚上,还没完成,于是同事就先中断掉该操作。

第二天观察temp的表空间的使用情况,temp表空间的使用率为0,但是查sort usage中还是有10多个sid在使用temp,其余大部分的session已经在使用temp2.

由于temp2的表空间比较小,因此晚上跑应用的批量作业的时候,报错ora-1652 temp表空间无法扩展了。因此为了能让应用顺利跑完成,我们将临时表空间再次切换回temp。

问题发生了,应用的人来报告不少事务无法完成,很多任务都挂起完成不了,于是赶紧登录数据库检查情况,发现了很多enqueue等待:

先看看这些enqueue的sid:

为了查明enqueue的情况,做一个hanganalyze:

进一步去看trace文件:

从trace 文件可以看到,在Chain 1部分,sid为166和31的session已经被sid为7的session hold住。而166和31的session即为enqueue的等待。

另外我们再挑一个enqueue的sid,看看被什么堵塞,如sid 127的,我们看到sid 127被165行,即sid为166的堵塞,而166是被sid为7的session堵塞。

其他类似的enqueue session我们最终都能归结到sid 为7的堵塞上去。

而sid为7的session是smon进程,等待是smon timer,从smon的功能上去理解,smon进程正在清理temp中的临时数据,我们需要等待smon进程清理干净。

正常情况下,我们可以直接把hang的源头的session杀掉,就可解决enqueue的问题,但是由于源头是smon,直接杀进程将导致数据库crash,而等待smon完全清理完还需要未知的很多时间,会影响到业务的正常操作,为了尽快解决问题,我们在这里选择的处理方式是重启数据库。

重启完数据库后,enqueue消失。

相关文章

6条评论

  1. 最后通过重启解决的啊??

    分享一个我处理过的类似案例:

    enqueue TS的一次解决

    客户反应今天系统特别慢,什么操作都很慢,有时候能查询,但是没法保存和修改,而且慢从早上就开始了

    检查系统当前的等待事件,发现出现一大堆的enqueue,大概有百来个,主要的enqueue有TS和SS,以及TX等

    出现这种情况要找少见的锁来先处理,因为别的锁可能是TS锁导致的,尤其是SS enqueue,于是从TS锁开始着手,TS enqueue和表空间有关,可能是Temp segement或者是一般的表空间,id2如果是0那么就是temp segment,如果id2=1那么就是数据表空间,既然SS enqueue也存在,那么这里的TS肯定是temp segment了,检查TS enqueue的holder,检查发现锁的拥有者是smon(做了hanganalyze后发现active chain里源头也是smon),同时smon的等待事件是smon timer,说明了smon并没有干活,而是空闲等待,那为什么smon获得锁后不释放呢,因此怀疑数据库可能报了什么错,从alert.log检查,发现后台报了一大堆的checkpoint not complete(不要被这个迷惑,但是它也是个结果并非原因,可能是dbwr也在wait,所以导致checkpoint hold了,同时检查v$log发现并非所有的log都是active的还有很多是inactive的,因此就算加log意义也不大),因此继续往前看日志,发现之前有人做了删除临时表空间的操作,但是并没有成功(可能是wait的时间太长)所以取消了,因此基本上思路已经明确

    可能是在删除临时表空间的时候,因为有人获得了ts enqueue(检查v$sort_usage),因此smon必须在等待,而之后ts释放了,smon获得了锁,然后前台取消了删除临时表空间的操作,而smon获得锁并没有被释放,导致session hang住

    检查v$tempfile发现已经有了新加的临时表空间了,同时检查v$sort_usage,发现有部分session在使用旧的临时表空间,因此就修改数据库的默认临时表空间,同时kill掉使用之前临时表空间的session,让他们都去使用新的临时表空间,这样就不受smon获取之前临时表空间TS enqueue的影响,操作完这部分后,发现enqueue TS 和enqueue SS全部消失,同时TX也少了很多

  2. re maclean:是9i 的。
    re performance:谢谢分享!我的情况和你也比较类似,上述的enqueue通过解码都是ss类型的,之前也有尝试删除temp但是中断的情况。
    由于业务逻辑的关系,我们不敢也不能杀那些在旧的temp表空间的进程,从而让他们去用新的temp。我们也不敢杀那些新的temp上的session,从而他们去再次切回去用老的temp。

  3. 前几天,遇到这样一个问题,数据库的oracle进程非常多,但是alert没有报错误,系统的资源情况也正常,无论是内存,CPU等.sqlplus登录没有反应,请问这种情况如何判断呢?

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据