통계정보갱신 / 테이블 DML 변경량 확인(DBA_TAB_MODIFICATIONS)

출처는 아래와 같습니다.

[출처] 통계정보갱신 / 테이블 DML 변경량 확인(DBA_TAB_MODIFICATIONS)|작성자 한여름

http://blog.naver.com/k65fac/40105702092

-------------------------------------------------------------------------------------------------------

 

통계정보 갱신 식별

 

alter table emp monitoring;

 

statistics_level이 typical 이상 설정시 오라클을 테이블에 발생하는 dml을 모니터링한다. 수집된 테이블별 DML은 *_tab_modifications뷰

를 통해 조회할수 있으며, insert,update,delete는 마지막 통계정보가 수집된 이후의 dml 발생량이다.

오라클은 모니터링 대상 테이블에 10%이상 변경이 발생했을때 해당 테이블을 stale 상태(*_tab_statistics 뷰에서 stale_stats=yes)로 변경

하고, gather_database_stats 또는 gather_schema_stats 프로시저를 호출하면서 option 인자에 'gather stale' 또는 'gather auto'를

지정하면 stale 상태인 테이블에 대해 통계정보를 수집한다.

 

11g에서는 object별 임계치 설정이 가능

 

실제 10%이상 변경이 되어서 *_tab_modifications *_tab_statistics뷰의 stale_stats 컬럼에 변화가 생기지 않는것은 변경된 결과를 shared_pool에 모았다가 smon이 3시간  주기로 딕셔너리에 반영하기 때문이다. 즉시 반영을 하려면 dbms_stats.flush_database_monitoring_info 프로시저를 호출하면 된다.

 

 

테이블에 대한 dml 변경량을 확인하는 뷰로 최근 ANALYZE 이후 10% 이상 변경분에 대한 기록을

남긴다.  v$segment_statistics view 병행하여 테이블에 대한 변경량을 추정을 할수 있음.

 

10%의 오차와 통계정보 수집시기에 따른 오차가 발생할수 있으며, 대략적인 변동을 추정하는 자료로

활용가능함.

 

I am having the same exact problem, tables are getting modified and TIMESTAMP column in *_TAB_MODIFICATIONS is not getting updated, however, UPDATES/DELETES/INSERTS columns are.

This is 10gR2. STATISTICS_LEVEL is TYPICAL. I have ran many times dbms_stats.FLUSH_DATABASE_MONITORING_INFO but, only UPDATES/DELETES/INSERTS change, TIMESTAMP remains the same. Also, I have one table that is >= 10% modified, but GATHER_STATS_JOB is not choosing it for collecting statistics.

 

<blockquote></blockquote>

DBA_TAB_MODIFICATIONS View Source

Oracle 11g's data dictionary defines the DBA_TAB_MODIFICATIONS view using the following source query:

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#

;

 

 

*예제 2

Automatic stats(default 10g, manual 9i). Examine status
begin
  dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
end;

select 
num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name,
inserts, updates, deletes, timestamp, truncated
, to_char(perc_updates, 'FM999,999,999,990.00') perc_updates 
from (
 select a.* 
 , nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates
 from (
   select 
    (select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
    and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows
  , (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
    and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed
  , (inserts + updates + deletes) tot_updates
  , DBA_TAB_MODIFICATIONS.* 
  from sys.DBA_TAB_MODIFICATIONS
 ) a
) b
where perc_updates > 10

;

Column description of the DBA_TAB_MODIFICATIONS view:
TABLE_OWNER 
Description of DBA_TAB_MODIFICATIONS.TABLE_OWNER: "Owner of modified table"
TABLE_NAME 
Description of DBA_TAB_MODIFICATIONS.TABLE_NAME: "Modified table"
PARTITION_NAME 
Description of DBA_TAB_MODIFICATIONS.PARTITION_NAME: "Modified partition"
SUBPARTITION_NAME 
Description of DBA_TAB_MODIFICATIONS.SUBPARTITION_NAME: "Modified subpartition"
INSERTS 
Description of DBA_TAB_MODIFICATIONS.INSERTS: "Approximate number of rows inserted since last analyze"
UPDATES 
Description of DBA_TAB_MODIFICATIONS.UPDATES: "Approximate number of rows updated since last analyze"
DELETES 
Description of DBA_TAB_MODIFICATIONS.DELETES: "Approximate number of rows deleted since last analyze"
TIMESTAMP 
Description of DBA_TAB_MODIFICATIONS.TIMESTAMP: "Timestamp of last time this row was modified"
TRUNCATED 
Description of DBA_TAB_MODIFICATIONS.TRUNCATED: "Was this object truncated since the last analyze?"
DROP_SEGMENTS 
 
Description of DBA_TAB_MODIFICATIONS.DROP_SEGMENTS: "Number of (sub)partition segment dropped since the last analyze?"


Re: DBA_TAB_MODIFICATIONS not updated  
Yves KERVADEC 
Gold
  Yves KERVADEC  Sep 16, 2013 4:06 PM  (in response to Stefan Koehler)  

--------------- 일부 사이트에서 아래와 같은 현상이 생김 -------------------------
카운트는 실행되지만 실제 테이블 목록이 나타나지 않는경우, 쿼리나 히든 파라메터를 이용하여
조회해 볼수 있음

You were true Stefan
 
I's fully the bug you quoted, and it's a scary one !
 
SQL> select count(1) from DBA_TAB_MODIFICATIONS;
  COUNT(1)
      414
SQL> select * from  DBA_TAB_MODIFICATIONS;
no rows selected
 
I've tested changing the predicate order (underlined in yellow) of the code used by the DBA_TAB_MODIFICATIONS view as said in the here under MOC Kb and it works...
 
-- the regular view code
SQL> select u.name, o.name, null, null,
  2 m.inserts, m.updates, m.deletes, m.timestamp,
  3 decode(bitand(m.flags,1),1,'YES','NO'),
  4 m.drop_segments
  5  from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
  6 where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
  7 union all
  8 select u.name, o.name, o.subname, null,
  9 m.inserts, m.updates, m.deletes, m.timestamp,
 10 decode(bitand(m.flags,1),1,'YES','NO'),
 11 m.drop_segments
 12  from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
 13 where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
 14 union all
 15 select u.name, o.name, o2.subname, o.subname,
 16 m.inserts, m.updates, m.deletes, m.timestamp,
 17 decode(bitand(m.flags,1),1,'YES','NO'),
 18 m.drop_segments
 19  from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
 20 sys.user$ u
 21 where o.obj# = m.obj# and o.owner# = u.user# and o.obj# = tsp.obj# and o2.obj# = tsp.pobj#;
 
no rows selected
 
-- the view code with predicate order changed
SQL> select u.name, o.name, null, null,
  2 m.inserts, m.updates, m.deletes, m.timestamp,
  3 decode(bitand(m.flags,1),1,'YES','NO'),
  4 m.drop_segments
  5  from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
  6 where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
  7  union all
  8 select u.name, o.name, o.subname, null,
  9 m.inserts, m.updates, m.deletes, m.timestamp,
 10 decode(bitand(m.flags,1),1,'YES','NO'),
 11 m.drop_segments
 12  from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
 13 where o.obj# = m.obj# and o.owner# = u.user# and o.type#=19
 14  union all
 15 select u.name, o.name, o2.subname, o.subname,
 16 m.inserts, m.updates, m.deletes, m.timestamp,
 17 decode(bitand(m.flags,1),1,'YES','NO'),
 18 m.drop_segments
 19  from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
 20 sys.user$ u
 21 where o.obj# = m.obj# and o.obj# = tsp.obj# and o2.obj# = tsp.pobj# and o.owner# = u.user#;
 
414 rows selected.
 
 
So I will set the parameter "_optimizer_join_factorization"=false to fix this issue.
 
Thanks a lot for your help !
 
Best regards
 
 
13984324  wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS
 
Affects:

Product (Component)
 
Oracle Server (Rdbms) 
 
Range of versions believed to be affected
 
Versions >= 11.2 but BELOW 12.1 
 
Versions confirmed as being affected
 •11.2.0.3 
•11.2.0.2 
 
Platforms affected  
Generic (all / most platforms affected) 
 
 
 
 
 
Fixed:

This issue is fixed in
 •11.2.0.4 (Future Patch Set) 
 
 
Wrong result are possible with :
 
1) at least 3 branches in UNION ALL
2) at least 2 join factorization predicates
3) Predicate order in branches is different
4) Correct result with "_optimizer_join_factorization"=false;