Excessive Deletes From SMON_SCN_TIME Error

Excessive Deletes From SMON_SCN_TIME

by

It’s Always Friday Afternoon!

I got a support call late last Friday afternoon (why do most calls seem to come on a Friday afternoon just before you finish for the weekend?) about bad performance on one of our applications we host for a customer.

When I logged into the server and took a look at what it was doing, the CPU was getting hammered by the Oracle SMON process.

The Problem – Excessive Deletes From SMON_SCN_TIME

As it was just past the hour, I generated an AWR report for the previous hour and I could immediately see the problem – the following SQL statement had been executed over 40,000 times and was responsible for the vast majority of DB Time:

delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_scn_time where thread=0)

A quick check on Metalink (Oracle’s support site) turned up document ID 375401.1 High Executions Of Statement “delete from smon_scn_time…”

According to this document the problem affects database versions from 10.1.0.2 to 10.2.0.4 and is described as follows:

The delete statement deletes the oldest rows from smon_scn_time to clear space for new rows.  SMON wakes up every 5 minutes and checks how many on-disk mappings we have–the max is 144000.

The new mappings are then added for the last period (since SMON last updated), and if this is over 144000, SMON will then issue the delete statement:

delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)

There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does not free enough mappings, then there will be multiple executions.

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

Validating the Problem

You can check the inconsistency by validating the structure from within SQL*Plus:

SQL> analyze table smon_scn_time validate structure cascade;

When I did this check, I got the following error:

SQL> analyze table smon_scn_time validate structure cascade;

analyze table smon_scn_time validate structure cascade
*
ERROR at line 1 :
ORA-01499: table/Index Cross Reference Failure – see trace file

The Solution

It did indeed look as though this was the cause of my issue so I followed the steps recommended by Oracle Support to fix it:

  1. Make a backup in case of problems
  2. Drop index smon_scn_time_scn_idx;
  3. Drop index smon_scn_time_tim_idx;
  4. Create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
  5. Create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);

After doing this, performance returned to previous normal levels and just to check, I analyzed the table smon_scn_time again – this time no errors were returned.

So if you suddenly find you’re suffering performance problems due to excessive deletes from smon_scn_time, follow the steps above to fix it.

Leave a Comment