[ORACLE 9i] resource_plan

more..

 

desc dbms_resource_manager

exec dbms_resource_manager.create_pending_area(); //메모리영역지정

exec dbms_resource_manager.create_consumer_group(consumer_group=>'oltp',comment=>'Transaction process');
exec dbms_resource_manager.create_consumer_group(consumer_group=>'dss',comment=>'batch process');

exec dbms_resource_manager.create_plan(plan=>'day',comment=>'day plan');
exec dbms_resource_manager.create_plan(plan=>'night',comment=>'night plan');

exec dbms_resource_manager.create_plan_directive(plan=>'day',group_or_subplan=>'oltp',comment=>'..',cpu_p1=>'60');
exec dbms_resource_manager.create_plan_directive(plan=>'day',group_or_subplan=>'dss',comment=>'..',cpu_p1=>'30');
exec dbms_resource_manager.create_plan_directive(plan=>'day',group_or_subplan=>'other_groups',comment=>'..',cpu_p1=>'10');

exec dbms_resource_manager.create_plan_directive(plan=>'night',group_or_subplan=>'oltp',comment=>'..', cpu_p1=>'30');
exec dbms_resource_manager.create_plan_directive(plan=>'night',group_or_subplan=>'dss',comment=>'..', cpu_p1=>'60');
exec dbms_resource_manager.create_plan_directive(plan=>'night',group_or_subplan=>'other_groups',comment=>'..', cpu_p1=>'10');

exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area(); // 메모리 확보후 전송 ..

create user oe
identified by oe
default tablespace users;

grant connect, resource to hr, oe;

exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'hr',consumer_group=>'oltp', grant_option => false);
exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'sh',consumer_group=>'oltp', grant_option => false);
exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'oe',consumer_group=>'dss', grant_option => false);

exec dbms_resource_manager.set_initial_consumer_Group(user=>'hr',consumer_group=>'oltp');
exec dbms_resource_manager.set_initial_consumer_Group(user=>'sh',consumer_group=>'oltp');
exec dbms_resource_manager.set_initial_consumer_Group(user=>'oe',consumer_group=>'dss');

alter system set resource_manager_plan=day;
select * from dba_rsrc_plans;
select * from dba_rsrc_plan_directives;
select * from dba_rsrc_consumer_groups;
alter system set resource_manager_plan=day;
select * from v$rsrc_plan;
select * from dba_users;

exec dbms_resource_manager.create_pending_area();

exec dbms_resource_manager.update_plan_directive(PLAN=>'DAY',GROUP_OR_SUBPLAN =>'OLTP',NEW_COMMENT =>'....',NEW_CPU_P1=>60, NEW_ACTIVE_SESS_POOL_P1=>5,NEW_QUEUEING_P1=>600,NEW_PARALLEL_DEGREE_LIMIT_P1=>20,

NEW_MAX_EST_EXEC_TIME=>18000, NEW_UNDO_POOL=>100000 );

exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area();

select * from dba_rsrc_plan_directives;

--권한 위임 -- 유저 생성

create user oracle
identified by oracle
default tablespace users;

grant connect, resource to hr, oracle;

exec dbms_resource_manager_privs.grant_switch_consumer_Group(grantee_name=>'ORACLE',consumer_group=>'OLTP',grant_option=>false);

exec dbms_resource_manager.set_initial_consumer_group(user=>'ORACLE',consumer_group=>'OLTP');

select * from dba_users;

----------------------------------------------------------------------

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan',
   COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan',
   COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan',
   COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Online_group',
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Batch_group',
   COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_group',
   COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group',
   COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Postman_group',
   COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_group',
   COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'Bug_Online_group',
   COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'Bug_Batch_group',
   COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'Bug_Maintenance_group',
   COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100,
   PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
   GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
   CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_Postman_group',
   COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_users_group',
   COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'Mail_Maintenance_group',
   COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
   GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
   CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
   GROUP_OR_SUBPLAN => 'maildb_plan',
   COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
   GROUP_OR_SUBPLAN => 'bugdb_plan',
   COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

 

-------------------------------------------------------------------------
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/toc.htm

--Using the Database Resource Manager--
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/dbrm.htm#5080
------------------------------------------------------------------------