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.
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:
greate. but what is the purpose of dbms_lock.sleep
please explain
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 !
Post a Comment