Why Oracle Auto Tasks Slow Your Database and How to Fix Them

Background

In Oracle DBA work, many customers encounter performance issues caused by the built‑in auto‑task jobs that run during specific time windows.

Description

The problems typically appear during two special windows: weekdays 22:00‑02:00 (4 hours) and weekends 06:00‑02:00 (20 hours). These are the periods when Oracle auto tasks execute.

Analysis

Oracle auto task consists of three jobs:

auto optimizer stats collection – gathers optimizer statistics automatically in a defined window; when a table’s data changes >10 % the stats are refreshed, which can change execution plans and slow the system.

sql tuning advisor – daily collects high‑resource SQL statements, analyses them in the window and generates tuning recommendations without applying changes; it can consume I/O and CPU.

auto space advisor – analyses segment fragmentation and suggests space reclamation; for large objects it may create temporary tables, fill TEMP tablespace, generate REDO and increase I/O.

Key observations: the jobs run automatically (“auto”) inside the defined windows, and their side effects (plan changes, resource contention, alerts) can impact production workloads.

Recommendations

Disable the auto space advisor and sql tuning advisor, keeping only auto optimizer stats collection to minimise risk.

If the system has weekend online traffic, shorten the weekend window to the same 4‑hour weekday window (e.g., start at 22:00).

For very large tables, consider excluding them from automatic stats collection and manage stats manually.

In environments requiring absolute stability, disable all three jobs and perform a manual stats collection after deployment and after major data changes.

Example

A customer experienced an unexpected outage after the SYS_AUTO_SQL_TUNING_TASK caused memory overflow. Disabling the sql tuning advisor and manually adjusting the auto space advisor resolved the issue.

SQL snippets

Check auto‑task status:

SQL> select client_name,status,WINDOW_GROUP from dba_autotask_client;
CLIENT_NAME                          STATUS   WINDOW_GROUP
---------------------------------------------------------------- -------- --------------------
auto optimizer stats collection      DISABLED ORA$AT_WGRP_OS
auto space advisor                  ENABLED  ORA$AT_WGRP_SA
sql tuning advisor                   ENABLED  ORA$AT_WGRP_SQ

Disable a task (e.g., auto space advisor):

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

View window definitions:

select t1.window_name, t1.repeat_interval, t1.duration
from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
where t1.window_name = t2.window_name
and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

Modify a window’s duration (example for Thursday to 12 hours):

BEGIN
  dbms_scheduler.set_attribute(
    name      => '"SYS"."THURSDAY_WINDOW"',
    attribute => 'DURATION',
    value     => numtodsinterval(12,'hour'));
END;
/