图片很清楚的讲明了 ORM 的关系结构
Resource consumer group (资源用户组)
资源管理器将资源统一分配给资源用户组，而不是某一个具体的会话，当会话建立时会根据自身属性映射到不同的用户组，并从中申请系统资源
Resource plan (资源计划)
一个数据库可以有多个Resource Plan,但是同一时刻只能有一个Plan状态为Active（由初始化参数 resource_manager_plan 决定），关联多个 Resource Plan directive
Resource plan directive (资源计划指令)
创建一个计划指令后就决定了系统资源如何分配给一个资源用户组
创建一个资源计划
1.处理pending area
所有关于 Resource Manager 组件创建和更改都必须在 Pending Area 中完成，文档说可以理解为一个“草稿区”
SQL> exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA
PL/SQL procedure successfully completed.
2.创建资源计划
DBMS_RESOURCE_MANAGER.CREATE_PLAN
(
plan IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT NULL, -- deprecated
active_sess_pool_mth IN VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth IN VARCHAR2 DEFAULT
'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',
mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS'
);
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => 'main_plan',comment => 'main database plan',sub_plan => FALSE);
END;
/
可以通过 DBA_RSRC_PLANS 进行查看
3.创建资源用户组
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
consumer_group IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT NULL,
mgmt_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN');
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'kaifa',comment => 'kaifa',mgmt_mth => 'ROUND-ROBIN');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'yingyong',comment => 'yingyong',mgmt_mth => 'ROUND-ROBIN');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'lingdao',comment => 'lingdao',mgmt_mth => 'ROUND-ROBIN');
END;
/
我们为开发与应用和领导,各自创建了资源用户组,可以通过 DBA_RSRC_CONSUMER_GROUPS 进行查看
ROUND-ROBIN --该用户组内各会话的CPU资源采取轮询的分配方法
4.映射资源用户组
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute IN VARCHAR2,
value IN VARCHAR2,
consumer_group IN VARCHAR2 DEFAULT NULL);
上图为 attribute 的内容
create user yanfa1 identified by oracle;
create user yanfa2 identified by oracle;
create user ying1 identified by oracle;
create user ying2 identified by oracle;
create user lingdao identified by oracle;
grant connect,resource,dba to yanfa1;
grant connect,resource,dba to yanfa2;
grant connect,resource,dba to ying1;
grant connect,resource,dba to ying2;
grant connect,resource,dba to lingdao;
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'yanfa1',consumer_group => 'kaifa');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'yanfa2',consumer_group => 'kaifa');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'ying1',consumer_group => 'yingyong');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'ying2',consumer_group => 'yingyong');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'CLIENT_MACHINE',value => 'Lenovo-PC',consumer_group => 'lingdao');
END;
/
我们为2个研发用户,2个应用用户,一个领导用户 与资源用户组做了关联.其中领导比较特殊,领导采用机器的方式进行关联
可以通过 DBA_RSRC_GROUP_MAPPINGS 查看映射结果
5.创建资源计划指令
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
comment IN VARCHAR2,
cpu_p1 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p2 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p3 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p4 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p5 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p6 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p7 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p8 IN NUMBER DEFAULT NULL, -- deprecated
active_sess_pool_p1 IN NUMBER DEFAULT NULL,
queueing_p1 IN NUMBER DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
switch_group IN VARCHAR2 DEFAULT NULL,
switch_time IN NUMBER DEFAULT NULL,
switch_estimate IN BOOLEAN DEFAULT FALSE,
max_est_exec_time IN NUMBER DEFAULT NULL,
undo_pool IN NUMBER DEFAULT NULL,
max_idle_time IN NUMBER DEFAULT NULL,
max_idle_blocker_time IN NUMBER DEFAULT NULL,
switch_time_in_call IN NUMBER DEFAULT NULL, -- deprecated
mgmt_p1 IN NUMBER DEFAULT NULL,
mgmt_p2 IN NUMBER DEFAULT NULL,
mgmt_p3 IN NUMBER DEFAULT NULL,
mgmt_p4 IN NUMBER DEFAULT NULL,
mgmt_p5 IN NUMBER DEFAULT NULL,
mgmt_p6 IN NUMBER DEFAULT NULL,
mgmt_p7 IN NUMBER DEFAULT NULL,
mgmt_p8 IN NUMBER DEFAULT NULL,
switch_io_megabytes IN NUMBER DEFAULT NULL,
switch_io_reqs IN NUMBER DEFAULT NULL,
switch_for_call IN BOOLEAN DEFAULT NULL);
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'kaifa', --可以指定用户组，也可以指定Sub Plan和上一个参数构成了 Plan Diretive 的唯一属性
comment => 'Directives for yanfa',
active_sess_pool_p1 => 1, --最大活跃会话数，达到该限制后其他会话进入 Inactive session queue
queueing_p1 => 1, --在 Inactive session queue 的等待超时时间
parallel_degree_limit_p1 => 2, --最大并行度
switch_group => 'KILL_SESSION', --在满足指定条件后，对会话所采取的操作
switch_time => 60, --执行时间限制，会话操作执行时间超过该值后，即被采取上一步的相应操作（second）
switch_estimate => FALSE, --如果设置为 TRUE,在操作执行前Oracle先估算执行时间以决定是否对其切换用户组
max_est_exec_time => 0, --最大估算执行时间，优化器对操作时间进行估算，如果超过该值则直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --会话最大空闲时间
max_idle_blocker_time => 3, --Blocker会话的最大空闲时间
mgmt_p1 => 10, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/
由于研发的帐号比较严格,只允许一个帐号在线,只允许 2 个并行, 如果一个查询超过了 60 秒就会被kill
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'yingyong', --可以指定用户组，也可以指定Sub Plan，和上一个参数构成了 Plan Diretive 的唯一属性
comment => 'Directives for yingyong',
active_sess_pool_p1 => 2, --最大活跃会话数，达到该限制后其他会话进入 Inactive session queue
queueing_p1 => 1, --在 Inactive session queue 的等待超时时间
parallel_degree_limit_p1 => 2, --最大并行度
switch_group => 'KILL_SESSION', --在满足指定条件后，对会话所采取的操作
switch_time => 0, --执行时间限制，会话操作执行时间超过该值后，即被采取上一步的相应操作（second）
switch_estimate => FALSE, --如果设置为 TRUE,在操作执行前Oracle先估算执行时间以决定是否对其切换用户组
max_est_exec_time => 0, --最大估算执行时间，优化器对操作时间进行估算，如果超过该值则直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --会话最大空闲时间
max_idle_blocker_time => 3, --Blocker会话的最大空闲时间
mgmt_p1 => 10, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/
应用的帐号权限也不高,但可以两个用户同时在线.
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'lingdao', --可以指定用户组，也可以指定Sub Plan，和上一个参数构成了 Plan Diretive 的唯一属性
comment => 'Directives for lingdao',
active_sess_pool_p1 => 1, --最大活跃会话数，达到该限制后其他会话进入 Inactive session queue
queueing_p1 => 1 , --在 Inactive session queue 的等待超时时间
parallel_degree_limit_p1 => 200, --最大并行度
switch_group => 'KILL_SESSION', --在满足指定条件后，对会话所采取的操作
switch_time => 0, --执行时间限制，会话操作执行时间超过该值后，即被采取上一步的相应操作（second）
switch_estimate => FALSE, --如果设置为 TRUE,在操作执行前Oracle先估算执行时间以决定是否对其切换用户组
max_est_exec_time => 0, --最大估算执行时间，优化器对操作时间进行估算，如果超过该值则直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --会话最大空闲时间
max_idle_blocker_time => 3, --Blocker会话的最大空闲时间
mgmt_p1 => 0, --CPU使用限制（Level 1）
mgmt_p2 => 50); --CPU使用限制（Level 2）
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'OTHER_GROUPS', --可以指定用户组，也可以指定Sub Plan，和上一个参数构成了 Plan Diretive 的唯一属性
comment => 'Directives for OTHER_GROUPS',
mgmt_p1 => 20, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/
OTHER_GROUPS 建议不要进行限制,否则库会启不来.
领导还是比较牛的,可以给 200个并行, cpu 只可以使用 (100-40) 的一半也就是 30%这就很牛了, 大家看到 oracle 这个实例一共可使用 os 的 10 + 10 + 20+ 30 共70% cpu,留下的 30% cpu 留下给其它实例与os. OTHER_GROUPS 就是留下其它用户,比如系统system,sys来使用的资源.
7.验证并提交
SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()
PL/SQL procedure successfully completed.
SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()
PL/SQL procedure successfully completed.
我们看到验证成功,也提交成功
8.修改参数进行测试
SQL> alter system set resource_manager_plan='main_plan' scope=both;
System altered.
重启后进行测试
7.1 测试开发用户只有一人active
新建立两session
同时运行select count(*) from system.wh left join system.wh1 on wh.object_id=wh1.object_id; 大约 1亿条数据
第两个session 报
ERROR at line 1:
ORA-07454: queue timeout, 1 second(s), exceeded
后面依次进行测试即可
附（动态性能视图及数据字典）
DBA_RSRC_CATEGORIES
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_GROUP_MAPPINGS
DBA_RSRC_IO_CALIBRATE
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_RSRC_MAPPING_PRIORITY
DBA_RSRC_PLAN_DIRECTIVES
DBA_RSRC_PLANS
V$RSRC_CONS_GROUP_HISTORY
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY
V$RSRC_SESSION_INFO
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY
