Finding Stale Statistics in Oracle Database

Statistics are very important to generate efficient execution plans and thus directly related to database performance. We have nightly maintenance window, which runs for an hour, each alternate day.

As we are growing very fast, the 1 hour window sometimes can't cover all the tables and indexes.

Here is how we could find the stale/obsolete statistics - data for those objects have been modified more than 10%.

How to find?

SQL> SET SERVEROUT ON

SQL> DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

In my case, it were showing a couple of tables -

CRITICAL_INCIDENT - TABLE
EXTRA_TEMPLATE - TABLE
MODULE_ASSIGNMENT - TABLE


How to fix?

Simple - by collecting statistics for those objects -

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'PROD', tabname=>'EXTRA_TEMPLATE', estimate_percent=>50, cascade=>true);


More:

* The important view from where we could find the stale information and number of modifications done on a table is USER_TAB_MODIFICATIONS

* Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();