Introduction

dbms_stats.gather_schema_stats is equiped with the option gather auto. The Oracle documentation says that “Oracle implicitly determines which objects need new statistics” without exactly exposing the criteria for selecting objects. However, gather auto seems to be the concatenation of gather stale and gather empty, which can be verified by running the test case below.

In 12c Oracle introduced the options parameter for gather_table_stats as well. Unfortunately, it turned out that this parameter doesn’t behave in a consistent manner.

Schema Statistics

First, I’ll create a schema and a test table:

create user u identified by Temp_1234 ;
grant dba to u ;
connect u/Temp_1234

create table t (a number) ;

alter session set nls_date_format='dd.mm.yy hh24:mi:ss' ;

set NULL NULL
column stale_stats format a4

As expected, there is no statistics for the new table t:

select stale_stats,last_analyzed 
  from user_tab_statistics 
  where table_name='T' 
;

STAL LAST_ANALYZED
---- -----------------
NULL NULL

gather_schema_stats with options gather auto will gather the empty statistics – no surprise there:

BEGIN
  SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
     OwnName           => 'U'
    ,Options           => 'GATHER auto'
);
END;
/

select stale_stats,last_analyzed 
  from user_tab_statistics 
  where table_name='T' 
;

STAL LAST_ANALYZED
---- -----------------
NO   14.09.16 20:22:31

Inserting a row will render statistics stale:

insert into t values (1) ;
commit ;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

select stale_stats,last_analyzed 
  from user_tab_statistics 
  where table_name='T' ;

STAL LAST_ANALYZED
---- -----------------
YES  14.09.16 20:22:31

Finally, gather auto in gather_schema_stats will also trigger the statistics are stale:

exec DBMS_LOCK.SLEEP(1);

BEGIN
  SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
     OwnName           => 'U'
    ,Options           => 'GATHER auto'
);
END;
/

select stale_stats,last_analyzed 
  from user_tab_statistics 
  where table_name='T' ;

STAL LAST_ANALYZED
---- -----------------
NO   14.09.16 20:23:51

In conclusion, gather_schema_stats gathers statistics for all the objects with stale and empty statistics in case gather auto is specified.

Table Statistics

In Oracle 12c the option gather auto was introduced for gather_table_stats as well. However, the table statistics will be gathered even though the statistics are neither stale nor empty.

exec DBMS_LOCK.SLEEP(1);

begin
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName            => 'U'
    ,TabName            => 'T'
    ,Options            => 'GATHER auto'
);
end;
/

select stale_stats,last_analyzed 
  from user_tab_statistics 
  where table_name='T' ;

STAL LAST_ANALYZED
---- -----------------
NO   14.09.16 20:25:38

Summary

Gathering schema statistics with gather auto is a convenient way to selectively gather statistics for the specified schema. In particular, the statistics will be gathered for the objects with stale and empty statistics. In 12c the parameter options was introduced for gather_table_stats as well. However, this parameter doesn’t seem to have any impact there. As a workaround, the column user_tab_statistics.stale_stats has to be queried for making the decision whether to gather table statistics.