Gather Stats Task

=================================
What Is The Default Gather Stats Task?
=================================

Out of the box, Oracle comes with a default job, that gathers statistics. 

What is this task?

Statistics gathering is implemented using the Automatic Optimizer Statistics Collection Maintenance task. 

The name of the task is ‘auto optimizer stats collection‘.

The task is scheduled to run during the maintenance window, and it is supposed to gather statistics on the objects with stale or missing stats.

Why are lots of people referring to this task as a job?
In Oracle 10G there was a default job that was gathering statistics (a dba_jobs job).
Starting with 11G, there is no gather stats job.

The information below applies to 11g and 12c.

=================================
Automatic Optimizer Statistics Collection. 
=================================
Oracle has implemented maintenance tasks, and one of them is the Automatic Optimizer Statistics Collection.
This task runs a program, called gather_stats_prog, which in turn invokes the following plsql block: 
dbms_stats.gather_database_stats_job_proc

SELECT client_name, task_name, status, program_action
  FROM DBA_AUTOTASK_TASK, 
       DBA_SCHEDULER_PROGRAMS
WHERE UPPER(task_name)=UPPER(program_name)
  AND client_name='auto optimizer stats collection';

client_name                     task_name
------------------------------- -------------------    
auto optimizer stats collection gather_stats_prog

status    program_action
--------- -----------------------------------------    
ENABLED   dbms_stats.gather_database_stats_job_proc  

Reference

http://dbaparadise.com/2017/11/how-are-statistics-gathered-in-the-database/

In case DBMS_STATS.GATHER_DATABASE_STATS_JOB is slow and using a lot of CPU, it might be because of missing dictionary stats.

 

SOLUTION: Gather dictionary stats and fixed object stats by executing following: 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;