I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale

Search My Oracle Blog

Custom Search

01 May, 2012

USER_TAB_MODIFICATIONS -- 1

Last week, conducting a portion of a training program on SQL Tuning, I briefly spoke about USER_TAB_MODIFICATIONS.

Here is a quick example to show how this view maintains information about the level of DML (INSERT / UPDATE / DELETE row counts) that have occurred since the last GATHER_STATS execution against the table. This information is used by the GATHER_STATS call to determine if statistics are stale (by comparing the level of DML with the last known NUM_ROWS in the table).

Notes :
(1) Once a GATHER_STATS is executed, the view is "reset" for the table(s) where Stats have been updated.
(2) The View is updated only by a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO call which can be executed manually OR is, nevertheless, updated automatically by the background processes -- just as Workload statistics (used for AWRs) are updated automatically.

In view of the above two points, you should not rely on simply querying USER_TAB_MODIFICATIONS to identify the level of DML.

As you can see, even if I populate my target table with rows, USER_TAB_MODIFICATIONS does not reflect this information until and unless DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO is called. Similarly, the last set of commands demnostrates how a DBMS_STATS.GATHER_TABLE_STATS has purged this view of information on my target tble (UTM_TARGET).
.
.
.

2 comments:

I put the 120seconds (2minutes) sleep so that the timestamps get incremented. That way one can check the timestamp reported by SQLPlus and the TIMESTAMP from the USER_TAB_MODIFICAITONS view to confirm that what I am presenting truly does show the statistics !