SMON process consumes 100 % CPU

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 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: