Oracle Resource Management 资源管理笔记

最新推荐文章于 2022-05-04 00:47:12 发布

congjiao5415

最新推荐文章于 2022-05-04 00:47:12 发布

阅读量151

点赞数

Oracle Resource Management是用来控制资源管理，特别是针对资源不足的时候。

Resource Mananger的元素：Resource consumer group, Resource plan, Resource allocation method, Resource plan directive.

1 Resource Plan: 指定一些consumer group，并在这些consumer group中分配资源。

2 Resource Consumer Group是用户或会话的group。

3 Resource plan directive是指资源如何在group中分配。

4 Resource allocation method是指CPU, Active Session Pool with queuing, Degree with parallesism limit, Automatic Consumer Group switch, Cancelling or terminating sessions, execution time limit, undo pool, idle time limit.

CPU method: 有8个levels的控制。上一个level没有用的cpu资源自动转到下一个level。

Active Session Pool with queuing - 并发的会话数

Degree with parallesism limit - 并行的粒度

Automatic Consumer Group switch - 自动group切换

Cancelling or terminating sessions - 终止长时间的会话

execution time limit - 最大的执行时间

undo pool - 整个group的undo总数

idle time limit - 空闲的时间

5 创建相关的组

BEGIN
dbms_resource_manager.create_pending_area;
dbms_resource_manager.DELETE_CONSUMER_GROUP('oltp');
dbms_resource_manager.DELETE_CONSUMER_GROUP('batch');
dbms_resource_manager.validate_pending_area;
dbms_resource_manager.submit_pending_area;
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
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.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

6 创建resource plan

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_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
SWITCH_GROUP => 'batch', SWITCH_TIME => 1,SWITCH_ESTIMATE => TRUE,
UNDO_POOL => 200, MAX_EST_EXEC_TIME => 0);
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 => 10);
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;
/

7 将resource group分配给user
create user jacky identified by jacky default tablespace users temporary tablespace temp;
grant connect,resource to jacky;

首先用将group的权限赋给user，然后设置user默认的group

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'oltp',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'batch',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'jacky', 'oltp');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

在系统中启用plan
alter system set RESOURCE_MANAGER_PLAN = erp_plan scope=both;

alter system set resource_limit = true scope=both;

以用户登录，查看用户的group
select sid, RESOURCE_CONSUMER_GROUP, username from v$session where username = 'JACKY';

8 删除plan

在系统中禁用plan
alter system set RESOURCE_MANAGER_PLAN = '' scope=both;

alter system set resource_limit = false scope=both;

已用plan的用户登出

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE(PLAN => 'erp_plan');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

9 测试plan的效果

9.1 测试最长执行时间超时
创建group
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
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.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

创建plan，设置最长执行时间是9
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_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
SWITCH_GROUP => 'batch', SWITCH_TIME => 1,SWITCH_ESTIMATE => TRUE,
UNDO_POOL => 200, MAX_EST_EXEC_TIME => 9);
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 => 10);
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;
/

赋予权限
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'oltp',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'batch',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'jacky', 'oltp');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

在系统中启用
alter system set RESOURCE_MANAGER_PLAN = erp_plan scope=both;

alter system set resource_limit = true scope=both;

用户登录并运行下列语句，出现超时错误。

select * from (select * from test order by object_id desc ) where rownum < 10;

SQL> select * from (select * from test order by object_id desc ) where rownum < 10;
select * from (select * from test order by object_id desc ) where rownum < 10
*
ERROR at line 1:
ORA-07455: estimated execution time (66 secs), exceeds limit (9 secs)

9.2 测试自动group切换
在系统中禁用plan
alter system set RESOURCE_MANAGER_PLAN = '' scope=both;

alter system set resource_limit = false scope=both;

已用plan的用户登出

创建group
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
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.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

创建plan，设置最长执行时间是100,自动切换时间为1
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_PLAN_DIRECTIVE(PLAN => 'erp_plan',
GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
SWITCH_GROUP => 'batch', SWITCH_TIME => 1,SWITCH_ESTIMATE => TRUE,
UNDO_POOL => 200, MAX_EST_EXEC_TIME => 100);
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 => 100);
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;
/

赋予权限
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'oltp',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'batch',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
GRANTEE_NAME =>'JACKY',
CONSUMER_GROUP=>'batch',
GRANT_OPTION=>FALSE);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'jacky', 'oltp');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

在系统中启用
alter system set RESOURCE_MANAGER_PLAN = erp_plan scope=both;

alter system set resource_limit = true scope=both;

用户登录并运行下列语句，并查看用户当前的group是batch

SQL> select sid, RESOURCE_CONSUMER_GROUP, username from v$session where username = 'JACKY';

SID RESOURCE_CONSUMER_GROUP USERNAME
---------- -------------------------------- ------------------------------
1105 BATCH JACKY

参考：http://tech.it168.com/a2010/0409/871/000000871380.shtml

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-672087/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-672087/

congjiao5415

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle Resource Management 资源管理笔记

Oracle Resource Management是用来控制资源管理，特别是针对资源不足的时候。Resource Mananger的元素：Resource consumer group, Resource plan, Re...
复制链接

扫一扫