当前位置: 首页 > BASIC > 正文

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

/

11G10G发生了一些变化,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)

本文固定链接: http://www.htz.pw/2014/07/06/smon%e7%bb%b4%e6%8a%a4smon_scn_time%e8%a1%a8%e4%b8%8esmon_scn_time%e7%9a%84%e5%ae%9a%e4%b9%89%e4%b8%8e%e9%87%8d%e5%bb%ba.html | 认真就输

该日志由 huangtingzhong 于2014年07月06日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: smon维护smon_scn_time表与smon_scn_time的定义与重建 | 认真就输
关键字: ,