内置资源计划
Oracle 11g包括一个内置的MIXED_WORKLOAD_PLAN资源计划，旨在给的OLTP业务批量操作优先权，这个计划包括两个资源消耗组(INTERACTIVE_GROUP和BATCH_GROUP)，如果分配给INTERACTIVE_GROUP组时超过60秒，就会自动切换到BATCH_GROUP组。
RESOURCE_MANAGER_PLAN参数用来告诉实例使用哪个资源计划。
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = MIXED_WORKLOAD_PLAN;
然后你必须将资源消耗组授权给单个用户，下面的例子显示将两个资源消耗组授权给TEST用户，并指定默认的资源消耗组是INTERACTIVE_GROUP。查询DBA_USERS视图可以获得已经设置好的默认设置。
BEGIN
-- Assign users to consumer groups
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'test',
consumer_group => 'interactive_group',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'test',
consumer_group => 'batch_group',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group');
END;
/
SELECT initial_rsrc_consumer_group FROM dba_users WHERE username = 'TEST';
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
INTERACTIVE_GROUP
1 row selected.
SQL>
-- Assign users to consumer groups
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'test',
consumer_group => 'interactive_group',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'test',
consumer_group => 'batch_group',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group');
END;
/
SELECT initial_rsrc_consumer_group FROM dba_users WHERE username = 'TEST';
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
INTERACTIVE_GROUP
1 row selected.
SQL>
以TEST用户连接到数据库，查询v$session视图正在工作的资源消耗组。
CONN test/test
SELECT resource_consumer_group
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
RESOURCE_CONSUMER_GROUP
--------------------------------
INTERACTIVE_GROUP
1 row selected.
SQL>
SELECT resource_consumer_group
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
RESOURCE_CONSUMER_GROUP
--------------------------------
INTERACTIVE_GROUP
1 row selected.
SQL>
如果我们创建一个长时间的调用，可以看到资源消耗组的切换。
CONN test/test
SET SERVEROUTPUT ON
DECLARE
l_cg v$session.resource_consumer_group%TYPE;
BEGIN
SELECT resource_consumer_group
INTO l_cg
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
-- Sleep to cause switch based on time.
DBMS_LOCK.sleep(65);
SELECT resource_consumer_group
INTO l_cg
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
END;
/
Current Consumer Group: INTERACTIVE_GROUP
Current Consumer Group: BATCH_GROUP
PL/SQL procedure successfully completed.
SQL>
SET SERVEROUTPUT ON
DECLARE
l_cg v$session.resource_consumer_group%TYPE;
BEGIN
SELECT resource_consumer_group
INTO l_cg
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
-- Sleep to cause switch based on time.
DBMS_LOCK.sleep(65);
SELECT resource_consumer_group
INTO l_cg
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
END;
/
Current Consumer Group: INTERACTIVE_GROUP
Current Consumer Group: BATCH_GROUP
PL/SQL procedure successfully completed.
SQL>
正如我们所期望的，调用最初分配的是INTERACTIVE_GROUP资源组，当调用超过60秒时就切换到BATCH_GROUP组了。
