今天突然发现线上一台oracle
数据库 服务器cpu 跑的很高，感觉不是很正常，仔细看了下；发现是smon 进程吃掉了一个cpu。
那么这个smon 进程到底在倒腾啥玩意
对smon 进程开启10046 跟下不就全明了了么
分析trace 文件就这么一个sql语句 ，这玩意在删smon_scn_time
delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_scn_time where thread=0)
SQL> select count (*) from sys.smon_scn_time;
COUNT(*)
----------
57123
SQL> select object_id from dba_objects where object_name = 'SMON_SCN_TIME';
OBJECT_ID
----------
575
SQL> select * from v$locked_object where object_id = 575;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
LOCKED_MODE
-----------
27 15 33429 575 164
oracle 30801
3---行独占锁
查了下mos，找到一个文档 LOCK ON SYS.SMON_SCN_TIME (文档 ID 747745.1)
本故障的现象：
smon 进程在数据库一启动后就会对smon_scn_time 加一个锁，并且永远都不会释放
根本原因是表和索引不一致。每次删除记录为0条，因此删除操作会一直持续，为了保证表中记录小于
最大映射范围值。
With the Partitioning, OLAP and Data Mining options
[oracle@lixora adump]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jul 15 09:25:26 2015
Copyright (c) 1982, 2006,
Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
----限制smon更新 smon_scn_time 表
SQL> alter system set events '12500 trace name context forever, level 10 ';
System altered.
---手动清理表记录
SQL> delete from smon_scn_time;
2452 rows deleted.
SQL> commit;
Commit complete.
----关闭限制
SQL> alter system set events '12500 trace name context off ';
System altered.
这里留了一个疑惑？
为什么smon_scn_time 表和索引会不一致呢？
|