What is Table Monitoring in Oracle
Table monitoring is checking for how many updates, deletes, and inserts happened in the tables so that we can find out if the statistics are stale or good for the table. It is a very useful functionality in gathering stats for the tables in Oracle
How to Set the Table Monitoring in Oracle
Before Oracle 10g -The automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on the table.
Depending on the MONITORING flag, the GATHER_STATS_JOB job collected “GATHER EMPTY” and “GATHER STALE” on the flagged objects.
ALTER TABLE <tablename> MONITORING;
CREATE TABLE <tablename> MONITORING;
10g onwards -The MONITORING and NOMONITORING keywords are deprecated and will be ignored. Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.
When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.
When STATISTICS_LEVEL is set to ALL, then monitoring is enabled and Timed OS statistics is also collected
By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled.
It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g and above
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. This information on “changes made” is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. The data dictionary information is made visible through the views DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and
USER_TAB_MODIFICATIONS.
Oracle uses these views to identify tables that have stale statistics.
Whenever there is a 10% change in data in a table, Oracle considers its statistics to be stale.
select u.TIMESTAMP,
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(t.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
order by t.last_analyzed
/
Up to date statistics are important to generate good execution plans. Automatic statistics collection job using DBMS_STATS packages depends on the monitoring data to determine when to collect statistics on objects with stale statistics.
- Before Oracle11g, the staleness threshold is hardcoded at 10%. This means that an object is considered stale if the number of rows inserted, updated or deleted since the last statistics gathering time is more than 10% of the number of rows. There is no way to modify this value before Oracle 11g.
- Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.
E.g. to modify and check the staleness threshold for table SCOTT in USER schema:
exec dbms_stats.set_table_prefs(null,'SCOTT','STALE_PERCENT',27)
Example
(1) First create the table
create table Test as select * from user_objects where rownum < 101;
(2) Count the rows
SQL> select count(*) from test;
COUNT(*)
----------
100
(3) Gather the stats
exec execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST');
(4) Check the modified rows
SQL> select * from user_tab_modifications where table_name='TEST' ;
no rows selected
(5) Insert more rows
insert into Test select * from test;
commit;
Now we have inserted equal amount of rows,it will show off in user_tab_modofications table
(6) Flush the information from SGA to Dictionary table
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SQL> select * from user_tab_modifications where table_name='TEST' ;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ------------------------------
------------------------------ ---------- ---------- ---------- --------- ---
TEST
100 0 0 1-OCT-17 NO
(7) If the stats is gathered again on this table, this information will be deleted
exec execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST');
select * from user_tab_modifications where table_name='TEST' ;
no rows selected
Related Articlescheck Stale statistics in oracle
Incremental Statistics Gathering in 11g
Optimizer Mode
Top Oracle 12c New Features for developers
Optimizer hints
Oracle underscore parameter