Print Friendly, PDF & Email
The post provides a number of steps to follow to find out if statistics are gathered regularly in the Oracle database.

QUESTIONS
Does my database gather statistics automatically? If not what should I do?

RELATED
RESCHEDULING OF THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB

SOLUTION
First of all, identify if the Automatic Optimizer Statistics Collection Job is ENABLED by querying the DBA_AUTOTASK_OPERATION view

SET PAGES 999 
SET LINES 200
COL CLIENT_NAME FOR A31 
COL OPERATION FOR A10 
COL ATTRIBUTES FOR A40
COL STATUS FOR A10 

SELECT CLIENT_NAME,
       STATUS,
       OPERATION_TAG,
       ATTRIBUTES 
FROM 
       DBA_AUTOTASK_OPERATION 
WHERE 
       CLIENT_NAME LIKE '%optimizer%';
CLIENT_NAME                     STATUS     OPERATION ATTRIBUTES
------------------------------- ---------- --------- ----------------------------------------
auto optimizer stats collection ENABLED    OPT       ON BY DEFAULT, VOLATILE, SAFE TO KILL

If the job status is ENABLED then the Automatic Optimizer Statistics Collection Job executes automatically. If not, it must be enabled by

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   CLIENT_NAME => 'auto optimizer stats collection', 
   OPERATION   => NULL, 
   WINDOW_NAME => NULL);
END;
/

To know when the job runs you should identify a WINDOW GROUP the job belongs to. Here the Automatic Optimizer Statistics Collection Job is assigned to ORA$AT_WGRP_OS group.

COL CLIENT_NAME FOR A40
COL WINDOW_GROUP FOR A20 

SELECT CLIENT_NAME,
       STATUS,
       WINDOW_GROUP
FROM 
       DBA_AUTOTASK_CLIENT 
WHERE 
       CLIENT_NAME LIKE '%optimizer%';
CLIENT_NAME                              STATUS     WINDOW_GROUP
---------------------------------------- ---------- --------------------
auto optimizer stats collection          ENABLED    ORA$AT_WGRP_OS

Logically a window group is like a container with different schedules for the same job. By default the ORA$AT_WGRP_OS group has 7 schedules which called as WINDOW NAME.

COL WINDOW_GROUP_NAME FOR A20
COL WINDOW_NAME FOR A20

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS
WHERE WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- --------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW

Every WINDOW NAME is linked to a specific day of the week and starts at a specific period of time. By querying the DBA_AUTOTASK_WINDOW_CLIENTS view it is easy to identify on what days of the week the Automatic Optimizer Statistics Collection Job executes.

COL "STARTUP TIME" FOR A40 
COL OPTIMIZER_STATS FOR A15

SELECT WINDOW_NAME,
       WINDOW_NEXT_TIME "STARTUP TIME",
       AUTOTASK_STATUS,
       OPTIMIZER_STATS
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME          STARTUP TIME                             AUTOTASK OPTIMIZER_STATS
-------------------- ---------------------------------------- -------- ---------------
MONDAY_WINDOW        27-NOV-17 10.00.00.000000 PM +03:00      ENABLED  ENABLED
TUESDAY_WINDOW       28-NOV-17 10.00.00.000000 PM +03:00      ENABLED  ENABLED
WEDNESDAY_WINDOW     29-NOV-17 10.00.00.000000 PM +03:00      ENABLED  ENABLED
THURSDAY_WINDOW      30-NOV-17 10.00.00.000000 PM +03:00      ENABLED  DISABLED
FRIDAY_WINDOW        01-DEC-17 10.00.00.000000 PM +03:00      ENABLED  ENABLED
SATURDAY_WINDOW      02-DEC-17 06.00.00.000000 AM +03:00      ENABLED  ENABLED
SUNDAY_WINDOW        03-DEC-17 06.00.00.000000 AM +03:00      ENABLED  ENABLED

In this example, the Automatic Optimizer Statistics Collection Job executes every day except Thursday. If the Automatic Optimizer Statistics Collection Job is DISABLED (see OPTIMIZER_STATS column) for one or more named WINDOW NAME then it can be enabled by a statement like

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   CLIENT_NAME => 'auto optimizer stats collection', 
   OPERATION   => NULL, 
   WINDOW_NAME => 'THURSDAY_WINDOW');
END;
/

Another view named the DBA_AUTOTASK_SCHEDULE displays the schedule of maintenance windows for the next 32 days for each client.

COL WINDOW_NAME FOR A20 
COL START_TIME FOR A40
COL DURATION FOR A15 

SELECT * FROM DBA_AUTOTASK_SCHEDULE ORDER BY START_TIME;
WINDOW_NAME          START_TIME                               DURATION
-------------------- ---------------------------------------- ---------------
MONDAY_WINDOW        27-NOV-17 10.00.00.754725 PM +03:00      +000 04:00:00
TUESDAY_WINDOW       28-NOV-17 10.00.00.754725 PM +03:00      +000 04:00:00
WEDNESDAY_WINDOW     29-NOV-17 10.00.00.754725 PM +03:00      +000 04:00:00
THURSDAY_WINDOW      30-NOV-17 10.00.00.754725 PM +03:00      +000 04:00:00
FRIDAY_WINDOW        01-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00

...

TUESDAY_WINDOW       26-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
WEDNESDAY_WINDOW     27-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
THURSDAY_WINDOW      28-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00

32 rows selected.

To find out details about the latest job executions for the specific WINDOW NAME you can refer to the DBA_AUTOTASK_JOB_HISTORY view

COL WINDOW_NAME FOR A20
COL WINDOW_START_TIME FOR A20 
COL JOB_NAME FOR A25 
COL JOB_START_TIME_MAN FOR A25 
COL JOB_DURATION FOR A15 
COL JOB_INFO FOR A2 
COL JOB_STATUS FOR A10 
COL WINDOW_DURATION FOR A30 

SET LINES 300 
SELECT * from (
SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_START_TIME,'DD-MM-YYYY HH24:MI:SS') WINDOW_START_TIME,
       WINDOW_DURATION,
       JOB_NAME,
       JOB_STATUS,
       TO_CHAR(JOB_START_TIME,'DD-MM-YYYY HH24:MI:SS') JOB_START_TIME_MAN,
       JOB_DURATION,
       JOB_ERROR,
       JOB_INFO 
FROM 
       DBA_AUTOTASK_JOB_HISTORY 
WHERE
       CLIENT_NAME='auto optimizer stats collection' AND
       WINDOW_NAME='&WINDOW_NAME' 
ORDER BY JOB_START_TIME DESC) 
WHERE ROWNUM < 5;
Enter value for window_name: THURSDAY_WINDOW

WINDOW_NAME          WINDOW_START_TIME    WINDOW_DURATION                JOB_NAME                  JOB_STATUS JOB_START_TIME_MAN        JOB_DURATION     JOB_ERROR JO
-------------------- -------------------- ------------------------------ ------------------------- ---------- ------------------------- --------------- ---------- --
THURSDAY_WINDOW      23-11-2017 22:00:03  +000000000 04:00:00.004220     ORA$AT_OS_OPT_SY_4867     SUCCEEDED  23-11-2017 22:00:05       +000 00:32:23            0
THURSDAY_WINDOW      16-11-2017 22:00:03  +000000000 03:59:59.996482     ORA$AT_OS_OPT_SY_4799     SUCCEEDED  16-11-2017 22:00:05       +000 00:05:39            0
THURSDAY_WINDOW      09-11-2017 22:00:01  +000000000 03:59:59.990886     ORA$AT_OS_OPT_SY_4679     SUCCEEDED  09-11-2017 22:00:03       +000 00:57:49            0
THURSDAY_WINDOW      02-11-2017 22:00:01  +000000000 03:59:59.996578     ORA$AT_OS_OPT_SY_4642     SUCCEEDED  02-11-2017 22:00:03       +000 00:57:14            0

REFERENCES
DBMS_AUTO_TASK_ADMIN.DISABLE still shows the client_name enabled in DBA_AUTOTASK_CLIENT view (Doc ID 1565768.1)

 
 

Version  : 16:47 27.11.2017
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0