How to check and manage the Optimizer Statistic

 Optimizer Statistic

How to check and managing the Optimizer Statistic

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

-- Following command will show you about he Jobs those are scheduled.

SQL> col WINDOW_NAME format a50
SQL>  SELECT window_name, to_char(window_next_time,'dd.mm.yy hh24:mi:ss'),
     window_active aktiv, optimizer_stats, segment_advisor, sql_tune_advisor
     FROM dba_autotask_window_clients;  2    3

WINDOW_NAME                                        TO_CHAR(WINDOW_NE AKTIV OPTIMIZE SEGMENT_ SQL_TUNE
-------------------------------------------------- ----------------- ----- -------- -------- --------
MONDAY_WINDOW                                      07.10.19 22:00:00 FALSE ENABLED  ENABLED  ENABLED
TUESDAY_WINDOW                                     08.10.19 22:00:00 FALSE ENABLED  ENABLED  ENABLED
WEDNESDAY_WINDOW                                   09.10.19 22:00:00 FALSE ENABLED  ENABLED  ENABLED
THURSDAY_WINDOW                                    10.10.19 22:00:00 FALSE ENABLED  ENABLED  ENABLED
FRIDAY_WINDOW                                      04.10.19 22:00:00 FALSE ENABLED  ENABLED  ENABLED
SATURDAY_WINDOW                                    05.10.19 06:00:00 FALSE ENABLED  ENABLED  ENABLED
SUNDAY_WINDOW                                      06.10.19 06:00:00 FALSE ENABLED  ENABLED  ENABLED

7 rows selected.

-- Check the status of the Jobs.
COL CLIENT_NAME FORMAT a31
SQL> select client_name, status from sys.dba_autotask_task;

CLIENT_NAME                     STATUS
------------------------------- --------
sql tuning advisor              ENABLED
auto optimizer stats collection ENABLED
auto space advisor              ENABLED

you can either disable a single auto task or all the auto task together. As per Oracle Documentation.

-- DISABLE one by one:
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL
);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (
    client_name  => 'sql tuning advisor'
,   operation    => NULL
,   window_name  => NULL
);
END;
/

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

-- ENABLE one by one:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL
);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (
    client_name  => 'sql tuning advisor'
,   operation    => NULL
,   window_name  => NULL
);
END;
/


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

-- You can change the time and duration of the job.

begin
  dbms_scheduler.set_attribute(
    name      => 'SATURDAY_WINDOW',
    attribute => 'duration',
    value     => numtodsinterval(3, 'hour'));
end;
/

select window_name, duration from dba_scheduler_windows where window_name = 'SATURDAY_WINDOW';

The best person is the one who benefits all human beings. [Prophet Muhammad (PBUH)]