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.


23:13:45 SQL> REM Demo User_Tab_Modifications
23:13:45 SQL> 
23:13:45 SQL> alter session set nls_date_format='DD-MON HH24:MI:SS';

Session altered.

23:13:45 SQL> 
23:13:45 SQL> -- create the target table
23:13:45 SQL> drop table UTM_TARGET  purge;

Table dropped.

23:13:45 SQL> create table UTM_TARGET
23:13:45   2  as select * from dba_objects
23:13:45   3  where 1=2;

Table created.

23:13:45 SQL> select count(*) from UTM_TARGET;

  COUNT(*)
----------
         0

23:13:45 SQL> 
23:13:45 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:15:45 SQL> 
23:15:45 SQL> -- query USER_TAB_MODIFICATIONS
23:15:45 SQL> select timestamp,inserts,updates,deletes
23:15:45   2  from user_tab_modifications
23:15:45   3  where table_name = 'UTM_TARGET';

no rows selected

23:15:45 SQL> 
23:15:45 SQL> -- insert rows and commit
23:15:45 SQL> insert into UTM_TARGET
23:15:45   2  select * from dba_objects;

76630 rows created.

23:15:46 SQL> commit;

Commit complete.

23:15:46 SQL> 
23:15:46 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:17:46 SQL> 
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46   2  from user_tab_modifications
23:17:46   3  where table_name = 'UTM_TARGET';

no rows selected

23:17:46 SQL> 
23:17:46 SQL> -- flush monitoring info
23:17:46 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:17:46 SQL> 
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46   2  from user_tab_modifications
23:17:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46      76630          0          0

23:17:46 SQL> 
23:17:46 SQL> -- what about Direct Path INSERTs ? are they captured
23:17:46 SQL> insert /*+ APPEND */ into UTM_TARGET
23:17:46   2  select * from dba_objects;

76630 rows created.

23:17:46 SQL> 
23:17:46 SQL> -- run a query. It should error if Direct Path INSERT has been used
23:17:46 SQL> select count(*) from UTM_TARGET;
select count(*) from UTM_TARGET
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


23:17:46 SQL> 
23:17:46 SQL> -- commit so that the Direct Path INSERT is visible
23:17:46 SQL> commit;

Commit complete.

23:17:46 SQL> 
23:17:46 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:19:46 SQL> 
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46   2  from user_tab_modifications
23:19:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46      76630          0          0

23:19:46 SQL> 
23:19:46 SQL> -- flush monitoring info
23:19:46 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:19:46 SQL> 
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46   2  from user_tab_modifications
23:19:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:19:46     153260          0          0

23:19:46 SQL> 
23:19:46 SQL> -- run an UPDATE
23:19:46 SQL> update UTM_TARGET
23:19:46   2  set owner = 'SYTEM2' where owner = 'SYSTEM';

1058 rows updated.

23:19:47 SQL> commit;

Commit complete.

23:19:47 SQL> 
23:19:47 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- flush monitoring info
23:21:47 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47   2  from user_tab_modifications
23:21:47   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:21:47     153260       1058          0

23:21:47 SQL> 
23:21:47 SQL> 
23:21:47 SQL> -- what happens after we call  DBMS_STATS.GATHER_TABLE_STATS  ?
23:21:47 SQL> exec dbms_stats.gather_table_stats('HEMANT','UTM_TARGET');

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47   2  from user_tab_modifications
23:21:47   3  where table_name = 'UTM_TARGET';

no rows selected

23:21:47 SQL> -- the GATHER_STATS call has now flushed the view
23:21:47 SQL> -- the DML "counter" has got reset to 0 -- w.r.t. the time when the Gather_Stats is executed
23:21:47 SQL> 
23:21:47 SQL> 
23:21:47 SQL> -- run a DELETE
23:21:47 SQL> delete UTM_TARGET
23:21:47   2  where owner = 'SYTEM2' ;

1058 rows deleted.

23:21:47 SQL> commit;

Commit complete.

23:21:47 SQL> 
23:21:47 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:23:47 SQL> 
23:23:47 SQL> -- flush monitoring info
23:23:47 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:23:47 SQL> 
23:23:47 SQL> -- query USER_TAB_MODIFICATIONS
23:23:47 SQL> select timestamp,inserts,updates,deletes
23:23:47   2  from user_tab_modifications
23:23:47   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:23:47          0          0       1058

23:23:47 SQL> 
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:

Unknown said...

greate. but what is the purpose of dbms_lock.sleep
please explain

Hemant K Chitale said...

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 !