oracle 启动mmon进程,M000 进程、MMON进程及其后台工作

在系统上，意外跟踪了m000进程，生成了大量的跟踪文件，就顺便研究一下这个进程的工作。

M000 进程是MMON后台从属(Slave)进程，这在跟踪文件头部可以清晰的看到：

Windows thread id: 1196, image: ORACLE.EXE (m000)

*** ACTION NAME:(Advisor Task Maintenance) 2009-12-24 20:36:14.921

*** MODULE NAME:(MMON_SLAVE) 2009-12-24 20:36:14.921

*** SERVICE NAME:(SYS$BACKGROUND) 2009-12-24 20:36:14.921

*** SESSION ID:(159.5) 2009-12-24 20:36:14.921

kcbzgb[1]: ts=706480574  [sid,cnum_repl,anum_repl,cnum_write,anum_write]=[3,3493,1286,0,0,0]

kcbzgb[1]: ts=706480575  [sid,cnum_repl,anum_repl,cnum_write,anum_write]=[3,3493,1272,0,0,0]

kcbzgb[1]: ts=706480575  [sid,cnum_repl,anum_repl,cnum_write,anum_write]=[3,3493,1270,0,0,19111]这个进程从事的工作很多，跟踪到的有：

1.Advisor Task Maintenance  2.Auto-CPUUSAGE Action 3.Auto-Flush Slave Action

4.Auto-Purge Slave Action

在CPUUSAGE相关的操作中，涉及以下SQL操作：

select last_sample_date_num, last_sample_date, total_samples

from

WRI$_DBU_CPU_USAGE_SAMPLE where dbid     = :dbid and version  = :version

insert into   WRI$_DBU_CPU_USAGE(dbid, version, timestamp,

cpu_count, cpu_core_count, cpu_socket_count) select :dbid, :version,

:bind1,        gv.gv_cpu_count, gv.gv_cpu_core_count,

gv.gv_cpu_socket_count  from   (select sum(cpu_count_current)        as

gv_cpu_count,           sum(cpu_core_count_current)   as gv_cpu_core_count,

sum(cpu_socket_count_current) as gv_cpu_socket_count      from

GV$LICENSE) gv  where (nvl(gv_cpu_count,        -1),

nvl(gv_cpu_core_count,   -1),         nvl(gv_cpu_socket_count, -1))   not

in (select nvl(cpu_count,        -1) as cu_cpu_count,

nvl(cpu_core_count,   -1) as cu_cpu_core_count,

nvl(cpu_socket_count, -1) as cu_cpu_socket_count           from (select

cpu_count, cpu_core_count, cpu_socket_count                   from

WRI$_DBU_CPU_USAGE                  order by timestamp desc)

where rownum <= 1)

update WRI$_DBU_CPU_USAGE_SAMPLE set last_sample_date     = :bind1,

last_sample_date_num = :bind2, total_samples        = total_samples + 1,

last_sample_period   = decode(total_samples, 0, 0, :bind3 -

last_sample_date_num)

where

dbid = :dbid and version = :version在一些ADVISOR的建议任务里，可以看到如下的递归SQL，在这里你可以彻底理解idl_sb4$,idl_ub1$,idl_ub2$,idl_char$等字典表的用途：

select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece

from

idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece

from

idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#

select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece

from

idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#

select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece

from

idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#

BEGIN    prvt_advisor.delete_expired_tasks;  END;这里还执行了如下一条递归SQL：

SELECT T.ID

FROM

WRI$_ADV_TASKS T, WRI$_ADV_PARAMETERS P WHERE T.ID = P.TASK_ID AND T.STATUS

<> :B4 AND BITAND(T.PROPERTY, :B3 + :B2 ) = 0 AND P.NAME = 'DAYS_TO_EXPIRE'

AND DECODE(P.NAME, 'DAYS_TO_EXPIRE', DECODE(P.VALUE, 'UNLIMITED', :B1 + 1,

'UNUSED', T.MTIME + 30, DECODE(TO_NUMBER(P.VALUE), 0, :B1 + 1, T.MTIME +

TO_NUMBER(P.VALUE))), :B1 + 1) < :B1

M000的工作还包括WRH历史数据的记录，如：

INSERT INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number,

sample_id,    sample_time, session_id, session_serial#, user_id,    sql_id,

sql_child_number,    sql_plan_hash_value, force_matching_signature,

service_hash,    session_type,    flags ,    sql_opcode,

plsql_entry_object_id, plsql_entry_subprogram_id,    plsql_object_id,

plsql_subprogram_id,    blocking_session, blocking_session_serial#,

qc_session_id, qc_instance_id,    xid,    current_obj#, current_file#,

current_block#,    event_id, seq#,    p1, p2, p3, wait_time, time_waited,

program, module, action, client_id )  (SELECT :snap_id, :dbid,

:instance_number, a.sample_id,          a.sample_time, a.session_id,

a.session_serial#, a.user_id,          a.sql_id, a.sql_child_number,这里同样可以看到对于COL_USAGE$表的操作：

lock table sys.col_usage$ in exclusive mode nowait

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

0  TABLE ACCESS FULL COL_USAGE$ (cr=0 pr=0 pw=0 time=0 us)

********************************************************************************

delete from sys.col_usage$ c

where

not exists   (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = c.obj# )

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.03       0.73         13       2074        609         198

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.03       0.74         13       2074        609         198

Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

0  DELETE  COL_USAGE$ (cr=2074 pr=13 pw=0 time=736123 us)

198   FILTER  (cr=2073 pr=13 pw=0 time=681436 us)

2747    TABLE ACCESS FULL COL_USAGE$ (cr=23 pr=8 pw=0 time=57138 us)

934    INDEX UNIQUE SCAN I_OBJ1 (cr=2050 pr=5 pw=0 time=373011 us)(object id 36)

很多信息很有意思，暂时记录以上几笔。

-The End-