当前位置:操作系统 > Unix/Linux >>

Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1]

Why does DBA_TAB_MODIFICATIONS sometimes have no values [ID 762738.1]
 
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 易做图yzed 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 , 易做图yze 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: *** 易做图yze 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 易做图yze 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 successf
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,