资源规划组的使用。

资源规划组的使用。
资源规划组可以给ORACLE提供更多的控制资源管理决策的权利。
可以解决系统开销过大，效率低下，不适当的资源分配等与资源相关的问题。
资源规划组的管理主要是由两个包来完成的。Dbms_resource_manager和dbms_resource_manager_privs。
为scott用户授权：
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
(GRANTEE_NAME => 'scott', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', -
ADMIN_OPTION => FALSE);
可用过程REVOKE_SYSTEM_PRVILEGE来收回权限。
权限ADMINISTER_RESOURCE_MANAGER的授予和回收只能通过过程来完成不能通过grant和revoke这两个命令来完成。

来看一个例子。创建一个简单的资源计划。
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80,
CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
END;
/

上面的过程创建了如下的资源规划：
Consumer Group Level 1 Level 2 Level 3
SYS_GROUP 100% - -
mygroup1 - 80% -
mygroup2 - 20% -
OTHER_GROUPS - - 100%

下面再来一个复杂的资源计划：包括下面的步骤：
" Using the Pending Area for Creating Plan Schemas
" Creating Resource Plans
" Creating Resource Consumer Groups
" Specifying Resource Plan Directives
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_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;

上面过程
Group CPU Resource Allocation % Active Session Pool Parameters Automatic Switching Parameters Maximum Estimated Execution Time Undo Pool
oltp Level 1: 80% Switch to group: batch
Switch time: 3
Use estimate: TRUE -- Size: 200K

batch Level 2: 100% Pool size: 5
Timeout: 600 -- Time: 3600 --
OTHER_GROUPS Level 3: 100% -- -- -- --

资源规划 create_simple_plan