Sunday, February 24, 2013

0 Resource Manager

Resource manager provide the more flexible way to control the resource usage than using the profile.

  • Resource consumer group:  Consumer group is the high level profile which can assign to the user.
  • Resource plan and sub plan: Plan can includes the sub plan. The plan is grouping of the plan directive for the resource restrictions.
  • Resource plan directive : Itemize resource and the limit. We can define the directive and assign it to the plan/sub plan  . The plan directive must assign to the consumer group. OTHER_GROUPS is required when create the plan directive. This group will be used by all other user which does not have explicitly consumer group assign .

 

Related tables:

  • DBA_RSRC_CONSUMER_GROUPS
  • DBA_RSRC_PLANS
  • DBA_RSRC_PLAN_DIRECTIVES
  • DBA_USERS
  • DBA_RSRC_CONSUMER_GROUP_PRIVS

image

This demo is going to include below topics:

  • Show current active resource manager
  • Create consumer group
  • Create plan and plan directive
  • Update plan
  • Assign the plan to the user
  • Change or disable the resource manager
  • Delete the plan
  • Delete the consumer group

show current active resource manager

show parameter resource_manager_plan

image

image

image

Create consumer group

BEGIN
    sys.dbms_resource_manager.clear_pending_area();
    sys.dbms_resource_manager.create_pending_area();
    sys.dbms_resource_manager.create_consumer_group( CONSUMER_GROUP=>'PO_USER',COMMENT=>'my test resource group');
    sys.dbms_resource_manager.submit_pending_area();
END;
/
select consumer_group,comments from DBA_RSRC_CONSUMER_GROUPS 

image

image

Create plan and plan directive

BEGIN
    SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN( plan    => 'PO_PLAN1', comment => 'Plan for testing.');
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( 
        plan                        => 'PO_PLAN1',
        group_or_subplan             => 'PO_USER',
        comment                      => 'Plan for Po testing',
        MGMT_P1                       => 15,
        PARALLEL_DEGREE_LIMIT_P1     => 2,
        ACTIVE_SESS_POOL_P1          => 4);
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( 
        plan                        => 'PO_PLAN1',
        group_or_subplan             => 'OTHER_GROUPS',
        comment                      => 'This is require group',
        MGMT_P1                       => 10);
    SYS.DBMS_RESOURCE_MANAGER.validate_pending_area;
    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

image

Confirm & query the plan

select * from DBA_RSRC_PLAN_DIRECTIVES where plan='PO_PLAN1';
select PLAN,num_plan_directives,comments from DBA_RSRC_PLANS where PLAN='PO_PLAN1';

image

image

Update plan

BEGIN
    SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.UPDATE_PLAN(plan    => 'PO_PLAN1', new_comment => 'update po plan for testing');
    SYS.DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE ( 
        plan                        => 'PO_PLAN1',
        group_or_subplan             => 'PO_USER',
        new_switch_io_megabytes         => 10
        );
    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

Confirm the update result

select PLAN,group_or_subplan,mgmt_p1,parallel_degree_limit_p1,active_sess_pool_p1,switch_io_megabytes,comments from DBA_RSRC_PLAN_DIRECTIVES where plan='PO_PLAN1';

image

Before

image

After

image

 

 

Assign the plan to the user

BEGIN
    SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name   => 'spa_user',
        consumer_group => 'PO_USER',
        grant_option   => FALSE);
    SYS.DBMS_RESOURCE_MANAGER.set_initial_consumer_group('spa_user', 'PO_USER');
    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

image

Query the user’s consumer group

select username,profile,initial_rsrc_consumer_group from DBA_USERS

image

Change the resource manager

show parameter resource_manager_plan
alter system set  resource_manager_plan='PO_PLAN1'

image

Login to the new user and check its resource plan

SELECT username, resource_consumer_group  FROM   v$session where username='SPA_USER';

image

If user already has the session connect to the database, the resource manager would effective right away.

To disable the resource manager, just change the resource_manager_plan to empty.

Delete the plan

This would also delete the plan directive.

BEGIN
    SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.DELETE_PLAN ('PO_PLAN1'); 
    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
 
 
select * from DBA_RSRC_PLAN_DIRECTIVES where plan='PO_PLAN1';
select PLAN,num_plan_directives,comments from DBA_RSRC_PLANS where PLAN='PO_PLAN1';

image

 

delete consumer group

Consumer group only can be removed when it is not being refer by the plan. otherwise, we would get the error:  ORA-29381: plan/consumer_group XXX referred to by another plan and cannot be deleted

image

BEGIN
    SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    SYS.DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP ('PO_USER'); 
    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
select consumer_group,comments from DBA_RSRC_CONSUMER_GROUPS 

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates