一．SMON_SCN_TIME 表结构说明
SMON_SCN_TIME表存放的是SCN和Time之前的映射关系。 该表由SMON 进程负责维护。
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba
SQL>
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-dd hh24: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_wrp number, /* scn.wrp, compatibility */
scn_bas number, /* scn.bas, compatibility */
num_mappings number,
tim_scn_map raw(1200),
scn number default 0, /* scn */
orig_thread number default 0 /* for downgrade */
) cluster smon_scn_to_time_aux (thread)
/
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)
tablespace SYSAUX
/
create unique index smon_scn_time_scn_idx on smon_scn_time(scn)
tablespace SYSAUX
/
我们可以直接delete掉SMON_SCN_TIME表中的记录：
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba
SQL> delete from smon_scn_time;
2120 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from smon_scn_time;
COUNT(1)
----------
0
二．SMON_SCN_TIME表记录保存策略说明
2.1 Oracle 9i
根据MOS文档的说明：
How To Map SCN To Timestamp Before 10g? [ID 365536.1]
SYS.SMON_SCN_TIME will 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 the records will be recycled.
This means that data 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 "delete from smon_scn_time..." [ID 375401.1]
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.
--SMON进程每个5分钟唤醒一次来更新SCN和TIME之间的映射关系，并且检查SMON_SCN_TIME表中的记录数，该表的记录数最大是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)
--SMON进程会把最新的SCN_TIME映射关系写入SMON_SCN_TIME表，如果该表的记录数超过144000条，那么就会执行上面的delete操作，删除最早的记录。
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.
--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
--停止smon进程收集scn信息
SQL> alter system set events '12500 trace 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
--开启smon进程收集scn信息
SQL> alter system set events '12500 trace name 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更新SMON_SCN_TIME基表，因为这样会影响flashback Query闪回查询的正常使用，但是在某些异常恢复的场景中SMON_SCN_TIME数据讹误可能导致实例的Crash，那么可以利用12500事件禁止触发SMON_SCN_TIME表更新。
四．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表中有大量的记录。
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba
SQL> select count(*) from sys.smon_scn_time;
COUNT(*)
----------
137545
1 row selected.
--正常情况下，9i最多只能保存1440条记录。
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
4.1.2 处理方法
设置12500事件，停止SMON 进程更新SMON_SCN_TIME表，然后手工删除表中的记录。
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba
SQL> alter system set events '12500 trace name context forever, level 10';
SQL> delete from smon_scn_time;
SQL> commit;
SQL> alter system set events '12500 trace name context off';
Now restart the instance.
4.2 Oracle 10g SMON_SCN_TIME 表频繁的被delete
High Executions Of Statement "delete from smon_scn_time..." [ID 375401.1]
4.2.1 现象
AWR报告显示smon_scn_time的删除操作频繁的被执行。
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0);
导致这种现象的原因是SMON_SCN_TIME表和表上的索引不一致。需要重建SMON_SCN_TIME上的索引。
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
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;
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
https://www.cndba.cn/dave/article/1285
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle 11g
573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
