How to set Table Monitoring in Oracle and Relationship with STATISTICS_LEVEL

Last updated on July 28th, 2018 at 04:08 pm

What is Table Monitoring in Oracle

Table monitoring is checking for how many updates,deletes ,insert 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 -Automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on 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.

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.

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 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 depend on the monitoring data to determine when to collect statistics on objects with stale statistics.

Prior to 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 prior to 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:

SQL> exec dbms_stats.set_table_prefs(null,’SCITT’,’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’ ;

Related Articles

Oracle Tutorial: How to check Stale statistics

Incremental Statistics Gathering in 11g

Optimizer Mode

Top Oracle 12c New Features for developers

How to use Optimizer hints

How to find optimizer underscore parameter

Leave a Reply