Enabling Oracle Database Resource Manager and Switching Plans

You enable Oracle Database Resource Manager (the Resource Manager) by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan, identifying the plan to be used for the current instance. If no plan is specified with this parameter, the Resource Manager is not enabled.

By default the Resource Manager is not enabled, except during preconfigured maintenance windows, described later in this section.

The following statement in a text initialization parameter file activates the Resource Manager upon database startup and sets the top plan as mydb_plan.

RESOURCE_MANAGER_PLAN = mydb_plan

You can also activate or deactivate the Resource Manager, or change the current top plan, using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure or the ALTER SYSTEM statement.

The following SQL statement sets the top plan to mydb_plan, and activates the Resource Manager if it is not already active:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';

An error message is returned if the specified plan does not exist in the data dictionary.

Automatic Enabling of the Resource Manager by Oracle Scheduler Windows

The Resource Manager automatically activates if an Oracle Scheduler window that specifies a resource plan opens. When the Scheduler window closes, the resource plan associated with the window is disabled, and the resource plan that was running before the Scheduler window opened is reenabled. (If no resource plan was enabled before the window opened, then the Resource Manager is disabled.) In an Oracle Real Application Clusters environment, a Scheduler window applies to all instances, so the window's resource plan is enabled on every instance.

Note that by default a set of automated maintenance tasks run during maintenance windows, which are predefined Scheduler windows that are members of the MAINTENANCE_WINDOW_GROUP window group and which specify the DEFAULT_MAINTENANCE_PLAN resource plan. Thus, the Resource Manager activates by default during maintenance windows. You can modify these maintenance windows to use a different resource plan, if desired.

Disabling Plan Switches by Oracle Scheduler Windows

In some cases, the automatic change of Resource Manager plans at Scheduler window boundaries may be undesirable. For example, if you have an important task to finish, and if you set the Resource Manager plan to give your task priority, then you expect that the plan will remain the same until you change it. However, because a Scheduler window could activate after you have set your plan, the Resource Manager plan might change while your task is running.

To prevent this situation, you can set the RESOURCE_MANAGER_PLAN initialization parameter to the name of the plan that you want for the system and prepend "FORCE:" to the name, as shown in the following SQL statement:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

Using the prefix FORCE: indicates that the current resource plan can be changed only when the database administrator changes the value of the RESOURCE_MANAGER_PLAN initialization parameter. This restriction can be lifted by rerunning the command without preceding the plan name with "FORCE:".

The DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure has a similar capability.

Disabling the Resource Manager

To disable the Resource Manager, complete the following steps:

1. Issue the following SQL statement:

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
   

2. Disassociate the Resource Manager from all Oracle Scheduler windows.

   To do so, for any Scheduler window that references a resource plan in its resource_plan attribute, use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set resource_plan to the empty string (''). Qualify the window name with the SYS schema name if you are not logged in as user SYS. You can view Scheduler windows with the DBA_SCHEDULER_WINDOWS data dictionary view. See "Altering Windows" and Oracle Database PL/SQL Packages and Types Reference for more information.

   Note:

   By default, all maintenance windows reference the DEFAULT_MAINTENANCE_PLAN resource plan. To completely disable the Resource Manager, you must alter all maintenance windows to remove this plan. However, use caution, because resource consumption by automated maintenance tasks will no longer be regulated, which may adversely affect the performance of your other sessions. See Chapter 26, "Managing Automated Database Maintenance Tasks" for more information on maintenance windows.