需要定时kill非inactive session，一种做法是通过编写脚本or脚本定时运行,从而实现该功能;另外一种方法是通过设置profile中的idle_time来实现该功能,但是这其中有两个细节问题需要注意：1.v$session.status=SNIPED最好做清理，2.未提交事务超时可能强制回滚
使用ORACLE PROFILE准备

SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 1;

Profile created.

SQL> select * from dba_profiles where profile='KILLIDLE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------
KILLIDLE                       COMPOSITE_LIMIT                  KERNEL   DEFAULT
KILLIDLE                       SESSIONS_PER_USER                KERNEL   DEFAULT
KILLIDLE                       CPU_PER_SESSION                  KERNEL   DEFAULT
KILLIDLE                       CPU_PER_CALL                     KERNEL   DEFAULT
KILLIDLE                       LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
KILLIDLE                       LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
KILLIDLE                       IDLE_TIME                        KERNEL   1
KILLIDLE                       CONNECT_TIME                     KERNEL   DEFAULT
KILLIDLE                       PRIVATE_SGA                      KERNEL   DEFAULT
KILLIDLE                       FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
KILLIDLE                       PASSWORD_LIFE_TIME               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_REUSE_TIME              PASSWORD DEFAULT
KILLIDLE                       PASSWORD_REUSE_MAX               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
KILLIDLE                       PASSWORD_LOCK_TIME               PASSWORD DEFAULT
KILLIDLE                       PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

SQL> ALTER USER CHF PROFILE KILLIDLE;

User altered.

SQL> SELECT USERNAME,PROFILE FROM DBA_USERS where username='CHF';

USERNAME                       PROFILE
------------------------------ ------------------------------
CHF                            KILLIDLE

SQL> SHOW PARAMETER resource_limit 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
resource_limit                       boolean     FALSE

SQL> ALTER SYSTEM SET resource_limit=TRUE;

System altered.

如果要profile生效,需要修改resource_limit=true,IDLE_TIME单位为分钟

测试IDLE_TIME

--session 1
SQL> show user;
USER is "CHF"

SQL> select * from t_xifenfei;

        ID
----------
         1

--删除一条记录
SQL> delete from t_xifenfei;

1 row deleted.

--查询sid
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        20

--开始不操作该会话时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 22:30:02

--session 2
SQL> show user;
USER is "SYS"

--查询时间
SQL> select status,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$session where sid=20;

STATUS   TO_CHAR(SYSDATE,'YY
-------- -------------------
INACTIVE 2013-02-12 22:31:00

--session 1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
----已经报会话超时

--session 2
SQL> select status,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$session where sid=20;

STATUS   TO_CHAR(SYSDATE,'YY
-------- -------------------
SNIPED   2013-02-12 22:34:40
----会话状态为sniped

--session 1
SQL> conn chf/xifenfei
Connected.
SQL> select * from t_xifenfei;

        ID
----------
         1
----事务回滚

SNIPED – An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again.
因为SNIPED的session只有当该session的终端发一个连接信息给数据库，然后终端才会终止连接，如果该客户端一直不发送类似访问，则该连接一直存在，数据库就很可能因为会话数目超过了数据库参数配置从而出现了ORA-00018错误，业务不能正常运行.出现该问题可以通过如下脚本kill -9 pid解决

kill SNIPED session 脚本

#!/bin/sh
tmpfile=/tmp/.kill_sniped
sqlplus system/manager <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile

另外补充说明，IDLE_TIME是对于空闲时间超过了它的配置时间就会去强制终止会话,如果该会话中存在事务,但是inactive时间超过了IDLE_TIME配置时间，数据库依然会强制终止会话，并且回滚事务

思考为什么dbms_shared_pool.purge清理掉某条sql在shared pool中的信息，为什么当该sql再次执行的时候FIRST_LOAD_TIME时间没有发生改变
测试purge某条sql,再次加重该sql,FIRST_LOAD_TIME不变

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 16:44:00

SQL>  select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';     

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/16:43:59

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

ADDRESS  HASH_VALUE
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
2587FFAC  515825595
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual


SQL>  exec dbms_shared_pool.purge('2587FFAC,515825595','C');

PL/SQL procedure successfully completed.

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

no rows selected

SQL> !date
Tue Feb 12 16:55:15 CST 2013

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 16:55:23

SQL> select FIRST_LOAD_TIME FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

FIRST_LOAD_TIME
--------------------------------------
2013-02-12/16:43:59

这里可以看出来第一次执行sql语句的时候，FIRST_LOAD_TIME为2013-02-12/16:43:59，然后我使用dbms_shared_pool.purge”清除掉”了SQL语句在shared pool中的信息，但是当我再次执行执行相同的sql时候，查询发现FIRST_LOAD_TIME时间未发生改变.因为v$sql中对应的只有一张基表x$kglcursor_child,并没有where条件,而让记录不在v$sql中显示,证明是x$基表的东西发生了改变,而该基表是直接来自内存,从而个人猜测,oracle的dbms_shared_pool.purge是在shared pool该sql的内存某些部位增加了某些标记,从而使得该sql不能在v$sql等相关视图中显示,如果sql以前占用的内存区域没有被老化出shared pool,下次该sql再次访问的时候,优先启用该内存区域并修改相关值,从而出现了我们的FIRST_LOAD_TIME不改变的现象.

验证猜测

--session 1
SQL> exec dbms_shared_pool.purge('2587FFAC,515825595','C');

PL/SQL procedure successfully completed.

SQL> select FIRST_LOAD_TIME FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

no rows selected

SQL> declare
  2  begin 
  3  FOR a IN  1..10000000  
  4  LOOP  
  5  EXECUTE IMMEDIATE 'insert into t_xifenfei values ('||a||')';
  6  END LOOP;
  7  commit;
  8  end;  
  9  /

--session 2
SQL> select count(sql_text) from v$sql where sql_text like 'insert into t_xifenfei%'
  2  ;

COUNT(SQL_TEXT)
---------------
            444

SQL> /

COUNT(SQL_TEXT)
---------------
            445

SQL> /

COUNT(SQL_TEXT)
---------------
            444

SQL> /

COUNT(SQL_TEXT)
---------------
            442
--动态sql还在执行，但是共享池中的该sql不再增加，说明共享池已经满，
--部分历史的sql语句已经被刷新出共享池purge的sql语句肯定被老化出来了shared pool，然后再次执行该sql语句

--session 3
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 17:09:08

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/17:09:07

这里可以看到当shared pool发生部分数据被刷出来之时，而且根据先进先出的原则,我们可以知道开始被purge的sql语句肯定被老化出shared pool,从而当再次执行相同sql的时候，生成了新的FIRST_LOAD_TIME,从而验证了部分猜测.
在此也补充另外一个朋友的咨询问题:在什么情况下FIRST_LOAD_TIME会发生改变,我认为是当sql语句占用的内存区域被老化出去,然后再进入内存的时候会发生改变,flush shared_pool实现效果和老化出来一样

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 17:09:08

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/17:09:07

SQL> alter system flush shared_pool;

System altered.

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

no rows selected

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 18:52:33

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/18:52:33

因为shared pool的东西很复杂,我这里也只是大概的初步猜测，没有深入到系统级别dump之类的方法分析，如果有兴趣的朋友可以深入研究并探讨.

*_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$表中信息清空

为什么不建议客户把业务数据存放到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.