了解你所不知道的SMON功能(九):维护MON_MODS$字典基表

SMON后台进程的作用还包括维护MON_MODS$基表，当初始化参数STATISTICS_LEVEL被设置为TYPICAL或ALL时默认会启用Oracle中表监控的特性，Oracle会默认监控表上的自上一次分析以后(Last analyzed)发生的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断，并将这些操作数量的近似值记录到数据字典基表MON_MODS$中，我们常用的一个DML视图dba_tab_modifications的数据实际来源于另一个数据字典基表MON_MODS_ALL$，SMON定期会将MON_MODS$中符合要求的数据MERGE到MON_MODS_ALL$中。

Rem DML monitoring

create table mon_mods$
(
  obj#              number,                                 /* object number */
  inserts           number,  /* approx. number of inserts since last analyze */
  updates           number,  /* approx. number of updates since last analyze */
  deletes           number,  /* approx. number of deletes since last analyze */
  timestamp         date,     /* timestamp of last time this row was changed */
  flags             number,                                         /* flags */
                                           /* 0x01 object has been truncated */
  drop_segments     number   /* number of segemnt in part/subpartition table */
)
  storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods$_obj on mon_mods$(obj#)
  storage (maxextents unlimited)
/

Rem DML monitoring, has info aggregated to global level for paritioned objects
create table mon_mods_all$
(
  obj#              number,                                 /* object number */
  inserts           number,  /* approx. number of inserts since last analyze */
  updates           number,  /* approx. number of updates since last analyze */
  deletes           number,  /* approx. number of deletes since last analyze */
  timestamp         date,     /* timestamp of last time this row was changed */
  flags             number,                                         /* flags */
                                           /* 0x01 object has been truncated */
  drop_segments     number   /* number of segemnt in part/subpartition table */
)
  storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#)
  storage (maxextents unlimited)
/

Rem =========================================================================
Rem End Usage monitoring tables
Rem =========================================================================

VIEW DBA_TAB_MODIFICATIONS

select u.name, o.name, null, null,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
     sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
      o.obj# = tsp.obj# and o2.obj# = tsp.pobj#

现象：

SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$)，
同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中，并清空原MON_MODS$中的数据。
MON_MODS_ALL$作为dba_tab_modifications视图的数据来源，起到辅助统计信息收集的作用,详见拙作<Does GATHER_STATS_JOB gather all objects’ stats every time?>。

SMON具体将DML统计数据刷新到SYS.MON_MODS$、合并到MON_MODS_ALL$、并清除数据的操作如下：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com

/* 填充mon_mods$字典基表 */

lock table sys.mon_mods$ in exclusive mode nowait

insert into sys.mon_mods$
  (obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
values
  (:1, :2, :3, :4, :5, :6, :7)

update sys.mon_mods$
   set inserts       = inserts + :ins,
       updates       = updates + :upd,
       deletes       = deletes + :del,
       flags        =
       (decode(bitand(flags, :flag), :flag, flags, flags + :flag)),
       drop_segments = drop_segments + :dropseg,
       timestamp     = :time
 where obj# = :objn

lock table sys.mon_mods_all$ in exclusive mode

/* 以下merge命令会将mon_mods$中的记录合并到mon_mods_all$,
   若有匹配的记录，则在原记录的基础上增加inserts、updates、deletes总数，
   否则插入新的记录 
*/

merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm)                           
dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
into sys.mon_mods_all$ mm
using (select m.obj#          obj#,
              m.inserts       inserts,
              m.updates       updates,
              m.deletes       deletes,
              m.flags         flags,
              m.timestamp     timestamp,
              m.drop_segments drop_segments fr om sys.mon_mods$ m,
              tab$            t where m.obj# = t.obj#) v
on (mm.ob j# = v.obj#)
when matched then
  update
     set mm.inserts       = mm.inserts + v.inserts,
         mm.updates       = mm.updates + v.updates,
         mm.deletes       = mm.deletes + v.deletes,
         mm.flags         = mm.flags + v.flags - bitand(mm.flags, v.flags) /* bitor(mm.flags,v.flags) */,
         mm.timestamp     = v.timestamp,
         mm.drop_segments = mm.drop_segments + v.drop_segments
when NOT matched then
  insert
    (obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
  values
    (v.obj#,
     v.inserts,
     v.updates,
     v.deletes,
     sysdate,
     v.flags,
     v.drop_segments) / all merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm)                           
dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
  into sys.mon_mods_all$ mm using
    (select m.obj#          obj#,
            m.inserts       inserts,
            m.updates       updates,
            m.deletes       deletes,
            m.flags         flags,
            m.timestamp     timestamp,
            m.drop_segments drop_segments fr om sys.mon_mods$ m,
            tab$            t where m.obj# = t.obj#) v on
    (mm.ob j# = v.obj#)
when matched then
  update
     set mm.inserts       = mm.inserts + v.inserts,
         mm.updates       = mm.updates + v.updates,
         mm.deletes       = mm.deletes + v.deletes,
         mm.flags         = mm.flags + v.flags - bitand(mm.flags, v.flags) 
         /* bitor(mm.flags,v.flags) */,
         mm.timestamp     = v.timestamp,
         mm.drop_segments = mm.drop_segments + v.drop_segments
when NOT matched then
  insert
    (obj#, inserts, updates, deletes, timestamp, flags, drop_segments)
  values
    (v.obj#,
     v.inserts,
     v.updates,
     v.deletes,
     sysdate,
     v.flags,
     v.drop_segments)

/* 最后删除sys.mon_mods$上的相关记录 */

delete /*+ dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
from sys.mon_mods$ m
 where exists (select /*+ unnest */
         *
          from sys.tab$ t
         where t.obj# = m. obj#)

  select obj#
    from sys.mon_mods$
   where obj# not in (select obj# from sys.obj$)

Used to have a FULL TABLE SCAN on obj$ associated with monitoring information 
extracted in conjunction with mon_mods$ executed by SMON periodically.

因为当SMON或用户采用”DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO”存储过程将DML数据刷新到mon_mods$或mon_mods_all$中时会要求持有表上的排它锁，所以在RAC环境中可能出现死锁问题。

另外在早期版本中SMON可能因维护监控表而造成shutdown immediate缓慢或系统性能下降的问题，详见:

SMON维护MON_MODS$时相关的Stack CALL

kglpnal <- kglpin <- kxsGetRuntimeLock
<- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiall0
<- opikpr <- opiodr <- PGOSF175_rpidrus <- skgmstack <- rpiswu2
<- kprball <- kprbbnd0 <- kprbbnd <- ksxmfmel <- ksxmfm
<- ksxmfchk <- ksxmftim <- ktmmon <- ktmSmonMain <- ksbrdp
<- opirip <- opidrv <- sou2o <- opimai_real <- ssthrdmain
<- main <- libc_start_main <- start

如何禁止SMON维护MON_MODS$

注意在缺省参数环境中创建的表总是启用table monitoring的:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create table maclean1 (t1 int);          

Table created.

/* 在10g以后nomonitoring或monitoring选项不再有效  */

SQL> create table maclean2 (t1 int) nomonitoring;

Table created.

SQL>  select table_name,monitoring from dba_tables  where table_name like 'MACLEAN%';


TABLE_NAME                     MON
------------------------------ ---
MACLEAN1                       YES
MACLEAN2                       YES

通常来说我们不需要禁止SMON维护MON_MODS$，除非是在SMON维护过程中遭遇shutdown过慢、性能降低或者异常情况恢复SMON随机terminate实例的问题。

在10g以前可以使用MONITORING和NOMONITORING这2个选项来控制表级别的监控是否被开启，此外我们还可以通过dbms_stats.ALTER_SCHEMA_TAB_MONITORING(‘maclean’,false)存储过程在schema级别的monitoring是否被开启，但是在10g以后这些方法不再有效，MONITORING和NOMONITORING选项被废弃(In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored.)，其原有功能被STATISTICS_LEVEL参数所覆盖。

Table-monitoring特性现在完全由STATISTICS_LEVEL参数所控制：
当STATISTICS_LEVEL设置为BASIC时，Table-monitoring将被禁用
当STATISTICS_LEVEL设置为TYPICAL或ALL时,Table-monitoring将启用

换而言之我们可以通过设置STATISTICS_LEVEL为BASIC达到禁止SMON后台进程该种功能的作用，具体修改该参数的命令如下：

show parameter statistics_level
alter system set statistics_level = basic;

但是请注意如果你正在使用AMM或ASMM自动内存管理特性的话，那么STATISTICS_LEVEL参数是不能设置为BASIC的，因为Auto-Memory或Auto-Sga特性都依赖于STATISTICS_LEVEL所控制的性能统计信息。若一定要这样做那么首先要diable AMM&ASMM:


 #diable 11g AMM ,have to bounce instance
 #alter system set memory_target =0 scope=spfile;
 #diable 10g ASMM
 alter system set sga_target=0;
 alter system set statistics_level = basic;

1 条评论

maclean August 22, 2011

Prior to Oracle 10g, automated collection of statistics for objects that had become stale
was controlled by the setting of the MONITORING flag on table.
Depending on the MONITORING flag, the GATHER_STATS_JOB job collected "GATHER EMPTY" and
"GATHER STALE" on the flagged objects.

In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored.
Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled.
It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g.
By setting this parameter to BASIC, you will be disabling most of the manageability features in 10g.
The following will be disabled:
ASH (Active Session History)
AWR (Automatic Workload Repository)
ASMM(Automatic Shared Memory Management)
ADDM(Automatic Database Diagnostic Monitor)

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the
table since the last time statistics were gathered.
This information on "changes made" is maintained in the SGA and periodically (about every 15 minutes)
the SMON flushes the data into the data
dictionary tables. The data dictionary information is made visible through the views DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS and
USER_TAB_MODIFICATIONS.

Oracle uses these views to identify tables that have stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.

Up to date statistics are important to generate good execution plans. Automatic statistics
collection job using DBMS_STATS packages depend on the monitoring data to determine when to
collect statistics on objects with stale statistics.

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10%
of NUM_ROWS from dba_tables:

select u.TIMESTAMP,
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
order by t.last_analyzed
/

The 10000 is there so as to pick up only the biggest changes.

Example:
--------
Let us take an example:

Step 1:
-------

Create table EMP. Its description is as follows

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from user_tab_modifications;

no rows selected

Initially there are 14 rows in EMP.

Step 2:
-------

Set parameter STATISTICS_LEVEL='TYPICAL'

SQL> alter system set STATISTICS_LEVEL='TYPICAL';

System altered.

Step 3:
-------

Insert additional 14 rows. This will increase the data in EMP by 50% and
therefore the statistics in EMP will be regarded as stale by Oracle.

SQL> insert into emp select * from emp;

14 rows created.
SQL>commit;

Step 4:
-------

The information about the inserts, updates and deletes on tables will be
in SGA. After every 15 minutes SMON will push this data in data-dictionary
tables. You can view them by querying table dba_tab_modifications.

Execute the following procedure to manually push the information about
the modifications in the tables from SGA to data-dictionary tables:

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- --------- ---
EMP
14 0 0 16-OCT-03 NO

Step 5:
-------

If a monitored table has been modified more than 10%, then these
statistics are considered stale

Prior to Oracle11g, the staleness threshold is hardcoded at 10%. This means that an object is
considered stale if the number of rows inserted,
updated or deleted since the last statistics gathering time is more than 10% of the number of rows.
There is no way to modify this value prior to Oracle 11g.
Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference.
This can be set globally using
DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.

E.g. to modify and check the staleness threshold for table EMP in SCOTT schema:

SQL> exec dbms_stats.set_table_prefs(null,'EMP','STALE_PERCENT',17)

Analyze the tables whose statistics have become stale using the following command:

execute DBMS_STATS.GATHER_SCHEMA_STATS ('RAJIV',
NULL,
FALSE,
'FOR ALL COLUMNS SIZE 1',
NULL,
'DEFAULT',
TRUE,
NULL,
NULL,
'GATHER STALE',
'LIST' );

Step 6:
-------

Query dba_tab_modifications to check whether the table has been analyzed
or not?

No rows in dba_tab_modifications indicates that the table is analyzed.

1 条评论

发表评论 取消回复

发表评论取消回复