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-