Working With Oracle Consumer Groups

The following table describes the delivered PeopleSoft resource groups and the PeopleTools executables they contain.

While PeopleTools delivers a set of predefined resource groups that you can map to Oracle consumer groups, you only need to create consumer groups for the resource groups where you need to introduce control of system resource usage.

For example, if COBOL and PeopleSoft Analytic Calculation Engine processing are the only areas of your PeopleSoft system that cause unwanted resource usage, then you only need to create Oracle consumer groups to map to ANALYTIC SERVER and COBOL PeopleSoft resource groups.

This section describes the process of defining the resource plan, consumer groups, and plan directives to correspond to the PeopleSoft resource groups.

To create the plan, groups, and directives, you can use the tool of your choice, such as SQL Plus, Oracle SQL Developer, or the Resource Manager interface in Oracle Enterprise Manager.

To create a resource plan, consumer group(s), and directives:

1. Connect to the Oracle SID containing the PeopleSoft schema.

2. Create a pending area.

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

3. Create a resource plan, with a name of your choice.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'PeopleSoft_plan',
   COMMENT => 'Resource plan/method for PeopleSoft Users Sessions');
   

4. Create the desired consumer groups.

   Create the number of consumer groups required to correspond to the PeopleSoft resource groups that need to be controlled. For this example, assume that only the resource usage of executables related to the application server and PeopleSoft Query need to be controlled.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
   (CONSUMER_GROUP => 'Application Server', 
   COMMENT => 'Resource consumer group/method for online users sessions');
   
   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PSQuery', 
   COMMENT => 'Resource consumer group/method for PSQuery sessions');
   

5. Create the directives for the consumer groups you created.

   For example:

   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
   GROUP_OR_SUBPLAN => 'Application Server',
   COMMENT => 'Applications Server sessions at level 1', CPU_P1 => 50, 
   CPU_P2=> 0, PARALLEL_DEGREE_LIMIT_P1 => 8);
   
   EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
   GROUP_OR_SUBPLAN => 'PSQuery',
   COMMENT => 'PSQuery sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
   PARALLEL_DEGREE_LIMIT_P1 => 2);
   

6. Validate the resource plan.

   EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

7. Submit the plan.

   EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

8. Grant the PeopleSoft schema user (PeopleSoft Access ID) these additional Oracle privileges necessary to administer resource plans:

   GRANT_SYSTEM_PRIVILEGE ADMINSTER_RESOURCE MANAGER

   EXECUTE
   DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE('<ACCESS_ID>',
   'ADMINISTER_RESOURCE_MANAGER',TRUE);
   

   GRANT_SWITCH_CONSUMER_GROUP

   EXECUTE 
   DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('<ACCESS_ID>', 
   '<CONSUMER_GROUP_NAME>', FALSE);

9. Enable the Database Resource Manager in your PeopleSoft SID.

   For completing this, you have two options:

   • Set RESOURCE_MANAGER_PLAN='PeopleSoft_plan’ in the init.ora file for PeopleSoft SID.

   • Issue the following ALTER statement: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='PeopleSoft_plan’;

You can issue each SQL statement separately, or you may elect to create a single script to create the required consumer groups. The following is a sample SQL script for creating a resource plan with consumer groups for your PeopleSoft system.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();


DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'PeopleSoft_plan',
COMMENT => 'Resource plan/method for PeopleSoft Users Sessions');


DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Application Server',
COMMENT => 'Resource consumer group/method for online users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PSQuery',
COMMENT => 'Resource consumer group/method for PSQuery sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'PubSub',
COMMENT => 'Resource consumer group/method for PUBSUB sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Application Engine',
COMMENT => 'Resource consumer group/method for Application Engine');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Cobol',
COMMENT => 'Resource consumer group/method for Cobol');


DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Application Server',
COMMENT => 'Applications Server sessions at level 1', CPU_P1 => 50, CPU_P2=> 0,
 PARALLEL_DEGREE_LIMIT_P1 => 8);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'PSQuery',
COMMENT => 'PSQuery sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
PARALLEL_DEGREE_LIMIT_P1 => 2);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'PubSub',
COMMENT => 'PubSub sessions at level 1', CPU_P1 => 10, CPU_P2 => 0, 
PARALLEL_DEGREE_LIMIT_P1 => 3);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Application Engine',
COMMENT => 'Application Engine sessions at level 1', CPU_P1 => 10, CPU_P2 => 50,
 CPU_P3 => 50);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'PeopleSoft_plan', 
GROUP_OR_SUBPLAN => 'Cobol',
COMMENT => 'Cobol sessions at level 1', CPU_P1 => 10, CPU_P2 => 50, CPU_P3 => 50);


DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

The Pt Ora Resource page enables you to map PeopleSoft resource groups with the Oracle consumer groups you have defined.

Select PeopleTools, Utilities, Administration, Oracle Resource Management.

Image: Pt Ora Resource page

This example illustrates the fields and controls on the Pt Ora Resource page.

CONSUMER_GROUP => 'PSQuery'