how to see the active resource plan manager

select * from v$rsrc_plan;

Resource Manager Plan Changes Settings Every Week [ID 759503.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Goal

Detailed description with Example
----------------------------------
1) Noticed that the resource manager plan was set to

"resource_manager_plan string SCHEDULER[0x22BB]:SYSTEM_PLAN".

2) Changed this using the command

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' SCOPE=BOTH;

3) Every week when checking on this database setting it goes back to the setting

"SCHEDULER[0x22BB]:SYSTEM_PLAN".

I keep having to issue the following command, but the resource manager plan keeps changing back

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' SCOPE=BOTH;

The database is not being recycled so why is the setting changing. It is causing performance issues every time it changes back.
What could cause this setting to change back?

Solution

It is possible that the scheduler windows are altering the resource plan. A scheduler window can have a resource plan associated with it. The default plan associated with default weekend and weeknight windows is the system_plan.

You can see that by looking at the following view:

select * from V$RSRC_PLAN_HISTORY
order by SEQUENCE#;

To set the resource plan such that the scheduler cannot alter the plan, do the following:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:Desired_Resource_Plan' scope=both;

OR

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:' scope=both; --> For Null

OR

Change the active windows to use the null resource manager plan (or other unrestrictive plan) using:

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
--And
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

(execute the same for any other scheduler windows that exist, changing the window name).

From there on, the scheduler windows will not be able to change the resource plan.

The active resource plan can be shown by:

select * from v$rsrc_plan;