Bo's Oracle Station

查看: 2766|回复: 0

第35次活动:2017-11-22(星期三晚上7:00-9:30)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-11-22 20:02:05 | 显示全部楼层 |阅读模式
  1. select  * from dba_rsrc_plan_directives d
  2.   where d.plan='PLAN1' ;
  3.   
  4.   ----
  5.   BEGIN
  6. dbms_resource_manager.clear_pending_area();
  7. dbms_resource_manager.create_pending_area();
  8. dbms_resource_manager.update_plan_directive(
  9.     plan => 'PLAN1',group_or_subplan => 'GROUP1',
  10.     new_switch_time=> -1
  11.    
  12. );
  13. dbms_resource_manager.submit_pending_area();
  14. END;
  15. ----
  16. select  bytes/1024/1024  from dba_segments s where s.segment_name='T_BIG';

  17. ----

  18. select s.USERNAME,s.RESOURCE_CONSUMER_GROUP, terminal
  19. from v_$session s
  20. where s.TERMINAL in ('pts/2');

  21. ----

  22. BEGIN
  23. dbms_resource_manager.clear_pending_area();
  24. dbms_resource_manager.create_pending_area();
  25. dbms_resource_manager.update_plan_directive(
  26.     plan => 'PLAN1',group_or_subplan => 'GROUP1',
  27.     new_switch_io_megabytes=> 20
  28.    
  29. );
  30. dbms_resource_manager.submit_pending_area();
  31. END;
  32. ---

  33. BEGIN
  34. dbms_resource_manager.clear_pending_area();
  35. dbms_resource_manager.create_pending_area();
  36. dbms_resource_manager.update_plan_directive(
  37.     plan => 'PLAN1',group_or_subplan => 'GROUP1',
  38.     new_max_idle_time=> 30
  39.    
  40. );
  41. dbms_resource_manager.submit_pending_area();
  42. END;
复制代码

Screenshot.png

如果要切进去的组活跃会话指标已满,就切不进去(在原组运行):
Screenshot-1.png

关于simple plan:
  1. begin
  2.    dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
  3.    consumer_group1 => 'GROUP1',
  4.    group1_cpu => 85,
  5.    consumer_group2 => 'GROUP2',
  6.    group2_cpu =>  15);
  7. end;
复制代码

Screenshot-2.png

max_utilization_limit:
  1. select  * from dba_rsrc_plan_directives d
  2.   where d.plan='PLAN1' ;
  3.   
  4. BEGIN
  5. dbms_resource_manager.clear_pending_area();
  6. dbms_resource_manager.create_pending_area();
  7. dbms_resource_manager.update_plan_directive(
  8.     plan => 'PLAN1',group_or_subplan => 'GROUP1',
  9.   new_max_utilization_limit => 90
  10.    
  11. );
  12. dbms_resource_manager.submit_pending_area();
  13. END;

复制代码
最终看CPU怎么控制:
Screenshot-3.png

  1. SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
  2. FROM v$rsrcmgrmetric_history
  3. ORDER BY begin_time;

  4. ----
  5. SELECT name, consumed_cpu_time, cpu_wait_time
  6. FROM v$rsrc_consumer_group;
复制代码
  1. select  * from dba_rsrc_group_mappings;

  2. select  s.USERNAME , s.RESOURCE_CONSUMER_GROUP,
  3. s.STATUS  from v_$session s
  4. where s.MACHINE ='WORKGROUP2\STATION199';

  5. BEGIN
  6. dbms_resource_manager.clear_pending_area();
  7. dbms_resource_manager.create_pending_area();
  8. dbms_resource_manager.set_consumer_group_mapping(
  9.     dbms_resource_manager.client_machine,
  10.     '192.168.0.199',
  11.     NULL
  12. );
  13. dbms_resource_manager.set_consumer_group_mapping(
  14.     dbms_resource_manager.client_machine,
  15.     'WORKGROUP2\STATION199',
  16.     'GROUP2'
  17. );
  18. dbms_resource_manager.submit_pending_area();
  19. END;






  20. BEGIN
  21. dbms_resource_manager.clear_pending_area();
  22. dbms_resource_manager.create_pending_area();
  23. dbms_resource_manager.set_consumer_group_mapping_pri(
  24.     EXPLICIT => 1,  CLIENT_MACHINE => 2,
  25.     SERVICE_MODULE_ACTION => 3,
  26.     SERVICE_MODULE => 4,
  27.     MODULE_NAME_ACTION => 5,
  28.     MODULE_NAME => 6,
  29.     SERVICE_NAME => 7,
  30.     ORACLE_USER => 8,
  31.     CLIENT_PROGRAM => 9,
  32.     CLIENT_OS_USER => 10
  33. );
  34. dbms_resource_manager.submit_pending_area();
  35. END;

  36. select  * from dba_rsrc_mapping_priority;


复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 21:36 , Processed in 0.063572 second(s), 27 queries .

快速回复 返回顶部 返回列表