Refreshing stale statistics using the DBMS_STATS package and the GATHER AUTO option.

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => USER,
cascade => TRUE,
options => 'GATHER AUTO');
END;
/

The results of the statistics refresh are evident in the %_TABLES views, as shown below.

SELECT table_name,
num_rows,
avg_row_len
FROM user_tables;

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS 1000 21

To automate the gathering of statistics, simply schedule a job to the call the DBMS_STATS package. The example below refreshes stale statistics for for schema "TIM" at midnight every day.

SET SERVEROUTPUT ON
DECLARE
l_job BINARY_INTEGER;
BEGIN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN DBMS_STATS.gather_schema_stats (' ||
'ownname => ''TIM'', cascade => TRUE, options => ''GATHER AUTO''); END;',
next_date => SYSDATE,
interval => '/* 1 Day Interval */ TRUNC(SYSDATE) + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || TO_CHAR(l_job));
END;
/

Alternatively we could set monitoring on for all database tables, with the exception of those in the SYS schema, and use the following procedure:

DBMS_STATS.gather_database_stats(cascade => TRUE, options => 'GATHER AUTO');

Since only those tables with stale or empty stats are processed the overhead on the system is greatly reduced.

Note. In Oracle 10g, gathering stale statistics for the database once a day happens by default.