数据库中大量对表smon_scn_time的delete操作

前段时间遇到一个问题，数据库中对表smon_scn_time有大量的delete操作，消耗了很高的CPU和内存资源。

问题现象：
从AWR报告中很明确会看到下面这个语句的资源消耗较高：
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0);
注意，上面这条语句是10.2.0.1 －10.2.0.4版本中的。
在10.2.0.5版本中的语句是下面的，有点不一样
delete from smon_scn_time where thread=0 and scn= (select min(scn) from smon_scn_time where thread=0);

问题原因分析：
参考官方文档：
High Executions Of Statement "delete from smon_scn_time..." (文档 ID 375401.1)
smon进程会定期清理smon_scn_time表中的数据，以便释放空间。正常情况下smon进程会每5分钟检查一次，如果这个表中的记录数正常不会超过144000条。每次smon进程会清理一条。如果一次清理不能释放足够的空间，smon会执行多次知道释放足够空间为止。

上面这条语句的含义是查找scn（或者time_mp）最小的那条记录，然后把这条记录从smon_scn_time表中删除，正常情况下，每次肯定会有一条记录被删除。而在这个问题中，我们手工执行该条delete SQL，会发现没有记录被删掉。很明显有问题。

按照官方文档提示的执行
analyze table smon_scn_time validate structure cascade;

并没有报出提示的错误，而是报资源忙。

但如果使用hint跳过索引去查询和执行的话，这条记录是存在的。
也就是说，确实如mos所说，表和索引的数据发生了不一致。


解决方法：
索引和表中的数据不一致，只能重建索引。参考mos提供的方法：
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;

但是也会碰到一个比较头疼的问题，删除和创建索引的时候，一致提示资源忙。

参考LOCK ON SYS.SMON_SCN_TIME (文档 ID 747745.1)设置12500事件也不行。

正好处理的时间是检修时间，可以重启数据库。因此重启数据库，在open数据库的时候，第一时间运行drop和create索引。重启了几次数据库，做这同样的操作才最终把问题处理完成。

同时也有个奇怪的现象。如果在数据库mount的状态下设置12500事件的话，当数据库open的时候，表smon_scn_time就会找不到。只能重新启动数据库才行。不知道这个是10g中在mount状态时desc视图的话，就会产生对象在buffer中状态异常的bug，还是因为12500事件的原因。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-2100141/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-2100141/