两个DBMS包
DBMS_RESOURCE_MANAGER 管理资源计划
DBMS_RESOURCE_MANAGER_PRIVS 给用户授予管理资源的权限
plan schema:
包括一个顶计划和下面的子计划和消耗组
自计划的配额也是以100%为基础来分配的
创建简单的资源计划:
DECLARE
old_group varchar2(30);
BEGIN
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'sys', 'backup_cg');
创建简单的资源计划:
CREATE_SIMPLE_PLAN里最多可以建8个组
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80,
CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
END;
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80,
CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
END;
这种方式不需要创建pending area.
用pending area创建资源计划
在创建资源计划前,必须创建pending area,创建资源计划后,必须验证并提交pending area
创建pending_area
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
验证
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
提交(提交后会自动释放pending area)
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
手动释放
EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
资源计划的参数
PLAN 资源计划名称
COMMENT 描述
CPU_MTH CPU分配方式,包括EMPHASIS(按百分比)和RATIO(按比例,几比几比几)。其中EMPHASIS是默认的
ACTIVE_SESS_POOL_MTH 最多可以有多少个活动session,默认为ACTIVE_SESS_POOL_ABSOLUTE
PARALLEL_DEGREE_LIMIT_MTH 并行度,默认 PARALLEL_DEGREE_LIMIT_ABSOLUTE
QUEUEING_MTH 队列资源分配方式,决定哪些session先执行。默认FIFO_TIMEOUT
创建资源计划
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'great_bread', COMMENT => 'great plan');
修改资源计划
EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'great_bread', NEW_COMMENT => 'great plan for great bread');
删除资源计划
EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread'); --只删除计划,不删除对应的资源组
级联删除用DELETE_PLAN_CASCADE
Ratio策略
决定在一个level里各资源组可以得到的CPU比例。
下面给Gold service,Silver service,Bonze service,Lowest service设置了10比5比2比1的比例.
如果当前只有gold service 和 sliver service存在,则它俩按10比5来分配。
DBMS_RESOURCE_MANAGER.CREATE_PLAN
(PLAN => 'service_level_plan',
CPU_MTH -> 'RATIO',
COMMENT => 'service level plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'GOLD_CG',
COMMENT => 'Gold service level customers',
CPU_P1 => 10);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'SILVER_CG',
COMMENT => 'Silver service level customers',
CPU_P1 => 5);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'BRONZE_CG',
COMMENT => 'Bonze service level customers',
CPU_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'Lowest priority sessions',
CPU_P1 => 1);
(PLAN => 'service_level_plan',
CPU_MTH -> 'RATIO',
COMMENT => 'service level plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'GOLD_CG',
COMMENT => 'Gold service level customers',
CPU_P1 => 10);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'SILVER_CG',
COMMENT => 'Silver service level customers',
CPU_P1 => 5);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'BRONZE_CG',
COMMENT => 'Bonze service level customers',
CPU_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'Lowest priority sessions',
CPU_P1 => 1);
创建资源消耗组
参数:
CONSUMER_GROUP 名称
COMMENT 描述
CPU_MTH CPU分配方式,默认为ROUND_ROBIN,使用ROUND_ROBIN cheduler来session正确执行。还有RUN_TO_COMPLETION选项
特殊资源消耗组(无法修改和删除)
DEFAULT_CONSUMER_GROUP 所有未明确指定消耗组的用户和session的默认消耗组。
OTHER_GROUPS 不能被明确指定给用户,应用于所有不在当前plan schema中的消耗组的用户。
同时,还有ORACLE提供的SYSTEM_PLAN 资源计划下的SYS_GROUP和LOW_GROUP两个消耗组
创建消耗组
创建之前必须创建pending area
EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'sales', COMMENT => 'retail and wholesale sales');
修改消耗组
UPDATE_CONSUMER_GROUP
删除消耗组
DELETE_CONSUMER_GROUP
创建资源计划指令 resource plan directives
资源计划指令将消耗组分配到资源计划中,并为各种分配方式指定参数
参数:
PLAN 计划名称
GROUP_OR_SUBPLAN 消耗组活子计划名称
COMMENT 备注
CPU_P1到CPU_P8 对于EMPHASIS方式,指定当前级别的百分比,对于RATIO,指定分配比例,RATIO只对CPU_P1有效,对后面的级别不适用。
ACTIVE_SESS_POOL_P1 最大活动session数,默认UNLIMITED
QUEUEING_P1 队列中的超时时间,默认UNLIMITED
PARALLEL_DEGREE_LIMIT_P 并行度,默认UNLIMITED
SWITCH_GROUP
SWITCH_TIME
SWITCH_ESTIMATE
MAX_EST_EXEC_TIME
UNDO_POOL
MAX_IDLE_TIME session的最大空闲时间
MAX_IDLE_BLOCKER_TIME 被阻塞的session的最大空闲时间
SWITCH_TIME_IN_CALL
创建resource plan directive
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'great_bread',
GROUP_OR_SUBPLAN => 'sales',
COMMENT => 'sales group',
CPU_P1 => 60,
PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'great_bread',
GROUP_OR_SUBPLAN => 'market',
GROUP_OR_SUBPLAN => 'market',
COMMENT => 'marketing group',
CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'great_bread',
GROUP_OR_SUBPLAN => 'develop',
GROUP_OR_SUBPLAN => 'develop',
COMMENT => 'development group',
CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'great_bread',
GROUP_OR_SUBPLAN =>'OTHER_GROUPS',
GROUP_OR_SUBPLAN =>'OTHER_GROUPS',
COMMENT => 'this one is required',
CPU_P1 => 0,
CPU_P2 => 100);
END;
END;
修改resource plan directive
EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
PLAN => 'great_bread',
GROUP_OR_SUBPLAN => 'develop',
NEW_CPU_P1 => 15);
删除
DELETE_PLAN_DIRECTIVE
管理消耗组
用到的存储过程
SET_CONSUMER_GROUP_MAPPING
SET_CONSUMER_GROUP_MAPPING_PRI
改变正在执行的session的消耗组
可以不用踢出session的情况下改变CPU等配额。
参数是session的 sid,serial#和消耗组名称
EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (
'17',
'12345',
'high_priorty');
改变用户的消耗组
EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (
'scott',
'low_group');
用DBMS_SESSION包切换消耗组
如果被授予过switch权限,用户可以改变自己的消耗组
参数:
NEW_CONSUMER_GROUP 新组
OLD_CONSUMER_GROUP 老组(这个是OUT型的参数)
INITIAL_GROUP_ON_ERROR 切换发生错误的时候的行为,TRUE表示切换错误的时候,用户切换到初始消耗组,FALSE表示发生错误就报错
例子:
SET serveroutput onDECLARE
old_group varchar2(30);
BEGIN
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
END;
允许用户将自己切换到指定消耗组
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
'scott',
'bug_batch_group(组名)', TRUE(SCOTT也可以授权别人切换到该组));
'bug_batch_group(组名)', TRUE(SCOTT也可以授权别人切换到该组));
收回切换权限
EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
'scott',
'bug_batch_group');
'bug_batch_group');
将session自动映射到消耗组
通过session的属性和消耗组自动连接,分为login attributes和runtime attributes两种属性
使用的存储过程:
SET_CONSUMER_GROUP_MAPPING
SET_CONSUMER_GROUP_MAPPING_PRI
创建映射
参数:
ATTRIBUTE
VALUE
CONSUMER_GROUP
其中ATTRIBUTE支持的属性:
Login属性:
ORACLE_USERSERVICE_NAMECLIENT_OS_USERCLIENT_PROGRAMCLIENT_MACHINE
Runtime属性:
MODULE_NAMEMODULE_NAME_ACTIONSERVICE_MODULESERVICE_MODULE_ACTION
例子:
将
sys
映射到
backup_cg
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'sys', 'backup_cg');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
设置session属性作为映射条件时的优先级
其中必须制定EXPLICIT ,并且EXPLICIT 必须为1
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
EXPLICIT => 1,
SERVICE_MODULE_ACTION => 2,
SERVICE_MODULE => 3,
MODULE_NAME_ACTION => 4,
MODULE_NAME => 5,
SERVICE_NAME => 6,
ORACLE_USER => 7,
CLIENT_PROGRAM => 8,
CLIENT_OS_USER => 9,
CLIENT_MACHINE => 10);
END;
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
EXPLICIT => 1,
SERVICE_MODULE_ACTION => 2,
SERVICE_MODULE => 3,
MODULE_NAME_ACTION => 4,
MODULE_NAME => 5,
SERVICE_NAME => 6,
ORACLE_USER => 7,
CLIENT_PROGRAM => 8,
CLIENT_OS_USER => 9,
CLIENT_MACHINE => 10);
END;
使资源计划生效
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';
加上FORCE:可以禁止scheduler自动切换资源计划
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';
下面是官方文档给出的一个练习例子
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
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_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 => 'Online_group', COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group', COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group', COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group', COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group', COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_group', COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group', COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group', COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group', COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group', COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_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 => '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 => '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_Maint_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 => '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 => '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_Maint_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);
GROUP_OR_SUBPLAN => '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 => '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_Maint_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 => '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 => '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_Maint_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;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
例子2
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan', COMMENT => 'Resource plan/method for ERP Database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', COMMENT => 'Resource consumer group/method for OLTP jobs');
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan', COMMENT => 'Resource plan/method for ERP Database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', COMMENT => 'Resource consumer group/method for OLTP jobs');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', COMMENT => 'Resource consumer group/method for BATCH jobs');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
SWITCH_GROUP => 'batch', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE,
UNDO_POOL => 200);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100,
ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600,
MAX_EST_EXEC_TIME => 3600);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
SWITCH_GROUP => 'batch', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE,
UNDO_POOL => 200);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100,
ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600,
MAX_EST_EXEC_TIME => 3600);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
监控、调整resource manager
查看用户消耗组权限
下例中,SCOTT可以对应MARKET和SALES两个组,他可以讲别的用户分派到SALES组,MARKET和SALES两个都不是SCOTT的初始组
SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
GRANTEE GRANTED_GROUP GRANT_OPTION INITIAL_GROUP
------------------------------ ------------------------------ ------------ -------------
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
PUBLIC LOW_GROUP NO NO
SCOTT MARKET NO NO
SCOTT SALES YES NO
SYSTEM SYS_GROUP NO YES
查看数据库中定义的资源计划
SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS;
PLAN COMMENTS STATUS
----------- ------------------------------------------------------- ------
SYSTEM_PLAN Plan to give system sessions priority ACTIVE
BUGDB_PLAN Resource plan/method for bug users sessions ACTIVE
MAILDB_PLAN Resource plan/method for mail users sessions ACTIVE
MYDB_PLAN Resource plan/method for bug and mail users sessions ACTIVE
GREAT_BREAD Great plan for great bread ACTIVE
ERP_PLAN Resource plan/method for ERP Database ACTIVE
查看活动的session的消耗组
SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
----- ------- ------------------------ --------------------------------
.
.
.
11 136 SYS SYS_GROUP
13 16570 SCOTT SALES
查看当前活动的计划
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;
System altered.
SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;
NAME IS_TO
-------------------------------------
MYDB_PLAN TRUE
MAILDB_PLAN FALSE
BUGDB_PLAN FALSE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1076281/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26239116/viewspace-1076281/