Oracle Tutorial: How to check Stale statistics

Stats can become stale when the data in the table changes substantially.Up to date statistics are important to generate good execution plans

How Oracle decide if the stats has become stale

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables:

Parameter setting required to Track the table changes

Prior to 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.

In 10g 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 and above
By setting these parameter,Oracle 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. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). 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.

How to check Stale statistics

The below plsql procedure find out all the tables in SCOTT schema which is stale stats


ObjList dbms_stats.ObjectTab;
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);
FOR k in ObjList.FIRST..ObjList.LAST
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(k).ObjName || ‘ ‘ || ObjList(k).ObjType || ‘ ‘ || ObjList(k).partname);

The below sql can also be used to find out insert,updates,deletes

select u.TIMESTAMP,
decode(num_rows,0,’Table Stats indicate No Rows’,
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,’999.99′)
) 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 > 3000 or u.updates > 3000 or u.deletes > 3000)
order by t.last_analyzed

Some other Important takeaways
Prior to Oracle11g, the staleness threshold is hard coded 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.