oracle table modified
来源:互联网 发布:产品目录软件 编辑:程序博客网 时间:2024/06/06 19:07
exec
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
select * from dba_tab_modifications where table_name like '%TMS_PTINT%';
select table_name,owner,logging,dropped,STATUS,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name ='EMP';
DBA_UPDATABLE_COLUMNS
DBA_UNUSED_COL_TABS
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
dbms_metadata.get_ddl
*********************************
DBA_TAB_MODIFICATIONS
sys.dba_tab_modifications用来存储自上次收集统计信息以来,DML语句对某个表一共修改了多少行数据.
另外还有all_tab_modifications和user_tab_modifications。
SQL> desc sys.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
通过dbms_metadata.get_ddl语句取得dba_tab_modifications的定义.
--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#;
对应的基表是mon_mods_all$,同时关联了obj$, tabsubpart$, user$.
SQL> desc sys.mon_mods_all$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NUMBER
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
FLAGS NUMBER
DROP_SEGMENTS NUMBER
做一个简单的测试,看看dba_tab_modifications是如何统计DML语句对某一张表的变化的.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1';
no rows selected
SQL> delete from t1 where rownum < 100;
99 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
--删除了99行
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1';
TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
T1 0 0 99 NO 17-APR-14
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1';
no rows selected
SQL> delete from t1 where rownum < 100;
99 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
--删除了99行
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1';
TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
T1 0 0 99 NO 17-APR-14
测试DML操作累计达到表记录数的10%左右时, 该表的统计信息变为过期状态。
SQL> select count(*) from t1;
COUNT(*)
----------
100000
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 NO
SQL> delete from t1 where rownum <= 9999;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1' 2 3 ;
TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
T1 0 0 9999 NO 17-APR-14
--T1表的统计信息已经显示为STALE状态,需要重新收集统计信息
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 YES
--重新收集T1表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
--DML修改的记录信息已经从dba_tab_modifications中清除
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1' 2 3 ;
no rows selected
--T1表的统计信息状态已经更新
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 NO
SQL> select count(*) from t1;
COUNT(*)
----------
100000
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 NO
SQL> delete from t1 where rownum <= 9999;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1' 2 3 ;
TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP
------------------------------ ---------- ---------- ---------- --- ---------
T1 0 0 9999 NO 17-APR-14
--T1表的统计信息已经显示为STALE状态,需要重新收集统计信息
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 YES
--重新收集T1表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1');
PL/SQL procedure successfully completed.
--DML修改的记录信息已经从dba_tab_modifications中清除
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from sys.dba_tab_modifications
where table_owner=user and table_name= 'T1' 2 3 ;
no rows selected
--T1表的统计信息状态已经更新
SQL> select table_name,stale_stats from dba_tab_statistics
where owner=user and table_name='T1'; 2
TABLE_NAME STA
------------------------------ ---
T1 NO
到了这里我们知道一个DML语句在执行过程中,除了通常我们所知道的生成执行计划、产生相应的等待事件之外,还需要记录该语句执行改变了相关表多少行记录。同时dbms_stats.flush_database_monitoring_info()会定期执行刷新操作,将改变的记录刷新到dba_tab_statistics表,当一个表的变化累计到一定的程度的时候,则会将dba_tab_statistics.stale_stats字段更改为YES, 标志该表的统计信息已经过期,需要重新收集统计信息。
===========================================================================================================
STEP1: *** create a table crc.gs , analyze it and then fill test_gs.gs with 100 rows and perform some DML
-------------------------------------------------------------------------------------
create user crc identified by crc
default tablespace users temporary tablespace temp;
grant connect,resource to crc;
connect crc/crc
alter session set nls_language = american;
alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS';
create table crc.gs (i number);
begin
dbms_stats.gather_schema_stats(
ownname =>'CRC',
estimate_percent => dbms_stats.auto_sample_size,
method_opt =>'FOR ALL COLUMNS SIZE AUTO',
degree => 1,
granularity => 'ALL',
cascade => true,
options => 'GATHER'
);
end;
/
begin
for i in 1..100 loop
insert into CRC.gs values(i);
end loop;
commit;
end;
/
delete from CRC.gs where i between 40 and 60;
commit;
update CRC.gs set i=i+1000 where i between 80 and 100;
commit;
STEP2: *** select and use the procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
-------------------------------------------------------------------------------------------
Note: The procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary.
Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS
views are updated immediately, without waiting for the Oracle database to flush them periodically (per default every 3 hours). This procedure is useful when you need up-to-date information in those views.
SQL> select TABLE_OWNER, TABLE_NAME, INSERTS , UPDATES, DELETES, TIMESTAMP from
2 sys.dba_tab_modifications where TABLE_OWNER='CRC';
no rows selected
SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> col table_name format a5
SQL> col table_owner format a10
SQL> select TABLE_OWNER, TABLE_NAME, INSERTS , UPDATES, DELETES, TIMESTAMP from
2 sys.dba_tab_modifications where TABLE_OWNER='CRC';
TABLE_OWNER TABLE INSERTS UPDATES DELETES TIMESTAMP
------------- ----- ---------- ---------- ---------- -----------------
CRC GS 100 21 21 18-03-09 15:34:37
==> Because of the 'high' volumne of DML (100 inserts, 21 updates and 21 deletes) we have an entry in the table
sys.dba_tab_modifications for the table 'GS'.
STEP3: *** analyze again the table GS which leads to an empty sys.DBA_TAB_MODIFICATIONS for table 'GS'
-----------------------------------------------------------------------------------------------------------------------
SQL> begin
2 dbms_stats.gather_schema_stats(
3 ownname =>'CRC',
4 estimate_percent => dbms_stats.auto_sample_size,
5 method_opt =>'FOR ALL COLUMNS SIZE AUTO',
6 degree => 1,
7 granularity => 'ALL',
8 cascade => true,
9 options => 'GATHER'
10 );
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select TABLE_OWNER, TABLE_NAME, INSERTS , UPDATES, DELETES, TIMESTAMP from
2 sys.dba_tab_modifications where TABLE_OWNER='CRC';
no rows selected ==> which is normal due to the analyze command
STEP4: *** now perform only 1 update on the table, flush the monitoring information out and then check the
entry in DBA_TAB_MODIFICATIONS
---------------------------------------------------------------
SQL> update crc.gs gs set i=i+100 where i=30;
1 row updated.
SQL> commit;
Commit complete.
SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select TABLE_OWNER, TABLE_NAME, INSERTS , UPDATES, DELETES, TIMESTAMP from
2 sys.dba_tab_modifications where TABLE_OWNER='CRC';
no rows selected
Note: please be aware that this is only an example. Other values/dml changes may show different results.
References
NOTE:456535.1 - DB Monitoring Automatic Statistics Collection Not updating fields correctly
- oracle table modified
- error: variably modified 'table' at file scope
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- oracle table
- Oracle Temp Table
- Oracle Pipelined Table
- Temp Table in Oracle
- Oracle Pipelined Table Functions
- Oracle Pipelined Table
- zoj 1649 Rescue (bfs+队列)
- 机房收费系统VB版(一)——系统分析
- javaScript学习_利用表单获取数据 .
- 微信公众帐号开发教程第8篇-文本消息中使用网页超链接
- android ImageButton 和Button的图标区别
- oracle table modified
- 深入理解计算机系统之异常控制流----fork打印问题
- 八大排序算法
- Android 多线程下载
- 简单题
- 使用jquery获取url以及jquery获取url参数的方法
- 翻译www.djangobook.com之第八章:高级视图和URL配置
- Oracle建立全文索引详解
- IPV6编程