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: to - Release: 10.2 to 11.2
Information in this document applies to any platform.


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

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

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

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?


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;




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','');
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;

Niciun comentariu:

Trimiteți un comentariu