没有数据变化是不会产生dba_tab_modifications记录的 应该这样说,当表10%或者更多的数据变化时候,才会重新收集表信息,然后会修改该表记录 Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1] -------------------------------------------------------------------------------- Modified 18-MAR-2009 Type HOWTO Status MODERATED In this Document Goal Solution References--------------------------------------------------------------------------------This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review. Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.3 Information in this document applies to any platform. Oracle Server Enterprise Edition - Version: 10.2.0.3 Goal The goal is to explain why the view DBA_TAB_MODIFICATIONS does sometimes have no values even when the parameter STATISTICS_LEVEL is set to TYPICAL and the specific schema has been analyzed successful using the package DBMS_STATS.GATHER_SCHEMA_STATS. In addition all the tables in that schema shows MONITORING=YES in the view dba_tables. Solution The updates to the table *_tab_modifications are related to the volumne of updates for a table. There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications .See example below: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/crcalter 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 selectedSQL> 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 commandSTEP4: *** 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 selectedNote: 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
应该这样说,当表10%或者更多的数据变化时候,才会重新收集表信息,然后会修改该表记录
Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1] -------------------------------------------------------------------------------- Modified 18-MAR-2009 Type HOWTO Status MODERATED In this Document
Goal
Solution
References--------------------------------------------------------------------------------This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3
Goal
The goal is to explain why the view DBA_TAB_MODIFICATIONS does sometimes have no values
even when the parameter STATISTICS_LEVEL is set to TYPICAL and the specific schema has been analyzed successful using the package DBMS_STATS.GATHER_SCHEMA_STATS.
In addition all the tables in that schema shows MONITORING=YES in the view dba_tables.
Solution
The updates to the table *_tab_modifications are related to the volumne of updates for a table.
There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications .See example below: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/crcalter 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 selectedSQL> 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 commandSTEP4: *** 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 selectedNote: 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