Database Reference
In-Depth Information
Every time the job has to be stopped because the window is closed, a trace file, containing the list of all objects
that have not been processed, is written in the directory referenced by the
background_dump_dest
initialization
parameter. The following is an excerpt of such a trace file:
GATHER_STATS_JOB: Stopped by Scheduler.
Consider increasing the maintenance window duration if this happens frequently.
The following objects/segments were not analyzed due to timeout:
TABLE: "SH"."SALES"."SALES_1995"
TABLE: "SH"."SALES"."SALES_1996"
TABLE: "SH"."SALES"."SALES_H1_1997"
...
TABLE: "SYS"."WRI$_OPTSTAT_AUX_HISTORY".""
TABLE: "SYS"."WRI$_ADV_OBJECTS".""
TABLE: "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY".""
error 1013 in job queue process
ORA-01013: user requested cancel of current operation
To enable or disable the
gather_stats_job
job, the following PL/SQL calls are available:
dbms_scheduler.enable(name => 'sys.gather_stats_job')
dbms_scheduler.disable(name => 'sys.gather_stats_job')
Per default, only the
sys
user is able to execute them. Other users need the
alter
object privilege. For example,
after executing the following SQL statement, the
system
user can not only alter but also drop the
gather_stats_job
job:
GRANT ALTER ON gather_stats_job TO system
The 11g and 12c Way
As of version 11.1, the gathering of object statistics is integrated into the automated maintenance tasks. As a result,
the
gather_stats_job
job described in the preceding section no longer exists. The current configuration, which in the
following example is the default configuration of version 11.2, can be viewed with the following queries. The output
was generated with the
dbms_stats_job_11g.sql
script:
SQL> SELECT task_name, status
2 FROM dba_autotask_task
3 WHERE client_name = 'auto optimizer stats collection';
TASK_NAME STATUS
----------------- -------
gather_stats_prog ENABLED
SQL> SELECT program_action, number_of_arguments, enabled
2 FROM dba_scheduler_programs
3 WHERE owner = 'SYS'
4 AND program_name = 'GATHER_STATS_PROG';
