Oracle smon_scn_time 表说明

一．SMON_SCN_TIME 表结构说明

SMON_SCN_TIME表存放的是SCN和Time之前的映射关系。该表由SMON 进程负责维护。

SQL> desc smon_scn_time

Name Null? Type

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

THREAD NUMBER

TIME_MP NUMBER

TIME_DP DATE

SCN_WRP NUMBER

SCN_BAS NUMBER

NUM_MAPPINGS NUMBER

TIM_SCN_MAP RAW(1200)

SCN NUMBER

ORIG_THREAD NUMBER

SQL> alter session set nls_date_format='yyyy-mm-ddhh24:mi:ss';

Session altered.

SQL> select time_dp,scn from smon_scn_time where rownum<5;

TIME_DP SCN

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

2013-03-15 10:31:04 2092348

2013-03-15 10:35:49 2092452

2013-03-15 10:41:00 2092581

2013-03-15 10:45:46 2092682

在Oracle 11g中，该表的创建SQL在$ORACLE_HOME/rdbms/admin/dtxnspc.bsq 文件中。

create table smon_scn_time (

thread number, /* thread, compatibility */

time_mp number, /* time this recent scn represents */

time_dp date, /* time as date, compatibility */

scn_wrpnumber, /*scn.wrp, compatibility */

scn_bas number, /* scn.bas, compatibility */

num_mappings number,

tim_scn_map raw(1200),

scnnumber default 0, /* scn*/

orig_thread number default 0 /* for downgrade */

) cluster smon_scn_to_time_aux (thread)

create unique index smon_scn_time_tim_idxon smon_scn_time(time_mp)

tablespace SYSAUX

create unique index smon_scn_time_scn_idxon smon_scn_time(scn)

tablespace SYSAUX

我们可以直接delete掉SMON_SCN_TIME表中的记录：

SQL> delete from smon_scn_time;

2120 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from smon_scn_time;

COUNT(1)

----------

二．SMON_SCN_TIME表记录保存策略说明

2.1 Oracle 9i

根据MOS文档的说明：

How To Map SCN To Timestamp Before 10g? [ID365536.1]

SYS.SMON_SCN_TIMEwill have a maximum of 1440 rows and each record will be for a 5 minute period.Oracle maintains this information for a maximum of 5 days after which therecords will be recycled.

This means thatdata is stored 12 times per hour * 24 hours * 5 days = 1440 rows.

在Oracle 9i版本中，SMON_SCN_TIME 表中最多存放1440条记录。 SMON 进程每隔5分钟生成一次SCN和TIME 之前的映射，并更新到SMON_SCN_TIME表。该表的维护周期是5天。

因此该表最多存放的记录是：12*24*5=1440条记录。

超过1440条的记录在下次循环中会被删除。

2.2 Oracle 10g以后的版本

在oracle 10g以后的版本，SMON_SCN_TIME表的维护策略发生了变化。

根据MOS文档的说明：

High Executions Of Statement "deletefrom smon_scn_time..." [ID 375401.1]

The deletestatement deletes the oldest rows from smon_scn_time to clear space for newrows. SMON wakes up every 5 minutes and checks how many on-disk mappingswe have--the max is 144000.

--SMON进程每个5分钟唤醒一次来更新SCN和TIME之间的映射关系，并且检查SMON_SCN_TIME表中的记录数，该表的记录数最大是144000条。

The new mappingsare then added for the last period (since SMON last updated), and if this isover 144000, SMON will then issue the delete statement:

delete fromsmon_scn_time where thread=0 and time_mp = (select min(time_mp) fromsmon_scn_time where thread=0)

--SMON进程会把最新的SCN_TIME映射关系写入SMON_SCN_TIME表，如果该表的记录数超过144000条，那么就会执行上面的delete操作，删除最早的记录。

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

--SMON进程每次被唤醒都会更新SMON_SCN_TIME表，如果一次delete操作不能释放足够的空间映射空间，就会执行多次delete操作。

三．禁用SMON 进程对SMON_SCN_TIME 表的更新

可以设置12500事件停止SMON进程对SMON_SCN_TIME。

具体操作如下：

SQL> select count(1) from smon_scn_time;

COUNT(1)

----------

2115

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

System altered.

SQL> select sysdate from dual;

SYSDATE

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

2013-03-20 13:06:15

SQL> select count(1) from smon_scn_time;

COUNT(1)

----------

2115

SQL> alter system set events '12500 tracename context off';

System altered.

SQL> select sysdate from dual;

SYSDATE

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

2013-03-20 13:19:58

SQL> select count(1) from smon_scn_time;

COUNT(1)

----------

2119

四．SMON_SCN_TIME 表相关的2个案例

4.1 Oracle 9i SMON_SCN_TIME 表被锁

LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]

4.1.1 现象

Oracle 9i，SYS.SMON_SCN_TIME 被 SMON 进程已排它锁占用，并且锁不能释放，导致数据库出现性能问题，并且SMON_SCN_TIME表中有大量的记录。

SQL> selectcount(*) from sys.smon_scn_time;

COUNT(*)
----------
137545
1 row selected.

--正常情况下，9i最多只能保存1440条记录。

SQL> select object_id from dba_objectswhere object_name = 'SMON_SCN_TIME';
OBJECT_ID
----------
575
1 row selected.

SQL> select * fromv$locked_object where object_id = 575;

XIDUSNXIDSLOT 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

4.1.2 处理方法

设置12500事件，停止SMON 进程更新SMON_SCN_TIME表，然后手工删除表中的记录。

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

SQL> delete from smon_scn_time;

SQL> commit;

SQL> alter system set events '12500 tracename context off';

Now restart the instance.

4.2 Oracle 10g SMON_SCN_TIME 表频繁的被delete

High Executions Of Statement "deletefrom smon_scn_time..." [ID 375401.1]

4.2.1 现象

AWR报告显示smon_scn_time的删除操作频繁的被执行。

delete fromsmon_scn_time where thread=0 and time_mp = (select min(time_mp) fromsmon_scn_time where thread=0);

导致这种现象的原因是SMON_SCN_TIME表和表上的索引不一致。需要重建SMON_SCN_TIME上的索引。

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

4.2.2 处理方法

connect / as sysdba
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;

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

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

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

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware