如何彻底禁用resource manager

最新推荐文章于 2023-05-13 09:27:13 发布

wgz7747147820

最新推荐文章于 2023-05-13 09:27:13 发布

阅读量696

点赞数

分类专栏： orale 文章标签： oracle

本文链接：https://blog.csdn.net/wgz7747147820/article/details/111252234

版权

orale 专栏收录该内容

204 篇文章 0 订阅

订阅专栏

select 'exec dbms_scheduler.set_attribute('''||window_name||''',''resource_plan'','''');' from dba_scheduler_windows
exec dbms_scheduler.set_attribute('MONDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('TUESDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('FRIDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('SATURDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('SUNDAY_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','resource_plan','');
exec dbms_scheduler.set_attribute('WEEKEND_WINDOW','resource_plan','');

SYMPTOMS
After upgrade to 11g getting the following messages in the alert.log.

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK”
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK”

Is database resource manager and sql tuning advisor being turned on by default in 11g.

CAUSE
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:

Oracle Database 11g, by default, the Automatic Tuning Optimizer runs regularly during the Oracle scheduler Maintenance window, as the new automated maintenance task called the SQL Tuning Advisory task.

So this is a default behavior in Oracle 11g.

SOLUTION
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(’’,‘RESOURCE_PLAN’,’’);

2]. To disable SQL tuning you can use the below procedure.

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