Print Friendly, PDF & Email

 
QUESTIONS

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

 
RELATED

 
SOLUTION

First of all, identify if Automatic Optimizer Statistics Collection Job (AOSCJ) runs on regular basis. It can be done easily by querying a corresponding 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 AOSCJ executes automatically. If not, you should enable it 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. By default all automated maintenance tasks are 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

Knowing a window name you can easily find out if it’s enabled and when it starts

COL "STARTUP TIME" FOR A40 
SELECT WINDOW_NAME,WINDOW_NEXT_TIME "STARTUP TIME",AUTOTASK_STATUS,OPTIMIZER_STATS FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          STARTUP TIME                             AUTOTASK OPTIMIZE
-------------------- ---------------------------------------- -------- --------
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

Here AOSCJ executes everyday excepting Thursday. To start the job your window name should be ENABLED. If one or more windows have status DISABLED you can enable it by

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

To get the extended schedule of maintenance windows for the next 32 days

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
SATURDAY_WINDOW      02-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
SUNDAY_WINDOW        03-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
MONDAY_WINDOW        04-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
TUESDAY_WINDOW       05-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
WEDNESDAY_WINDOW     06-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
THURSDAY_WINDOW      07-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
FRIDAY_WINDOW        08-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
SATURDAY_WINDOW      09-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
SUNDAY_WINDOW        10-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
MONDAY_WINDOW        11-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
TUESDAY_WINDOW       12-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
WEDNESDAY_WINDOW     13-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
THURSDAY_WINDOW      14-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
FRIDAY_WINDOW        15-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
SATURDAY_WINDOW      16-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
SUNDAY_WINDOW        17-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
MONDAY_WINDOW        18-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
TUESDAY_WINDOW       19-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
WEDNESDAY_WINDOW     20-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
THURSDAY_WINDOW      21-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
FRIDAY_WINDOW        22-DEC-17 10.00.00.754725 PM +03:00      +000 04:00:00
SATURDAY_WINDOW      23-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
SUNDAY_WINDOW        24-DEC-17 06.00.00.754725 AM +03:00      +000 20:00:00
MONDAY_WINDOW        25-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 execution you can refer to 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

 
 

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