How Table Monitoring and STATISTICS_LEVEL parameter are connected from 10g onwards



Last updated on July 17th, 2015 at 06:05 pm

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.

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.

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)


Leave a Reply