Automatic Statistics Gathering Job

Oracle will automatically collect statistics for all database objects, which are missing statistics of have stale statistics by running an Oracle AutoTask during a predefined maintenance window (10pm to 2am weekdays and 6am to 2am at the weekends).  Statistics on a table are considered stale when more than STALE_PERCENT (default 10% total of inserts,updates, deletes rows) in a table.  Oracle monitors DML activities for all objects and records the changes in the SGA.  The monitoring information is periodically flushed to disk and is stored in the *_tab_modifications view.

It’s also possible to manually flush this data by calling the procedure DBMS_STATS.FLUSH_MONITORING_INFO. 

SQL> SELECT client_name, status

  2  FROM DBA_AUTOTASK_TASK

  3  WHERE client_name like 'auto optimizer %';

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

select

num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name,

inserts, updates, deletes, timestamp, truncated

, to_char(perc_updates, 'FM999,999,999,990.00') perc_updates

from (

        select a.*

        , nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates

        from (

          select

                  (select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name

                  and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows

                , (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name

                  and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed

                , (inserts + updates + deletes) tot_updates

                , DBA_TAB_MODIFICATIONS.*

                from sys.DBA_TAB_MODIFICATIONS

        ) a

) b

where perc_updates > 5 and

table_owner = 'TRAX_OWNER'

/

If you like to change the ‘STALE_PERCENT’ you can use

SQL> BEGIN

  2  DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');

  3  END;

  4  /

PL/SQL procedure successfully completed.

To disable automatic statistics job gathering altogether:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;