月归档：五月 2013

使用IDLE_TIME注意事项

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

需要定时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配置时间，数据库依然会强制终止会话，并且回滚事务

发表在 Oracle | 一条评论

dbms_shared_pool.purge工作原理猜测

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

思考为什么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之类的方法分析，如果有兴趣的朋友可以深入研究并探讨.

发表在 Oracle | 标签为 dbms_shared_pool.purge | 一条评论

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$ | 评论关闭

17813235971

QQ 咨询

月归档：五月 2013

使用IDLE_TIME注意事项

dbms_shared_pool.purge工作原理猜测

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

文章分类

最近发表

月归档：五月 2013

使用IDLE_TIME注意事项

dbms_shared_pool.purge工作原理猜测

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

标签云

文章分类

最近发表