jump to navigation

DBA_TAB_MODIFICATIONS July 2, 2010

Posted by mwidlake in internals, performance, statistics.
Tags: , ,
trackback

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?

About these ads

Comments»

1. Kellyn Pedersen - July 3, 2010

Hi Martin!
Great job on this subject and how nice to see somebody that may be more obsessed with this table than I am! :)

I am partial to this feature for capturing what is statistically stale in a database and have turned many of my developers on to using dba_tab_modifications to get counts, even using it to decide in their code when they need to update stats.

I have one complaint- not sure if you have worked with this table in databases with ridiculous amouts of partitioning, but the job that updates the table, in my research at this time, justifies staleness for partitions at the table level. This can be impacting, as where one partition may be stale for a considerable time, it’s only inserted, with the rest of the stale partitions in one, large load to the dba_tab_modifications table when the table it belongs to is scanned by the OCM job in the scheduler.

The impact to the database, as either utilizing the job set by Oracle or by basing your own scripts that utilize this table to justify what needs statistics gathered on, you suddenly can have tens of thousands of partitions that need to have statistics gathered for the night’s maintenance window. With limited windows for maintenance on large warehouse and mart environments, I’ve set checks with thresholds and “staleness level” checks that decide what and how much is updated, then will go out and update everything that is left in the table over the weekend if anything was over the daily run’s threshold.

I would love to see more of this logic be in place in Oracle’s job so I would not have disable and write up my own job to ensure that statistics gathering is not more of an impact than not having statistics updated… :)
Thoughts?

2. mwidlake - July 3, 2010

Hi Kellyn,

Thanks for the nice words and comment.

I have used DBA_TAB_MODIFICATIONS (DTM) on database with sizeable partitioning – hundreds of tables with hundreds to thousands of partitions each – and not had issues with DTM being updated. This is on version 10 and 9, not 11 though. What is OCM in this contect, Oracle Configuration Manager? Is it that you are looking at the data in a central repository and the configuration manager is not picking the data up for you?

For very large database, I have found that the automatic stats gathering job is simply not up to the job (if you are interested look back at my post http://mwidlake.wordpress.com/2009/07/20/automated-statistics-gathering-silently-fails/ – it is one of my earlier attempts so a little rough and ready). Like many others who shepard these big database, I have written my own code using DTM to gather stats. I use a level of staleness of more like 20%, gather table and index stats with their own statements, gather at table and partition level seperately and have a list of exceptions that need stats gathering at lower levels of change. I also alter the sample size down as the segment increases in size, so that though the number rows/blocks considered increases as the segment size increases, it is more like the sample size increases by 25% as the table doubles.

I really need to repeat all this work on 11 but my last couple of clients have been on 10 and I simply don’t have time to create a fake VLDB at home to check it all out. Here’s hoping my next client is on 11!

Oracle 11 does allow the percentage change at which to consider a table stale to be altered, both the default and for individual tables, and I think that is a step forward. It is also a lot better at rolling up partition stats to global stats but I have really not played with that feature as much as I need before I go giving a proper opinion.

Regards,

Martin

3. dba_tab_modifications « Oracle Scratchpad - July 6, 2010

[...] don’t follow the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading. Leave a [...]

4. Hemant K Chitale - July 8, 2010

I need to understand the “TIMESTAMP”. For example see this output :

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> @List_Table_DML_Counts
SQL> select m.table_name, m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows, t.last_analyzed
  2  from sys.dba_tab_modifications m, dba_tables t
  3  where m.table_owner = t.owner
  4  and m.table_name = t.table_name
  5  and m.table_owner = 'HEMANT'
  6  group by m.table_name, m.timestamp, t.num_rows, t.last_analyzed
  7  order by 1,2
  8  /

TABLE_NAME                     TIMESTAMP SUM(M.INSERTS) SUM(M.UPDATES) SUM(M.DELETES)   NUM_ROWS LAST_ANAL
------------------------------ --------- -------------- -------------- -------------- ---------- ---------
T_1                            09-APR-10           1032              0              0    2449614 23-JUN-10
T_2                            24-JUN-10           1774              0           1774    1070949 24-JUN-10
T_3                            25-JUN-10              0           4473              0      60514 24-JUN-10
T_4                            09-APR-10           4132              0              0   11137459 23-JUN-10
T_5                            24-JUN-10              1              0              0         77 24-JUN-10
T_6                            16-JUN-10              0              1              0       4396 23-JUN-10

6 rows selected.

SQL> 

Although the schema has many more tables, only 6 appear in DBA_TAB_MODIFICATIONS. That would mean that only these 6 have had DML since the last GATHER_STATS. However, the supposed last DML pre-dates the Gather_Stats. If Gather_Stats is supposed to have deleted entries, then there should be no entries earlier than the Gather_Stats ?

mwidlake - July 8, 2010

Hi Hemant,

The gathering of stats on a table (or partition) only removes the records for that table (or partition). The other records stay as they were.

The TIMESTAMP column is a the date when that record was created. For ages I thought it was the last time the record had been updated for that segment, but it is not. (NB the column is of type date, despite being called TIMESTAMP. Grrrr).

Use of the old ANALYZE command also removes the relevant record from DBA+TAB_MODIFICATIONS.

So I do not know why your timestamp is before the last analyzed date.

Except, you are grouping records. Why are you grouping records? Are these tables, perhaps, partitioned? You are not excluding objects where sys.dba_tab_modifications.partition_name is not null. So you could be getting partition records in DTM and then you link to DBA_TABLES. The table could well have a later last_analyzed time than some of the partitions.

5. Hemant K Chitale - July 8, 2010

The output captured above doesn’t show that t.last_analyzed in my query shows ’23-JUN-10′ and ’24-JUN-10′ for these tables.

mwidlake - July 8, 2010

I wrapped your code with (in the below I have replaced [ with { and } with } so it prints ):
{sourcecode language=’sql’ gutter=’false’}
and
{/sourcecode}
tags. That makes it fit nicely.

6. Uwe Hesse - July 8, 2010

Very nice posting, thank you! One additional remark: MONITORING is no longer really an attribute of tables – as you pointed out, it is just ignored if you specify it explicitly with ALTER TABLE. Instead, it is derived from the parameter STATISTICS_LEVEL. If this parameter is set to BASIC, all tables are on NOMONITORING. With the setting TYPICAL (the default) or ALL, all tables are on MONITORING.

mwidlake - July 8, 2010

Thanks Uwe. And nice point about MONITORING being controlled via STATISTICS_LEVEL on 10 up. I guess you can turn it off on 10, but only for all tables and by setting STATISTIVS_LEVEL to BASIC.

Is there ever a situation where setting it to BASIC gives you more than you lose? :-)

7. Log Buffer #194, A Carnival of The Vanities for DBAs | The Pythian Blog - July 12, 2010

[...] Martin Widlake gives an overview and demonstration of a little know view, DBA_TAB_MODIFICATIONS. [...]

8. Pythian Group: Log Buffer #194, A Carnival of The Vanities for DBAs | Weez.com - July 12, 2010

[...] Martin Widlake gives an overview and demonstration of a little know view, DBA_TAB_MODIFICATIONS. [...]

9. Alexey - July 22, 2010

>The TIMESTAMP column is a the date when that record was created. For ages I thought it was the last time the record had been >updated for that segment, but it is not.

It IS so, at least in 11.1.0.7

mwidlake - July 22, 2010

Hi Alexy, Thanks for that.
Yes, my understanding under 10 and 11 is that the timestamp column is when the record is created (not last updated, which would have been nice in some ways). It always has been when I have checked it out anyway, but with oracle internals you can rarely be 100% sure there is not some exception…

10. David Horgan - January 22, 2014

Hi,

thanks for the blog. Is there much of an overhead in terms of space, memory usage, performance when turning on monitoring on 9i?

Thanks,
David

mwidlake - January 22, 2014

Hi David, as far as I am aware, no there is no significant overhead in turning on monitoring on 9i. However, it is a long time since I looked at turning on monitoring of table modifications under 9i! As it flushes data automatically every 15 minutes under 9i, the data actually stays more current without you having to do a flush.
One concern I would have is whether the implementation under 9 is as good as in later versions and you might get some latch contention in the internals or locks in the data dictionary if you monitored lots and lots of active tables. I’d do a quick google/metalink (Whoops, MyOracleSupport) search if I was about to do this for a client.

David Horgan - January 22, 2014

Ok great, thank you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 163 other followers

%d bloggers like this: