SMON是System Monitor Process的简称，是oracle非常核心的进程。
Smon 进程有哪些作用：
- temporary space reclamation:Creates and manages the temporary tablespace metadata
- data dictionary cleanup:Cleans up the data dictionary when it is in a transient and inconsistent state(obj$,ind$…)
- undo tablespace management：Maintains the undo tablespace by onlining, offlining, and shrinking the undo segments based on undo space usage statistics
- Recover Dead transaction
- Instance Recovery,In an Oracle RAC database, the SMON process of one instance can perform instance recovery for other instances that have failed
- Free Space COALESCE
- Maintains the SCN to time mapping table used to support Oracle Flashback
从trace中验证是否有这些功能：
|
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 |
SMON: calculate time per CPU cycle begin SMON: check for unpinning handles begin SMON: cleanup index online rebuild begin SMON: cleanup IOT-PMO failures begin SMON: cleanup temp segment begin SMON: clean up temp segments SMON: clean up temp segments in slave SMON: cleanup the cursor transient packages begin SMON: cleanup the cursor transient types begin SMON: CoaLesce Space begin SMON: flush modification to disk begin SMON: IMU monitor begin SMON: launch SMCO begin SMON: obj$ cleanup begin SMON: offline rollback segment begin SMON: offline SMU segment begin SMON: parallel transaction recovery begin SMON: Parallel transaction recovery tried SMON: poll flashback broadcast channel begin SMON: poll segment info broadcast channel begin SMON: poll shared mount broadcast channel begin SMON: Posted, but not for trans recovery, so skip it. SMON: prime sort segment begin SMON: process sort segment requests begin SMON: rbseg 1 shrunk 128 blocks SMON: scn->time mapping begin SMON: shrink rollback segment begin SMON: system monitor process posted msgflag0x0008 (-/-/UNDORCV/-/-/-/-) SMON: system monitor process posted msgflag0x0200 (-/-/-/-/TMPSDROP/-/-) SMON: system monitor started |
SMON的统计：
|
1 2 3 4 5 6 7 8 9 10 |
SYS@LX(lx):1>select name from v$sysstat where name like '%SMON%'; NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ total number of times SMON posted SMON posted for undo segment recovery SMON posted for txn recovery for other instances SMON posted for instance recovery SMON posted for undo segment shrink SMON posted for dropping temp segment |
如何跟踪SMON：
|
1 |
ORA-10500: turn on traces for SMON |
禁用smon的一些功能：
|
1 2 3 4 5 6 7 8 9 10 11 |
ORA-08105: Oracle event to turn off smon cleanup for online index build ORA-10061: disable SMON from cleaning temp segment ORA-10269: Don't do coalesces of free space in SMON ORA-10510: turn off SMON check to offline pending offline rollback segment ORA-10511: turn off SMON check to cleanup undo dictionary ORA-10512: turn off SMON check to shrink rollback segments ORA-22834: Event to turn off smon cleanup for transient types ORA-10052: don't clean up obj$ ORA-10513: disabled smon transaction recover _column_tracking_level：disable smon cleanup col_usage$ 12500 trace name context forever, level 10：disable smon SMON_SCN_TIME |
1.temporary space reclamation
- SMON: clean up temp segments
- SMON: process sort segment requests begin (ksqgtl: acquire SS-00000003-00000001 mode=X flags=SHORT why=”contention”)
- SMON: prime sort segment begin
Temporary Segments in a Permanent Tablespace(SMON: system monitor process posted msgflag:0x0200 (-/-/-/-/TMPSDROP/-/-)
The background process System Monitor (SMON) frees the temporary segments when the statement has been completed.If a large number of sort segments has been created, then SMON may take some time to drop them; this process automatically implies a loss of overall database performance.After SMON has freed up the temporary segment, the space is released for use by other objects.
Temporary Segments in a Temporary Tablespace(下一次实例启动)
The background process SMON actually de-allocates the sort segment after the instance has been started and the database has been opened. Thus, after the database has been opened, SMON may be seen to consume large amounts of CPU as it first de-allocates the (extents from the) temporary segment, and after that performs free space coalescing of the free extents created by the temporary segment cleanup. This behavior will be exaggerated if the temporary tablespace, in which the sort segment resides, has inappropriate (small) default NEXT storage parameters.
Forcing cleanup of TEMPORARY segments
select ts# from sys.ts$ where name = ‘<Tablespace name>’ and online$ != 3;
If ts# is 5, an example of dropping the temporary segments in that tablespace
would be:
alter session set events ‘immediate trace name DROP_SEGMENTS level 6’;
2.data dictionary cleanup
- SMON: cleanup index online rebuild begin(kdicclean is run by smon every 1 hour，called from SMON to find if there is any online builder death and cleanup our ind$ and obj$ and drop the journal table, stop journaling）
- SMON: obj$ cleanup begin(after startup of DB is started cleanup function again，the cleanup is scheduled to run after startup and then every 12 hours）
。。。
08104, 00000, “this index object %s is being online built or rebuilt”
// *Cause: the index is being created or rebuild or waited for recovering
// from the online (re)build
// *Action: wait the online index build or recovery to complete - smon清理
- 在10g之后用dbms_repair.online_index_clean手工清理这些信息，在Oracle 9i下，需要打Bug 3805539 后才可以使用该工具
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE RetVal BOOLEAN; OBJECT_ID BINARY_INTEGER; WAIT_FOR_LOCK BINARY_INTEGER; BEGIN OBJECT_ID := 53367; WAIT_FOR_LOCK := NULL; RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (); COMMIT; END; / |
- 修改字典数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
(1)update ind$ set flags=flags-512 where obj#=<object id>; ind$.flags number not null, /* mutable flags: anything permanent should go into property */ /* unusable (dls) : 0x01 */ /* analyzed : 0x02 */ /* no logging : 0x04 */ /* index is currently being built : 0x08 */ /* index creation was incomplete : 0x10 */ /* key compression enabled : 0x20 */ /* user-specified stats : 0x40 */ /* secondary index on IOT : 0x80 */ /* index is being online built : 0x100 */ /* index is being online rebuilt : 0x200 */ /* index is disabled : 0x400 */ /* global stats : 0x800 */ /* fake index(internal) : 0x1000 */ /* index on UROWID column(s) : 0x2000 */ /* index with large key : 0x4000 */ /* move partitioned rows in base table : 0x8000 */ /* index usage monitoring enabled : 0x10000 */ /* 4 bits reserved for bitmap index version : 0x1E0000 */ (2)drop table <owner>.sys_journal_<object_id>; |
3.undo tablespace management
- onlining undo segments
- offlining undo segments
- shrinking undo segments
4.Recover Dead transaction
- SMON: system monitor process posted msgflag0x0008 (-/-/UNDORCV/-/-/-/-)
5.Maintains the SCN to time mapping table used to support Oracle Flashback
- SMON: scn->time mapping begin
- SMON_SCN_TIME is updated in an interval, hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table.
The interval is :
Version Interval
11g 3 seconds
10g 6 seconds
9.2 5 minutes
从10g开始SMON也会清理SMON_SCN_TIME中的记录了，SMON后台进程会每5分钟被唤醒一次，检查SMON_SCN_TIME在磁盘上的映射记录总数，若总数超过144000条，则会使用以下语句删除最老的一条记录
6.Instance Recovery
Instance Recovery Phases
- Cache Recovery
The first phase of instance recovery is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the online redo log to the data files. Because rollback data is recorded in the online redo log, rolling forward also regenerates the corresponding undo segments.
Rolling forward proceeds through as many online redo log files as necessary to bring the database forward in time. After rolling forward, the data blocks contain all committed changes recorded in the online redo log files. These files could also contain uncommitted changes that were either saved to the data files before the failure, or were recorded in the online redo log and introduced during cache recovery.
- Transaction Recovery
After the roll forward, any changes that were not committed must be undone. Oracle Database uses the checkpoint position, which guarantees that every committed change with an SCN lower than the checkpoint SCN is saved on disk. Oracle Database applies undo blocks to roll back uncommitted changes in data blocks that were written before the failure or introduced during cache recovery. This phase is called rolling back or transaction recovery.
7.Free Space COALESCE
- SMON: CoaLesce Space begin