Today, I suddenly found an Oracle database server on the line the CPU ran high, feeling is not very normal, looked carefully, found that the Smon process ate a CPU.
So what's this smon process doing Daoteng?
Open 10046 to the Smon process.
Parsing trace files Just such an SQL statement, this thing is deleted 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---Line exclusive lock
Checked the MoS and found a document. LOCK on SYS. Smon_scn_time (document ID 747745.1)
The phenomenon of this fault:
The Smon process will add a lock to the smon_scn_time after the database is started and will never release
The root cause is inconsistent tables and indexes. Each delete record is 0, so the delete operation persists, in order to ensure that the record in the table is less than
The maximum mapping range value.
With the partitioning, OLAP and Data Mining options
[Email protected] 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
----Limit Smon Update smon_scn_time table
Sql> alter system set events ' 12500 Trace name context forever, Level 10 ';
System altered.
---manual cleanup of table records
Sql> Delete from Smon_scn_time;
2452 rows deleted.
Sql> commit;
Commit complete.
----Close Limit
Sql> alter system set events ' 12500 Trace name context off ';
System altered.
Left a doubt here?
Why are smon_scn_time tables and indexes inconsistent?
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Analysis of high CPU overhead anomaly in Smon process encounter
