Category: Statistics

N0 Stats

SQL> SELECT OWNER, TABLE_NAME from dba_tables where last_analyzed is null;

SQL> SELECT OWNER, TABLE_NAME from dba_indexes  where last_analyzed is null;

SQL>  select count(*) from dba_ind_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       795

SQL>  select count(*) from dba_tab_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       406

Stale Stats

SQL> SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES' ;

SQL>  SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_IND_STATISTICS WHERE STALE_STATS='YES';

Locked Stats

 SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL ;

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_IND_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL;

Collect stats

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.