用资源管理器限制大数据量查询

  最近生产库上经常因为一些查询账号登陆数据仓库，做了大数据量查询，sql语句甚至不带条件，严重拖累了数据库性能，
因此我增加了resource manager限制这些查询账号的最大执行时间，以及cpu使用率，目的是降低这些账号查询对数据库正常业务的影响。

1.资源管理器创建的步骤：
Step 1: Create a pending area.

Step 2: Create, modify, or delete consumer groups.

Step 3: Create the resource plan.

Step 4: Create resource plan directives.

Step 5: Validate the pending area.

Step 6: Submit the pending area.

2.我的创建脚本
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 
  /* Create consumer groups.
   * By default, users start in OTHER_GROUPS, which is automatically
   * created for every database.
   */

    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'MAIN_GROUP',
    'rptdw main users group');
   
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'QUERY_GROUP',
    'using for query users ,limit parallel threads and cpu consume percentage');
   
  /* Create a plan to manage these consumer groups */
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    'RPTDW_PLAN',
    'Plan for limit long-running queries and too many parallel querys');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'RPTDW_PLAN', 'OTHER_GROUPS', 'Directive for rptdw system users activity',
    mgmt_p1 => 100);
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'RPTDW_PLAN', 'QUERY_GROUP', 'Directive for query users activity',
    mgmt_p2 => 80,
    parallel_degree_limit_p1 => 2,
    max_est_exec_time => 120);
 
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
   
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

3.如果中间报错，需要清空悬挂区。
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

4,到所有RAC的节点用sys用户执行，让资源管理器计划生效。
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = rptdw_plan ;

5.我想把以下数据库账号加入新建的资源计划的QUERY_GROUP消费组
SUPPORT
FROMBI
MAIN1
MAIN4
MAIN_DBA
MREAD_Q
OPS$MON
BOCO4A
REPORT1

-- 是否有必要？
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'SUPPORT', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'FROMBI', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'MAIN1', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'MAIN4', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'MAIN_DBA', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'MREAD_Q', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'OPS$MON', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'BOCO4A', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'MAIN2', 'QUERY_GROUP');
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

执行成功
PL/SQL procedure successfully completed

我想把其他数据库账号加入消费组OTHER_GROUPS

DET

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'DET', 'OTHER_GROUPS');
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
报错：
ERROR at line 1:
ORA-29396: cannot switch group to OTHER_GROUPS
ORA-06512: at "SYS.DBMS_RMIN", line 302
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 709
ORA-06512: at line 3
注意，无法把数据库用户映射到OTHER_GROUPS，OTHER_GROUPS相当于默认组

6.给账号分配权限，然后修改这些账号的初始化的消费者组
begin
  dbms_resource_manager_privs.grant_system_privilege(grantee_name=>'MAIN2',admin_option=>true);
end;
/

begin
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'SUPPORT',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'FROMBI',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'MAIN1',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'MAIN4',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'MAIN_DBA',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'MREAD_Q',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'OPS$MON',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'BOCO4A',consumer_group =>'QUERY_GROUP',grant_option => false);
  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name =>'MAIN2',consumer_group =>'QUERY_GROUP',grant_option => false);
end;
/
                                 
begin
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('SUPPORT','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('FROMBI','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MAIN1','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MAIN4','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MAIN_DBA','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MREAD_Q','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('OPS$MON','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('BOCO4A','QUERY_GROUP');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MAIN2','QUERY_GROUP');
end;
/

7.回头看了最大执行时间2分钟，感觉太少了，修改消费组QUERY_GROUP的计划指导
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.update_plan_directive(
         PLAN                  => 'RPTDW_PLAN',
         GROUP_OR_SUBPLAN      => 'QUERY_GROUP',
         new_max_est_exec_time => 1800
        );
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

8.测试一下
用限制账号main2登陆，执行
select count(*) from report1.T_F_USERVISIT_PLT
报错:
ORA-07455: 估计执行时间（119039秒）超出了限制（1800秒）
资源管理器生效。

9.资源管理器的两个包
SYS.dbms_resource_manager
SYS.dbms_resource_manager_privs
相关视图：
select * from V$RSRC_PLAN ;
select * from V$RSRC_CONSUMER_GROUP ;
select * from V$RSRC_SESSION_INFO ;
select * from V$RSRC_PLAN_HISTORY ;
select * from V$RSRC_CONS_GROUP_HISTORY ;
select * from V$RSRCMGRMETRIC ;
select * from V$RSRCMGRMETRIC_HISTORY ;
SELECT plan,status,comments FROM dba_rsrc_plans;
SELECT sid,serial#,username,resource_consumer_group FROM gv$session;
SELECT sid,serial#,username,resource_consumer_group FROM gv$session where resource_consumer_group='QUERY_GROUP' ;
select * from dba_rsrc_plans ;
select * from dba_rsrc_consumer_groups ;
select * from dba_rsrc_plan_directives t where t.plan='RPTDW_PLAN';
select * from dba_rsrc_group_mappings ;