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)]
