Enable/Disable Internal Automated Jobs

 

Recently faced performance issue on database - overall system went slow.

From AWR reports it was noted that 

[ sql_id b6usrg82hwsa3 --  call dbms_stats.gather_database_stats_job_proc ( ) ] was executed repeated number of times consuming high CPU.

Workaround was to disable Internal Automated tasks

SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT; 
CLIENT_NAME                             STATUS
-----------------------------------------------------
auto optimizer stats collection		enabled
auto space advisor                      enabled
sql tuning advisor                      enabled

Syntax to Disable :-
exec DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION', NULL, NULL); 
begin DBMS_AUTO_TASK_ADMIN.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);end;

Syntax to Enable
exec DBMS_AUTO_TASK_ADMIN.ENABLE('AUTO OPTIMIZER STATS COLLECTION', NULL, NULL); 
begin DBMS_AUTO_TASK_ADMIN.enable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);end;

CPU performance showed much improvement after above action plan was implemented.

As the internal stats collection process is disabled, we need to run statistics manually

Procedure to Schedule Statistics Manually 
create or replace procedure gather_db_stats_weekly
as
begin
DBMS_STATS.gather_database_stats;
end;
 /

----- dbms scheduler program
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'gather_db_stats_weekly_PRG'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'gather_db_stats_weekly'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => 'Gather Weekly Statistics on Database'
    );
  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'gather_db_stats_weekly_PRG');
END;
/
 
--- dbms_scheduler job to run every week Saturday at 2:00 AM
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.EXEC_GATHER_DB_STATS_WEEKLY'
      ,start_date      => TO_TIMESTAMP_TZ('2023/06/05 02:10:00.000000 Asia/Riyadh','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'Freq=Weekly;ByDay=SAT;ByHour=2;ByMinute=00'
      ,end_date        => NULL
      ,program_name    => 'SYS.GATHER_DB_STATS_WEEKLY_PRG'
      ,comments        => 'Weekly job on saturday'
    );
 
 SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.EXEC_gather_db_stats_weekly');
 END;
 /

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 

SELECT CLIENT_NAME,
       STATUS,
       CONSUMER_GROUP,
       CLIENT_TAG,
       WINDOW_GROUP 
FROM 
       DBA_AUTOTASK_CLIENT;

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 

SELECT CLIENT_NAME,
       STATUS,
       CONSUMER_GROUP,
       CLIENT_TAG,
       WINDOW_GROUP 
FROM 
       DBA_AUTOTASK_CLIENT;

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME FOR A18
COL REPEAT_INTERVAL FOR A72
COL WINDOW_DURATION FOR A32
COL ENABLED FOR A10

SELECT WINDOW_NAME,
       REPEAT_INTERVAL,
       TO_CHAR(EXTRACT(DAY FROM DURATION),'90') || ' Days ' || 
       TO_CHAR(EXTRACT(HOUR FROM DURATION),'90') || ' Hours ' ||
       TO_CHAR(EXTRACT(MINUTE FROM DURATION),'90') || ' Minutes ' 
       WINDOW_DURATION,
       ENABLED 
FROM 
       DBA_SCHEDULER_WINDOWS;