ENSURING THAT MY DATABASE GATHERS STATISTICS ON A DAILY BASIS
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
REFERENCIES
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
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0