통계정보 갱신 식별
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 상태인 테이블에 대해 통계정보를 수집한다.
10g부터 조회 가능 하고 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?”
<기본편> 10% 이상 변경 된 테이블만 확인하는 쿼리
select *
from (select a.owner,
a.table_name
from dba_tables a,
sys.dba_tab_modifications b
where a.owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and b.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and a.table_name=b.table_name
and TRUNCATED = ‘NO’
and b.partition_name is null
and ( TRUNC((INSERTS-DELETES+UPDATES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or TRUNC((INSERTS-DELETES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or num_rows is null)
group by a.OWNER, a.TABLE_NAME
union
select a.table_owner owner,
a.table_name
from dba_tab_partitions a,
sys.dba_tab_modifications b
where a.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and b.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and a.table_name=b.table_name
and TRUNCATED = ‘NO’
and b.partition_name = a.partition_name
and ( TRUNC((INSERTS-DELETES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or TRUNC((INSERTS-DELETES+UPDATES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or num_rows is null )
group by a.TABLE_OWNER, a.TABLE_NAME )
group by owner, table_name
order by owner desc , table_name;
<응용편> 10% 이상 변경된 테이블 만 통계정보 뜨고 싶을 때
select ‘exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ”’||owner||”’ , tabname => ”’||table_name||”’, estimate_percent=> 100 , method_opt => ” FOR ALL COLUMNS SIZE ‘||
case
when owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’) then ‘ 1’
else ‘ 1′
end ||”’ , granularity => ”GLOBAL”, cascade => true , degree => 3 ) ; ‘ scripts
from (select a.owner,
a.table_name
from dba_tables a,
sys.dba_tab_modifications b
where a.owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and b.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and a.table_name=b.table_name
and TRUNCATED = ‘NO’
and b.partition_name is null
and ( TRUNC((INSERTS-DELETES+UPDATES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or TRUNC((INSERTS-DELETES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or num_rows is null)
group by a.OWNER, a.TABLE_NAME
union
select a.table_owner owner,
a.table_name
from dba_tab_partitions a,
sys.dba_tab_modifications b
where a.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and b.table_owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
and a.table_name=b.table_name
and TRUNCATED = ‘NO’
and b.partition_name = a.partition_name
and ( TRUNC((INSERTS-DELETES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or TRUNC((INSERTS-DELETES+UPDATES)/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
or num_rows is null )
group by a.TABLE_OWNER, a.TABLE_NAME )
group by owner, table_name
order by owner desc , table_name;
또다른 테이블 I/O 확인하는 쿼리 로 v$segment_statistics view로 조회하는 쿼리입니다.
이 쿼리는 dba_tab_modifications 테이블에서 DML에 의해 변경된 건수와
dba_tables의 num_rows를 비교해 10% 이상 변경된 테이블 조회 쿼리로 확정치라고 볼 수 있습니다.
select table_owner, m.table_name, num_rows,
round(num_rows/10,0) “NUM_ROWS/10”,
inserts+updates+deletes “to_changed”,
INSERTS,UPDATES,DELETES,TIMESTAMP,LAST_ANALYZED,
round(LAST_ANALYZED-TIMESTAMP,0) GAP
from dba_tab_modifications m, dba_tables t
where table_owner not in (‘SYS’)
and t.table_name=m.table_name
and t.owner=m.table_owner
and round(num_rows/10,0)-(inserts+updates+deletes)<0
order by LAST_ANALYZED-TIMESTAMP;
V$뷰는 인스턴스 기동후 부터 데이터가 축적되는 값이기 때문에, 정확한 값이라기 보다는 대략적인 참조용 입니다.