Fix high CPU from auto space advisor

SQL> select client_name,status,MEAN_JOB_CPU,MEAN_JOB_DURATION FROM dba_autotask_client;

 

CLIENT_NAME                         STATUS   MEAN_JOB_CPU                    MEAN_JOB_DURATION

---

auto optimizer stats collection     ENABLED  +000000000 00:05:17.971043544   +000000000 00:07:39.854354354

auto space advisor                  DISABLED +000000000 01:07:21.684045455   +000000000 01:10:22.871969697

sql tuning advisor                  ENABLED  +000000000 00:05:15.859067103   +000000000 00:11:36.518821604

 

As we can see the auto space job ran for 1 hour 10 minutes and most of time it occupied a full CPU. With 3 databases running the same job at the same time, that caused the 100% CPU alert generated.

 

Here is how we disable the job:

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>’auto space advisor’, operation=>NULL, window_name=>NULL);

 

##########  Enable auto stats job ########

Enabled auto stats gathering. Modified the weekend window to start from 11PM Saturday for 30 hours (end at 5AM EST Monday).

Also made manual stats cronjob only run on Saturday early morning.

 

SQL to enable auto stats gathering

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEEKNIGHT_WINDOW');
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'WEEKEND_WINDOW');
END;

 

SQL to modify the weekend windows:

 

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."WEEKEND_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEEKEND_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(1800, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEEKEND_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=23;BYMINUTE=0;BYSECOND=10');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."WEEKEND_WINDOW"');
END;

CRON JOB on the server:

Statistic

50 02 * * 6 /home/oracle/scripts/statistic_mbfix.sh > /tmp/tst.out 2>&1