数据库资源管理resource_managed
---为限制个人用户使用并行占用大量资源，影响程序进行资源限制
---查看当前程序用户是否都在指定的profile中
-----为了建立一个资源计划，需要创建一个待定区域。这只是一个工作区域，在将计划应用到服务器之前，可以在这里定义和验证计划。下面的示例演示了如何设置资源计划，最后给出了完整的计划定义。请记住，只有完整和有效的计划才能应用到服务器，所以不要尝试单独运行这些命令。
-----首先，我们创建一个挂起区域。
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/
-----接下来我们创建一个计划。
BEGIN
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'parrllel_plan',
comment => 'Plan for a combination of high and low priority tasks.');
END;
/
-----然后我们创建一个web和一批消费者组。
BEGIN
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'APPLYCATIONS',
comment => 'APPLYCATIONS- high priority');
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'NOPROCEDURE',
comment => 'NOPROCEDURE - low priority');
END;
/
-----然后，我们将消费者组分配到计划并指出它们的相对优先级，记住要添加OTHER_GROUPS计划指令。
----程序组APPLYCATIONS 1级cpu资源最大可以占用90%，NOPROCEDURE二级cpu资源分配在一级分配后的90中再分配90%，OTHER_GROUPS占用所有剩余资源
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
plan => 'parrllel_plan',
group_or_subplan => 'APPLYCATIONS',
comment => 'High Priority',
MGMT_P1 => 90,
MGMT_P2 => 0,
parallel_degree_limit_p1 => 80);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'parrllel_plan',
group_or_subplan => 'NOPROCEDURE',
comment => 'Low Priority',
MGMT_P1 => 0,
MGMT_P2 => 90,
parallel_degree_limit_p1 => 4,
MAX_IDLE_TIME => 3600);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'parrllel_plan',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users - level 3',
MGMT_P1 => 0,
MGMT_P2 => 0,
MGMT_P3 => 100);
END;
/
==========授予切换权限
---将用户分配到各个消费者组。会话可以在已分配给它的使用者组之间手动切换，但在下面的示例中，我们设置了默认的使用者组，并假定会话在其生命周期中将一直使用该用户组。
可以授予角色和public
------给程序用户赋予到APPLYCATIONS组中，其他用户赋予到NOPROCEDURE组中
BEGIN
-- Assign users to consumer groups
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'PUBLIC',
consumer_group => 'APPLYCATIONS',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'PUBLIC',
consumer_group => 'NOPROCEDURE',
grant_option => FALSE);
如果您授予角色切换到特定资源使用者组的权限，则任何被授予该角色并启用该角色的用户都可以将其会话切换到该使用者组。
如果您授予PUBLIC了切换到特定使用者组的权限，则任何用户都可以切换到该使用者组。
如果该GRANT_OPTION参数为TRUE，则被授予使用方组切换特权的用户还可以将该使用方组的切换特权授予其他用户。
===============方案1 set_initial_consumer_group 11Gr1已经被弃用
---进行用户分配
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('DBMT', 'APPLYCATIONS');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('HW_HOUYUHANG', 'NOPROCEDURE');
END;
/
===============方案2 SET_CONSUMER_GROUP_MAPPING 根据会话的登录和运行属性建立该会话和资源用户组的映射关系
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping_pri
-----会话属性可以是以下之一
属性 类型 描述
ORACLE_USER 登录 Oracle数据库用户名
SERVICE_NAME 登录 客户端用来建立连接的数据库服务名称
CLIENT_OS_USER 登录 正在登录的客户端的操作系统用户名
CLIENT_PROGRAM 登录 用于登录服务器的客户端程序的名称
CLIENT_MACHINE 登录 客户端与之建立连接的计算机的名称
CLIENT_ID 登录 会话的客户端标识符客户端标识符会话属性由该DBMS_SESSION.SET_IDENTIFIER过程设置。
MODULE_NAME 运行 由DBMS_APPLICATION_INFO.SET_MODULE过程或等效的OCI属性设置设置的当前正在运行的应用程序中的模块名称
MODULE_NAME_ACTION 运行 通过以下过程或其等效的OCI属性设置来设置当前模块和正在执行的操作的组合：
DBMS_APPLICATION_INFO.SET_MODULE
DBMS_APPLICATION_INFO.SET_ACTION
该属性指定为模块名称，后跟句点（。），然后是操作名（module_name.action_name）。
SERVICE_MODULE 运行 服务和模块名称的组合，格式如下：service_name.module_name
SERVICE_MODULE_ACTION 运行 服务名称，模块名称和操作名称的组合，格式如下：service_name.module_name.action_name
ORACLE_FUNCTION 运行 RMAN或数据泵操作。有效值是DATALOAD，BACKUP和COPY。这些值中的每一个都有预定义的映射。如果您的会话正在执行这些功能中的任何一个，它将自动映射到预定义的使用者组。
例如，以下PL / SQL块使用户每次登录时都SCOTT映射到DEV_GROUP使用者组：
必须在运行该SET_CONSUMER_GROUP_MAPPING过程之前创建一个挂起的区域。
begin
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM,'JDBC Thin Client','APPLYCATIONS');
end;
/
begin
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM,'%TNS V1-V3%','APPLYCATIONS');
end;
/
begin
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM,'plsqldev%','NOPROCEDURE');
end;
/
-------您可以value在SET_CONSUMER_GROUP_MAPPING过程的参数中使用通配符表示大多数属性的值。
要使用通配符指定值，请使用与SQLLIKE运算符相同的语义。具体来说，通配符使用以下语义：
% 用于多字符通配符
_ 对于单个字符通配符
逃脱通配符
仅当属性为以下之一时，才可以使用通配符：
CLIENT_OS_USER
CLIENT_PROGRAM
CLIENT_MACHINE
MODULE_NAME
MODULE_NAME_ACTION
SERVICE_MODULE
SERVICE_MODULE_ACTION
-----最后，我们验证并应用资源计划
BEGIN
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
-----要删除规则，请SET_CONSUMER_GROUP_MAPPING对所需的属性/值对运行该过程并指定NULL使用者组。
============启用资源计划
----设置当前实例使用的资源计划
----RESOURCE_MANAGER_PLAN参数用于告诉实例使用哪个资源计划。这可以在init中设置。或使用ALTER SYSTEM命令。
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'parrllel_plan';
----强制指定资源计划防止更改，在资源计划前加force
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';
----禁用资源管理计划
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
----现在我们可以看到，通过这些用户连接的会话被分配给了正确的使用者组。
SQL> SELECT username, resource_consumer_group
FROM v$session
WHERE username IN ('WEB_USER','BATCH_USER');
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
BATCH_USER BATCH_CG
WEB_USER WEB_CG
2 rows selected.
-----
select username,resource_consumer_group,program,inst_id,count(*) from gv$session group by username,resource_consumer_group,program,inst_id order by count(*) desc
****注意：我在测试过程中如果是非常繁忙的OLTP系统可能因为该资源限制出现latch free的等待事件，影响倒不大，占了一定的AAS，使用的话需要酌情考虑
=====================================
-----或者，您可以使用单个用户，并根据所完成的处理类型切换当前会话的使用者组。假设已经为用户分配了用户组的切换特权，此切换操作如下所示。
DECLARE
v_old_cg VARCHAR2(30);
BEGIN
DBMS_SESSION.switch_current_consumer_group (
new_consumer_group => 'BATCH_CG',
old_consumer_group => v_old_cg,
initial_group_on_error => FALSE);
END;
/
-----禁用和清除计划
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.delete_plan_cascade(
plan => 'parrllel_plan');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
-----将用户从资源计划组中撤销
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
REVOKEE_NAME => 'SCOTT',
CONSUMER_GROUP => 'OLTP');
END;
/
-----开启资源组
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;
System altered.
SELECT name, is_top_plan FROM v$rsrc_plan;
NAME IS_TOP_PLAN
----------------------------
MYDB_PLAN TRUE
MAILDB_PLAN FALSE
BUGDB_PLAN FALSE
----查看当前资源组和用户的分配关系
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 MARKETING NO NO
SCOTT SALES YES YES
SYSTEM SYS_GROUP NO YES
表27-8资源管理器数据字典视图
视图 描述
DBA_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
DBA视图列出了所有资源使用者组以及被授予它们的用户和角色。USER视图列出了授予用户的所有资源使用者组。
----------------------------------------------------------------------------------------------
DBA_RSRC_CONSUMER_GROUPS
列出数据库中存在的所有资源使用者组。
----------------------------------------------------------------------------------------------
DBA_RSRC_MANAGER_SYSTEM_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
DBA视图列出了已被授予Resource Manager系统特权的所有用户和角色。USER视图列出了被授予DBMS_RESOURCE_MANAGER程序包系统特权的所有用户。
----------------------------------------------------------------------------------------------
DBA_RSRC_PLAN_DIRECTIVES
列出数据库中存在的所有资源计划指令。
----------------------------------------------------------------------------------------------
DBA_RSRC_PLANS
列出数据库中存在的所有资源计划。
----------------------------------------------------------------------------------------------
DBA_RSRC_GROUP_MAPPINGS
列出所有会话属性的所有各种映射对。
----------------------------------------------------------------------------------------------
DBA_RSRC_MAPPING_PRIORITY
列出每个属性的当前映射优先级。
----------------------------------------------------------------------------------------------
DBA_HIST_RSRC_PLAN
显示有关资源计划激活的历史信息。此视图包含的AWR快照V$RSRC_PLAN_HISTORY。
----------------------------------------------------------------------------------------------
DBA_HIST_RSRC_CONSUMER_GROUP
显示有关消费者组的历史统计信息。此视图包含的AWR快照V$RSRC_CONS_GROUP_HISTORY。
----------------------------------------------------------------------------------------------
DBA_USERS
USER_USERS
DBA视图包含有关数据库所有用户的信息。它包含每个用户的初始资源使用者组。USER视图包含有关当前用户的信息。它包含当前用户的初始资源使用者组。
----------------------------------------------------------------------------------------------
V$RSRC_CONS_GROUP_HISTORY
对于视图中的每个条目V$RSRC_PLAN_HISTORY，包含计划中每个消费者组的条目，显示该消费者组的累积统计信息。
----------------------------------------------------------------------------------------------
V$RSRC_CONSUMER_GROUP
显示有关活动资源使用者组的信息。该视图可用于调整。
----------------------------------------------------------------------------------------------
V$RSRCMGRMETRIC
显示过去一分钟内每个消费者组消耗的资源的历史记录和累积的CPU等待时间（由于资源管理）。
----------------------------------------------------------------------------------------------
V$RSRCMGRMETRIC_HISTORY
以分钟为单位显示过去一小时每个消费者组的资源消耗历史记录和累积的CPU等待时间（由于资源管理）。如果启用了新的资源计划，则将清除历史记录。
----------------------------------------------------------------------------------------------
V$RSRC_PLAN
显示所有当前活动的资源计划的名称。
----------------------------------------------------------------------------------------------
V$RSRC_PLAN_HISTORY
显示何时在实例上启用或禁用Resource Manager计划。它可以帮助您了解随着时间的推移如何在消费者组之间共享资源。
----------------------------------------------------------------------------------------------
V$RSRC_SESSION_INFO
显示每个会话的资源管理器统计信息。显示会话如何受到资源管理器的影响。可用于调音。
----------------------------------------------------------------------------------------------
V$SESSION
列出每个当前会话的会话信息。具体来说，列出每个当前会话的资源使用者组的名称。
---------------------------------------
----关于消费者组的资源限制更改
CPU_P1已经主键被弃用建议使用MGMT_P1
PROCEDURE CREATE_PLAN_DIRECTIVE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN VARCHAR2 IN
GROUP_OR_SUBPLAN VARCHAR2 IN
COMMENT VARCHAR2 IN DEFAULT
CPU_P1 NUMBER IN DEFAULT
CPU_P2 NUMBER IN DEFAULT
CPU_P3 NUMBER IN DEFAULT
CPU_P4 NUMBER IN DEFAULT
CPU_P5 NUMBER IN DEFAULT
CPU_P6 NUMBER IN DEFAULT
CPU_P7 NUMBER IN DEFAULT
CPU_P8 NUMBER IN DEFAULT
ACTIVE_SESS_POOL_P1 NUMBER IN DEFAULT
QUEUEING_P1 NUMBER IN DEFAULT
PARALLEL_DEGREE_LIMIT_P1 NUMBER IN DEFAULT
SWITCH_GROUP VARCHAR2 IN DEFAULT
SWITCH_TIME NUMBER IN DEFAULT
SWITCH_ESTIMATE BOOLEAN IN DEFAULT
MAX_EST_EXEC_TIME NUMBER IN DEFAULT
UNDO_POOL NUMBER IN DEFAULT
MAX_IDLE_TIME NUMBER IN DEFAULT
MAX_IDLE_BLOCKER_TIME NUMBER IN DEFAULT
SWITCH_TIME_IN_CALL NUMBER IN DEFAULT
MGMT_P1 NUMBER IN DEFAULT
MGMT_P2 NUMBER IN DEFAULT
MGMT_P3 NUMBER IN DEFAULT
MGMT_P4 NUMBER IN DEFAULT
MGMT_P5 NUMBER IN DEFAULT
MGMT_P6 NUMBER IN DEFAULT
MGMT_P7 NUMBER IN DEFAULT
MGMT_P8 NUMBER IN DEFAULT
SWITCH_IO_MEGABYTES NUMBER IN DEFAULT
SWITCH_IO_REQS NUMBER IN DEFAULT
SWITCH_FOR_CALL BOOLEAN IN DEFAULT
MAX_UTILIZATION_LIMIT NUMBER IN DEFAULT
PARALLEL_TARGET_PERCENTAGE NUMBER IN DEFAULT
PARALLEL_QUEUE_TIMEOUT NUMBER IN DEFAULT
PARALLEL_SERVER_LIMIT NUMBER IN DEFAULT
UTILIZATION_LIMIT NUMBER IN DEFAULT
SWITCH_IO_LOGICAL NUMBER IN DEFAULT
SWITCH_ELAPSED_TIME NUMBER IN DEFAULT
SHARES NUMBER IN DEFAULT
PARALLEL_STMT_CRITICAL VARCHAR2 IN DEFAULT
SESSION_PGA_LIMIT NUMBER IN DEFAULT
----如此创建将无论数据库负载如何，Oracle数据库的系统工作负载都不会超过90%的CPU，只留下10%的CPU给共享该服务器的其他应用程序。
CopyBEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'MAXCAP_PLAN',
COMMENT => 'Limit overall database CPU');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'MAXCAP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'This group is mandatory',
UTILIZATION_LIMIT => 90);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/