In the previous steps, we noted that users in the database are organized into several Consumer Groups. But how are resources distributed among these groups?

The answer lies in the Plans.

The Plan is responsible for distributing resources among the Consumer Groups. There may be multiple Resource Manager Plans in a single database, but at any given moment, only one Plan is active. This active Plan controls the resources and is applied to all the database.

Upon creating a database, three Plans are created, as discussed previously.

Principally, resources can be distributed among groups at eight levels.

In reality, there are several resources that we can control through the Resource Manager Plan:

1. CPU Method: This allows you to manage and allocate resources for a specific CPU processing unit among Consumer Groups or Subplans.
2. Active Session Pool with Queuing: This enables you to specify the number of active sessions that can be connected at the same time per Consumer Group. If the number exceeds a specified limit, those sessions will remain in line waiting for one of the active sessions to finish. Generally, you can also set a time limit for a session to wait in line before an error is issued.
3. Degree of Parallelism: This allows you to control the level of concurrency for any operation within a Consumer Group.
4. Execution Time Limit: This allows you to set a maximum allowed time for executing operations.
5. Idle Time Limit: This allows you to specify how long a session can be idle before it is terminated.
6. Undo Pool: Through this, you can control the amount of Undo that can be generated by a Consumer Group or Subplan.

We will use the CREATE_PLAN procedure found in the DBMS_RESOURCE_MANAGER package to create the PLAN, and this procedure includes several parameters.

We will also use the CREATE_PLAN_DIRECTIVE procedure to allocate resources to the Consumer Groups via the PLAN.

We will create the PLAN using the CREATE_PLAN procedure, but we will later create DIRECTIVES using the CREATE_PLAN_DIRECTIVE procedure to distribute the resources among the Consumer Groups through the PLAN.

Let's assume we will create a PLAN called NEW_PLAN and we will distribute the resources using DIRECTIVES across the Consumer Groups, specifically:

(CPU Method, Active Session, Degree of Parallelism, Execution Time Limit, Idle Time Limit, Undo Pool)

These are the parameters for the CREATE_PLAN_DIRECTIVE procedure:

• Plan: name of resource plan
• group_or_subplan: name of consumer group or subplan
• comment: comment for the plan directive
• cpu_p1: first parameter for the CPU resource allocation method
• cpu_p2: second parameter for the CPU resource allocation method
• cpu_p3: third parameter for the CPU resource allocation method
• cpu_p4: fourth parameter for the CPU resource allocation method
• cpu_p5: fifth parameter for the CPU resource allocation method
• cpu_p6: sixth parameter for the CPU resource allocation method
• cpu_p7: seventh parameter for the CPU resource allocation method
• cpu_p8: eighth parameter for the CPU resource allocation method
• active_sess_pool_p1: first parameter for the max. active sessions allocation method
• queueing_p1: queue timeout in seconds
• parallel_degree_limit_p1: first parameter for the degree of parallelism allocation method
• switch_group: group to switch once switch time is reached
• switch_time: max execution time within a group
• switch_estimate: use execution time estimate to assign group?
• max_est_exec_time: max. estimated execution time in seconds
• undo_pool: max. cumulative undo allocated for consumer groups
• max_idle_time: max. idle time
• max_idle_blocker_time: max. idle time when blocking other sessions
• switch_time_in_call: max execution time within a top call - will switch back to home group after call

Note that there are 8 levels for distributing CPU resources among groups, ensuring that each DIRECTIVE is directed to only one Consumer Group.

Now, let's assume we want to create a PLAN named NEW_PLAN that targets three groups: (SYS_GROUP, NEW_GROUP, OTHER_GROUPS).

This means we need one PLAN and three DIRECTIVES, each aimed at a specific Consumer Group.

Let’s say we want to allocate a portion of CPU resources to the SYS_GROUP as follows:

• Level 1: 60%
• Level 2: 50%
• Level 3: 40%

This means that at Level 1, the priority for SYS_GROUP is 60%, while the remaining 40% will be distributed between the other two groups (NEW_GROUP & OTHER_GROUPS) as follows:

• Level 1: 20%
• Level 2: 25%
• Level 3: 30%

DECLARE

PLAN VARCHAR2(200);

GROUP_OR_SUBPLAN VARCHAR2(200);

COMMENT VARCHAR2(200);

CPU_P1 NUMBER;

CPU_P2 NUMBER;

CPU_P3 NUMBER;

ACTIVE_SESS_POOL_P١ NUMBER;

MAX_IDLE_TIME NUMBER;

BEGIN

PLAN := 'NEW_PLAN';

COMMENT := 'THIS IS NEW PLAN';

GROUP_OR_SUBPLAN := 'OTHER_GROUPS';

CPU_P1 :=20 ;

CPU_P2 :=25 ;

CPU_P3 :=30 ;

ACTIVE_SESS_POOL_P1 :=3 ;

MAX_IDLE_TIME := 2;

SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN,COMMENT );

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'OTHER_GROUPS', 'DIRECTIVE TO OTHER_GROUPS ', 20 ,25,30,3,2 );

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'SYS_GROUP', 'DIRECTIVE TO SYS_GROUP ',60,50,40,4,3 ); 

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'NEW_GROUP', 'DIRECTIVE TO NEW_GROUP ', 20,25,30,2,1);

SYS.DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

COMMIT;

END; 

We have created a PLAN called NEW_PLAN, and we have also created three Directives directed at the groups (OTHER_GROUPS & SYS_GROUP & NEW_GROUP).

It should be noted that you can handle eight levels; after that, the remaining variables such as active_sess_pool_p1 can be granted, which is to determine the maximum number of active Sessions that belong to one Consumer Group. After this number, the Sessions will remain in the wait queue. Also, max_idle_time is used to determine the time that a Session can remain idle before it is terminated. The same applies to the remaining variables.

Now we can activate this Plan in the database by using the command:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='NEW_PLAN'; 

At this moment, this Plan will be activated on the database.

You can inquire about the Plan and the Directive through:

• DBA_RSRC_PLANS
• DBA_RSRC_PLAN_DIRECTIVES