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

来源：恒创科技编辑：恒创科技编辑部

2023-12-23 20:54:59

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

用户资源管理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的使用进行详细的说明。

三、举例

下面通过一个简单的用户资源管理计划的建立来让大家熟悉下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就在以后的时间保留在切换的组中。