设为首页收藏本站

Botang唐波's Oracle Station

查看: 21|回复: 0

资源消费者组和资源计划

[复制链接]

708

主题

1070

帖子

7759

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7759
发表于 2018-8-11 10:19:07 | 显示全部楼层 |阅读模式
资源消费者组创建的语句:
  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.create_consumer_group(consumer_group => ?,comment => ? , cpu_mth => ?);
  5. dbms_resource_manager.submit_pending_area();
  6. BEGIN
  7. dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
  8. END;END;
复制代码
让用户默认是有资格进的两个组的其中的一个:
  1. BEGIN
  2.     dbms_resource_manager.set_initial_consumer_group(
  3.         user => 'HR',
  4.         consumer_group => 'GROUP1'
  5.     );
  6. END;
复制代码
  1. DECLARE
  2. spfileValue VARCHAR2(1000);
  3. scopeValue VARCHAR2(10) := 'MEMORY';
  4. planName VARCHAR2(100) :=?;
  5. BEGIN
  6. dbms_resource_manager.clear_pending_area();
  7. dbms_resource_manager.create_pending_area();
  8. dbms_resource_manager.create_plan( plan => ?, comment => ?,max_iops => ?,max_mbps => ? );
  9. dbms_resource_manager.create_plan_directive(
  10.     plan => ?,
  11.     group_or_subplan => ?,
  12.     comment => ?,
  13.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
  14.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
  15.     parallel_degree_limit_p1 => ? ,
  16.     switch_io_reqs => ? ,
  17.    switch_io_megabytes => ?
  18. ,
  19.     active_sess_pool_p1 => ?,
  20.     queueing_p1 => ?,
  21.     switch_group => ?,
  22.     switch_time => ?,
  23.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
  24.     max_est_exec_time => ?,
  25.     undo_pool => ? ,
  26.     max_idle_time => ?,
  27.     max_idle_blocker_time => ?,
  28.     switch_for_call => case ? when 'false' then false when 'true' then true else false end

  29. );
  30. dbms_resource_manager.create_plan_directive(
  31.     plan => ?,
  32.     group_or_subplan => ?,
  33.     comment => ?,
  34.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
  35.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
  36.     parallel_degree_limit_p1 => ? ,
  37.     switch_io_reqs => ? ,
  38.    switch_io_megabytes => ?
  39. ,
  40.     active_sess_pool_p1 => ?,
  41.     queueing_p1 => ?,
  42.     switch_group => ?,
  43.     switch_time => ?,
  44.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
  45.     max_est_exec_time => ?,
  46.     undo_pool => ? ,
  47.     max_idle_time => ?,
  48.     max_idle_blocker_time => ?,
  49.     switch_for_call => case ? when 'false' then false when 'true' then true else false end

  50. );
  51. dbms_resource_manager.create_plan_directive(
  52.     plan => ?,
  53.     group_or_subplan => ?,
  54.     comment => ?,
  55.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
  56.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
  57.     parallel_degree_limit_p1 => ? ,
  58.     switch_io_reqs => ? ,
  59.    switch_io_megabytes => ?
  60. ,
  61.     active_sess_pool_p1 => ?,
  62.     queueing_p1 => ?,
  63.     switch_group => ?,
  64.     switch_time => ?,
  65.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
  66.     max_est_exec_time => ?,
  67.     undo_pool => ? ,
  68.     max_idle_time => ?,
  69.     max_idle_blocker_time => ?,
  70.     switch_for_call => case ? when 'false' then false when 'true' then true else false end

  71. );
  72. dbms_resource_manager.create_plan_directive(
  73.     plan => ?,
  74.     group_or_subplan => ?,
  75.     comment => ?,
  76.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
  77.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
  78.     parallel_degree_limit_p1 => ? ,
  79.     switch_io_reqs => ? ,
  80.    switch_io_megabytes => ?
  81. ,
  82.     active_sess_pool_p1 => ?,
  83.     queueing_p1 => ?,
  84.     switch_group => ?,
  85.     switch_time => ?,
  86.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
  87.     max_est_exec_time => ?,
  88.     undo_pool => ? ,
  89.     max_idle_time => ?,
  90.     max_idle_blocker_time => ?,
  91.     switch_for_call => case ? when 'false' then false when 'true' then true else false end

  92. );
  93. dbms_resource_manager.submit_pending_area();
  94. select value into spfileValue from v$parameter where name = 'spfile';
  95. IF spfileValue IS NOT NULL then
  96. EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
  97. END IF;
  98. dbms_resource_manager.switch_plan( plan_name => ? , sid => ? , allow_scheduler_plan_switches => FALSE );
  99. END;
复制代码
  1. select * from dba_rsrc_consumer_groups;
复制代码
   CONSUMER_GROUP_IDCONSUMER_GROUPCPU_METHODMGMT_METHODINTERNAL_USECOMMENTSCATEGORYSTATUSMANDATORY
112162ORA$AUTOTASK_SPACE_GROUPROUND-ROBINROUND-ROBINYESConsumer group for space management advisorsMAINTENANCE YES
212163ORA$AUTOTASK_STATS_GROUPROUND-ROBINROUND-ROBINYESConsumer group for gathering optimizer statisticsMAINTENANCE YES
312164ORA$AUTOTASK_MEDIUM_GROUPROUND-ROBINROUND-ROBINYESConsumer group for medium-priority maintenance tasks MAINTENANCE YES
412169LOW_GROUPROUND-ROBINROUND-ROBINNOConsumer group for low-priority sessionsOTHER NO
512170ORA$APPQOS_0ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
612171ORA$APPQOS_1ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
712172ORA$APPQOS_2ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
812176ORA$APPQOS_6ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
912177ORA$APPQOS_7ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
1012178ETL_GROUPROUND-ROBINROUND-ROBINNOConsumer group for ETLBATCH NO
1178977GROUP1ROUND-ROBINROUND-ROBINNOgroup1OTHER NO
1278978GROUP2ROUND-ROBINROUND-ROBINNOgroup2OTHER NO
1312173ORA$APPQOS_3ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
1412174ORA$APPQOS_4ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
1512175ORA$APPQOS_5ROUND-ROBINROUND-ROBINNOConsumer group for Application QOSINTERACTIVE YES
1612179DSS_GROUPROUND-ROBINROUND-ROBINNOConsumer group for DSS queriesBATCH NO
1712180DSS_CRITICAL_GROUPROUND-ROBINROUND-ROBINNOConsumer group for critical DSS queriesINTERACTIVE NO
1812315AUTO_TASK_CONSUMER_GROUPROUND-ROBINROUND-ROBINNOSystem maintenance task consumer groupOTHER NO
1912157ORA$AUTOTASK_URGENT_GROUPROUND-ROBINROUND-ROBINYESConsumer group for urgent maintenance tasks MAINTENANCE YES
2012158BATCH_GROUPROUND-ROBINROUND-ROBINNOConsumer group for batch operationsBATCH NO
2112165INTERACTIVE_GROUPROUND-ROBINROUND-ROBINNOConsumer group for interactive, OLTP operationsINTERACTIVE NO
2212166OTHER_GROUPSROUND-ROBINROUND-ROBINNOConsumer group for users not included in any consumer group with a directive in the currently active planOTHER YES
2312167DEFAULT_CONSUMER_GROUPROUND-ROBINROUND-ROBINNOConsumer group for users not assigned to any consumer groupOTHER YES
2412168SYS_GROUPROUND-ROBINROUND-ROBINNOConsumer group for system administratorsADMINISTRATIVE YES
2512159ORA$DIAGNOSTICSROUND-ROBINROUND-ROBINYESConsumer group for diagnosticsMAINTENANCE YES
2612160ORA$AUTOTASK_HEALTH_GROUPROUND-ROBINROUND-ROBINYESConsumer group for health checksMAINTENANCE YES
2712161ORA$AUTOTASK_SQL_GROUPROUND-ROBINROUND-ROBINYESConsumer group for SQL tuningMAINTENANCE YES

  1. select  * from dba_rsrc_plans;
复制代码
   PLAN_IDPLANNUM_PLAN_DIRECTIVESCPU_METHODMGMT_METHODACTIVE_SESS_POOL_MTHPARALLEL_DEGREE_LIMIT_MTHQUEUEING_MTHSUB_PLANCOMMENTSSTATUSMANDATORY
112149ORA$AUTOTASK_HIGH_SUB_PLAN4EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTYESDefault sub-plan for high-priority, automated maintenance tasks.  This sub-plan is referenced by ORA$AUTOTASK_SUB_PLAN and should not be referenced directly. YES
212150DEFAULT_MAINTENANCE_PLAN4EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNODefault plan for maintenance windows that prioritizes SYS_GROUP operations and allocates the remaining 5% to diagnostic operations and 25% to automated maintenance operations. YES
312151DEFAULT_PLAN4EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNODefault, basic, pre-defined plan that prioritizes SYS_GROUP operations and allocates minimal resources for automated maintenance and diagnostics operations. YES
412152INTERNAL_QUIESCE2EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOPlan for quiescing the database.  This plan cannot be activated directly.  To activate, use the quiesce command. YES
512153INTERNAL_PLAN1EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOInternally-used plan for disabling the resource manager. YES
678979PLAN14EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOplan1 NO
712155DSS_PLAN8EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOExample plan for DSS workloads that prioritizes DSS queries over ETL. NO
812156ETL_CRITICAL_PLAN8EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOExample plan for DSS workloads that prioritizes ETL and critical DSS queries. NO
912147MIXED_WORKLOAD_PLAN6EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOExample plan for a mixed workload that prioritizes interactive operations over batch operations. NO
1012148ORA$AUTOTASK_SUB_PLAN3EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTYESDefault sub-plan for automated maintenance tasks.  A directive to this sub-plan should be included in every top-level plan to manage the resources consumed by the automated maintenance tasks. YES
1112154APPQOS_PLAN7EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOPlan for Application QOS Management that provides a fixed set of allocations to the consumer groups that Application QOS uses to manage workload resource allocation. YES

  1. select   * from dba_rsrc_plan_directives  where plan='PLAN1';
复制代码
2.png

-----------------------------------------------------------
3.png

下面做一个ratio的:
  1. BEGIN
  2.     dbms_resource_manager.clear_pending_area();
  3.     dbms_resource_manager.create_pending_area();
  4.     dbms_resource_manager.create_plan( plan => 'PLAN2',
  5.                                                                    comment => 'PLAN2',
  6.                                                                    cpu_mth =>'RATIO'
  7.                                                                     );
  8.     dbms_resource_manager.create_plan_directive(
  9.         plan => 'PLAN2',
  10.         group_or_subplan => 'SYS_GROUP',
  11.         comment => 'SYS_GROUP',
  12.         mgmt_p1 => 70
  13.             );
  14.     dbms_resource_manager.create_plan_directive(
  15.         plan =>'PLAN2' ,
  16.         group_or_subplan => 'OTHER_GROUPS',
  17.         comment => 'OTHER_GROUPS',
  18.         mgmt_p1 => 2
  19.     );
  20.     dbms_resource_manager.create_plan_directive(
  21.         plan => 'PLAN2',
  22.         group_or_subplan => 'GROUP1',
  23.         comment => 'GROUP1',
  24.         mgmt_p1 => 20
  25.     );
  26.     dbms_resource_manager.create_plan_directive(
  27.         plan => 'PLAN2',
  28.         group_or_subplan => 'GROUP2',
  29.         comment => 'GROUP2',
  30.         mgmt_p1 => 8 );
  31.       dbms_resource_manager.submit_pending_area;
  32.     END;
复制代码
  1. select  * from dba_rsrc_plans  p
  2.   where p.plan='PLAN2';
复制代码
   PLAN_IDPLANNUM_PLAN_DIRECTIVESCPU_METHODMGMT_METHODACTIVE_SESS_POOL_MTHPARALLEL_DEGREE_LIMIT_MTHQUEUEING_MTHSUB_PLANCOMMENTSSTATUSMANDATORY
194845PLAN24RATIORATIOACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOPLAN2 NO

  1. select   * from dba_rsrc_plan_directives  where plan='PLAN2';
复制代码
   PLANGROUP_OR_SUBPLANTYPECPU_P1CPU_P2CPU_P3CPU_P4CPU_P5CPU_P6CPU_P7CPU_P8MGMT_P1MGMT_P2MGMT_P3MGMT_P4MGMT_P5MGMT_P6MGMT_P7MGMT_P8ACTIVE_SESS_POOL_P1QUEUEING_P1PARALLEL_TARGET_PERCENTAGEPARALLEL_DEGREE_LIMIT_P1SWITCH_GROUPSWITCH_FOR_CALLSWITCH_TIMESWITCH_IO_MEGABYTESSWITCH_IO_REQSSWITCH_ESTIMATEMAX_EST_EXEC_TIMEUNDO_POOLMAX_IDLE_TIMEMAX_IDLE_BLOCKER_TIMEMAX_UTILIZATION_LIMITPARALLEL_QUEUE_TIMEOUTSWITCH_TIME_IN_CALLSWITCH_IO_LOGICALSWITCH_ELAPSED_TIMEPARALLEL_SERVER_LIMITUTILIZATION_LIMITPARALLEL_STMT_CRITICALCOMMENTSSTATUSMANDATORY
1PLAN2GROUP2CONSUMER_GROUP8000000080000000 FALSE FALSE FALSEGROUP2 NO
2PLAN2SYS_GROUPCONSUMER_GROUP700000000700000000 FALSE FALSE FALSESYS_GROUP NO
3PLAN2OTHER_GROUPSCONSUMER_GROUP2000000020000000 FALSE FALSE FALSEOTHER_GROUPS NO
4PLAN2GROUP1CONSUMER_GROUP200000000200000000 FALSE FALSE FALSEGROUP1 NO

RATIO的不能有第二级CPU分配:
4.png

修改上限:
  1. begin
  2.   dbms_resource_manager.clear_pending_area;
  3.   dbms_resource_manager.create_pending_area;
  4.   dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
  5.                                         group_or_subplan =>'SYS_GROUP',
  6.                                          new_max_utilization_limit => 100);                                          
  7.   dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
  8.                                         group_or_subplan =>'OTHER_GROUPS',
  9.                                          new_max_utilization_limit => 30);
  10.   dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
  11.                                         group_or_subplan =>'GROUP1',
  12.                                          new_max_utilization_limit => 50);
  13.    dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
  14.                                         group_or_subplan =>'GROUP2',
  15.                                          new_max_utilization_limit => 8);
  16.    dbms_resource_manager.submit_pending_area;
  17. end;
复制代码

上下限都设置好的情况:
  1. select   d.plan, d.group_or_subplan,d.mgmt_p1,d.max_utilization_limit
  2. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码
   PLANGROUP_OR_SUBPLANMGMT_P1MAX_UTILIZATION_LIMIT
1PLAN1GROUP12050
2PLAN1GROUP288
3PLAN1OTHER_GROUPS230
4PLAN1SYS_GROUP70100

----------------------------------------------------------------------------------------------------------并行度(没有子计划)
  1. DECLARE
  2. spfileValue VARCHAR2(1000);
  3. execText VARCHAR2(1000);
  4. scopeValue VARCHAR2(30) := 'MEMORY';
  5. planName VARCHAR2(100) :='PLAN1';
  6. BEGIN
  7. dbms_resource_manager.clear_pending_area();
  8. dbms_resource_manager.create_pending_area();
  9. dbms_resource_manager.update_plan_directive(
  10.     plan => 'PLAN1',
  11.     group_or_subplan => 'GROUP1',
  12.     new_comment => '',
  13.     new_switch_elapsed_time => NULL,
  14.     new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  15.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  16.     new_parallel_degree_limit_p1 => 1 ,
  17.     new_parallel_target_percentage => NULL ,
  18.     new_parallel_queue_timeout => NULL ,
  19.     new_parallel_stmt_critical => 'false' ,
  20.     new_switch_io_logical => NULL ,
  21.     new_switch_io_reqs => NULL,
  22.     new_switch_io_megabytes => NULL ,
  23.     new_active_sess_pool_p1 => NULL,
  24.     new_queueing_p1 => NULL,
  25.     new_switch_group => NULL,
  26.     new_switch_time => NULL,
  27.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  28.     new_undo_pool => NULL ,
  29.     new_max_idle_time => NULL,
  30.     new_max_idle_blocker_time => NULL,
  31.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  32. );
  33. dbms_resource_manager.update_plan_directive(
  34.     plan => 'PLAN1',
  35.     group_or_subplan => 'GROUP2',
  36.     new_comment => '',
  37.     new_switch_elapsed_time => NULL,
  38.     new_mgmt_p1 => 8, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  39.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  40.     new_parallel_degree_limit_p1 => NULL ,
  41.     new_parallel_target_percentage => NULL ,
  42.     new_parallel_queue_timeout => NULL ,
  43.     new_parallel_stmt_critical => 'false' ,
  44.     new_switch_io_logical => NULL ,
  45.     new_switch_io_reqs => NULL,
  46.     new_switch_io_megabytes => NULL ,
  47.     new_active_sess_pool_p1 => NULL,
  48.     new_queueing_p1 => NULL,
  49.     new_switch_group => NULL,
  50.     new_switch_time => NULL,
  51.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  52.     new_undo_pool => NULL ,
  53.     new_max_idle_time => NULL,
  54.     new_max_idle_blocker_time => NULL,
  55.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  56. );
  57. dbms_resource_manager.update_plan_directive(
  58.     plan => 'PLAN1',
  59.     group_or_subplan => 'OTHER_GROUPS',
  60.     new_comment => '',
  61.     new_switch_elapsed_time => NULL,
  62.     new_mgmt_p1 => 2, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  63.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  64.     new_parallel_degree_limit_p1 => NULL ,
  65.     new_parallel_target_percentage => NULL ,
  66.     new_parallel_queue_timeout => NULL ,
  67.     new_parallel_stmt_critical => 'false' ,
  68.     new_switch_io_logical => NULL ,
  69.     new_switch_io_reqs => NULL,
  70.     new_switch_io_megabytes => NULL ,
  71.     new_active_sess_pool_p1 => NULL,
  72.     new_queueing_p1 => NULL,
  73.     new_switch_group => NULL,
  74.     new_switch_time => NULL,
  75.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  76.     new_undo_pool => NULL ,
  77.     new_max_idle_time => NULL,
  78.     new_max_idle_blocker_time => NULL,
  79.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  80. );
  81. dbms_resource_manager.update_plan_directive(
  82.     plan => 'PLAN1',
  83.     group_or_subplan => 'SYS_GROUP',
  84.     new_comment => '',
  85.     new_switch_elapsed_time => NULL,
  86.     new_mgmt_p1 => 65, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  87.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  88.     new_parallel_degree_limit_p1 => NULL ,
  89.     new_parallel_target_percentage => NULL ,
  90.     new_parallel_queue_timeout => NULL ,
  91.     new_parallel_stmt_critical => 'false' ,
  92.     new_switch_io_logical => NULL ,
  93.     new_switch_io_reqs => NULL,
  94.     new_switch_io_megabytes => NULL ,
  95.     new_active_sess_pool_p1 => NULL,
  96.     new_queueing_p1 => NULL,
  97.     new_switch_group => NULL,
  98.     new_switch_time => NULL,
  99.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  100.     new_undo_pool => NULL ,
  101.     new_max_idle_time => NULL,
  102.     new_max_idle_blocker_time => NULL,
  103.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  104. );
  105. dbms_resource_manager.submit_pending_area();
  106. END;
复制代码

--------
[root@station86 ~]# watch -n 0.1 "ps aux | grep ora_p0"
同时进行以下操作:
  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> !ps
  4.   PID TTY          TIME CMD
  5. 2031 pts/3    00:00:00 sqlplus
  6. 4294 pts/3    00:00:00 ps
复制代码
  1. select  * from v$rsrc_plan;

  2. select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
  3. from v_$session s
  4. where s.TERMINAL='pts/3';
复制代码
以上观察不到并行度,
改变:
  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.update_plan_directive(
  5.     plan => 'PLAN1',
  6.     group_or_subplan => 'GROUP1',
  7.     new_parallel_degree_limit_p1 => -1
  8.     );
  9. dbms_resource_manager.submit_pending_area;
  10. end;
复制代码
  1. select   d.plan, d.group_or_subplan,d.parallel_degree_limit_p1
  2. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码

   PLANGROUP_OR_SUBPLANPARALLEL_DEGREE_LIMIT_P1
1PLAN1OTHER_GROUPS
2PLAN1SYS_GROUP
3PLAN1GROUP1
4PLAN1GROUP2
5PLAN1PLAN2

5.png

  1. SQL> create index i_big  on t_big ( line )  parallel 40 ;

  2. Index created.

  3. SQL> set linesize 10000
  4. SQL> select  * from v$pq_sesstat;

  5. STATISTIC                       LAST_QUERY SESSION_TOTAL
  6. ------------------------------ ---------- -------------
  7. Queries Parallelized                        0              0
  8. DML Parallelized                        0              0
  9. DDL Parallelized                        1              1
  10. DFO Trees                                1              1
  11. Server Threads                                8              0
  12. Allocation Height                        4              0
  13. Allocation Width                        1              0
  14. Local Msgs Sent                      5381           5381
  15. Distr Msgs Sent                         0              0
  16. Local Msgs Recv'd                     5381           5381
  17. Distr Msgs Recv'd                        0              0

  18. 11 rows selected.

  19. SQL>
复制代码
1.png

  1. select   d.plan, d.group_or_subplan,d.active_sess_pool_p1,d.queueing_p1
  2. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码
   PLANGROUP_OR_SUBPLANACTIVE_SESS_POOL_P1QUEUEING_P1
1PLAN1PLAN2
2PLAN1SYS_GROUP
3PLAN1GROUP124
4PLAN1GROUP2
5PLAN1OTHER_GROUPS

2.png

  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.update_plan_directive(
  5.     plan => 'PLAN1',
  6.     group_or_subplan => 'GROUP1',
  7.     new_active_sess_pool_p1 => -1,
  8.     new_queueing_p1=>-1
  9.     );
  10. dbms_resource_manager.submit_pending_area;
  11. end;
复制代码
undo池:
  1. select   d.plan, d.group_or_subplan,d.undo_pool
  2. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码
   PLANGROUP_OR_SUBPLANUNDO_POOL
1PLAN1OTHER_GROUPS
2PLAN1GROUP2
3PLAN1PLAN2
4PLAN1SYS_GROUP
5PLAN1GROUP18

undo池中两个表加起来算,两个会话也是加起来算的:
3.png

  1.   BEGIN
  2.     dbms_resource_manager.clear_pending_area();
  3.     dbms_resource_manager.create_pending_area();
  4.     dbms_resource_manager.update_plan_directive(
  5.         plan => 'PLAN1',
  6.         group_or_subplan => 'GROUP1',
  7.         new_undo_pool => -1      
  8.         );
  9.     dbms_resource_manager.submit_pending_area;
  10.     end;
复制代码

----------------------------------------------------------组切换:
6.png
  1. select   d.plan, d.group_or_subplan,
  2.            d.switch_group,
  3.            d.switch_for_call,
  4.            d.switch_time,
  5.            d.switch_estimate,
  6.            d.max_est_exec_time
  7. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码
   PLANGROUP_OR_SUBPLANSWITCH_GROUPSWITCH_FOR_CALLSWITCH_TIMESWITCH_ESTIMATEMAX_EST_EXEC_TIME
1PLAN1SYS_GROUP FALSE FALSE
2PLAN1GROUP1GROUP2TRUE5TRUE
3PLAN1OTHER_GROUPS TRUE FALSE
4PLAN1GROUP2 TRUE FALSE
5PLAN1PLAN2 FALSE FALSE

  1. BEGIN
  2.     dbms_resource_manager.clear_pending_area();
  3.     dbms_resource_manager.create_pending_area();
  4.     dbms_resource_manager.update_plan_directive(
  5.         plan => 'PLAN1',
  6.         group_or_subplan => 'GROUP1',
  7.         new_switch_for_call =>  true  ,
  8.         new_switch_estimate=>  false
  9.         );
  10.     dbms_resource_manager.submit_pending_area;
  11.     end;
复制代码

  1. select   d.plan, d.group_or_subplan,
  2.            d.switch_group,
  3.            d.switch_for_call,
  4.            d.switch_io_megabytes,
  5.            d.switch_io_reqs,
  6.            d.switch_estimate
  7. from dba_rsrc_plan_directives  d where plan='PLAN1';
复制代码
   
PLANGROUP_OR_SUBPLANSWITCH_GROUPSWITCH_FOR_CALLSWITCH_IO_MEGABYTESSWITCH_IO_REQSSWITCH_ESTIMATE
1PLAN1OTHER_GROUPS TRUE FALSE
2PLAN1GROUP2 TRUE FALSE
3PLAN1PLAN2 FALSE FALSE
4PLAN1GROUP1GROUP2FALSE40 FALSE
5PLAN1SYS_GROUP FALSE FALSE

组间切换优先于一切:
7.png


IDEL time对照一下PROFILE:
resource_limit 要为true。

ALTER PROFILE "PROFILE1" LIMIT CONNECT_TIME 2
IDLE_TIME 2

SQL> select  * from employees;
select    * from employees
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again


  1. DECLARE
  2. spfileValue VARCHAR2(1000);
  3. execText VARCHAR2(1000);
  4. scopeValue VARCHAR2(30) := 'MEMORY';
  5. planName VARCHAR2(100) :='PLAN1';
  6. BEGIN
  7. dbms_resource_manager.clear_pending_area();
  8. dbms_resource_manager.create_pending_area();
  9. dbms_resource_manager.update_plan_directive(
  10.     plan => 'PLAN1',
  11.     group_or_subplan => 'GROUP1',
  12.     new_comment => '',
  13.     new_switch_elapsed_time => NULL,
  14.     new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  15.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  16.     new_parallel_degree_limit_p1 => NULL ,
  17.     new_parallel_target_percentage => NULL ,
  18.     new_parallel_queue_timeout => NULL ,
  19.     new_parallel_stmt_critical => 'false' ,
  20.     new_switch_io_logical => NULL ,
  21.     new_switch_io_reqs => NULL,
  22.     new_switch_io_megabytes => NULL ,
  23.     new_active_sess_pool_p1 => NULL,
  24.     new_queueing_p1 => NULL,
  25.     new_switch_group => NULL,
  26.     new_switch_time => NULL,
  27.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  28.     new_undo_pool => NULL ,
  29.     new_max_idle_time => 60,
  30.     new_max_idle_blocker_time => NULL,
  31.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  32. );
  33. dbms_resource_manager.submit_pending_area();
  34. END;
复制代码

SQL> select  * from employees;
select    * from employees
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 19055
Session ID: 514 Serial number: 23539





--------------------------------------------------------------------------------


simple plan就是迅速上手:
12c的simple plan 是ratio的:
  1.   begin
  2.      dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
  3.      consumer_group1 => 'GROUP1',group1_cpu => 80,
  4.      consumer_group2 => 'GROUP2',group2_cpu => 20 );
  5.   end;
复制代码
  1. select  * from dba_rsrc_plan_directives
  2. where plan='PLAN3';
复制代码
   PLANGROUP_OR_SUBPLANTYPECPU_P1CPU_P2CPU_P3CPU_P4CPU_P5CPU_P6CPU_P7CPU_P8MGMT_P1MGMT_P2MGMT_P3MGMT_P4MGMT_P5MGMT_P6MGMT_P7MGMT_P8ACTIVE_SESS_POOL_P1QUEUEING_P1PARALLEL_TARGET_PERCENTAGEPARALLEL_DEGREE_LIMIT_P1SWITCH_GROUPSWITCH_FOR_CALLSWITCH_TIMESWITCH_IO_MEGABYTESSWITCH_IO_REQSSWITCH_ESTIMATEMAX_EST_EXEC_TIMEUNDO_POOLMAX_IDLE_TIMEMAX_IDLE_BLOCKER_TIMEMAX_UTILIZATION_LIMITPARALLEL_QUEUE_TIMEOUTSWITCH_TIME_IN_CALLSWITCH_IO_LOGICALSWITCH_ELAPSED_TIMEPARALLEL_SERVER_LIMITUTILIZATION_LIMITPARALLEL_STMT_CRITICALCOMMENTSSTATUSMANDATORY
1PLAN3SYS_GROUPCONSUMER_GROUP500000000500000000 FALSE FALSE FALSESys group NO
2PLAN3OTHER_GROUPSCONSUMER_GROUP5000000050000000 FALSE FALSE FALSEOther groups NO
3PLAN3GROUP1CONSUMER_GROUP800000000800000000 FALSE FALSE FALSEGroup 1 NO
4PLAN3GROUP2CONSUMER_GROUP200000000200000000 FALSE FALSE FALSEGroup 2 NO

  1. select  * from dba_rsrc_plans  where plan='PLAN3';
复制代码
   PLAN_IDPLANNUM_PLAN_DIRECTIVESCPU_METHODMGMT_METHODACTIVE_SESS_POOL_MTHPARALLEL_DEGREE_LIMIT_MTHQUEUEING_MTHSUB_PLANCOMMENTSSTATUSMANDATORY
194850PLAN34RATIORATIOACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOSimple plan NO

11g 的simple plan:
  1. select * from dba_rsrc_plan_directives
  2. where plan='PLAN3';
复制代码
   PLANGROUP_OR_SUBPLANTYPECPU_P1CPU_P2CPU_P3CPU_P4CPU_P5CPU_P6CPU_P7CPU_P8MGMT_P1MGMT_P2MGMT_P3MGMT_P4MGMT_P5MGMT_P6MGMT_P7MGMT_P8ACTIVE_SESS_POOL_P1QUEUEING_P1PARALLEL_DEGREE_LIMIT_P1SWITCH_GROUPSWITCH_FOR_CALLSWITCH_TIMESWITCH_IO_MEGABYTESSWITCH_IO_REQSSWITCH_ESTIMATEMAX_EST_EXEC_TIMEUNDO_POOLMAX_IDLE_TIMEMAX_IDLE_BLOCKER_TIMEMAX_UTILIZATION_LIMITSWITCH_TIME_IN_CALLCOMMENTSSTATUSMANDATORY
1PLAN3GROUP1CONSUMER_GROUP080000000080000000 FALSE FALSE Level 2 Group 1 NO
2PLAN3SYS_GROUPCONSUMER_GROUP10000000001000000000 FALSE FALSE SYS Level 1 NO
3PLAN3OTHER_GROUPSCONSUMER_GROUP00100000000010000000 FALSE FALSE OTHER_GROUPS Level 3 NO
4PLAN3GROUP2CONSUMER_GROUP020000000020000000 FALSE FALSE Level 2 Group 2 NO

  1. select * from dba_rsrc_plans where plan='PLAN3';
复制代码
   PLAN_IDPLANNUM_PLAN_DIRECTIVESCPU_METHODMGMT_METHODACTIVE_SESS_POOL_MTHPARALLEL_DEGREE_LIMIT_MTHQUEUEING_MTHSUB_PLANCOMMENTSSTATUSMANDATORY
178993PLAN34EMPHASISEMPHASISACTIVE_SESS_POOL_ABSOLUTEPARALLEL_DEGREE_LIMIT_ABSOLUTEFIFO_TIMEOUTNOSimple plan NO

---------------------------------------------
查看资源计划控制历史:
  1.    SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
  2. FROM v$rsrcmgrmetric_history
  3. ORDER BY begin_time;
复制代码
   BEGIN_TIMECONSUMER_GROUP_NAMECPU_CONSUMED_TIMECPU_WAIT_TIME
18/11/2018 4:19:36 PMORA$AUTOTASK150
28/11/2018 4:19:36 PM_ORACLE_BACKGROUND_GROUP_00
38/11/2018 4:19:36 PMSYS_GROUP941
48/11/2018 4:19:36 PMOTHER_GROUPS851
58/11/2018 4:20:36 PMORA$AUTOTASK00
68/11/2018 4:20:36 PM_ORACLE_BACKGROUND_GROUP_00
78/11/2018 4:20:36 PMSYS_GROUP00
88/11/2018 4:20:36 PMOTHER_GROUPS221
98/11/2018 4:21:35 PMORA$AUTOTASK130
108/11/2018 4:21:35 PM_ORACLE_BACKGROUND_GROUP_00
118/11/2018 4:21:35 PMSYS_GROUP100
128/11/2018 4:21:35 PMOTHER_GROUPS20
138/11/2018 4:22:36 PMORA$AUTOTASK130
148/11/2018 4:22:36 PM_ORACLE_BACKGROUND_GROUP_00
158/11/2018 4:22:36 PMSYS_GROUP00
168/11/2018 4:22:36 PMOTHER_GROUPS882
178/11/2018 4:23:36 PMORA$AUTOTASK660
188/11/2018 4:23:36 PM_ORACLE_BACKGROUND_GROUP_00
198/11/2018 4:23:36 PMSYS_GROUP90
208/11/2018 4:23:36 PMOTHER_GROUPS51
218/11/2018 4:24:36 PMORA$AUTOTASK140
228/11/2018 4:24:36 PM_ORACLE_BACKGROUND_GROUP_00
238/11/2018 4:24:36 PMSYS_GROUP30
248/11/2018 4:24:36 PMOTHER_GROUPS141
258/11/2018 4:25:36 PMORA$AUTOTASK150
268/11/2018 4:25:36 PM_ORACLE_BACKGROUND_GROUP_00
278/11/2018 4:25:36 PMSYS_GROUP110
288/11/2018 4:25:36 PMOTHER_GROUPS42
298/11/2018 4:26:36 PMORA$AUTOTASK150
308/11/2018 4:26:36 PM_ORACLE_BACKGROUND_GROUP_00
318/11/2018 4:26:36 PMSYS_GROUP00
328/11/2018 4:26:36 PMOTHER_GROUPS51
338/11/2018 4:27:36 PMORA$AUTOTASK120
348/11/2018 4:27:36 PM_ORACLE_BACKGROUND_GROUP_00
358/11/2018 4:27:36 PMSYS_GROUP100
368/11/2018 4:27:36 PMOTHER_GROUPS81
378/11/2018 4:28:36 PMORA$AUTOTASK320
388/11/2018 4:28:36 PM_ORACLE_BACKGROUND_GROUP_00
398/11/2018 4:28:36 PMSYS_GROUP00
408/11/2018 4:28:36 PMOTHER_GROUPS161
418/11/2018 4:29:36 PMORA$AUTOTASK140
428/11/2018 4:29:36 PM_ORACLE_BACKGROUND_GROUP_00
438/11/2018 4:29:36 PMSYS_GROUP5890
448/11/2018 4:29:36 PMOTHER_GROUPS1383
458/11/2018 4:30:36 PMORA$AUTOTASK140
468/11/2018 4:30:36 PM_ORACLE_BACKGROUND_GROUP_00
478/11/2018 4:30:36 PMSYS_GROUP60
488/11/2018 4:30:36 PMOTHER_GROUPS202
498/11/2018 4:31:36 PMORA$AUTOTASK130
508/11/2018 4:31:36 PM_ORACLE_BACKGROUND_GROUP_00
518/11/2018 4:31:36 PMSYS_GROUP280
528/11/2018 4:31:36 PMOTHER_GROUPS61
538/11/2018 4:32:35 PMORA$AUTOTASK530
548/11/2018 4:32:35 PM_ORACLE_BACKGROUND_GROUP_00
558/11/2018 4:32:35 PMSYS_GROUP00
568/11/2018 4:32:35 PMOTHER_GROUPS403
578/11/2018 4:33:36 PMORA$AUTOTASK490
588/11/2018 4:33:36 PM_ORACLE_BACKGROUND_GROUP_00
598/11/2018 4:33:36 PMSYS_GROUP49351
608/11/2018 4:33:36 PMOTHER_GROUPS41
618/11/2018 4:34:35 PMORA$AUTOTASK350
628/11/2018 4:34:35 PM_ORACLE_BACKGROUND_GROUP_00
638/11/2018 4:34:35 PMSYS_GROUP31390
648/11/2018 4:34:35 PMOTHER_GROUPS1330
658/11/2018 4:35:36 PMORA$AUTOTASK140
668/11/2018 4:35:36 PM_ORACLE_BACKGROUND_GROUP_00
678/11/2018 4:35:36 PMSYS_GROUP120
688/11/2018 4:35:36 PMOTHER_GROUPS31
698/11/2018 4:36:35 PMORA$AUTOTASK140
708/11/2018 4:36:35 PM_ORACLE_BACKGROUND_GROUP_00
718/11/2018 4:36:35 PMSYS_GROUP00
728/11/2018 4:36:35 PMOTHER_GROUPS30
738/11/2018 4:37:36 PMORA$AUTOTASK150
748/11/2018 4:37:36 PM_ORACLE_BACKGROUND_GROUP_00
758/11/2018 4:37:36 PMSYS_GROUP4981
768/11/2018 4:37:36 PMOTHER_GROUPS1331
778/11/2018 4:39:36 PMORA$AUTOTASK340
788/11/2018 4:39:36 PM_ORACLE_BACKGROUND_GROUP_00
798/11/2018 4:39:36 PMSYS_GROUP870
808/11/2018 4:39:36 PMOTHER_GROUPS887
818/11/2018 4:40:36 PMORA$AUTOTASK140
828/11/2018 4:40:36 PM_ORACLE_BACKGROUND_GROUP_00
838/11/2018 4:40:36 PMSYS_GROUP10450
848/11/2018 4:40:36 PMOTHER_GROUPS372
858/11/2018 4:41:36 PMORA$AUTOTASK140
868/11/2018 4:41:36 PM_ORACLE_BACKGROUND_GROUP_00
878/11/2018 4:41:36 PMSYS_GROUP810
888/11/2018 4:41:36 PMOTHER_GROUPS51
898/11/2018 4:42:36 PMORA$AUTOTASK250
908/11/2018 4:42:36 PM_ORACLE_BACKGROUND_GROUP_00
918/11/2018 4:42:36 PMSYS_GROUP4670
928/11/2018 4:42:36 PMOTHER_GROUPS2752
938/11/2018 4:43:36 PMORA$AUTOTASK600
948/11/2018 4:43:36 PM_ORACLE_BACKGROUND_GROUP_00
958/11/2018 4:43:36 PMSYS_GROUP200
968/11/2018 4:43:36 PMOTHER_GROUPS3081
978/11/2018 4:44:36 PMORA$AUTOTASK4790
988/11/2018 4:44:36 PM_ORACLE_BACKGROUND_GROUP_00
998/11/2018 4:44:36 PMSYS_GROUP1170
1008/11/2018 4:44:36 PMOTHER_GROUPS3070
1018/11/2018 4:45:36 PMORA$AUTOTASK130
1028/11/2018 4:45:36 PM_ORACLE_BACKGROUND_GROUP_00
1038/11/2018 4:45:36 PMSYS_GROUP6000
1048/11/2018 4:45:36 PMOTHER_GROUPS11
1058/11/2018 4:46:36 PMORA$AUTOTASK140
1068/11/2018 4:46:36 PM_ORACLE_BACKGROUND_GROUP_00
1078/11/2018 4:46:36 PMSYS_GROUP00
1088/11/2018 4:46:36 PMOTHER_GROUPS20
1098/11/2018 4:47:36 PMORA$AUTOTASK3170
1108/11/2018 4:47:36 PM_ORACLE_BACKGROUND_GROUP_00
1118/11/2018 4:47:36 PMSYS_GROUP740
1128/11/2018 4:47:36 PMOTHER_GROUPS921
1138/11/2018 4:48:36 PMORA$AUTOTASK140
1148/11/2018 4:48:36 PM_ORACLE_BACKGROUND_GROUP_00
1158/11/2018 4:48:36 PMSYS_GROUP00
1168/11/2018 4:48:36 PMOTHER_GROUPS1281
1178/11/2018 4:49:36 PMORA$AUTOTASK2140
1188/11/2018 4:49:36 PM_ORACLE_BACKGROUND_GROUP_00
1198/11/2018 4:49:36 PMSYS_GROUP280
1208/11/2018 4:49:36 PMOTHER_GROUPS2252
1218/11/2018 4:50:36 PMORA$AUTOTASK150
1228/11/2018 4:50:36 PM_ORACLE_BACKGROUND_GROUP_00
1238/11/2018 4:50:36 PMSYS_GROUP510
1248/11/2018 4:50:36 PMOTHER_GROUPS481
1258/11/2018 4:51:36 PMORA$AUTOTASK130
1268/11/2018 4:51:36 PMOTHER_GROUPS241
1278/11/2018 4:51:36 PMSYS_GROUP100
1288/11/2018 4:51:36 PM_ORACLE_BACKGROUND_GROUP_00
1298/11/2018 4:52:36 PM_ORACLE_BACKGROUND_GROUP_00
1308/11/2018 4:52:36 PMOTHER_GROUPS1490
1318/11/2018 4:52:36 PMORA$AUTOTASK140
1328/11/2018 4:52:36 PMSYS_GROUP00
1338/11/2018 4:53:35 PMORA$AUTOTASK520
1348/11/2018 4:53:35 PMSYS_GROUP1380
1358/11/2018 4:53:35 PM_ORACLE_BACKGROUND_GROUP_00
1368/11/2018 4:53:35 PMOTHER_GROUPS181
1378/11/2018 4:55:36 PMSYS_GROUP760
1388/11/2018 4:55:36 PMOTHER_GROUPS2354
1398/11/2018 4:55:36 PMORA$AUTOTASK140
1408/11/2018 4:55:36 PM_ORACLE_BACKGROUND_GROUP_00
1418/11/2018 4:56:36 PM_ORACLE_BACKGROUND_GROUP_00
1428/11/2018 4:56:36 PMOTHER_GROUPS281
1438/11/2018 4:56:36 PMORA$AUTOTASK2910
1448/11/2018 4:56:36 PMSYS_GROUP00
1458/11/2018 4:58:36 PMGROUP100
1468/11/2018 4:58:36 PMGROUP200
1478/11/2018 4:58:36 PMOTHER_GROUPS241
1488/11/2018 4:58:36 PMSYS_GROUP32500
1498/11/2018 4:58:36 PM_ORACLE_BACKGROUND_GROUP_00
1508/11/2018 4:59:36 PMGROUP100
1518/11/2018 4:59:36 PMGROUP200
1528/11/2018 4:59:36 PMOTHER_GROUPS1501
1538/11/2018 4:59:36 PMSYS_GROUP15380
1548/11/2018 4:59:36 PM_ORACLE_BACKGROUND_GROUP_00
1558/11/2018 5:00:36 PMSYS_GROUP10
1568/11/2018 5:00:36 PM_ORACLE_BACKGROUND_GROUP_00
1578/11/2018 5:00:36 PMOTHER_GROUPS830
1588/11/2018 5:00:36 PMGROUP200
1598/11/2018 5:00:36 PMGROUP100
1608/11/2018 5:01:36 PMGROUP140
1618/11/2018 5:01:36 PMOTHER_GROUPS21
1628/11/2018 5:01:36 PMSYS_GROUP110
1638/11/2018 5:01:36 PM_ORACLE_BACKGROUND_GROUP_00
1648/11/2018 5:01:36 PMGROUP200
1658/11/2018 5:02:35 PMGROUP100
1668/11/2018 5:02:35 PMGROUP200
1678/11/2018 5:02:35 PMOTHER_GROUPS101
1688/11/2018 5:02:35 PMSYS_GROUP10
1698/11/2018 5:02:35 PM_ORACLE_BACKGROUND_GROUP_00
1708/11/2018 5:03:36 PMSYS_GROUP110
1718/11/2018 5:03:36 PMOTHER_GROUPS13321
1728/11/2018 5:03:36 PMGROUP200
1738/11/2018 5:03:36 PMGROUP100
1748/11/2018 5:03:36 PM_ORACLE_BACKGROUND_GROUP_00
1758/11/2018 5:04:35 PMGROUP100
1768/11/2018 5:04:35 PMGROUP200
1778/11/2018 5:04:35 PMOTHER_GROUPS4953324592
1788/11/2018 5:04:35 PMSYS_GROUP30
1798/11/2018 5:04:35 PM_ORACLE_BACKGROUND_GROUP_00
1808/11/2018 5:05:36 PMGROUP100
1818/11/2018 5:05:36 PMGROUP200
1828/11/2018 5:05:36 PMOTHER_GROUPS50
1838/11/2018 5:05:36 PMSYS_GROUP1650
1848/11/2018 5:05:36 PM_ORACLE_BACKGROUND_GROUP_00
1858/11/2018 5:06:35 PM_ORACLE_BACKGROUND_GROUP_00
1868/11/2018 5:06:35 PMGROUP100
1878/11/2018 5:06:35 PMGROUP200
1888/11/2018 5:06:35 PMOTHER_GROUPS12
1898/11/2018 5:06:35 PMSYS_GROUP00
1908/11/2018 5:07:36 PMGROUP100
1918/11/2018 5:07:36 PM_ORACLE_BACKGROUND_GROUP_00
1928/11/2018 5:07:36 PMGROUP200
1938/11/2018 5:07:36 PMOTHER_GROUPS2831
1948/11/2018 5:07:36 PMSYS_GROUP110
1958/11/2018 5:08:35 PMGROUP100
1968/11/2018 5:08:35 PMOTHER_GROUPS4753
1978/11/2018 5:08:35 PMSYS_GROUP00
1988/11/2018 5:08:35 PM_ORACLE_BACKGROUND_GROUP_00
1998/11/2018 5:08:35 PMGROUP200
2008/11/2018 5:09:36 PMSYS_GROUP810
2018/11/2018 5:09:36 PMOTHER_GROUPS5382
2028/11/2018 5:09:36 PMGROUP200
2038/11/2018 5:09:36 PMGROUP100
2048/11/2018 5:09:36 PM_ORACLE_BACKGROUND_GROUP_00
2058/11/2018 5:10:37 PMGROUP100
2068/11/2018 5:10:37 PMGROUP200
2078/11/2018 5:10:37 PMOTHER_GROUPS198325
2088/11/2018 5:10:37 PMSYS_GROUP00
2098/11/2018 5:10:37 PM_ORACLE_BACKGROUND_GROUP_00
2108/11/2018 5:11:36 PMGROUP100
2118/11/2018 5:11:36 PMGROUP200
2128/11/2018 5:11:36 PMOTHER_GROUPS1661
2138/11/2018 5:11:36 PMSYS_GROUP110
2148/11/2018 5:11:36 PM_ORACLE_BACKGROUND_GROUP_00
2158/11/2018 5:13:36 PMSYS_GROUP110
2168/11/2018 5:13:36 PMOTHER_GROUPS20
2178/11/2018 5:13:36 PMGROUP200
2188/11/2018 5:13:36 PMGROUP100
2198/11/2018 5:13:36 PM_ORACLE_BACKGROUND_GROUP_00
2208/11/2018 5:14:36 PMSYS_GROUP40
2218/11/2018 5:14:36 PMOTHER_GROUPS91
2228/11/2018 5:14:36 PMGROUP200
2238/11/2018 5:14:36 PMGROUP100
2248/11/2018 5:14:36 PM_ORACLE_BACKGROUND_GROUP_00
2258/11/2018 5:15:36 PMOTHER_GROUPS11
2268/11/2018 5:15:36 PMGROUP200
2278/11/2018 5:15:36 PM_ORACLE_BACKGROUND_GROUP_00
2288/11/2018 5:15:36 PMGROUP100
2298/11/2018 5:15:36 PMSYS_GROUP280
2308/11/2018 5:16:36 PMGROUP100
2318/11/2018 5:16:36 PMGROUP200
2328/11/2018 5:16:36 PMOTHER_GROUPS172
2338/11/2018 5:16:36 PMSYS_GROUP10
2348/11/2018 5:16:36 PM_ORACLE_BACKGROUND_GROUP_00
2358/11/2018 5:17:36 PMGROUP100
2368/11/2018 5:17:36 PMOTHER_GROUPS331
2378/11/2018 5:17:36 PMSYS_GROUP120
2388/11/2018 5:17:36 PM_ORACLE_BACKGROUND_GROUP_00
2398/11/2018 5:17:36 PMGROUP200
2408/11/2018 5:18:36 PMGROUP100
2418/11/2018 5:18:36 PMGROUP200
2428/11/2018 5:18:36 PMOTHER_GROUPS211
2438/11/2018 5:18:36 PMSYS_GROUP00
2448/11/2018 5:18:36 PM_ORACLE_BACKGROUND_GROUP_00
2458/11/2018 5:19:36 PMGROUP100
2468/11/2018 5:19:36 PMGROUP200
2478/11/2018 5:19:36 PMOTHER_GROUPS831
2488/11/2018 5:19:36 PMSYS_GROUP680
2498/11/2018 5:19:36 PM_ORACLE_BACKGROUND_GROUP_00

  1. SELECT name, consumed_cpu_time, cpu_wait_time
  2. FROM v$rsrc_consumer_group;
复制代码
   NAMECONSUMED_CPU_TIMECPU_WAIT_TIME
1GROUP100
2GROUP200
3OTHER_GROUPS3199
4SYS_GROUP14160
5_ORACLE_BACKGROUND_GROUP_00


-----------------------------------------

资源消费者组映射(前提是SCOTT既能进group1也能进group2):
  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.set_consumer_group_mapping(
  5.     dbms_resource_manager.oracle_user,
  6.     'SCOTT',
  7.     'GROUP2'
  8. );
  9. dbms_resource_manager.submit_pending_area();
  10. END;
复制代码
  1.         BEGIN
  2.     dbms_resource_manager.clear_pending_area();
  3.     dbms_resource_manager.create_pending_area();
  4.     dbms_resource_manager.set_consumer_group_mapping(
  5.         dbms_resource_manager.client_machine,
  6.         'station90.example.com',
  7.         'GROUP1'
  8.     );
  9.     dbms_resource_manager.submit_pending_area();
  10.     END;
复制代码
调节映射的优先级:
  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.set_consumer_group_mapping_pri(
  5.     EXPLICIT => 1,  CLIENT_MACHINE => 2,
  6.     SERVICE_MODULE_ACTION => 3,
  7.     SERVICE_MODULE => 4,
  8.     MODULE_NAME_ACTION => 5,
  9.     MODULE_NAME => 6,
  10.     SERVICE_NAME => 7,
  11.     ORACLE_USER => 8,
  12.     CLIENT_PROGRAM => 9,
  13.     CLIENT_OS_USER => 10,
  14.     CLIENT_ID => 11
  15. );
  16. dbms_resource_manager.submit_pending_area();
  17. END;
复制代码










回复

使用道具 举报

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

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-8-20 02:56 , Processed in 0.226082 second(s), 27 queries .

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