数据库用户资源管理涉及到的数据包有哪些

本篇内容介绍了“数据库用户资源管理涉及到的数据包有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

用户资源管理DBMS_RESOURCE_MANAGER

用户资源管理涉及到的数据包主要有两个：DBMS_RESOURCE_MANAGER和DBMS_RESOURCE_MANAGER _PRIVS。

其中包DBMS_RESOURCE_MANAGER主要是用于建立资源计划，建立资源用户组，建立资源分配方法等用户资源相关的管理；而DBMS_RESOURCE_MANAGER _PRIVS的主要用途是进行用户资源管理的权限控制。

一、前言

资源管理器有三个部件组成：资源用户组（Resource consumer group ）、资源规划（Resource plan ）、资源分配方法（Resource allocation method）及资源计划目录(Resource plan directives) 。它们的功能如下：

部件说明:

资源用户组: 根据数据库资源处理需求，将用户会话分成组

资源规划: 指定哪些资源分配给资源用户的命令

资源分配方法: 数据库资源管理器分配特殊资源时采用的方法，由资源用户组和资源规划来使用。

资源规划命令: 管理员使用这些命令将资源用户组与特殊规划连接起来，并在资源用户组之间分配资源。

数据库资源管理器可以完成：

1．确保某些用户处理少量的资源，不考虑对系统的加载和用户的数量。

2．按比例将CPU时间分配给不同的用户和程序，分配有效的处理资源。

3．限制一组用户可以使用的并行度。

4．对实例进行配置，使其能使用特殊的资源分配方法。例如，DBA不用关闭数据库实例就可以动态地改变这些配置方法。

二、概述

用户资源管理涉及到的数据包主要有两个：DBMS_RESOURCE_MANAGER和DBMS_RESOURCE_MANAGER _PRIVS。

其中包DBMS_RESOURCE_MANAGER主要是用于建立资源计划，建立资源用户组，建立资源分配方法等用户资源相关的管理；而DBMS_RESOURCE_MANAGER _PRIVS的主要用途是进行用户资源管理的权限控制。

对于一个简单的用户资源管理计划来说，仅仅使用DBMS_RESOURCE_MANAGER包就足够了，所以下面仅仅对DBMS_RESOURCE_MANAGER的使用进行详细的说明。

三、举例

下面通过一个简单的用户资源管理计划的建立来让大家熟悉下DBMS_RESOURCE_MANAGER包的使用方法。

（1）用户资源管理示意图

DW-PLAN DB_DEV OTHER_GROUPS TMP_DATA CPU 80% LEVEL 1 CPU 100% LEVEL 2 CPU 20% LEVEL 1

上面就是一个数据仓库的简单的用户资源管理计划示意图。资源用户管理计划的名字是DW_PLAN，在这个资源管理计划下有三个资源用户组，分别DB_DEV,TMP_DATA,OTHER_GROUPS。

这个资源管理计划里面仅仅包括对CPU的控制，其中用户组DB_DEV可以获得的资源CPU 80% LEVEL 1，用户组TMP_DATA可以获得的资源是CPU 20% LEVEL 1，而用户组OTHER_GROUPS可以获得的资源是CPU 100% LEVEL 2。

CPU的百分比很好理解的，比如说DB_DEV可以获得80%的CPU资源，他的级别是LEVEL 1。关于这个LEVEL是很让人迷惑的，其实LEVEL就是资源获取的优先级别，拿上面的例子来说，假设DB_DEV和TMP_DATA分别获得了系统的80%和20%的CPU资源，那么作为下一级LEVEL 2的OTHER_GROUPS将不会获得任何CPU的资源。当DB_DEV和TMP_DATA分别获得了系统的40%和10%的CPU资源，那么作为下一级LEVEL 2的OTHER_GROUPS将会获得50%的CPU资源。

换句话说，LEVEL 2所能获得的全部资源就是LEVEL 1所未能使用的那部分资源。优先级：LEVEL1 > LEVEL 2 > LEVEL 3……LEVEL N-1>LEVEL N

需要强调的一点是OTHER_GROUPS这个资源用户组。任何一个资源计划必须要包括这个OTHER_GROUPS用户组，如果你的资源计划没有包括这个用户组，那么将会得到一个ORA-07453的错误，要求你必须添加此用户组。这个用户组的作用就是作为一个后选项，当一个没有匹配到任何资源用户组的SESSION连接到数据库的时候会自动的匹配到OTHER_GROUPS下面，按照OTHER_GROUPS的资源限定执行SQL。

另外一个需要强调的是用户资源限定的生效条件。拿上面的例子来说，当系统的CPU使用率没有达到100%的时候，DW_PLAN这个资源计划并不会生效，各个资源用户也不会按照限定来分配CPU的使用。仅仅当CPU的使用率为100%的时候，资源计划才可以发挥功效，来限制各个资源用户组的CPU分配。这点是常常被大家忽略的，一定特别注意下。

资源计划代码如下：

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN=>'DW_PLAN',COMMENT=>'Resource plan/method for DW');

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=>'DB_DEV',COMMENT => 'Resource plan user group for DB_DEV');

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'TMP_DATA',COMMENT => 'Resource plan user group for TMP_DATA');

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'OTHER_GROUPS',COMMENT => 'Resource plan user group for OTHER_GROUPS');

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' DB_DEV ',COMMENT => ' DB_DEV ',CPU_P1 => 80);

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' TMP_DATA ',COMMENT => ' TMP_DATA ',CPU_P1 => 20);

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' OTHER_GROUPS ',COMMENT => ' OTHER_GROUPS ',CPU_P1 => 0, CPU_P2 => 100);

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

执行过程说明：

1．DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

创建一挂起区域，每次对资源计划进行操作之前都必须要执行挂起操作，申请一块内存。

2，DBMS_RESOURCE_MANAGER.CREATE_PLAN

创建一个资源计划，参数PLAN表示资源计划的名字，COMMENT为该资源计划的注释信息

3，DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP

创建一个资源用户组，参数CONSUMER_GROUP为资源用户组名字，COMMENT为该资源用户组的注释信息

4，DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

创建一个资源分配方法，参数PLAN为资源计划的名字，,GROUP_OR_SUBPLAN为下层资源用户的名字，COMMENT为资源分配方法的注释信息，CPU_P1表示该资源用户组在LEVEL上的CPU分配方案。

5，DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

验证用户资源计划的有效性

6，DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()

提交用户资源计划

（2）当建立好用户资源计划之后，就需要将特定的用户与特定的资源计划相关联。

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'CNODS', ' DB_DEV ')

通过这条命令就可以将用户CNODS分配到资源组DB_DEV下。

（3）关联好之后就可以将新建立的用户资源管理置为有效

dbms_resource_manager.switch_plan( plan_name => 'DW_PLAN', sid => 'ORCL' )

通过这条命令将当前的用户资源管理计划设置为DW_PLAN

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     1
resource_manager_plan                string      DW_PLAN
SQL>

（4）接下来检查初始化参数resource_limit，将其设置为TRUE

sys@ORCL> show parameter resource_limit

NAMETYPEVALUE

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

resource_limitbooleanTRUE

sys@ORCL> alter system set resource_limit=true scope=both;

系统已更改。

经过上面的步骤，就可以使用新生成的用户资源计划了。

上面仅仅介绍了关于CPU的资源管理，其实用户资源管理包还可以对相同用户的活动SESSION数量，SESSION空闲时间，UNDO空间进行管理。下面就分别详细的说明下各个管理的使用方法和注意事项。

四、ACTIVE_SESS_POOL_P1

这个参数控制的是资源用户组内的用户同时可以运行的最大的活动SESSION的数量。这里值得强调的是ACTIVE_SESS_POOL_P1并不限制那些非活动的SESSION，仅仅对那些活动的SESSION有限制，因为一般说来只有那些活动的SESSION才会消耗系统的资源。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => 'TEST_GROUP',NEW_ACTIVE_SESS_POOL_P1 => 1);

上面这条语句表示资源用户组TEST_GROUP内的用户同时仅能存在一个活动的事务。

举例：

假设TEST用户的资源组是TEST_GROUP

SESSION1：

test@ORCL> CONN TEST/TEST

test@ORCL> SELECT COUNT（*） FROM DBA_OBJECTS,DBA_OBJECTS;

SESSION2:

test@ORCL> CONNN TEST/TEST

可以发现此时SESSION2被阻塞了，仅仅当SESSION1的SQL执行完毕，变成INACTIVE状态后，SESSION2才可以连接到数据库。那么这这个时候就有两个SESSION连接到数据库，但当一个执行SQL的时候，另一个SESSION会立刻被挂起。

对我们数据仓库来说，这个参数的意义比较重大。因为我们要控制的是系统的资源，而系统的绝大部分资源被那些ACTIVE的SESSION所占据着，那么只要限定了并发的ACTIVE的SESSION数量，系统的资源就得到了有效的控制。

五、QUEUEING_P1

这个参数控制的是SESSION的等待时候，一个SESSION被放到等待队列中，那么正常的情况下，他会一直等待所需要的资源，当设置了QUEUEING_P1以后，当超过了QUEUEING_P1指定的时间后，系统会报错误ORA-07454，提醒已经等待超时。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => ' TEST_GROUP ',NEW_ACTIVE_SESS_POOL_P1 => 1,NEW_QUEUEING_P1 => 10);

SESSION1

CONN TEST/TEST

SESSION2:

CONNN TEST/TEST

SESSION1

test@ORCL> SELECT COUNT（*） FROM DBA_OBJECTS,DBA_OBJECTS;

SESSION2:

test@ORCL> select sysdate from dual;

select sysdate from dual

*

第 1 行出现错误:

ORA-07454: 队列超时, 已超过 10 秒

这个参数的用途很象SELECT * FROM XXX FOR UPDATE,避免用户一直等待。

六、PARALLEL_DEGREE_LIMIT_P1

这个参数就没什么好说的了，是限制用户执行SQL时候的并行度，就不举例说明了，调用方法如下：

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => ' TEST_GROUP ',NEW_ACTIVE_SESS_POOL_P1 => 1,NEW_QUEUEING_P1 => 10，NEW_PARALLEL_DEGREE_LIMIT_P1 => 2);

七、SWITCH_GROUP，NEW_SWITCH_TIME，NEW_SWITCH_ESTIMATE

这个三个参数之所以一起介绍，是因为他们共同的合作完成一项很重要的功能。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => 'DW_TEST', NEW_SWITCH_GROUP => ‘dw_sys’, NEW_SWITCH_TIME => 300, NEW_SWITCH_ESTIMATE => ‘true’);

上面这个条语句完成的功能是，如果一个SESSION的资源用户组是属于DW_TEST，他如果执行一个很复杂的SQL，系统估计这个条SQL的执行时间会超过300秒的时候，自动把这个SESSION切换到用户组dw_sys上，这样该SESSION会获得更多的资源，更快的执行完成这个SQL。但是执行完成之后，这个SESSION就在以后的时间保留在切换的组中。

另外一个和NEW_SWITCH_TIME对立的参数是NEW_SWITCH_TIME_IN_CALL，如果使用了这个参数，那么在执行完成后，切换回原来的资源用户组。

八、MAX_EST_EXEC_TIME

这个参数控制一个事务最大的执行时间。如果一个事务很复杂，执行时间很长，那么他就不会被系统执行。

九、UNDO_POOL

这个参数很让人迷惑，开始的时候我以为这个参数控制的一个用户在回滚表空间内最大的UNDO段的大小，因为UNDO段的块是循环使用的，所以只要单个事务产生的回滚信息不超过这个最大值就应该没有问题。

可实际上经过测试，这个参数控制的是个总量，即该用户产生的总的UNDO数量不能超过这个值，如果超过这个值就会报错。注意是总的UNDO，是个累加的值。

十、MAX_IDLE_TIME

这个参数控制的一个用户的SESSION最大的空闲时间，如果空闲时间超过这个限定，该SESSION会被终止。

十一、MAX_IDLE_BLOCKER_TIME

这个参数是控制那些占有资源的空闲SEESION的，当一个占有资源的SESSION空闲时间超过了MAX_IDLE_BLOCKER_TIME的限制，那么系统会要求他释放所占有的资源，一般就是ROLLBACK操作。

“数据库用户资源管理涉及到的数据包有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！