LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]

LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
	Modified 27-NOV-2008Type PROBLEMStatus MODERATED

In this Document
Symptoms
Cause
Solution

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 10.2.0.4
This problem can occur on any platform.

Symptoms

As soon as customer starts up the database, a lock is put on
SYS.SMON_SCN_TIME by SMON and it never go away.

Database Performance becomes slow.

SMON_SCN_TIME has huge no.of records.

SQL> select count(*) from sys.smon_scn_time;

COUNT(*)
----------
137545

1 row selected.

It is found that the object has been locked.

SQL> select object_id from dba_objects where object_name = 'SMON_SCN_TIME';

OBJECT_ID
----------
575

1 row selected.

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
-----------
5 5 1494 575 164
dbadmin 4444350
3 <= Locked in row exclusive mode

Cause

From the systemstate dump, it is seen that SMON process is doing some delete operation on that table.

Systemstate dump
~~~~~~~~~~~~~~~~~
PROCESS 8:
----------------------------------------
SO: 70000001fe572b0, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 70000001ff98ea0/70000001ff95f68, flag: (16) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 112
last post received-location: kcbzww
last process to post me: 70000001fe59230 2 0
last post sent: 0 0 112
last post sent-location: kcbzww
last process posted by me: 70000001fe59230 2 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 70000001fe9dd18
O/S info: user: dbadmin, term: UNKNOWN, ospid: 3367182
OSD pid info: Unix process pid: 3367182, image: oracle@dwic501 (SMON)
Dump of memory from 0x070000001FE41340 to 0x070000001FE41548
....
....
LIBRARY OBJECT HANDLE: handle=70000001fa60b38 mtx=70000001fa60c68(1) cdp=1
name=delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_scn_time
where thread=0)

What happens here is due to the inconsistency between the table and indexes. The delete returns
zero rows; so the delete is executed continuously to reduce the smon_scn_time below the maximum
mappings.

Because of this the database performance could become slow especially the gather_stats_job or any statistics collection.

Solution

To delete the records from SMON_SCN_TIME manually.

Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.

This should allow you to check the content of the table (count(*) for number of rows etc, analyze
validate to confirm if it is corrupt or not, plus check the actual row content in case there are any
timestamps in the
table in the future).

The content of this table just maintains a rough mapping between timestamps and SCN values
so if there are excess rows or rows in the future then you can delete rows from the table manually
to get back to a sensible start point.

The SMON time mapping is mainly for flashback type queries to map a time to an SCN so it is probably
simplest to copy the content to a holding table then delete ALL rows, then recycle the instance.
SMON should start to populate the table with new time / SCN pairs from the time that the instance
is started

SQL> conn / as sysdba

/* Set the event at system level */

SQL> alter system set events '12500 trace name context forever, level 10';

/* Delete the records from SMON_SCN_TIME */

SQL> delete from smon_scn_time;

SQL> commit;

SQL> alter system set events '12500 trace name context off';

Now restart the instance.

---

------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请