学习随笔：smon维护smon_scn_time表与smon_scn_time的定义与重建

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

学习随笔：smon维护smon_scn_time表与smon_scn_time的定义与重建

下面来看看smon_scn_time表的创建语句，文件都来至于sql.bsq文件

10G环境

rem

rem create the scn<->time tracking table that smon will maintain

rem as a circular queue – notice that we populate the entire

rem table with at least 144000 entries (enough for 5 days).

rem

rem -"thread" is for backward compatibility and is always 0

rem -"orig_thread" is for upgrade/downgrade

rem – scn_wrp, scn_bas, and time_dp are for backward compatibility

rem   and not queried by the ktf layer.

rem


create cluster smon_scn_to_time (

  thread number                         /* thread, compatibility */

)

/

create index smon_scn_to_time_idx on cluster smon_scn_to_time

/

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 (thread)

/


create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)

/


create unique index smon_scn_time_scn_idx on smon_scn_time(scn)

/

11G环境

create cluster smon_scn_to_time_aux (

  thread number                         /* thread, compatibility */

) tablespace SYSAUX

/

create index smon_scn_to_time_aux_idx on cluster smon_scn_to_time_aux

/

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

/
11G与10G发生了一些变化，cluster表更改为smon_scn_to_time_aux。

smon更新smon_scn_time表的时间间隔：

The interval is :

Version

Interval

11g

3 seconds

10g

6 seconds

9.2

5 minutes

来至于Error ORA-01466 while executing a flashback query. (Doc ID 281510.1)。

smon删除smon_scn_time记录：

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.

来至于High Executions Of Statement "delete from smon_scn_time…" (Doc ID 375401.1)

禁止smon更新smon_scn_time

alter system set event="12500 trace name context forever, level 10" scope=spfile;

来至于High CPU Usage and High Redolog Generation from SMON Process (Doc ID 1569564.1)

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)