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.


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:

Adnan Rafi 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 !

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016