创建用户组
----------------------------------------------------------------------------------------
BEGIN
dbms_resource_manager.clear_pending_area();
--This procedure lets you clear pending changes for the resource manager.
dbms_resource_manager.create_pending_area();
--创建未决区域 All changes to the plan schema must be done within a pending area
dbms_resource_manager.create_consumer_group(
consumer_group => 'MAX_GROUP',
comment => 'MAX_GROUP',
cpu_mth => 'ROUND-ROBIN');--循环
--The resource allocation method for distributing CPU among sessions in the consumer group.
--The default is ROUND-ROBIN, which uses a round-robin scheduler to ensure sessions are fairly executed.
--RUN-TO-COMPLETION specifies that sessions with the largest active time are scheduled ahead of other sessions.
dbms_resource_manager.submit_pending_area();
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('DOTENG', 'MAX_GROUP', true);
--用于将用户或role置入用户组并授予管理resource manager所需的系统权限。
END;END;
------------------------------------------------------------------------------------------
注释：使用dbms_resource_manager创建用户组、计划、指令时，必须先创建未决区域。
未决区域：位于sga内的一块内存区域，被用于配置resource manager对象是存储这些对象。
创建的用户组可以查看dba_rsrc_consumer_groups 或dba_users  INITIAL_RSRC_CONSUMER_GROUP
SQL> desc dba_rsrc_consumer_groups;
Name           Type           Nullable Default Comments                                                 
-------------- -------------- -------- ------- ---------------------------------------------------------
CONSUMER_GROUP VARCHAR2(30)   Y                consumer group name                                      
CPU_METHOD     VARCHAR2(30)   Y                CPU resource allocation method for the consumer group    
COMMENTS       VARCHAR2(2000) Y                Text comment on the consumer group                       
STATUS         VARCHAR2(30)   Y                PENDING if it is part of the pending area, NULL otherwise
MANDATORY      VARCHAR2(3)    Y                Whether the consumer group is mandatory                  
---------------------------------------------------------------------------------------------
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
    dbms_resource_manager.oracle_user,
    'DOTENG',
    'MAX_GROUP'
);
dbms_resource_manager.submit_pending_area();
END;
把用户显示置入用户组 这样后面针对用户的指令才能生效
---------------------------------------------------------------------------------------------

创建计划
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( 'MAX_PLAN', 'max_plan');--创建计划
dbms_resource_manager.create_plan_directive(
    plan => 'MAX_PLAN',
    group_or_subplan => 'MAX_GROUP',
    comment => '',
    cpu_p1 => 90, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => 5,
    queueing_p1 => 10,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
    plan => 'MAX_PLAN',
    group_or_subplan => 'OTHER_GROUPS',
    comment => '',
    cpu_p1 => NULL, cpu_p2 => 100, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => 5,
    queueing_p1 => 10,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
--每个resource manager计划必须包含一条针对用户组other groups的指令
--other groups 可以理解收容了被当前活动计划排斥的用户
--active_sess_pool_p1 活动会话池方法
--dba在不限制实际登陆数的情况下能够限制一个用户组能够同时运行的语句数。
--活动会话被定义为运行查询的会话或未提交事务当中的会话 select or dml
--同时运行的语句。。。这个微妙间发生的  很难观察。。
dbms_resource_manager.create_plan_directive(
    plan => 'MAX_PLAN',
    group_or_subplan => 'SYSTEM_PLAN',
    comment => '',
    cpu_p1 => 10, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL
);
dbms_resource_manager.submit_pending_area();
--This procedure lets you submit pending changes for the resource manager.
--It clears the pending area after validating and committing the changes (if valid).
dbms_resource_manager.switch_plan( plan_name => 'MAX_PLAN', sid => 'orcl' );
END;
--------------------------------------------------------------------------------

 

更新计划-使用者组可用最大撤销表空间

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.update_plan_directive(

    plan => 'MAX_PLAN',

    group_or_subplan => 'MAX_GROUP',

    new_comment => '',

    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,

    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,

    new_parallel_degree_limit_p1 => NULL,

    new_active_sess_pool_p1 => NULL,

    new_queueing_p1 => NULL,

    new_switch_group => NULL,

    new_switch_time => NULL,

    new_switch_estimate => false,

    new_max_est_exec_time => NULL,

    new_undo_pool => 3,--指定为3KB

    new_max_idle_time => NULL,

    new_max_idle_blocker_time => NULL,

    new_switch_time_in_call => NULL

);

dbms_resource_manager.submit_pending_area();

END;

--修改使用者组可用最大撤销表空间

--指定使用者组可以生成的最大还原空间

--如果超出则报如下异常

更新计划-指定最大估计执行时间

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.update_plan_directive(

    plan => 'MAX_PLAN',

    group_or_subplan => 'MAX_GROUP',

    new_comment => '',

    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,

    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,

    new_parallel_degree_limit_p1 => NULL,

    new_active_sess_pool_p1 => NULL,

    new_queueing_p1 => NULL,

    new_switch_group => NULL,

    new_switch_time => NULL,

    new_switch_estimate => false,

    new_max_est_exec_time => 1,

    new_undo_pool => NULL,

    new_max_idle_time => NULL,

    new_max_idle_blocker_time => NULL,

    new_switch_time_in_call => NULL

);

dbms_resource_manager.submit_pending_area();

END;

--指定最大估计执行时间。如果数据库估计 SQL 会超过指定的限制, 将不执行 SQL, 并返回一个错误。

--如果超出则报如下异常

更新计划-修改最大会话数的限制

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.update_plan_directive(

    plan => 'MAX_PLAN',

    group_or_subplan => 'MAX_GROUP',

    new_comment => '',

    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,

    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,

    new_parallel_degree_limit_p1 => NULL,

    new_active_sess_pool_p1 => 2,--最大会话数

    new_queueing_p1 => 1,-- 激活队列中等待的时间

    new_switch_group => NULL,

    new_switch_time => NULL,

    new_switch_estimate => false,

    new_max_est_exec_time => NULL,

    new_undo_pool => NULL,

    new_max_idle_time => NULL,

    new_max_idle_blocker_time => NULL,

    new_switch_time_in_call => NULL

);

dbms_resource_manager.submit_pending_area();

END;

--指定对使用者组中同时处于活动状态的最大会话数的限制。其它所有会话将在激活队列中等待。

--同时打开长时间三个大作业跑跑  报错。。

更新计划-修改最大空闲时间

 

 

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'MAX_PLAN',
    group_or_subplan => 'MAX_GROUP',
    new_comment => '',
    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,
    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,
    new_parallel_degree_limit_p1 => NULL,
    new_active_sess_pool_p1 => NULL,
    new_queueing_p1 => NULL,
    new_switch_group => NULL,
    new_switch_time => NULL,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => 1,
    new_max_idle_blocker_time => 3,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;

--最大空闲时间的标准 执行前一条语句后空闲的时间。 附加的是  是否影响其他会话 产生死锁

--空闲时间：指的是服务器进程空闲时间而不是用户进程空闲时间。

--max_idle_blocker_time的先决条件是设置了max_idle_time？？？

--v$session SECONDS_IN_WAIT 如果wait_class 是idle 则是空闲的时间

 

更新计划-修改最大并行度

 

 

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.update_plan_directive(

    plan => 'MAX_PLAN',

    group_or_subplan => 'MAX_GROUP',

    new_comment => '',

    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,

    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,

    new_parallel_degree_limit_p1 => 2,

    new_active_sess_pool_p1 => NULL,

    new_queueing_p1 => NULL,

    new_switch_group => NULL,

    new_switch_time => NULL,

    new_switch_estimate => false,

    new_max_est_exec_time => NULL,

    new_undo_pool => NULL,

    new_max_idle_time => NULL,

    new_max_idle_blocker_time => NULL,

    new_switch_time_in_call => NULL

);

dbms_resource_manager.submit_pending_area();

END;

启用并行需完成如下操作：

检查并行执行服务池是否开启

SQL> show parameter parallel_max

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_max_servers                 integer     20

检查自动指定并行度参数是否开启（或使用hint指定）

SQL> show parameter parallel_auto

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_automatic_tuning            boolean     FALSE

 

alter table tab_name parallel[noparallel];为表开启并行

alter session enable parallel dml;为指定会话开启并行。

更新计划-修改使用者组切换指令

 

 

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.update_plan_directive(

    plan => 'MAX_PLAN',

    group_or_subplan => 'MAX_GROUP',

    new_comment => '',

    new_cpu_p1 => 90, new_cpu_p2 => 100, new_cpu_p3 => NULL, new_cpu_p4 => NULL,

    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,

    new_parallel_degree_limit_p1 => NULL,

    new_active_sess_pool_p1 => NULL,

    new_queueing_p1 => NULL,

    new_switch_group => 'KILL_SESSION',-- 'CANCEL_SQL',

    new_switch_time => NULL,

    new_switch_estimate => false,

    new_max_est_exec_time => NULL,

    new_undo_pool => NULL,

    new_max_idle_time => NULL,

    new_max_idle_blocker_time => NULL,

    new_switch_time_in_call => 6

);

dbms_resource_manager.submit_pending_area();

END;

--指定在采取所选操作之前会话在使用者组中可以执行的最长时间。要选择所采取的操作, 可以选择中断会话, 取消当前 SQL 操作, 或者切换到其它使用者组。如果选择切换到其它使用者组, 请使用 '调用后切换回原始组' 复选框指定是否在调用后切换回来。选择调用后切换回原始组, 对于中间层服务器使用会话池的 3 层应用程序非常有用。

-- switch_time_in_call 一个会话的最长执行时间

--通过对执行时间的限制 进一步操作是否切换或终止

超出限定时间则报错：

ORA-00040: 已超过活动时间限制 - 调用中止

ORA-06512: 在 line 7