Oracle 11g включает три задачи автоматического обслуживания базы данных:
Автоматическая сборка статистики оптимизатора - собирает устаревшие или отсутствующие статистические данные для всех объектов схемы.
Имя задачи - это 'auto optimizer stats collection'
Автоматический советник по сегментам - определяет сегменты, которые могут быть реорганизованы для экономии места.
Имя задачи - 'auto space advisor'.
Автоматический советник по настройке SQL - определяет и пытается настроить SQL с высокой нагрузкой.
Имя задачи - 'sql tuning advisor'
Эти задачи выполняются во время окон обслуживания, которые планируется открыть за ночь.
auto optimizer stats collection
auto space advisor
sql tuning advisor
select operation_name, status from dba_autotask_operation;
OPERATION_NAME STATUS
------------------------------------------------
auto optimizer stats job ENABLED
auto space advisor job ENABLED
automatic sql tuning task ENABLED
select window_name,
window_active,
autotask_status,
to_char(window_next_time,'dd/mm/yyyy hh24:mi:ss') next_time
from DBA_AUTOTASK_WINDOW_CLIENTS
order by window_next_time;
WINDOW_NAME WINDOW_ACTIVE AUTOTASK_STATUS NEXT_TIME
---------------------------------------------------------------------------------------------------------
MONDAY_WINDOW FALSE DISABLED 01/10/2018 22:00:00
TUESDAY_WINDOW FALSE DISABLED 02/10/2018 22:00:00
WEDNESDAY_WINDOW FALSE DISABLED 03/10/2018 22:00:00
THURSDAY_WINDOW FALSE DISABLED 04/10/2018 22:00:00
FRIDAY_WINDOW FALSE DISABLED 05/10/2018 22:00:00
SATURDAY_WINDOW FALSE DISABLED 06/10/2018 06:00:00
SUNDAY_WINDOW FALSE DISABLED 07/10/2018 06:00:00
Отключить или включить отдельные автоматические задачи из всех окон обслуживания:
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
select operation_name, status from dba_autotask_operation;
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
select operation_name, status from dba_autotask_operation;
Изменение приоритетов.
BEGIN
DBMS_AUTO_TASK_ADMIN.override_priority(
client_name => 'auto optimizer stats collection',
priority => dbms_auto_task_admin.priority_high);
END;
/
select client_name, priority_override from dba_autotask_client;
begin
dbms_auto_task_admin.override_priority(
client_name => 'auto space advisor',
priority => dbms_auto_task_admin.priority_urgent);
end;
/
begin
dbms_auto_task_admin.override_priority(
client_name => 'auto space advisor',
priority => dbms_auto_task_admin.priority_medium);
end;
/
select client_name, priority_override from dba_autotask_client;
Настроим так:
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;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'auto optimizer stats collection',
operation => 'auto optimizer stats job',
window_name => NULL);
END;
/
Просмотр журнала сбора статистики:
select client_name,
job_status,
job_start_time,
job_duration,
job_info
from dba_autotask_job_history
where client_name like '%stats%'
order by job_start_time;
Удалять или добавлять задачи в отдельные окна обслуживания:
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
Глобальные параметры сбора статистики могут быть изменены с помощью процедуры
SET_GLOBAL_PREFS в пакете DBMS_STATS:
EXEC DBMS_STATS.alter_stats_history_retention(90);
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '5');
Включить инкрементальный сбор статистики:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE');
END;
/
Включить сбор статистики только по системному словарю:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
END;
/
Автоматический режим сбора статистики:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','AUTO');
END;
/
Проверить в каком режиме идет сбор статистики:
select DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') as AUTOSTATS_TARGET from dual;
Глобальные настройки режима сбора статистики:
select 'CASCADE = '||dbms_stats.GET_PREFS('CASCADE') as global_stats from dual
union all
select 'DEGREE = '||dbms_stats.GET_PREFS('DEGREE') from dual
union all
select 'ESTIMATE_PERCENT = '||dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual
union all
select 'METHOD_OPT = '||dbms_stats.GET_PREFS('METHOD_OPT') from dual
union all
select 'NO_INVALIDATE = '||dbms_stats.GET_PREFS('NO_INVALIDATE') from dual
union all
select 'GRANULARITY = '||dbms_stats.GET_PREFS('GRANULARITY') from dual
union all
select 'PUBLISH = '||dbms_stats.GET_PREFS('PUBLISH') from dual
union all
select 'INCREMENTAL = '||dbms_stats.GET_PREFS('INCREMENTAL') from dual
union all
select 'STALE_PERCENT = '||dbms_stats.GET_PREFS('STALE_PERCENT') from dual
union all
select 'AUTOSTATS_TARGET = '||DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') as AUTOSTATS_TARGET from dual;
GLOBAL_STATS
-----------------------------------------------
CASCADE = DBMS_STATS.AUTO_CASCADE
DEGREE = NULL
ESTIMATE_PERCENT = DBMS_STATS.AUTO_SAMPLE_SIZE
METHOD_OPT = FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE = DBMS_STATS.AUTO_INVALIDATE
GRANULARITY = AUTO
PUBLISH = TRUE
INCREMENTAL = FALSE
STALE_PERCENT = 10
AUTOSTATS_TARGET = AUTO
Параметры sql tuning advisor могут быть изменены с помощью процедуры SET_TUNING_TASK_PARAMETER в пакете DBMS_SQLTUNE.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1200);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES', 'FALSE');
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_SQL_PROFILES_PER_EXEC', 20);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10000);
END;
/
Вся группа окон MAINTENANCE_WINDOW_GROUP может быть включена или отключена с помощью процедур ENABLE и DISABLE в пакете DBMS_SCHEDULER.
MAINTENANCE_WINDOW_GROUP
BEGIN
DBMS_SCHEDULER.disable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP',
force => TRUE);
DBMS_SCHEDULER.enable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP');
END;
/
Изменить атрибуты окна можно с использованием процедуры SET_ATTRIBUTE пакета DBMS_SCHEDULER.
BEGIN
DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(180, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/
Если создали дополнительные окна обслуживания, назначьте их группе MAINTENANCE_WINDOW_GROUP,
и по умолчанию они будут назначены плану ресурсов DEFAULT_MAINTENANCE_PLAN.
При необходимости вы можете отредактировать этот план или создать новый план.
Если вы создаете новый план ресурсов, вы можете назначить его соответствующим окнам обслуживания с помощью пакета DBMS_SCHEDULER.
BEGIN
DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN',
value => 'MY_NEW_PLAN');
DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/
Чтобы очистить план ресурса, выполните следующую команду.
BEGIN
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN',
value => '');
END;
/
Следующие представления отображают информацию,
связанную с задачами автоматического обслуживания базы данных:
select * from dba_scheduler_wingroup_members;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY;
select * from DBA_AUTOTASK_SCHEDULE;
select * from dba_rsrc_plans;
select * from dba_rsrc_plan_directives;
select * from dba_rsrc_consumer_groups;
select * from dba_rsrc_consumer_group_privs;
select * from dba_rsrc_mapping_priority;
select * from dba_rsrc_group_mappings;
select * from dba_rsrc_manager_system_privs;
select initial_rsrc_consumer_group from dba_users;
select resource_consumer_group, current_queue_duration from v$session;
select * from v$rsrc_plan;
select * from v$rsrc_consumer_group;
Автоматическая сборка статистики оптимизатора - собирает устаревшие или отсутствующие статистические данные для всех объектов схемы.
Имя задачи - это 'auto optimizer stats collection'
Автоматический советник по сегментам - определяет сегменты, которые могут быть реорганизованы для экономии места.
Имя задачи - 'auto space advisor'.
Автоматический советник по настройке SQL - определяет и пытается настроить SQL с высокой нагрузкой.
Имя задачи - 'sql tuning advisor'
Эти задачи выполняются во время окон обслуживания, которые планируется открыть за ночь.
auto optimizer stats collection
auto space advisor
sql tuning advisor
select operation_name, status from dba_autotask_operation;
OPERATION_NAME STATUS
------------------------------------------------
auto optimizer stats job ENABLED
auto space advisor job ENABLED
automatic sql tuning task ENABLED
select window_name,
window_active,
autotask_status,
to_char(window_next_time,'dd/mm/yyyy hh24:mi:ss') next_time
from DBA_AUTOTASK_WINDOW_CLIENTS
order by window_next_time;
WINDOW_NAME WINDOW_ACTIVE AUTOTASK_STATUS NEXT_TIME
---------------------------------------------------------------------------------------------------------
MONDAY_WINDOW FALSE DISABLED 01/10/2018 22:00:00
TUESDAY_WINDOW FALSE DISABLED 02/10/2018 22:00:00
WEDNESDAY_WINDOW FALSE DISABLED 03/10/2018 22:00:00
THURSDAY_WINDOW FALSE DISABLED 04/10/2018 22:00:00
FRIDAY_WINDOW FALSE DISABLED 05/10/2018 22:00:00
SATURDAY_WINDOW FALSE DISABLED 06/10/2018 06:00:00
SUNDAY_WINDOW FALSE DISABLED 07/10/2018 06:00:00
Отключить или включить отдельные автоматические задачи из всех окон обслуживания:
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
select operation_name, status from dba_autotask_operation;
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
select operation_name, status from dba_autotask_operation;
Изменение приоритетов.
BEGIN
DBMS_AUTO_TASK_ADMIN.override_priority(
client_name => 'auto optimizer stats collection',
priority => dbms_auto_task_admin.priority_high);
END;
/
select client_name, priority_override from dba_autotask_client;
begin
dbms_auto_task_admin.override_priority(
client_name => 'auto space advisor',
priority => dbms_auto_task_admin.priority_urgent);
end;
/
begin
dbms_auto_task_admin.override_priority(
client_name => 'auto space advisor',
priority => dbms_auto_task_admin.priority_medium);
end;
/
select client_name, priority_override from dba_autotask_client;
Настроим так:
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;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'auto optimizer stats collection',
operation => 'auto optimizer stats job',
window_name => NULL);
END;
/
Просмотр журнала сбора статистики:
select client_name,
job_status,
job_start_time,
job_duration,
job_info
from dba_autotask_job_history
where client_name like '%stats%'
order by job_start_time;
Удалять или добавлять задачи в отдельные окна обслуживания:
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/
Глобальные параметры сбора статистики могут быть изменены с помощью процедуры
SET_GLOBAL_PREFS в пакете DBMS_STATS:
EXEC DBMS_STATS.alter_stats_history_retention(90);
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '5');
Включить инкрементальный сбор статистики:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE');
END;
/
Включить сбор статистики только по системному словарю:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
END;
/
Автоматический режим сбора статистики:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','AUTO');
END;
/
Проверить в каком режиме идет сбор статистики:
select DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') as AUTOSTATS_TARGET from dual;
Глобальные настройки режима сбора статистики:
select 'CASCADE = '||dbms_stats.GET_PREFS('CASCADE') as global_stats from dual
union all
select 'DEGREE = '||dbms_stats.GET_PREFS('DEGREE') from dual
union all
select 'ESTIMATE_PERCENT = '||dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual
union all
select 'METHOD_OPT = '||dbms_stats.GET_PREFS('METHOD_OPT') from dual
union all
select 'NO_INVALIDATE = '||dbms_stats.GET_PREFS('NO_INVALIDATE') from dual
union all
select 'GRANULARITY = '||dbms_stats.GET_PREFS('GRANULARITY') from dual
union all
select 'PUBLISH = '||dbms_stats.GET_PREFS('PUBLISH') from dual
union all
select 'INCREMENTAL = '||dbms_stats.GET_PREFS('INCREMENTAL') from dual
union all
select 'STALE_PERCENT = '||dbms_stats.GET_PREFS('STALE_PERCENT') from dual
union all
select 'AUTOSTATS_TARGET = '||DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') as AUTOSTATS_TARGET from dual;
GLOBAL_STATS
-----------------------------------------------
CASCADE = DBMS_STATS.AUTO_CASCADE
DEGREE = NULL
ESTIMATE_PERCENT = DBMS_STATS.AUTO_SAMPLE_SIZE
METHOD_OPT = FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE = DBMS_STATS.AUTO_INVALIDATE
GRANULARITY = AUTO
PUBLISH = TRUE
INCREMENTAL = FALSE
STALE_PERCENT = 10
AUTOSTATS_TARGET = AUTO
Параметры sql tuning advisor могут быть изменены с помощью процедуры SET_TUNING_TASK_PARAMETER в пакете DBMS_SQLTUNE.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1200);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES', 'FALSE');
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_SQL_PROFILES_PER_EXEC', 20);
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10000);
END;
/
Вся группа окон MAINTENANCE_WINDOW_GROUP может быть включена или отключена с помощью процедур ENABLE и DISABLE в пакете DBMS_SCHEDULER.
MAINTENANCE_WINDOW_GROUP
BEGIN
DBMS_SCHEDULER.disable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP',
force => TRUE);
DBMS_SCHEDULER.enable(
name => 'SYS.MAINTENANCE_WINDOW_GROUP');
END;
/
Изменить атрибуты окна можно с использованием процедуры SET_ATTRIBUTE пакета DBMS_SCHEDULER.
BEGIN
DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(180, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/
Если создали дополнительные окна обслуживания, назначьте их группе MAINTENANCE_WINDOW_GROUP,
и по умолчанию они будут назначены плану ресурсов DEFAULT_MAINTENANCE_PLAN.
При необходимости вы можете отредактировать этот план или создать новый план.
Если вы создаете новый план ресурсов, вы можете назначить его соответствующим окнам обслуживания с помощью пакета DBMS_SCHEDULER.
BEGIN
DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN',
value => 'MY_NEW_PLAN');
DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/
Чтобы очистить план ресурса, выполните следующую команду.
BEGIN
DBMS_SCHEDULER.set_attribute(
name => 'SYS.MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN',
value => '');
END;
/
Следующие представления отображают информацию,
связанную с задачами автоматического обслуживания базы данных:
select * from dba_scheduler_wingroup_members;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY;
select * from DBA_AUTOTASK_SCHEDULE;
select * from dba_rsrc_plans;
select * from dba_rsrc_plan_directives;
select * from dba_rsrc_consumer_groups;
select * from dba_rsrc_consumer_group_privs;
select * from dba_rsrc_mapping_priority;
select * from dba_rsrc_group_mappings;
select * from dba_rsrc_manager_system_privs;
select initial_rsrc_consumer_group from dba_users;
select resource_consumer_group, current_queue_duration from v$session;
select * from v$rsrc_plan;
select * from v$rsrc_consumer_group;
