合 Oracle中的SYS.SMON_SCN_TIME基表的作用是什么
Tags： OracleSCNSMON_SCN_TIME
简介
1 2 3 4 5 6 7 8 9 10 | SYS@LHR11G> set line 10000 SYS@LHR11G> SELECT * FROM DBA_TABLES D WHERE D.TABLE_NAME = 'SMON_SCN_TIME' ; OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGIN BA NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTIT IOT_TYPE TE SE NESTED BUFFER_POOL FLASH_CACHE CELL_FLASH_CAC ROW_MOVEMENT GLOBAL USER_S DURATION SKIP_CORRUPT MONITO CLUSTER_OWNER DEPENDENCIES COMPRESSION COMPRESS_FOR DROPPE READ_O SEGMEN RESULT_CACHE ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------ -- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------- ---------------- ----------- ------------------ ------ ------------------------ -- -- ------ -------------- -------------- -------------- ---------------- ------ ------ ------------------------------ ---------------- ------ ------------------------------------------------------------ ---------------- ---------------- ------------------------ ------ ------ ------ -------------- SYS SMON_SCN_TIME SYSAUX SMON_SCN_TO_TIME_AUX VALID 0 0 0 65536 1048576 1 2147483645 YES N 1585 370 0 0 0 1115 0 0 1 1 N ENABLED 1585 2023-06-2106:00:16 NO N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO DISABLED YES SYS DISABLED DISABLED NO NO YES DEFAULT SYS@LHR11G> |
SYS.SMON_SCN_TIME基表用于记录过去时间段中SCN与具体的时间戳（timestamp）之间的映射关系，因为是采样记录这种映射关系，所以SMON_SCN_TIME可以较为粗糙地定位某个SCN的时间信息。
实际的SMON_SCN_TIME是一张cluster table簇表。SMON_SCN_TIME基表的数据是由SMON后台进程来维护的。
在Oracle 11g中，该表的创建SQL在$ORACLE_HOME/rdbms/admin/dtxnspc.bsq文件中，可以直接查看：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | 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 / SYS@LHR11G> set linesize 120 SYS@LHR11G> desc sys.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 SYS@LHR11G> alter session set nls_date_format='yyyy-mm-ddhh24:mi:ss'; Session altered. SYS@LHR11G> select time_dp,scn from smon_scn_time where rownum<5; TIME_DP SCN ------------------ ---------- 2023-06-1620:20:51 9207054 2023-06-1519:06:06 9136998 2023-06-1620:35:22 9207644 2023-06-1823:38:18 9368711 |
从Oracle 10g开始，SMON会定时清理SMON_SCN_TIME中的记录。SMON后台进程会每5分钟被唤醒一次，检查SMON_SCN_TIME在磁盘上的映射记录总数，若总数超过144000条，则会使用以下语句删除最老的一条记录（TIME_MP列最小）：
1 2 3 | delete from smon_scn_time where thread = 0 and time_mp = (select min(time_mp) from smon_scn_time where thread = 0); |
若仅仅删除一条记录不足以获得足够的空间，则SMON会反复多次执行以上DELETE语句。
可以设置12500事件停止SMON进程对SMON_SCN_TIME的清理：
1 | alter system set events '12500 trace name context forever, level 10'; |
我们可以直接delete掉SMON_SCN_TIME表中的记录：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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表记录保存策略说明
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天。
因此该表最多存放的记录是：12245=1440条记录。
超过1440条的记录在下次循环中会被删除。
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;
