10500事件跟踪SMON

SMON进程是ORACLE的一个关键进程，负责INSTANCE RECOVERY，清理临时段，合并空闲空间，收缩回滚段，维护SMON_SCN_TIME表等。
我们可以通过10500事件来跟踪上述行为：


Level:  <=5  trace instance recovery
        > 5  trace posting of SMON




首先开启一个SESSION 删掉一些数据

SQL> delete test;

已删除48875行。

SQL>

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 21 18:00:40 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system set event='10500 trace name context forever,level 10' scope=spfile;

系统已更改。

SQL> startup force  <--异常关闭数据库，导致上面的事务在数据库启动后进行回滚
ORACLE 例程已经启动。

Total System Global Area  176160768 bytes
Fixed Size                  1247900 bytes
Variable Size             104858980 bytes
Database Buffers           58720256 bytes
Redo Buffers               11333632 bytes
数据库装载完毕。
数据库已经打开。


从SMON的TRACE文件可以看到SMON的行为：

Dump file c:\oracle\product\10.2.0\admin\plsqldb\bdump\plsqldb_smon_5604.trc
Mon Jan 21 19:25:36 2013
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU                 : 4 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1777M/3240M, Ph+PgF:3242M/5125M, VA:1729M/2047M

Instance name: plsqldb

Redo thread mounted by this instance: 0

Oracle process number: 8

Windows thread id: 5604, image: ORACLE.EXE (SMON)



*** SERVICE NAME:() 2013-01-21 19:25:36.437
*** SESSION ID:(120.1) 2013-01-21 19:25:36.437
*** 2013-01-21 19:25:36.437
SMON: system monitor started
*** 2013-01-21 19:25:36.718
SMON: system monitor process posted
*** 2013-01-21 19:25:54.593
SMON: system monitor process posted
*** 2013-01-21 19:25:54.812
SMON: system monitor process posted
*** 2013-01-21 19:25:57.765
SMON: system monitor process posted
Dead transaction 0x0009.01f.000002e5 recovered by 8 server(s)  <----SMON开启了8个PROCESS进行事务的恢复。
*** 2013-01-21 19:26:06.078
SMON: Parallel transaction recovery tried
*** 2013-01-21 19:26:12.546
Shrinking rbseg #0           <--收缩临时段
*** 2013-01-21 19:26:12.546
Shrinking rbseg #1
*** 2013-01-21 19:26:12.562
Shrinking rbseg #2
*** 2013-01-21 19:26:12.562
Shrinking rbseg #3
*** 2013-01-21 19:26:12.578
Shrinking rbseg #4
*** 2013-01-21 19:26:12.578
Shrinking rbseg #5
*** 2013-01-21 19:26:12.593
Shrinking rbseg #6
*** 2013-01-21 19:26:12.609
Shrinking rbseg #7
*** 2013-01-21 19:26:12.625
Shrinking rbseg #8
*** 2013-01-21 19:26:12.625
Shrinking rbseg #9
*** 2013-01-21 19:26:12.625
Shrinking rbseg #10
*** 2013-01-21 19:26:12.640
SMON: system monitor process posted
*** 2013-01-21 19:26:12.640

SMON: Posted too frequently, trans recovery disabled.

/*************************************************************************************/

*** 2013-01-25 01:59:43.602
SMON: process sort segment requests begin

*** 2013-01-25 01:59:43.602
SMON: process sort segment requests end

*** 2013-01-25 01:59:43.602
SMON: parallel transaction recovery begin

*** 2013-01-25 01:59:43.603
SMON: parallel transaction recovery end
SMON: check for unpinning handles begin
KGL handles upinned : 0
SMON: check for unpinning handles begin
SMON: IMU monitor begin
SMON: IMU monitor end
SMON: cleanup index online rebuild begin
SMON: cleanup index online rebuild begin
SMON: cleanup IOT-PMO failures begin
SMON: cleanup IOT-PMO failures end
SMON: CoaLesce Space begin
SMON: CoaLesce Space end
SMON: offline rollback segment begin
SMON: offline rollback segment end
SMON: offline rollback segment begin
SMON: offline rollback segment end
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end
/*************************************************************************************/