Oracle资源管理——对用户、程序、服务自定义资源分配

资源计划resource_manager_plan参数

SQL> show parameter resource

NAME     TYPE VALUE

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

resource_limit     boolean TRUE

resource_manager_cpu_allocation      integer 1

resource_manager_plan     string SCHEDULER[0x3006]:DEFAULT_MAIN

TENANCE_PLAN

#设置resource_manager_plan为DEFAULT_PLAN

SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN';

System altered.

SQL> show parameter resource

NAME     TYPE VALUE

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

resource_limit     boolean TRUE

resource_manager_cpu_allocation      integer 1

resource_manager_plan     string DEFAULT_PLAN

如果未设置resource_manager_plan的值，则不在实例中执行资源管理。

新建资源计划

可以使用PL/SQL包“DBMS_RESOURCE_MANAGER”的CREATE_PLAN、UPDATE_PLAN和DELETE_PLAN来创建、更新和删除资源计划。

#为资源管理器计划创建等待区（必须创建）

SQL>  exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

PL/SQL procedure successfully completed.

#新建资源计划,名称DEVELOPERS

SQL> execute DBMS_RESOURCE_MANAGER.CREATE_PLAN -

> (Plan => 'DEVELOPERS', -

> Comment => 'Developers, in Development database');

PL/SQL procedure successfully completed.

创建使用者组

#新建两个使用者组：ONLINE_DEVELOPERS和BATCH_DEVELOPERS

新建使用者组ONLINE_DEVELOPERS

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -

> (Consumer_Group => 'ONLINE_DEVELOPERS', -

> Comment => 'Online developers');

PL/SQL procedure successfully completed.

新建使用者组BATCH_DEVELOPERS

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -

> (Consumer_Group => 'BATCH_DEVELOPERS', -

> Comment => 'Batch developers');

PL/SQL procedure successfully completed.

资源分配方法

创建两个计划指导，以便将75%的CPU资源分配给ONLINE_DEVELOPERS组，25%的CPU资源分配给BATCH_DEVELOPERS组。
另外，将ONLINE_DEVELOPERS组并行性限制为12，将BATCH_DEVELOPERS组的并行性限制为6。

ONLINE_DEVELOPERS资源分配

SQL> exec dbms_resource_manager.create_plan_directive -

> (Plan => 'DEVELOPERS', -

> Group_or_subplan => 'ONLINE_DEVELOPERS', -

> Comment => 'Online developers', -

> Cpu_p1 => 75, -

> Cpu_p2 => 0, -

> Parallel_degree_limit_p1 => 12);

PL/SQL procedure successfully completed.

BATCH_DEVELOPERS资源分配

SQL> exec dbms_resource_manager.create_plan_directive -

> (Plan => 'DEVELOPERS', -

> Group_or_subplan => 'BATCH_DEVELOPERS', -

> Comment => 'Batch developers', -

> Cpu_p1 => 25, -

> Cpu_p2 => 0, -

> Parallel_degree_limit_p1 => 6);

PL/SQL procedure successfully completed.

OTHER_GROUPS(计划中必须包含此组)

SQL> exec dbms_resource_manager.create_plan_directive -

> (Plan => 'DEVELOPERS', -

> Group_or_subplan => 'OTHER_GROUPS', -

> Comment => 'Everyone else', -

> Cpu_p1 => 0, -

> Cpu_p2 => 100, -

> Parallel_degree_limit_p1 => 6);

PL/SQL procedure successfully completed.

验证是否有效

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.

清除更改

exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

分配使用者组

将用户分配到使用者组

赋予SCOTT切换到ONLINE_DEVELOPERS使用者组权限

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group -

> (grantee_name=>'SCOTT', -

> consumer_group=>'ONLINE_DEVELOPERS', -

> grant_option=>true);

PL/SQL procedure successfully completed.

将SCOTT分配到ONLINE_DEVELOPERS使用者组

SQL> exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP -

> ('scott','ONLINE_DEVELOPERS');

PL/SQL procedure successfully completed.

赋予用户administer_resource_manager权限

SQL> begin

  2  dbms_resource_manager_privs.grant_system_privilege(

  3  grantee_name=>'SCOTT',

  4  privilege_name=>'administer_resource_manager',

  5  admin_option=>true);

  6  end;

  7  /

PL/SQL procedure successfully completed.

激活资源计划

SQL> alter system set resource_manager_plan = 'DEVELOPERS';

System altered.

Resource Manager视图

DBA_RSRC_PLANS                  --> 资源计划和每个计划的状态

DBA_RSRC_PLAN_DIRECTIVES        --> 资源计划指导

DBA_RSRC_CONSUMER_GROUPS        --> 资源计划使用者组

DBA_RSRC_CONSUMER_GROUP_PRIVS   --> 使用者组用户和角色分配

DBA_RSRC_GROUP_MAPPINGS         --> 会话属性与使用者组之间的使用者组映射

DBA_RSRC_MAPPING_PRIORITY       --> 资源映射优先级

DBA_USERS                       --> INITIAL_RSRC_CONSUMER_GROUP列包含用户的初始化使用者

DBA_RSRC_MANAGER_SYSTEM_PRIVS   --> 被授予

DBMS_RESOURCE_MANAGER包上的权限的用户

查看每个计划的状态和特点：

SQL> set linesize 120

SQL> col plan for a30

SQL> col status for a30

SQL> select plan, status, num_plan_directives, mandatory from dba_rsrc_plans;

PLAN       STATUS      NUM_PLAN_DIRECTIVES MAN

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

DEFAULT_PLAN 4 YES

INTERNAL_QUIESCE 2 YES

DEVELOPERS 3 NO

INTERNAL_PLAN 1 YES

APPQOS_PLAN 7 YES

DEFAULT_MAINTENANCE_PLAN 4 YES

ETL_CRITICAL_PLAN 8 NO

MIXED_WORKLOAD_PLAN 6 NO

ORA$AUTOTASK_SUB_PLAN 3 YES

ORA$AUTOTASK_HIGH_SUB_PLAN 4 YES

DSS_PLAN 8 NO

11 rows selected.

status列值为PENDING说明计划尚未经过验证并成功提交；

mandatory列值为YES说明无法删除计划。

监控Resource Manager

下面3个动态性能视图显示Resource Manager的统计信息：

v$rsrc_consumer_group   -> 按使用者组列出的CPU使用率 

v$sysstat               -> 所有会话的CPU使用率 

v$sesstat               -> 按会话列出的CPU使用率

还可以通过EM的Resource Manager下statistics连接查询CPU、I/O、等待时间和排队会话的明细。

验证Resource Manager

SQL> select  SID,SERIAL#   ,USERNAME,RESOURCE_CONSUMER_GROUP  from v$session  where username = 'SCOTT';

no rows selected

用scott 执行很耗费CPU的语句

SQL> conn scott/oracle

SQL> select count(*) from all_objects a , all_objects b , all_objects c group by a.object_id;

SQL> select  SID,SERIAL#   ,USERNAME,RESOURCE_CONSUMER_GROUP  from v$session  where username = 'SCOTT';

       SID    SERIAL# USERNAME

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

RESOURCE_CONSUMER_GROUP

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

35 9261 SCOTT

ONLINE_DEVELOPERS

57 4723 SCOTT

_ORACLE_BACKGROUND_GROUP_

60 8117 SCOTT

_ORACLE_BACKGROUND_GROUP_