2018. 12. 14. 02:24 오라클
disable resource manager
Resource Manager and SQL Tuning Advisory DEFAULT_MAINTENANCE_PLAN (문서 ID 786346.1)
Summary of changes between 10g and 11g.
Resource Manager:
| Subject | 10g | 11g |
|---|---|---|
| Maintenance Window | 2 windows, WEEK and WEEKEND | Each day has its own window |
| Resource manager | Not enabled per default | Default resource plan specified |
Sql Tuning Advisory:
So this is a default behavior in Oracle 11g.
1]. To disable the resource manager you can use the below steps.
++ set the current resource manager plan to null (or another plan that is not restrictive):
alter system set resource_manager_plan='' scope=both
++ change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); and
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
For 11g, you need to change those too:
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
++ Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
SQL> execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
2]. To disable SQL tuning you can use the below procedure.
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
