目的：使用Oracle自带DBMS包REOURCE_MANAGE来控制指定用户的SQL并发度

一：DBMS_REOSUECE_MANAGE包介绍

DBMS_RESOURCE_MANAGER包是Oracle 9i版本后自带的一个程序包，可用于维护资源计划,自定义资源使用组和资源计划指令。将指定用户切入资源使用组，并在资源使用组下制定自定义的资源计划，可以限制指定用户的资源消耗，比如限制组内所有用户的CPU使用率，限制组内用户会话的并行度，限制组内用户的总活动会话数等。

1：资源计划

oracle从8i开始提供了资源管理器（Resource Manager）。这个RM可以让DBA管理数据库服务所使用的资源。

打个比方，无论是windows或者是linux，操作系统都具备将特定的资源分配给特定进程的能力，并且实现自动调度。打开windows的任务管理器就能看到一个进程所消耗的CPU和内存总是随着计算任务的多少而动态变化的，这就是资源管理。

一个资源计划包含在使用者组之间分配资源的一组指令

而ORACLE中的Resource Manager实现的是类似的功能。

总体来说RM所管理的资源包括：

• 一个组中所有会话的总CPU使用率
• 组中每个会话可用的并行程度
• 每个组中允许的活动会话数量
• 每个组允许的撤销空间量
• 终止空闲会话之前的所需要的时间
• 会话中一次调用的最长执行时间，他能触发将会话切换到另一个组中

3：创建资源组主要步骤

以限制CPU使用率为例：

可以指定每个组所使用CPU的比例和时间，例如白天将80%的CPU分配给指定用户组。

创建步骤如下：

-- 创建挂起区域

exec dbms_resource_manager.create_pending_area;

-- 创建计划

exec dbms_resource_manager.create_plan(-plan=>'PLAN_NAME',comment=>'PLAN_COMMET');

-- 根据需要设置计划内指令，具体代码在后面详细说明

-- 验证挂起区域

exec dbms_resource_manager.validate_pending_area;

-- 提交资源管理器挂起的更改

exec dbms_resource_manager.submit_pending_area;

-- 将指定用户加入资源组

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.oracle_user,'USERNAME', 'GROUP_NAME');

-- 激活资源计划

alter system set resource_manager_plan=group_name;

2：资源计划中的使用者组

使用者组是一组具有类似资源需求的用户，一个组可以包含多个用户，同时一个用户也可以同时属于多个组，当属于多个组时可以切换用户激活的是哪个组的权限，这个切换可以手动也可以自动完成。

在创建一个ORACLE实例时,会默认提供了18个使用者组

所有的组可以查询DBA_RSRC_CONSUMER_GROUPS和DBA_USERS这两个视图，一个是查看所有的组信息，一个是查看用户的组信息

查看指定用户所属的资源组：

select USERname,INITIAL_RSRC_CONSUMER_GRO from dba_users;

4：限制活动会话数量

RM允许DBA限制一个组同时运行的语句数量，但是不限制登录数量。例如一共10个业务用户都可以登录，但是这10个用户的会话中只允许三个会话可以同时提交任务或者作业，如果第四个会话提交那么这个会话必须等待，直到有会话完成任务。

这里的活动会话，指正在运行查询的会话或者处于未提交的事务中的会话。

5：限制并行程度

并行处理可以提高SQL的性能，但是也有资源消耗的代价，设置远远高于实际需要的并行程度有可能会对其他用户产生影响，甚至导致数据库被消耗殆尽导致hang起。RM允许DBA限制并行程度，可以对任何一个组下的组用户的每个会话允许使用的并行处理器数量设定一个硬限制。

使用参数new_parallel_degree_limit=>6，即可限制并行程度不能超过6个。

6：操作执行时间

简单的说就是在特定时间排除特定类型的作业，也可以启动特定类型的作业。

7：空闲会话终止时间

一个已经创建的会话，如果他又不做任何事情那么是会浪费数据库的连接资源。RM可以根据空闲会话的时间来自动终止会话。终止会话有两个标准：

• 根据会话空闲的时间，超过时间则自动终止
• 不仅检查会话的时间，还查看会话是否正持有封锁其他会话的记录和表锁

涉及到的参数：MAX_IDLE_TIME和MAX_IDLE_BLOCKER_TIME

8：撤销数据量

ORACLE中所有的DML语句都会生成UNDO，所有的UNDO都写入到UNDO表空间，一个大型事务则有可能会填满整个UNDO表空间，RM允许DBA进行设置,限制资源组内用户可使用的最大UNDO量，以防止一组用户的大型事务填满撤销表空间。

二：创建并发度资源限制组

测试环境：

这里测试用户分别为test,test1，这个两个用户下各有一个完全相同的测试表TEST,表数据量在1亿5千万条左右，这里不展示测试用户及测试数据的构造过程。

1：预先清理资源组

begin

sys.dbms_resource_manager. clear_pending_area();

sys.dbms_resource_manager. create_pending_area();

sys.dbms_resource_manager. delete_plan_cascade ('ACCT_PLAN');

sys.dbms_resource_manager. submit_pending_area();

end;

/

子程序说明：

• clear_pending_area():清理pend内存缓存
• cleate_pending_area():创建一个pend缓存区
• delete_plan_cascade ('ACCT_PLAN'):清理资源组ACCT_PLAN
• submit_pending_area():提交pend缓存区变更到资源管理

说明：对RM进行操作时，需要提前开辟一个pend缓存区，类似一个事务池，在这个缓存区内，使用资源命令定义所需的资源计划，然后再提交到RM中，与事务的概念相似，如果所做的资源计划变更不提交到RM,这些资源计划是不会生效的。

sys.dbms_resource_manager.delete_plan('ACCT_PLAN');

dbms_resource_manager.switch_consumer_group_for_user(user in varchar2,consumer_group in varchar2);

2：创建资源组并指定资源计划

begin

sys.dbms_resource_manager.clear_pending_area(); --清理pending缓存区内容

sys.dbms_resource_manager.create_pending_area(); --创建pending缓存区

sys.dbms_resource_manager.create_plan ( --创建建立资源计划

plan => 'ACCT_PLAN' -- 指定计划名

,comment => 'ACCT aaplication''s PLAN'); --资源计划说明

sys.dbms_resource_manager.create_plan_directive ( --建立资源计划指令

plan => 'ACCT_PLAN' --资源计划指令名

,group_or_subplan => 'OTHER_GROUPS' --指定切换组

,switch_estimate => FALSE --指定切换模式

,comment => 'ddd' );

sys.dbms_resource_manager.create_plan_directive (

plan => 'ACCT_PLAN'

,group_or_subplan => 'SYS_GROUP'

,switch_estimate => FALSE

,comment => 'ccc' );

sys.dbms_resource_manager.create_consumer_group (

consumer_group => 'ACCT_GROUP'

,comment => 'ACCT applications');

sys.dbms_resource_manager.create_plan_directive ( --创建资源组ACCT_PLAN资源计划

plan => 'ACCT_PLAN',

group_or_subplan => 'ACCT_GROUP',

switch_estimate => FALSE,

comment => 'bbb' );

sys.dbms_resource_manager.create_consumer_group (

consumer_group => 'CITY_GROUP'

,comment => 'CITY applications');

sys.dbms_resource_manager.create_plan_directive (

plan => 'ACCT_PLAN'

,group_or_subplan => 'CITY_GROUP'

,switch_estimate => FALSE

,parallel_degree_limit_p1 => 2 --限制资源组CITY_GROUP内的用户会话SQL

,comment => 'aaa' );

sys.dbms_resource_manager.submit_pending_area();

end;

/

3：设置切换组

-- 如下动作必须，否则用户登录后无法切换到指定的资源组

BEGIN

sys.dbms_resource_manager.clear_pending_area();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME => 'PUBLIC',

CONSUMER_GROUP => 'ACCT_GROUP',

GRANT_OPTION => TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME => 'PUBLIC',

CONSUMER_GROUP => 'CITY_GROUP',

GRANT_OPTION => TRUE);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

/

4：建立MAPPING并指定resource_manager_plan

BEGIN

dbms_resource_manager.clear_pending_area(); --清理PENDING缓存区

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); --创建PENDING缓存区

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.oracle_user,'TEST', 'CITY_GROUP'); --将指定用户TEST加入资源组CITY_GROUP

dbms_resource_manager.submit_pending_area();

END;

/

alter system set resource_manager_plan=ACCT_PLAN;

5：查看用户对应的资源组

select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile,INITIAL_RSRC_CONSUMER_GROUP

from

dba_users

where username in ('TEST','TEST1')

三：测试不同资源组用户的会话并发度

这里测试用户分别为test,test1，这个两个用户下各有一个完全相同的测试表TEST,表数据量在1亿5千万条左右，这里不展示测试用户及测试数据的构造过程。

测试用户TEST加入资源组CITY_GROUP，该测试组中已设置资源限制parallel_degree_limit_p1=> 2，指定该资源组中的用户会话的并发度不高于2。

测试用户TEST1未指定资源组，因此使用的是默认资源组DEFAULT_CONSUMER_GROUP，该资源组没有额外资源计划，不对该资源组的用户做资源限制。

测试SQL:

update /*+APPEND ENABLE_PARALLEL_DML PARALLEL(test,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

此外，此次测试过程中，保持ORACLE相关PARALLEL参数不变

1:使用test1用户执行测试SQL

>sqlplus test1/*****

>update /*+APPEND ENABLE_PARALLEL_DML PARALLEL(test,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

查看用户test1SQL并发度:

SELECT b.username,a.SID,a.SERIAL#,a.DEGREE,a.REQ_DEGREE from gv$px_session a,v$session b where a.sid=b.sid;

2:使用test用户执行测试SQL

>sqlplus test/*****

>update /*+APPEND ENABLE_PARALLEL_DML PARALLEL(test,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

查看用户test1SQL并发度:

SELECT b.username,a.SID,a.SERIAL#,a.DEGREE,a.REQ_DEGREE from gv$px_session a,v$session b where a.sid=b.sid;

3:将TEST1用户加入资源组CITY_GROUP

(1).将TEST1用户加入资源组CITY_GROUP

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,'TEST1', 'CITY_GROUP'); --将指定用户TEST1加入资源组CITY_GROUP

dbms_resource_manager.submit_pending_area();

END;

/

(2).查看用户所属资源组：

select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile,INITIAL_RSRC_CONSUMER_GROUP

from

dba_users

where username in ('TEST','TEST1');

测试用户TEST1已加入资源组CITY_GROUP

(3).重新执行测试语句

>sqlplus test1/*****

>update /*+APPEND ENABLE_PARALLEL_DML PARALLEL(test,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

查看用户test1SQL并发度:

SELECT b.username,a.SID,a.SERIAL#,a.DEGREE,a.REQ_DEGREE from gv$px_session a,v$session b where a.sid=b.sid;

将测试用户TEST1加入资源组CITY_GROUP后，再执行相同的测试SQL,SQL会话请求的并发度为10，但资源组资源计划将其并发度限制到了2

4：将TEST1资源组切回DEFAULT_CONSUMER_GROUP

(1):将TEST1资源组切回DEFAULT_CONSUMER_GROUP

BEGIN

dbms_resource_manager.clear_pending_area(); --清理PENDING缓存区

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); --创建PENDING缓存区

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.oracle_user,'TEST1', 'DEFAULT_CONSUMER_GROUP'); --将指定用户TEST切回资源组DEFAULT_CONSUMER_GROUP

dbms_resource_manager.submit_pending_area();

END;

/

(2).查看用户所属资源组：

select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile,INITIAL_RSRC_CONSUMER_GROUP

from

dba_users

where username in ('TEST','TEST1');

测试用户TEST1已切回资源组DEFAULT_CONSUMER_GROUP

(3).重新执行测试语句

>sqlplus test1/*****

>update /*+APPEND ENABLE_PARALLEL_DML PARALLEL(test,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

查看用户test1SQL并发度:

SELECT b.username,a.SID,a.SERIAL#,a.DEGREE,a.REQ_DEGREE from gv$px_session a,v$session b where a.sid=b.sid;

根据上述测试可看出，当测试用户TEST1切回资源组DEFAULT_CONSUMER_GROUP后，没有了资源计划的限制，再执行同一条测试SQL,SQL并发度又从2恢复到了10

四：其他并发场景测试

1：并发表

（1）设置测试表自带并发：

（2）查看用户所属资源组：

set line 250

col USERNAME for a8

col ACCOUNT_STATUS for a25

col TEMPORARY_TABLESPACE for a25

col DEFAULT_TABLESPACE for a15

col INITIAL_RSRC_CONSUMER_GROUP for a35

col profile for a25

select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile,INITIAL_RSRC_CONSUMER_GROUP

from

dba_users

where username='TEST';

（3）执行测试语句并查看并发度：

update test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

set line 250

col username for a10

col event for a28

col sql_text for a68

col wait_class for a20

col blocking_session for 999999999

col username for a12F

col machine for a25

col program for a25

select s.username,s.sid,s.serial#,s.event,s.wait_class,s.inst_id,s.status,s.port,s.blocking_session,s.sql_exec_start,s.logon_time,sq.sql_id from gv$session s,gv$sqlarea sq

where s.sql_id=sq.sql_id and s.username is not null and s.sql_id is not null and s.username='TEST';

从上图可以看出，测试表自身加了10并发，SQL语句中不做并发定义，测试用户不加入资源组，执行测试语句时，会话自发调用了10个并发子会话

（4）将测试用户切入限制资源组并启用资源计划:

（5）从新执行相同的测试语句：

（6）小节：

从上图看，表自身开10并发，将测试用户加入限制资源组后，再次执行测试SQL，虽然请求调用的并发度仍是10，但是实际分配的并发度受资源计划限制，只分配了2个并发度，并且测试库的process数也只增长了2个，从128变为了130。

根据上图测试结果可以看出，使用resource_manage在表本身带并发度的情况下，仍可以对指定用户下涉及并发表的SQL的并发度做限制

2：hint并发

（1）重置用户资源组及表并发度

（2）执行hint 并发测试语句

update /*+ PARALLEL(TEST,10) */ test set object_name='TEST0905' where object_id>1000 and object_id<100000000;

（3）将测试用户加入限制资源组

（4）重新执行测试SQL并检查并发度

（5）小节：

从上图看，使用hint对SQL调用10并发，将测试用户加入限制资源组后，再次执行测试SQL，虽然请求调用的并发度仍是10，但是实际分配的并发度受资源计划限制，只分配了2个并发度，并且测试库的process数也只增长了3个，从126上升到了129。

根据上图测试结果可以看出，使用hint方式调用并发的情况下，仍可以使用resource_manage对指定用户下使用hint方式调用并发的SQL的并发度做限制

3：会话内并发【

在会话内开并发后使用resource_manage对测试用户会话做并发度限制，看用户会话并发度是否有所下降。

在测试用户会话下开启会话并发

alter session force parallel query parallel 10;

确认测试用户的资源组及测试表的并发度

执行测试语句观察当前会话并发度

update test set object_name='TEST0905' where object_id>10000 and object_id<100000000;

将测试用户加入资源限制组后再执行测试语句

直接在之前的会话中重新执行测试语句，不切换测试会话

在旧的测试会话中查看测试用户的资源组查看测试用户的资源组

（5）小节：

从上图看，使用alter session parallel ……方式对SQL调用10并发，将测试用户加入限制资源组后，再次执行测试SQL，虽然请求调用的并发度仍是10，但是实际分配的并发度受资源计划限制，只分配了2个并发度，对应也只多出来两个并发子会话在执行测试语句，并且测试库的process数也只增长了3个，从126上升到了129。

根据上图测试结果可以看出，在会话内开启并发调用的情况下，仍可以使用resource_manage对指定用户下使用hint方式调用并发的SQL的并发度做限制

分析：

经过上述测试,不管是使用ENABLE_PARALLEL_DML,还是对表本身开并发度，或是使用hint方式调用并发，或是使用alter session parallel调用并发

当将测试用户从资源使用DEFAULT_CONSUMER_GROUP切换到新建资源组CITY_GROUP后，因为资源组CITY_GROUP中制定了资源计划：parallel_degree_limit_p1 => 2，加入了资源组CITY_GROUP的用户执行的SQL都收到这个资源计划的限制，不管SQL请求的并发度是多少，资源管理器RM都会将该组用户执行的SQL的并发度限制在2。

当用户切回其他资源使用组DEFAULT_CONSUMER_GROUP后，该资源组没有对SQL并发度做限制，因此用户SQL的并发度不受资源管理器RM的限制，只要SQL请求的并发度小于parallel_max_servers参数，那么数据库将直接分配SQL请求的并发度。

总结：

根据测试结果，确定在19c环境下，可以使用Oracle自带的DBMS_REOSUECE_MANAGE包来创建所需的资源使用组，再在资源使用组下制定对应的资源计划，在资源计划中可以限制SQL的并发度，再将指定用户切换到新建的资源组，从而可以通过资源管理器RM来限制指定用户的SQL并发度，避免相关用户执行sql时并发度过高，导致整个数据库异常，影响数据库中其他用户。

大致的流程如下：

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

-- 创建挂起区域

exec dbms_resource_manager.create_pending_area;

-- 创建计划

exec dbms_resource_manager.create_plan(-plan=>'PLAN_NAME',comment=>'PLAN_COMMET');

-- 根据需要设置计划内指令，具体代码在后面详细说明

-- 验证挂起区域

exec dbms_resource_manager.validate_pending_area;

-- 提交资源管理器挂起的更改

exec dbms_resource_manager.submit_pending_area;

-- 将指定用户加入资源组

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.oracle_user,'USERNAME', 'GROUP_NAME');

-- 激活资源计划

alter system set resource_manager_plan=group_name;

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

其他情况说明：

DBMS_REOSUECE_MANAGE不仅可以用来限制指定用户SQL的并发度，还可以限制指定用户的CPU使用率之和，UNDO使用量，SQL执行时间及活动会话数等。

此外，一个用户可以从属于多个资源使用组，但同一个时刻，该用户只会应用一个资源组的执行计划，可以设置触发条件，让一个用户在不同资源组之间切换。