透彻理解与灵活使用数据库资源管理器

yansw

透彻理解与灵活使用数据库资源管理器

（瑞贝卡股份有限公司 闫少伟 2010.07.15）

    假设你用一个用户连接数据库供web服务器使用。某个客户端查询等待时间过长而取消了查询，它的查询在数据库服务中并没有真正结束。另外假设进行财务成本计算需要很长时间，如果用户在正常工作日执行该操作，将会影响销售、生产现场录入、领导查询等人员的使用。我们希望将这些工作的优先级降低或者放到晚间执行。这都是用数据库资源管理的经典例子。

首先了解资源管理器程序包的一些过程的用法。
1、执行 execute DBMS_RESOURCE_MANAGER.create_pending_area();     创建未决区域。
2、创建资源消费者组。我这里创建一个一般用户组general和资源消费大户组heavy。
   Execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
        (consumer_group=>'general',Comment=>'一般人员');

   Execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
        (consumer_group=>'heavy',Comment=>'长时间占用人员');
创建计划名称
   Execute DBMS_RESOURCE_MANAGER.create_plan
(plan=>'g',COMMENT=>'一般负荷');

   Execute DBMS_RESOURCE_MANAGER.create_plan
(plan=>'h',COMMENT=>'重负荷');

3、创建计划指令
   Execute DBMS_RESOURCE_MANAGER.create_plan_directive
(plan=>'g',
Group_or_subplan=>'general',
Comment =>'一般人员',
Cpu_p1 =>75,
Cpu_p2 =>0,
Parallel_degree_limit_p1 =>12);
将 75%的系统资源赋给一般用户。
Execute DBMS_RESOURCE_MANAGER.create_plan_directive
(plan=>'h',
Group_or_subplan=>'heavy',
Comment =>'长时间占用人员',
Cpu_p1 =>25,
Cpu_p2 =>0,
Parallel_degree_limit_p1 =>6);
将 25%的系统资源赋给长时间占用用户。
       
        这里普通人员的并行化程度限制为12，这里长时间占用人员的并行化程度限制为6。
        这个过程比较复杂、且比较重要。语法清单如下：
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   plan                      IN VARCHAR2,
   group_or_subplan          IN VARCHAR2,
   comment                   IN VARCHAR2,
   cpu_p1                    IN NUMBER   DEFAULT NULL,
   cpu_p2                    IN NUMBER   DEFAULT NULL,
   cpu_p3                    IN NUMBER   DEFAULT NULL,
   cpu_p4                    IN NUMBER   DEFAULT NULL,
   cpu_p5                    IN NUMBER   DEFAULT NULL,
   cpu_p6                    IN NUMBER   DEFAULT NULL,
   cpu_p7                    IN NUMBER   DEFAULT NULL,
   cpu_p8                    IN NUMBER   DEFAULT NULL,
   active_sess_pool_p1       IN NUMBER   DEFAULT NULL,
   queueing_p1               IN NUMBER   DEFAULT NULL,
   parallel_degree_limit_p1  IN NUMBER   DEFAULT NULL,
   switch_group              IN VARCHAR2 DEFAULT NULL,   
   switch_time               IN NUMBER   DEFAULT NULL,
   switch_estimate           IN BOOLEAN  DEFAULT FALSE,
   max_est_exec_time         IN NUMBER   DEFAULT NULL,
   undo_pool                 IN NUMBER   DEFAULT NULL,
   max_idle_time             IN NUMBER   DEFAULT NULL,
               max_idle_blocker_time     IN NUMBER   DEFAULT NULL,
               switch_time_in_call       IN NUMBER   DEFAULT NULL);
       
       
4、将用户赋予资源消费者组
Execute DBMS_RESOURCE_MANAGER.set_initial_consumer_group(
user =>'UGYB001',Consumer_group =>'general');
5、Execute DBMS_RESOURCE_MANAGER.delete_consumer_group(consumer_group=>'general');
6、Execute DBMS_RESOURCE_MANAGER.validate_pending_area();
7、Execute DBMS_RESOURCE_MANAGER.submit_pending_area();
8、Execute DBMS_RESOURCE_MANAGER.clear_pending_area();
9、dbms_resource_manager.DELETE_PLAN();
10、dbms_resource_manager.update_PLAN();
11、dbms_resource_manager.DELETE_PLAN_cascade();


   下面是一个用户查询时间超过1分钟就取消查询的完整的流程
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN =''；
   清除已经存在的活跃计划
2、exec dbms_resource_manager.clear_pending_area();
   清除已经存在的组和计划
3、exec dbms_resource_manager.create_pending_area();
4、exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
在删除该计划时请进行查询，如果没有该计划就不需要删除了
5、exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
在删除该组时请进行查询，如果没有该计划就不需要删除了
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',    COMMENT=>'此消耗组限制每个语句的查询时间');
exec dbms_resource_manager.set_consumer_group_mapping( attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,    value => 'UFLK01',    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'  );
11、
exec dbms_resource_manager.create_plan(PLAN=> 'LIMIT_EXEC_TIME',COMMENT=>'执行时间超过后杀掉语句');  );
12、
exec dbms_resource_manager.create_plan_directive(    PLAN=> 'LIMIT_EXEC_TIME',    GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',    COMMENT=>'执行时间超过后杀掉语句',    SWITCH_GROUP=>'CANCEL_SQL',    SWITCH_TIME=>60,    SWITCH_ESTIMATE=>false  );
13、
exec dbms_resource_manager.create_plan_directive(    PLAN=> 'LIMIT_EXEC_TIME',    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',    COMMENT=>'别管其它组',    CPU_P1=>100  );
14、
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
15、
Exec  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
16、
exec dbms_resource_manager_privs.grant_switch_consumer_group('UFLK01','GROUP_WITH_LIMITED_EXEC_TIME',false);
17、
exec dbms_resource_manager.set_initial_consumer_group('UFLK01','GROUP_WITH_LIMITED_EXEC_TIME');

18、
select * from DBA_RSRC_CONSUMER_GROUPS;
select * from DBA_RSRC_GROUP_MAPPINGS;
select * from DBA_RSRC_PLANS;
select * from DBA_RSRC_PLAN_DIRECTIVES;
19、
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';
20、
SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;
列出当前活跃计划的消费者组执行信息。
21、
select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;


16、

如果要添加新的oracle 用户进入GROUP_WITH_LIMITED_EXEC_TIME 组，需要执行以下两步：
1、dbms_resource_manager_privs.grant_switch_consumer_group('UFLK02','GROUP_WITH_LIMITED_EXEC_TIME',false);
2、
exec dbms_resource_manager.set_initial_consumer_group('UFLK02','GROUP_WITH_LIMITED_EXEC_TIME');

      类似的请思考，限制某些用户的CPU占用，不让它占用超过25%。
更详细的内容请参阅oracle 官方文档库。http://www.oracle.com/pls/db102/homepage  


特别提醒，由于是原创，欢迎剽窃作为你毕业论文以及职称文章，且不用通知本人。  

renxiao2003

不错。学习了啊。

duolanshizhe

evaspring

很详细啊 ~

dingning239

楼主研究地很细