This is a short article on how to manage the automated Oracle database maintenance tasks.
Make sure the needed rights are in place by running the check:
SELECT grantee, privilege
WHERE owner = 'SYS'
AND table_name = 'DBMS_AUTO_TASK_ADMIN'
AND privilege = 'EXECUTE'
Typical output is:
GRANTEE PRIVILEGE -------------------------------- -------------------- RADU EXECUTE HARRY EXECUTE DBA EXECUTE IMP_FULL_DATABASE EXECUTE DATAPUMP_IMP_FULL_DATABASE EXECUTE EM_EXPRESS_ALL EXECUTE
If your user is not listed, you must grant EXECUTE in order to use the package:
grant EXECUTE on SYS.DBMS_AUTO_TASK_ADMIN to new_admin_user ;
We can check what is enabled by running:
select * from DBA_AUTOTASK_JOB_HISTORY order by window_start_time desc ; select distinct CLIENT_NAME from DBA_AUTOTASK_JOB_HISTORY ;
With typical output:
CLIENT_NAME ------------------------------- auto space advisor auto optimizer stats collection sql tuning advisor
In many environments is typical to disable one or both of the advisor tasks. Especially if we want to allow the stats collection to process as much as possible. This is done by using the secound DISABLE of the DBMS_AUTO_TASK_ADMIN package (the first one being used to simply disable all the auto tasks). Here are exemple of the code used to disable the data collection for the advisors:
BEGIN SYS.DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; / BEGIN SYS.DBMS_AUTO_TASK_ADMIN.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; /
A few checks that can be done
select * from dba_autotask_client
might return something like:
select client_name, operation_tag, status, last_change from DBA_AUTOTASK_OPERATION ;
select client_name, status, last_try_result from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_CLIENT_HISTORY
order by window_start_time desc
select * from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name in (select job_name from DBA_AUTOTASK_JOB_HISTORY)
order by log_date desc
The DB maintenance tasks can be checked with a SQL like below. In this case, for the last seven days:
select client_name, window_name, job_status, job_error
where window_start_time > sysdate - 7
order by window_start_time desc
select dbms_stats.get_param ('AUTOSTATS_TARGET') from dual;
should be either AUTO or ALL (not ORACLE).
select dbms_stats.get_prefs('GRANULARITY') from dual ; DBMS_STATS.GET_PREFS('GRANULARITY') --------------------------------------- AUTO
-- set table prefs exec dbms_stats.set_table_prefs(USER,'TEST','GRANULARITY','ALL') ; exec DBMS_STATS.GATHER_tab_STATS(ownname=>'SCHEMANAME', tabname=>'TEST', partname=>'&partition_name',estimate_percent => 1,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 16,granularity => 'ALL',cascade => TRUE);
If we disabled some of the jobs in order to improve stats collection, we can run a query like this one before and after the maintenance window in order to asses the improvements:
select count(*) from dba_tab_statistics where owner not in ('SYS', 'SYSTEM', 'AUDSYS','APPQOSSYS', 'CTXSYS','DBSNMP', 'XDB', 'GSMADMIN_INTERNAL','OUTLN', 'WMSYS', 'ORDDATA','ORDSYS', 'MDSYS', 'LBACSYS', 'DVSYS', 'OJVMSYS', 'DBSFWUSER' ) and table_name not in ('T1', 't2' ) -- any tables you want excluded and stale_stats != 'NO' ;
One peculiar detail I noticed in some environments is that no job starts at the maintenance window start time:
select client_name, WINDOW_START_TIME, JOB_START_TIME from DBA_AUTOTASK_JOB_HISTORY where window_start_time > sysdate - 2 order by window_start_time desc ;
gets me something like this:
The statements in the article were tested on Oracle EE 19c (19.6) running on an Exadata machine.
- Oracle 19c Documentation, Managing Automated Database Maintenance Tasks
- Automatic Statistics Gathering does not Complete – Diagnostics Interpretation Guidelines (Doc ID 1902112.1)
- Automatic Task (“auto optimizer stats collection” Does not Refresh Stale Index Statistics Unless Tables Statistics are Also Stale (Doc ID 1934741.1)
- FAQ: Automatic Statistics Collection (Doc ID 1233203.1)
- Automatic Optimizer Statistics Collection on Partitioned Table (Doc ID 1592404.1)
- How To Collect Statistics On Partitioned Table (Doc ID 1417133.1)
- Best Practices for Automatic Statistics Collection (Doc ID 377152.1)