SQL to check Stale statistics

Many times Oracle SQL plan get change which will cause abnormal behavior, most probable reason could be stale statistics marked by Oracle. Below is useful queries to identify the Objects and gather stats for those Objects. Oracle do have auto jobs which takes care stats gather requirement in defined Windows. One need to calibrate window based on requirement as stats gather as this is resource Intensive process, and should not conflict Business transaction. Oracle include this nightly maintenance Jobs by default scheduled in night time. Stats gather is vast topic, one need carefully touch stats gather on PROD environment.

---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
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';