Developing and Implementing Applications, continued

Implementing the Database Resource Manager

You can use the Database Resource Manager to allocate percentages of system resources to classes of users and jobs. For example, you could allocate 75 percent of the available CPU resources to your online users, leaving 25 percent to your batch users. To use the Database Resource Manager, you will need to create resource plans, resource consumer groups, and resource plan directives.

Prior to using the Database Resource Manager commands, you must create a “pending area” for your work. To create a pending area, use the CREATE_PENDING_AREA procedure of the DBMS_RESOURCE_MANAGER package. When you have completed your changes, use the VALIDATE_PENDING_AREA procedure to check the validity of the new set of plans, subplans, and directives. You can then either submit the changes (via SUBMIT_PENDING_AREA) or clear the changes (via CLEAR_PENDING_AREA). The procedures that manage the pending area do not have any input variables, so a sample creation of a pending area uses the following syntax:

execute DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

If the pending area is not created, you will receive an error message when you try to create a resource plan.

To create a resource plan, use the CREATE_PLAN procedure of the DBMS_RESOURCE_ MANAGER package. The syntax for the CREATE_PLAN procedure is shown in the following listing:

CREATE_PLAN
    (plan                     IN VARCHAR2,
     comment                  IN VARCHAR2,
     cpu_mth                  IN VARCHAR2 DEFAULT
            'EMPHASIS',
     active_sess_pool_mth   IN VARCHAR2 DEFAULT
            'ACTIVE_SESS_POOL_ABSOLUTE',
     parallel_degree_limit_mth IN VARCHAR2 DEFAULT
           'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
     queueing_mth              IN VARCHAR2 DEFAULT
            'FIFO_TIMEOUT')

When you create a plan, give the plan a name (in the plan variable) and a comment. By default, the CPU allocation method will use the “emphasis” method, allocating CPU resources based on percentage. The following example shows the creation of a plan called DEVELOPERS:

execute DBMS_RESOURCE_MANAGER.CREATE_PLAN - (Plan => 'DEVELOPERS', -
  Comment => 'Developers, in Development database');

NOTE

The hyphen (-) character is a continuation character in SQL*Plus, allowing a single command to span multiple lines.

In order to create and manage resource plans and resource consumer groups, you must have the ADMINISTER_RESOURCE_MANAGER system privilege enabled for your session. DBAs have this privilege with the with admin option. To grant this privilege to non-DBAs, you must execute the GRANT_SYSTEM_PRIVILEGE procedure of the DBMS_RESOURCE_MANAGER_PRIVS package. The following example grants the user MARTHA the ability to manage the Database Resource Manager:

execute DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_
PRIVILEGE - 
 (grantee_name => 'Martha', -
 privilege_name => 'ADMINISTER_RESOURCE_MANAGER', -
 admin_option => TRUE);

You can revoke MARTHA’s privileges via the REVOKE_SYSTEM_PRIVILEGE procedure of the DBMS_RESOURCE_MANAGER package.

With the ADMINISTER_RESOURCE_MANAGER privilege enabled, you can create a resource consumer group using the CREATE_CONSUMER_GROUP procedure within DBMS_RESOURCE_ MANAGER. The syntax for the CREATE_CONSUMER_GROUP procedure is shown in the following listing:

CREATE_CONSUMER_GROUP
    (consumer_group IN VARCHAR2,
     comment        IN VARCHAR2,
     cpu_mth        IN VARCHAR2 DEFAULT 'ROUND-ROBIN')

You will be assigning users to resource consumer groups, so give the groups names that are based on the logical divisions of your users. The following example creates two groups—one for online developers and a second for batch developers:

execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -
 (Consumer_Group => 'Online_developers', - 
 Comment => 'Online developers');
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -
 (Consumer_Group => 'Batch_developers', -
 Comment => 'Batch developers');

Once the plan and resource consumer groups are established, you need to create resource plan directives and assign users to the resource consumer groups. To assign directives to a plan, use the CREATE_PLAN_DIRECTIVE procedure of the DBMS_RESOURCE_MANAGER package. The syntax for the CREATE_PLAN_DIRECTIVE procedure is shown in the following listing:

CREATE_PLAN_DIRECTIVE
    (plan                     IN VARCHAR2,
     group_or_subplan         IN VARCHAR2,
     comment                  IN VARCHAR2,
     cpu_p1                   IN NUMBER   DEFAULT NULL, 
     cpu_p2                   IN NUMBER   DEFAULT NULL,
     cpu_p3                   IN NUMBER   DEFAULT NULL,
     cpu_p4                   IN NUMBER   DEFAULT NULL,
     cpu_p5                   IN NUMBER   DEFAULT NULL,
     cpu_p6                   IN NUMBER   DEFAULT NULL, 
     cpu_p7                   IN NUMBER   DEFAULT NULL,
     cpu_p8                   IN NUMBER  DEFAULT NULL,
     active_sess_pool_p1      IN NUMBER 
DEFAULT UNLIMITED, 
     queueing_p1              IN NUMBER 
DEFAULT UNLIMITED,
     parallel_degree_limit_p1 IN NUMBER 
DEFAULT NULL,
     switch_group             IN VARCHAR2 
DEFAULT NULL,
     switch_time              IN NUMBER 
DEFAULT UNLIMITED,
     switch_estimate          IN BOOLEAN 
DEFAULT FALSE,
     max_est_exec_time        IN NUMBER 
DEFAULT UNLIMITED,
     undo_pool                IN NUMBER 
DEFAULT UNLIMITED,
     max_idle_time            IN NUMBER 
DEFAULT NULL,
     max_idle_time_blocker    IN NUMBER 
DEFAULT NULL,
     switch_time_in_call      IN NUMBER DEFAULT NULL);  

The multiple CPU variables in the CREATE_PLAN_DIRECTIVE procedure support the creation of multiple levels of CPU allocation. For example, you could allocate 75 percent of all your CPU resources (level 1) to your online users. Of the remaining CPU resources (level 2), you could allocate 50 percent to a second set of users. You could split the remaining 50 percent of resources available at level 2 to multiple groups at a third level. The CREATE_PLAN_DIRECTIVE procedure supports up to eight levels of CPU allocations.

The following example shows the creation of the plan directives for the Online_developers and Batch_developers resource consumer groups within the DEVELOPERS resource plan:

execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE -
 (Plan => 'DEVELOPERS', -
 Group_or_subplan => 'Online_developers', -
 Comment => 'online developers', -
 Cpu_p1 => 75, -
 Cpu_p2=> 0, -
 Parallel_degree_limit_p1 => 12);
execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE -
 (Plan => 'DEVELOPERS', -
 Group_or_subplan => 'Batch_developers', -
 Comment => 'Batch developers', -
 Cpu_p1 => 25, -
 Cpu_p2 => 0, -
 Parallel_degree_limit_p1 => 6);

In addition to allocating CPU resources, the plan directives restrict the parallelism of operations performed by members of the resource consumer group. In the preceding example, batch developers are limited to a degree of parallelism of 6, reducing their ability to consume system resources. Online developers are limited to a degree of parallelism of 12.

To assign a user to a resource consumer group, use the SET_INITIAL_ CONSUMER_GROUP procedure of the DBMS_RESOURCE_MANAGER package. The syntax for the SET_INITIAL_ CONSUMER_GROUP procedure is shown in the following listing:

SET_INITIAL_CONSUMER_GROUP
    (user             IN VARCHAR2, 
     consumer_group   IN VARCHAR2)

If a user has never had an initial consumer group set via the SET_INITIAL_ CONSUMER_ GROUP procedure, the user is automatically enrolled in the resource consumer group named DEFAULT_CONSUMER_GROUP.

To enable the Resource Manager within your database, set the RESOURCE_MANAGER_ PLAN database initialization parameter to the name of the resource plan for the instance. Resource plans can have subplans, so you can create tiers of resource allocations within the instance. If you do not set a value for the RESOURCE_MANAGER_PLAN parameter, resource management is not performed in the instance.

You can dynamically alter the instance to use a different resource allocation plan via the set initial_consumer_group clause of the alter system command. For example, you could create a resource plan for your daytime users (DAYTIME_USERS) and a second for your batch users (BATCH_USERS). You could create a job that each day executes this command at 6:00 A.M.:

alter system set initial_consumer_group = 'DAYTIME_USERS';

Then at a set time in the evening, you could change consumer groups to benefit the batch users:

alter system set initial_consumer_group = 'BATCH_USERS';

The resource allocation plan for the instance will thus be altered without needing to shut down and restart the instance.

When using multiple resource allocation plans in this fashion, you need to make sure you don’t accidentally use the wrong plan at the wrong time. For example, if the database is down during a scheduled plan change, your job that changes the plan allocation may not execute. How will that affect your users? If you use multiple resource allocation plans, you need to consider the impact of using the wrong plan at the wrong time. To avoid such problems, you should try to minimize the number of resource allocation plans in use.

In addition to the examples and commands shown in this section, you can update existing resource plans (via the UPDATE_PLAN procedure), delete resource plans (via DELETE_PLAN), and cascade the deletion of a resource plan plus all its subplans and related resource consumer groups (DELETE_PLAN_CASCADE). You can update and delete resource consumer groups via the UPDATE_CONSUMER_GROUP and DELETE_CONSUMER_GROUP procedures, respectively. Resource plan directives may be updated via UPDATE_PLAN_DIRECTIVE and deleted via DELETE_PLAN_DIRECTIVE.

When you are modifying resource plans, resource consumer groups, and resource plan directives, you should test the changes prior to implementing them. To test your changes, create a pending area for your work. To create a pending area, use the CREATE_PENDING_AREA procedure of the DBMS_RESOURCE_MANAGER package. When you have completed your changes, use the VALIDATE_PENDING_AREA procedure to check the validity of the new set of plans, subplans, and directives. You can then either submit the changes (via SUBMIT_PENDING_ AREA) or clear the changes (via CLEAR_PENDING_AREA). The procedures that manage the pending area do not have any input variables, so a sample validation and submission of a pending area uses the following syntax:

execute DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();