惜分飞

MON_MODS$和MON_MODS_ALL$统计DML操作次数

发表于 2013 年 5 月 3 日由惜分飞

*_TAB_MODIFICATIONS视图是用来记录自从收集统计信息后的DML(包括truncate)操作的次数,通过试验分析数据库是如何实现该功能,并且应用该功能来实现数据库自动收集功能(表变化比例决定是否收集统计信息)

SQL> select * from v$version;

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

SQL> DESC DBA_TAB_MODIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)
 DROP_SEGMENTS                                      NUMBER

SQL> select text from dba_views where view_name='DBA_TAB_MODIFICATIONS';

TEXT
--------------------------------------------------------------------------------
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#

这里很清楚，通过union all关联了三个select 语句,分别是查询普通表,分区表,子分区表,这里也可以看出来

对应基表

SQL> desc sys.mon_mods_all$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER

SQL> desc sys.mon_mods$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER

这两个基表结构完全相同,通过收集信息dml操作MON_MODS$然后记录MON_MODS_ALL$中

测试MON_MODS$和MON_MODS_ALL$关系

SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;

      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0

SQL> !date
Fri May  3 01:51:08 CST 2013

SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;

      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0
--15分钟未完全刷新mon_mod$

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;

no rows selected

SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE obj#=69900;

      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        323          0          0 2013-05-03 01:54:18             0

这里测试证明DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO实现MON_MODS$刷新到MON_MODS_ALL$,但是未完全刷新MON_MODS$

测试MON_MODS_ALL$

SQL> create table t_xifenfei
  2  as
  3  select object_id,object_name from dba_objects;

Table created.

SQL> select object_id from user_objects where object_name='T_XIFENFEI';

 OBJECT_ID
----------
     76703

SQL> SELECT COUNT(*) FROM T_XIFENFEI;

  COUNT(*)
----------
     74806

--MON_MODS$无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;

no rows selected

--MON_MODS_ALL$中无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

no rows selected

--UPDATE 操作
SQL> UPDATE T_XIFENFEI SET OBJECT_NAME='WWW.XIFENFEI.COM' WHERE MOD(OBJECT_ID,10)=0;

7474 rows updated.

SQL> COMMIT;

Commit complete.

--MON_MODS$和MON_MODS_ALL$中无数据，因为未从内存中刷新到MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;

no rows selected

SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

no rows selected

--执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO操作
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

--MON_MODS_ALL$中有数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474          0          0 2013-02-12 09:02:05             0
--这里统计的update数据和实际更新条数一致

--MON_MODS$中无数据，因为FLUSH_DATABASE_MONITORING_INFO刷新SGA中的dml和MON_MODS$到MON_MODS_ALL$中，并清空MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;

no rows selected

--DELETE操作
----session 1
SQL>  DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,3)=2;

24940 rows deleted.

----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------这里可以看到，未提交的DML操作也统计到MON_MODS_ALL$中

----session 1
SQL> rollback;

Rollback complete.

----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

----session 2
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------通过这里可以rollback 操作也不能回滚MON_MODS_ALL$中未提交的事务

--再次DELETE操作
SQL> DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,5)=1;

14954 rows deleted.

----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      39894          0 2013-02-12 09:05:54             0
------DELETE操作在MON_MODS_ALL$中累加

--INSERT操作
SQL> insert into t_xifenfei select object_id,object_name from dba_objects;

74806 rows created.

SQL> commit;

Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.


SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
     74806       7474      39894          0 2013-02-12 09:07:51             0

--收集统计信息
SQL> EXEC DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

--MON_MODS_ALL$数据被清空
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

no rows selected

--TRUNCATE表被清空
SQL> truncate table t_xifenfei;

Table truncated.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;

   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0          0     134658          1 2013-02-12 09:29:49             0
----DELETES和FLAGS记录因为truncate操作而发生改变

从10GR2开始,数据库每15分钟就要把数据库的DML操作写入到mon_mods$(从SGA中写入到mon_mod$),但是这个写入过程1分钟,因此可能不是全部记录所有DML操作到mon_mods$.默认情况下,数据库每天会写入SGA中表的DML操作和mon_mods$到mon_mods_all$,也可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来人工写入到mon_mods_all$中,收集统计信息后mon_mods_all$表中信息清空

发表在 Oracle | 标签为 MON_MODS$, MON_MODS_ALL$ | 评论关闭

从执行效率上分析为什么SYSTEM不适宜存储业务数据

发表于 2013 年 4 月 25 日由惜分飞

为什么不建议客户把业务数据存放到SYSTEM表空间中,一直想通过试验的数据来说明问题,今天见老熊的邮件和同事的blog(为什么不要把用户表存储到SYSTEM表空间)来说明把业务数据存放在SYSTEM表空间中效率的影响
数据库版本

SQL> select * from v$version;

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

创建测试环境

SQL> conn chf/xifenfei
Connected.

SQL> create table t_xifenfei_u(id number) tablespace users;

Table created.

SQL> create table t_xifenfei_s(id number) tablespace system;

Table created.

SQL>  select table_name,tablespace_name from user_tables;   

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_XIFENFEI_U                   USERS
T_XIFENFEI_S                   SYSTEM

非系统表空间测试

SQL> select STATISTIC#,NAME from v$statname where name='CPU used by this session';

STATISTIC# NAME
---------- ----------------------------------------------------------------
        17 CPU used by this session

SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17         33

SQL> set timing on
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_u values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.97
SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17        629

Elapsed: 00:00:00.00

测试结果显示，非系统表空间中的表插入200000条记录，使用时间为5.97秒；使用CPU为629-33=596

系统表空间测试

SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_s values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.00
SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17       2019

Elapsed: 00:00:00.00

测试结果显示，对系统表空间中的表插入200000条记录，使用时间为14秒；使用CPU为2019-629=1390，基本上可以看出来无论是CPU消耗还是执行时间上，系统表空间占用都是非系统表空间两倍以上

分析原因

SQL> conn / as sysdba
Connected.

SQL> select * from (SELECT i.ksppinm NAME, i.ksppity TYPE, v.ksppstvl VALUE, 
  2  v.ksppstdf isdefault FROM x$ksppi i, x$ksppcv v WHERE i.indx = v.indx AND 
  3  i.ksppinm LIKE '/_%%' ESCAPE '/') where name like '%db_alw%';

NAME                                 TYPE VALUE           ISDEFAULT
------------------------------ ---------- --------------- ---------
_db_always_check_system_ts              1 TRUE            TRUE

SQL> alter system set "_db_always_check_system_ts"=false;

System altered.

SQL> conn chf/xifenfei
Connected.

SQL>  select * from v$mystat where STATISTIC#=17;   

       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17          1

Elapsed: 00:00:00.01
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_s values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.03

SQL> select * from v$mystat where STATISTIC#=17;   

       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17        582

通过这里可以发现,修改_db_always_check_system_ts=false之后,system表空间的操作基本上和非系统表空间所差无几(执行时间6.01秒,占用CPU 581=582-1)
在数据库默认情况下db_block_checking和db_block_checksum的值无论如何设置都不能对于SYSTEM表空间生效，也就是说SYSTEM表空间在没有修改_db_always_check_system_ts=false之前，对所有的块操作都要进行db_block_checking和db_block_checksum验证,从而使得数据块的操作效率较非SYSTEM表空间低下。对于一些插入较为频繁的aud$、FGA_LOG$、DEF$_AQCALL等表建议迁移到其他表空间

备注说明
DB_BLOCK_CHECKING

DB_BLOCK_CHECKING specifies whether or not Oracle performs block checking for database blocks.

Values:

OFF or FALSE

No block checking is performed for blocks in user tablespaces. However,
semantic block checking for SYSTEM tablespace blocks is always turned on.

LOW

Basic block header checks are performed after block contents change in memory 
(for example, after UPDATE or INSERT statements, on-disk reads, or 
inter-instance block transfers in Oracle RAC).

MEDIUM

All LOW checks and full semantic checks are performed for all objects except indexes 
(whose contents can be reconstructed by a drop+rebuild on encountering a corruption).

FULL or TRUE

All LOW and MEDIUM checks and full semantic checks are performed for all objects.

Oracle checks a block by going through the data in the block, making sure it is logically 
self-consistent. Block checking can often prevent memory and data corruption. Block checking 
typically causes 1% to 10% overhead,depending on workload and the parameter value. 
The more updates or inserts in a workload, the more expensive it is to turn on block checking.
You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.

For backward compatibility, the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

DB_BLOCK_CHECKSUM

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum
(a number calculated from all the bytes stored in the block) and store it in the cache header
of every data block when writing it to disk. Checksums are verified when a block is read -
only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum.
In FULL mode, Oracle also verifies the checksum before a change application from update/delete
statements and recomputes it after the change is applied. In addition, Oracle gives every log block
a checksum before writing it to the current log.

Starting with Oracle Database 11g, most of the log block checksum is done by the generating foreground
processes, while the LGWR performs the rest of the work, for better CPU and cache efficiency.
Prior to Oracle Database 11g, the LGWR solely performed the log block checksum.

If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace,
but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems.
If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk.
Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes
4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.

For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.

发表在 Oracle | 一条评论

ORACLE 7.3.4 截图欣赏

发表于 2013 年 4 月 19 日由惜分飞

此多媒体陈列厅包含 10 张图像。

在windows 4运行oracle 7.3.4是一个难得的搭配,今天有机会一见 … 继续阅读 →

更多多媒体陈列厅 | 评论关闭

17813235971

QQ 咨询

MON_MODS$和MON_MODS_ALL$统计DML操作次数

从执行效率上分析为什么SYSTEM不适宜存储业务数据

ORACLE 7.3.4 截图欣赏

文章分类

最近发表

MON_MODS$和MON_MODS_ALL$统计DML操作次数

从执行效率上分析为什么SYSTEM不适宜存储业务数据

ORACLE 7.3.4 截图欣赏

标签云

文章分类

最近发表