Creating an Oracle Resource Plan and Consumer 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.

Note:

This section covers information specific to PeopleSoft. It does not cover all topics related to Oracle Database Resource Manager. This documentation assumes that you have read and understand the information contained in Oracle Database Administrator’s Guide related to Oracle Database Resource 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’;