DBA_TAB_MODIFICATIONS July 2, 2010
Posted by mwidlake in internals, performance, statistics.Tags: data dictionary, SQL, statistics
21 comments
I’ve mentioned DBA_TAB_MODIFICATIONS (and there is also of course ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS) in a couple of posts, but mostly buried in long, rambling posts about other things. So here is an overview and demonstration of DBA_TAB_MODIFICATIONS. Skip to the end if you just want to see an example of how it works.
The below example is from 10.2.0.3 but for V11 it is pretty much the same, as is 9 except where I say otherwise.
SYS.DBA_TAB_MODIFICATIONS is used by oracle internally to track how many inserts, updates and deletes have been done to a table, table partition or table subpartition since the segment last had stats gathered on it with dbms_stats. It also records if the segment has been truncated since the last stats gather. Why since the last stats gather? Because Oracle uses it internally to detect how much a segment has changed and if it is 10% or more oracle will deem it stale for stats gathering purposes. (under 11 you can alter the stale percentage). Note I said SYS.DBA_TAB_MODIFICATIONS – on 10 at least the public synonym for it is missing, so unlike most other DBA_ views you need to state the owner (or create a synonym).
OWNER.TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU ------------------- ---------- ---------- ---------- ----------------- --- XXXXXXX.YYYYYYYYYYY 22598264 0 1 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 5 0 0 19-SEP-2007 01:47 NO XXXXXXX.YYYYYYYYYYY 888766 0 0 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 3191 1486 0 27-NOV-2009 05:11 NO XXXXXXX.YYYYYYYYYYY 34742 0 0 08-MAR-2010 15:16 NO XXXXXXX.YYYYYYYYYYY 0 7192 0 02-JUL-2010 05:00 NO XXXXXXX.YYYYYYYYYYY 0 1 0 10-MAR-2010 15:16 NO XXXXXXX.YYYYYYYYYYY 8 8 8 26-JAN-2010 08:05 NO XXXXXXX.YYYYYYYYYYY 1533536 0 2 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 281 0 0 11-SEP-2009 03:00 NO
Under oracle 9 you have to register a table as MONITORED before this information is gathered. Under 10 and 11 all tables/partitions/subpartitions are monitored and you can’t turn that monitoring off {you can try, but oracle ignores you 🙂 }.
The information can be very useful for checking how volatile a segment is, if it has been changed a lot since the last time stats were gathered on it and you can also add the values held in DBA_TAB_MODIFICATIONS to the value for NUM_ROWS held for the segment and get a very accurate estimate of the current number of rows. It is a lot, lot faster than actually counting them!
The information on inserts/updates and deletes is gathered for pretty much all DML against tables (see an up-coming post for an example of this not being true). Direct load SQL*Loader and other direct-io activity can skip being recorded but insert-append, using the /*+ append */ hint is recorded correctly {I suspect this was not true for V9 and 10.1 but am no longer sure}. This information is initially held in memory and only later pushed into DBA_TAB_MODIFICATIONS and so you may not see the latest information. Under oracle 9 this information is flushed down every 15 minutes I believe, under 10.1 it is 3 hours and under 10.2 onwards the information is only flushed down when stats are gathered against the segment OR you manually flush the information down to the database.
flushing the latest information is achieved with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. It is relatively quick to run, normally taking less than a few seconds.
When statistics are gathered on a segment, any corresponding rows in DBA_TAB_MODIFOCATIONS is deleted, not updated to zeros, and is recreated only when relevent inserts,updates, deletes or truncates occur on the segment.
the DBA_TAB_MODIFICATIONS view sits on top of sys.mon_mods_all$ as well as obj$,user$ and the usual suspects. sys.mon_mods_all$ does not contain any more information that the view exposes.
desc sys.dba_tab_modifications
Name Null? Type
----------------------------------------------------- -------- ------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER
--View description
TEXT
-----------------------------------------------------------------------
SYS DBA_TAB_MODIFICATIONS 9
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#
-- underlying sys.$ table
desc sys.mon_mods_all$
Name Null? Type
----------------------------------------------------- -------- ------
OBJ# NUMBER
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
FLAGS NUMBER
DROP_SEGMENTS NUMBER
Here is a demo of it in action:
TDB>-- clear down the test table.
TDB>-- if you do not have access to DBA_TAB_MODIFICATIONS change to ALL_TAB_MODIFICATIONS
TDB>drop table test_1 purge;
Table dropped.
TDB>create table test_1
2 as select * from all_objects where rownum<1
3 /
Table created.
TDB>select count(*) from test_1;
COUNT(*)
----------
0
1 row selected.
TDB>-- WHAT IS IN DBA_TAB_MODIFICATIONS
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- OK, let's flush down the information
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>-- Still nothing as no activity has occurred on the table.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- Now create some data
TDB>insert into test_1
2 select * from dba_objects where rownum <= 100
3 /
100 rows created.
TDB>commit;
Commit complete.
TDB>select count(*) from test_1;
COUNT(*)
----------
100
1 row selected.
TDB>-- WHAT IS IN DBA_TAB_MODIFICATIONS
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- OK, let's flush down the information
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 100 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- The information in DBA_TAB_MODIFICATIONS is used by Oracle to detect if a table
TDB>-- (or partition) in a tables is stale - changed by 10%
TDB>-- Gathering statistics on an object DELETES the record from DBA_TAB_MODIFICATIONS
TDB -- rather than setting all the values to zero.
TDB>--
TDB>exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_1')
PL/SQL procedure successfully completed.
TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- now do some activity again and flush it down to the dictionary
TDB>insert into test_1
2 select * from dba_objects where rownum <= 150
3 /
150 rows created.
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 150 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- Direct inserts via insert-append are also captured (I think this might have changed)
TDB>insert /*+ append */ into test_1
2 select * from dba_objects where rownum <= 170
3 /
170 rows created.
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 320 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- now a mixed bag of inserts, updates and deletes
TDB>insert into test_1
2 select * from dba_objects where rownum <= 75
3 and owner not in ('SYS','SYSTEM')
4 /
75 rows created.
TDB>--
TDB>update test_1 set created=sysdate
2 where object_type !='TABLE'
3 /
289 rows updated.
TDB>delete from test_1
2 where object_type='SEQUENCE'
3 /
10 rows deleted.
TDB>commit;
Commit complete.
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 395 289 10 NO 02-JUL-2010 10:21
1 row selected.
If you want to see what else I said about DBA_TAB_MODIFICATIONS then the end of this {way too long} post shows how to use it to get a very fast count of rows in a table and if you have issues with permissions flushing database monitoring see this link about permissions needed
Question. As the data gets flushed from memory to a data dictionary table, it persists the database being bounced. What happens to the data in memory about table changes when thers is a controlled shutdown and when the database crashes?
Martin Widlake's Yet Another Oracle Blog 