Site icon Radu Pârvu

Oracle Automated DB Maintenance Tasks

Radu

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
FROM dba_tab_privs
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
;

will return:

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
from DBA_AUTOTASK_JOB_HISTORY
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.

References

Exit mobile version