LOCK ON SYS.SMON_SCN_TIME [ID 747745.1] | ||
| ||
| Modified 27-NOV-2008 Type PROBLEM Status MODERATED |
|
In this Document
This document is being delivered to you via Oracle Support's (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.4This 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表空间和数据文件的关系,否则拒绝申请