SQL to check Stale statistics

SQL to check Stale statistics

---Using Anonymous PL/SQL block 

SQL> SET SERVEROUTPUT ON

  DECLARE

ObjList dbms_stats.ObjectTab;

BEGIN

dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');

FOR i in ObjList.FIRST..ObjList.LAST

LOOP

dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);

END LOOP;

END;

/

---Oracle 10g Onwards. 
col TABLE_NAME for a30
TABLE_NAME        STALE_STATS


BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ ORACLE’);
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL); 

col PARTITION_NAME for a20

col SUBPARTITION_NAME for a20

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

-- Oracle 12c

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table. Oracle monitors the DML activity for all tables and records it in the SGA. The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view. 

select table_name,inserts,updates,deletes

from USER_TAB_MODIFICATIONS

where table_name='PRODUCTS2';

TABLE_NAME   INSERTS  UPDATES  DELETES

PRODUCTS2      766             1532             32

Querying USER_TAB_MODIFICATIONS view to check DML activity on the PRODUCTS2 table

It is possible to manually flush this data by calling the procedure 

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO if you want to get up-to-date information at query time (internally the monitoring data is flushed before all statistics collection operations). You can then see which tables have stale statistics by querying the STALE_STATS column in the USER_TAB_STATISTICS view.

sql> conn hr/hr
Connected
sql> select table_name,stale_stats
from user_tab_statistics;

COUNTRIES            NO
DEPARTMENTS       NO
JOBS                           NO
EMPLOYEES
PRODUCTS2              YES
LOCATIONS              NO

Querying USER_TAB_STATISTICS to see if any tables have stale statistics

Tables where STALE_STATS is set to NO, have up to date statistics. Tables where STALE_STATS is set toYES,havestalestatistics.Tables where STALE_STATS is not  set are missing statistics altogether. 

If you already have a well-established statistics gathering procedure, or if for some other reason you want to disable automatic statistics gathering for your main application schema, consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using DBMS_STATS.SET_GLOBAL_PREFS procedure.
END; /
To disable the task altogether:

END; /