Database Resource Manager - CREATE_SIMPLE_PLAN

How to use Oracle's CREATE_SIMPLE_PLAN to implement a Database Resource Manager (DRM) in 10g.

You can use the following as a easy way to segregate certain user connections based on CLIENT_PROGRAM. For example: Oracle Discoverer (client and web-based) and Oracle SQL Developer.

This plan will demonstrate how to put sessions using these programs in a lower priority queue while maintaining the rest of your users in the "default" queue. SYS and SYSTEM users are in the highest queue.

Step 1:

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'DIS51USR.EXE',  

    consumer_group => 'LOW_GROUP');

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'DIS51WS@OCTEST.OCINT.COM (TNS V1-V3)',

    consumer_group => 'LOW_GROUP');

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'SQL DEVELOPER',

    consumer_group => 'LOW_GROUP');

END;

Step 2:

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Step 3:
exec dbms_resource_manager.submit_pending_area();

Now let's validate what was created using the following SQL.

Validate the Plan creation:

SELECT

     plan

    ,num_plan_directives

    ,cpu_method

    ,active_sess_pool_mth

    ,parallel_degree_limit_mth

    ,queueing_mth

    ,status

    ,mandatory

  FROM dba_rsrc_plans;

Groups within a Plan:SELECT     plan

    ,group_or_subplan

    ,type

    ,cpu_p1

    ,cpu_p2

    ,cpu_p3

    ,status

  FROM dba_rsrc_plan_directives

 WHERE plan = 'XXOC_SIMPLE_PLAN1';

User Mappings:

SELECT

     initial_rsrc_consumer_group,

     username

   FROM dba_users

 ORDER BY 1,2;

Special User Mappings (showing client_program):

SELECT *

FROM dba_rsrc_group_mappings;

Ready to Activate your Plan?

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = XXOC_SIMPLE_PLAN1;

(If RAC - run on each node. This means you can have different plans on different Nodes.)

View active plans:

SELECT * FROM V$RSRC_PLAN;

Monitor your Plans activity:

SELECT * FROM V$RSRC_CONSUMER_GROUP;

To allow for Group Switching between Default_Consumer_Group and Low_Group you can do the following:



BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'XXOC_SIMPLE_PLAN1',
group_or_subplan => 'DEFAULT_CONSUMER_GROUP',
new_comment => ' ',
new_cpu_p1 => 0, new_cpu_p2 => 80, new_cpu_p3 => 0, new_cpu_p4 => 0,
new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 0,
new_parallel_degree_limit_p1 => -1,
new_active_sess_pool_p1 => -1,
new_queueing_p1 => -1,
new_switch_group => 'LOW_GROUP',
new_switch_time => -1,
new_switch_estimate => true,
new_max_est_exec_time => -1,
new_undo_pool => -1
);
dbms_resource_manager.submit_pending_area();
END;