HOW TO KNOW WHETHER THE DATABASE COLLECTS STATISTICS REGULARLY

The post provides a number of steps to follow to find out if statistics are collected on a regular basis in the Oracle database.

HOW TO RESCHEDULE THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB

SOLUTION

Firstly, determine whether the autotask subsystem is activated. Status of the autotask subsystem is shown in DBA_AUTOTASK_WINDOW_CLIENTS view, AUTOTASK_STATUS column, which will be set to ENABLED or DISABLED.

SET LINES 200
SET PAGES 30
COL WINDOW_NAME FOR A18
COL WINDOW_SCHEDULED_TIME FOR A21
COL CURRENTLY_ACTIVE FOR A16
COL AUTOTASK_STATUS FOR A16
COL OPTIMIZER_STATS FOR A16
COL SEGMENT_ADVISOR FOR A16
COL SQL_TUNE_ADVISOR FOR A16

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_SCHEDULED_TIME,
       WINDOW_ACTIVE CURRENTLY_ACTIVE,
       AUTOTASK_STATUS,
       OPTIMIZER_STATS,
       SEGMENT_ADVISOR,
       SQL_TUNE_ADVISOR
FROM
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME        WINDOW_SCHEDULED_TIME CURRENTLY_ACTIVE AUTOTASK_STATUS  OPTIMIZER_STATS  SEGMENT_ADVISOR  SQL_TUNE_ADVISOR
------------------ --------------------- ---------------- ---------------- ---------------- ---------------- ----------------
MONDAY_WINDOW      05-SEP-2022 22:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
TUESDAY_WINDOW     06-SEP-2022 22:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
WEDNESDAY_WINDOW   07-SEP-2022 22:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
THURSDAY_WINDOW    08-SEP-2022 22:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
FRIDAY_WINDOW      09-SEP-2022 22:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
SATURDAY_WINDOW    10-SEP-2022 06:00:00  FALSE            ENABLED          ENABLED          ENABLED          ENABLED
SUNDAY_WINDOW      04-SEP-2022 06:00:00  TRUE             ENABLED          ENABLED          ENABLED          ENABLED

7 rows selected.

All automatic tasks for a particular WINDOW_NAME are deactivated if the associated AUTOTASK_STATUS column is set to DISABLED.

NOTE

To enable or disable all automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments

-- To disable 

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

-- To enable

EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;

To get details of individual autotask client

SET PAGES 30 
SET LINES 200
COL CLIENT_NAME FOR A31
COL STATUS FOR A10
COL TAG FOR A3
COL ATTRIBUTES FOR A55
COL WINDOW_GROUP FOR A25

SELECT CLIENT_NAME,
       STATUS,
       CLIENT_TAG TAG,
       ATTRIBUTES,
       WINDOW_GROUP
FROM
       DBA_AUTOTASK_CLIENT;

CLIENT_NAME                     STATUS     TAG ATTRIBUTES                                              WINDOW_GROUP
------------------------------- ---------- --- ------------------------------------------------------- -------------------------
sql tuning advisor              ENABLED    SQ  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL  ORA$AT_WGRP_SQ
auto optimizer stats collection ENABLED    OS  ON BY DEFAULT, VOLATILE, SAFE TO KILL                   ORA$AT_WGRP_OS
auto space advisor              ENABLED    SA  ON BY DEFAULT, VOLATILE, SAFE TO KILL                   ORA$AT_WGRP_SA

if the autotask subsystem is deactivated the individual autotask won't run even if it has ENABLED status.

NOTE

To enable or disable an individual autotask client. For instance, the auto optimizer stats collection task

-- To enable gathering optimizer statistics for all windows

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

-- To enable gathering optimizer statistics for the specific window only

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

-- To disable gathering optimizer statistics for all windows

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

-- To disable gathering optimizer statistics for the specific window name only

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

Details about the latest job executions can be found in DBA_AUTOTASK_JOB_HISTORY view

SET LINES 200
SET PAGES 30
SET SQLNUMBER OFF
COL CLIENT_NAME FOR A35
COL WINDOW_NAME FOR A20
COL JOB_NAME FOR A20
COL JOB_STATUS FOR A15
COL START_TIME FOR A20
COL JOB_DURATION FOR A15

SELECT CLIENT_NAME,
       WINDOW_NAME,
       JOB_NAME,
       JOB_STATUS,
       TO_CHAR(JOB_START_TIME,'DD-MM-YYYY HH24:MI:SS') START_TIME,
       JOB_DURATION,
       JOB_ERROR,
       JOB_ERROR
FROM
       DBA_AUTOTASK_JOB_HISTORY
WHERE
       UPPER(CLIENT_NAME)=UPPER('&AUTOTASK_NAME')
ORDER BY
       JOB_START_TIME;

Enter value for autotask_name: auto optimizer stats collection
old  12:        UPPER(CLIENT_NAME)=UPPER('&AUTOTASK_NAME')
new  12:        UPPER(CLIENT_NAME)=UPPER('auto optimizer stats collection')

CLIENT_NAME                         WINDOW_NAME          JOB_NAME             JOB_STATUS      START_TIME           JOB_DURATION     JOB_ERROR  JOB_ERROR
----------------------------------- -------------------- -------------------- --------------- -------------------- --------------- ---------- ----------
auto optimizer stats collection     SATURDAY_WINDOW      ORA$AT_OS_OPT_SY_381 SUCCEEDED       06-08-2022 16:24:31  +000 00:00:20            0          0
auto optimizer stats collection     SATURDAY_WINDOW      ORA$AT_OS_OPT_SY_384 SUCCEEDED       06-08-2022 20:28:31  +000 00:00:11            0          0
auto optimizer stats collection     SATURDAY_WINDOW      ORA$AT_OS_OPT_SY_401 SUCCEEDED       07-08-2022 00:29:23  +000 00:00:53            0          0
auto optimizer stats collection     SUNDAY_WINDOW        ORA$AT_OS_OPT_SY_421 SUCCEEDED       07-08-2022 10:53:32  +000 00:01:52            0          0
auto optimizer stats collection     SUNDAY_WINDOW        ORA$AT_OS_OPT_SY_441 SUCCEEDED       07-08-2022 15:01:50  +000 00:00:59            0          0
auto optimizer stats collection     SUNDAY_WINDOW        ORA$AT_OS_OPT_SY_461 SUCCEEDED       07-08-2022 20:01:25  +000 00:00:17            0          0
auto optimizer stats collection     TUESDAY_WINDOW       ORA$AT_OS_OPT_SY_481 SUCCEEDED       09-08-2022 22:30:36  +000 00:01:12            0          0
auto optimizer stats collection     SATURDAY_WINDOW      ORA$AT_OS_OPT_SY_501 SUCCEEDED       03-09-2022 10:54:23  +000 00:01:18            0          0
auto optimizer stats collection     SUNDAY_WINDOW        ORA$AT_OS_OPT_SY_521 SUCCEEDED       04-09-2022 13:55:20  +000 00:00:39            0          0

9 rows selected.

REFERENCES

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

Written At

05 SEPTEMBER 202200:30

Red Hat Release

7.x x64

Database Release

19.13.0.0.0

HOW TO KNOW WHETHER THE DATABASE COLLECTS STATISTICS REGULARLY

Tags In