Table별 DML 현황

Table 단위 DML 현황 조회

SELECT M.TABLE_OWNER,
   M.TABLE_NAME,T.NUM_ROWS,ROUND(NUM_ROWS/10,0) "NUM_ROWS/10", 
   INSERTS+UPDATES+DELETES "tot_changed",
   INSERTS, UPDATES, DELETES , 
   m.TIMESTAMP,
   LAST_ANALYZED,ROUND(sysdate - m.TIMESTAMP,0) GAP
FROM DBA_TAB_MODIFICATIONS M,DBA_TABLES T
WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND T.TABLE_NAME = M.TABLE_NAME
AND T.OWNER = M.TABLE_OWNER
AND M.TABLE_OWNER = 'SCOTT'
AND TO_CHAR(TIMESTAMP,'YYYY') = '2023'
ORDER BY LAST_ANALYZED - TIMESTAMP

 

% number of rows DML since last analyze time (마지막 통계정보 생성 이 후 DML Count(10%변경분))

 

개별 Table로 조회 시

select o.object_name,m.* 
from SYS.MON_MODS_ALL$ m, dba_objects o
where m.obj# = o.object_id
and object_name = 'TB_TEST01'

 

shared_pool에 모았다가 3시간주기로 Dictionary Veiw에 반영, 즉시 반영하려면
exec dbms_stats.flush_database_monitoring_info 실행