oracle资源管理器简介：
oracle资源管理器可以对属于一个特定组的所有用户的cpu，执行时长，空闲会话等待时长，占用undo大小，并发数等资源进行限制，并可灵活将当前组切换至其他资源组。
详细见下表

下边就是介绍如何使用 oracle 的资源管理器控制使长耗时的sql查询（其中也设置了会话最大的cpu使用率）。

首先创建2个测试表

SQL> sho user
USER is "OLTP_USER"
SQL> create table t1 as select owner,object_name from sys.dba_objects;

Table created.

SQL> insert into t1 select * from t1;

9970 rows created.

SQL> /

19940 rows created.

SQL> commit;

Commit complete.

SQL> create table t2 as select * from t1;

Table created.

注意：上边创建的2个测试表的列存在大量重复值。

将数据块从内存中刷新出去，将测试误差降到最低

SQL> alter system flush buffer_cache;

System altered.

使用笛卡尔积模拟一个长耗时的sql查询

SQL> set timing on
SQL> select count(*) from t1,t2 where t1.owner=t2.owner;

  COUNT(*)
----------
 794796640

Elapsed: 00:00:49.64

下边我们通过oracle资源管理器来控制该类长耗时的sql查询。
创建一个资源组

SQL> SHO USER
USER is "SYS"
SQL> begin   
  2  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'MAX_EXEC_TIME_GRP',COMMENT=> 'MAX_EXEC_TIME_GRP');
  5  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
  6  end;
  7  /

PL/SQL procedure successfully completed.

赋予用户权限

SQL> EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('oltp_user','MAX_EXEC_TIME_GRP', false);

PL/SQL procedure successfully completed.

设置资源组映射

SQL> begin   
  2  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  5  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER,'OLTP_USER','MAX_EXEC_TIME_GRP');
  6  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
  7  end;
  8  /

PL/SQL procedure successfully completed.

创建资源计划

SQL> begin
  2  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4  DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'MAX_EXEC_TIME_PLAN',COMMENT => 'MAX_EXEC_TIME_PLAN');
  5  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
  6  (
  7  PLAN => 'MAX_EXEC_TIME_PLAN',
  8  GROUP_OR_SUBPLAN => 'MAX_EXEC_TIME_GRP',
  9  COMMENT    => '',
 10  CPU_P1     => 60,
 11  MAX_EST_EXEC_TIME  => 5			//允许会话执行最大的时间，单位是秒
 12  );  
 13  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
 14  (
 15  PLAN => 'MAX_EXEC_TIME_PLAN',
 16  GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
 17  COMMENT    => 'OTHER_GROUPS',
 18  CPU_P1     => 40
 19  );  
 20  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
 21  end;
 22  /

PL/SQL procedure successfully completed.

使资源计划生效

SQL> exec DBMS_RESOURCE_MANAGER.SWITCH_PLAN(plan_name=>'MAX_EXEC_TIME_PLAN',sid=>'yallonking');

PL/SQL procedure successfully completed.

再次测试长耗时sql查询

SQL> show user
USER is "OLTP_USER"
SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from t1,t2 where t1.owner=t2.owner;
select count(*) from t1,t2 where t1.owner=t2.owner
                     *
ERROR at line 1:
ORA-07455: estimated execution time (14 secs), exceeds limit (5 secs)

SQL> select count(*) from t1
  2  union all
  3  select count(*) from t2;

  COUNT(*)
----------
     39880
     39880

Elapsed: 00:00:00.01

注意：该处资源管理器预计第一个查询需要耗时14s（实际执行所需时间可能比5s大或者小，后边会证实），因其大于设置的5s，所以未允许该sql继续执行，而是终止查询并返回一定的信息。
而并没有限制其他预估执行时间小于5s的查询。

下边使该资源计划失效，进行对比

SQL> show user
USER is "SYS"
SQL> exec DBMS_RESOURCE_MANAGER.SWITCH_PLAN(plan_name=>'',sid=>'yallonking');

PL/SQL procedure successfully completed.

SQL> sho user
USER is "OLTP_USER"
SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> select count(*) from t1,t2 where t1.owner=t2.owner;

  COUNT(*)
----------
 794796640

Elapsed: 00:00:54.66

注意：此处已恢复正常的查询。

对于资源管理器来说，其对sql执行的时间只是预估，并不是实际运行的时间。
下边是在资源管理器生效情况下的查询

SQL> select count(*) from t1,t2 where rownum<2;
select count(*) from t1,t2 where rownum<2
                     *
ERROR at line 1:
ORA-07455: estimated execution time (20855 secs), exceeds limit (5 secs)

但是该sql在资源管理器失效的情况下，实际执行却如下：

SQL> select count(*) from t1,t2 where rownum<2;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01

注意：此处实际执行用时不到1s，但是在上边资源管理器预估的却是20855s。

删除资源组和资源计划

SQL> show user
USER is "SYS"
SQL> begin   
  2  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  3  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  4  DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'MAX_EXEC_TIME_PLAN');
  5  DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'MAX_EXEC_TIME_GRP');
  6  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
  7  end;
  8  /

PL/SQL procedure successfully completed.

至此，全部OK！