통계정보 갱신 , 기타 테이블별 I/O 확인하는 방법 테이블 DML 변경량 확인 쿼리


 

통계정보 갱신 식별

 

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$뷰는 인스턴스 기동후 부터 데이터가 축적되는 값이기 때문에, 정확한 값이라기 보다는 대략적인 참조용 입니다.

 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다