Thursday, June 14, 2012

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;

1 comment:

  1. excellent Article...thank you so much...Anthony Ballo

    ReplyDelete