资源消费者组和资源计划

botang

资源消费者组创建的语句：

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. );
    
31. dbms_resource_manager.create_plan_directive(
    
32.     plan => ?,
    
33.     group_or_subplan => ?,
    
34.     comment => ?,
    
35.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
    
36.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
    
37.     parallel_degree_limit_p1 => ? ,
    
38.     switch_io_reqs => ? ,
    
39.    switch_io_megabytes => ?
    
40. ,
    
41.     active_sess_pool_p1 => ?,
    
42.     queueing_p1 => ?,
    
43.     switch_group => ?,
    
44.     switch_time => ?,
    
45.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
    
46.     max_est_exec_time => ?,
    
47.     undo_pool => ? ,
    
48.     max_idle_time => ?,
    
49.     max_idle_blocker_time => ?,
    
50.     switch_for_call => case ? when 'false' then false when 'true' then true else false end
    
51. 
    
52. );
    
53. dbms_resource_manager.create_plan_directive(
    
54.     plan => ?,
    
55.     group_or_subplan => ?,
    
56.     comment => ?,
    
57.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
    
58.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
    
59.     parallel_degree_limit_p1 => ? ,
    
60.     switch_io_reqs => ? ,
    
61.    switch_io_megabytes => ?
    
62. ,
    
63.     active_sess_pool_p1 => ?,
    
64.     queueing_p1 => ?,
    
65.     switch_group => ?,
    
66.     switch_time => ?,
    
67.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
    
68.     max_est_exec_time => ?,
    
69.     undo_pool => ? ,
    
70.     max_idle_time => ?,
    
71.     max_idle_blocker_time => ?,
    
72.     switch_for_call => case ? when 'false' then false when 'true' then true else false end
    
73. 
    
74. );
    
75. dbms_resource_manager.create_plan_directive(
    
76.     plan => ?,
    
77.     group_or_subplan => ?,
    
78.     comment => ?,
    
79.     mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
    
80.     mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
    
81.     parallel_degree_limit_p1 => ? ,
    
82.     switch_io_reqs => ? ,
    
83.    switch_io_megabytes => ?
    
84. ,
    
85.     active_sess_pool_p1 => ?,
    
86.     queueing_p1 => ?,
    
87.     switch_group => ?,
    
88.     switch_time => ?,
    
89.     switch_estimate => case ? when 'false' then false when 'true' then true else false end,
    
90.     max_est_exec_time => ?,
    
91.     undo_pool => ? ,
    
92.     max_idle_time => ?,
    
93.     max_idle_blocker_time => ?,
    
94.     switch_for_call => case ? when 'false' then false when 'true' then true else false end
    
95. 
    
96. );
    
97. dbms_resource_manager.submit_pending_area();
    
98. select value into spfileValue from v$parameter where name = 'spfile';
    
99. IF spfileValue IS NOT NULL then
    
100. EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
     
101. END IF;
     
102. dbms_resource_manager.switch_plan( plan_name => ? , sid => ? , allow_scheduler_plan_switches => FALSE );
     
103. END;

复制代码

1. select * from dba_rsrc_consumer_groups;

复制代码

	CONSUMER_GROUP_ID	CONSUMER_GROUP	CPU_METHOD	MGMT_METHOD	INTERNAL_USE	COMMENTS	CATEGORY	STATUS	MANDATORY
1	12162	ORA$AUTOTASK_SPACE_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for space management advisors	MAINTENANCE		YES
2	12163	ORA$AUTOTASK_STATS_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for gathering optimizer statistics	MAINTENANCE		YES
3	12164	ORA$AUTOTASK_MEDIUM_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for medium-priority maintenance tasks	MAINTENANCE		YES
4	12169	LOW_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for low-priority sessions	OTHER		NO
5	12170	ORA$APPQOS_0	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
6	12171	ORA$APPQOS_1	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
7	12172	ORA$APPQOS_2	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
8	12176	ORA$APPQOS_6	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
9	12177	ORA$APPQOS_7	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
10	12178	ETL_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for ETL	BATCH		NO
11	78977	GROUP1	ROUND-ROBIN	ROUND-ROBIN	NO	group1	OTHER		NO
12	78978	GROUP2	ROUND-ROBIN	ROUND-ROBIN	NO	group2	OTHER		NO
13	12173	ORA$APPQOS_3	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
14	12174	ORA$APPQOS_4	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
15	12175	ORA$APPQOS_5	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for Application QOS	INTERACTIVE		YES
16	12179	DSS_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for DSS queries	BATCH		NO
17	12180	DSS_CRITICAL_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for critical DSS queries	INTERACTIVE		NO
18	12315	AUTO_TASK_CONSUMER_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	System maintenance task consumer group	OTHER		NO
19	12157	ORA$AUTOTASK_URGENT_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for urgent maintenance tasks	MAINTENANCE		YES
20	12158	BATCH_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for batch operations	BATCH		NO
21	12165	INTERACTIVE_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for interactive, OLTP operations	INTERACTIVE		NO
22	12166	OTHER_GROUPS	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for users not included in any consumer group with a directive in the currently active plan	OTHER		YES
23	12167	DEFAULT_CONSUMER_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for users not assigned to any consumer group	OTHER		YES
24	12168	SYS_GROUP	ROUND-ROBIN	ROUND-ROBIN	NO	Consumer group for system administrators	ADMINISTRATIVE		YES
25	12159	ORA$DIAGNOSTICS	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for diagnostics	MAINTENANCE		YES
26	12160	ORA$AUTOTASK_HEALTH_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for health checks	MAINTENANCE		YES
27	12161	ORA$AUTOTASK_SQL_GROUP	ROUND-ROBIN	ROUND-ROBIN	YES	Consumer group for SQL tuning	MAINTENANCE		YES

1. select  * from dba_rsrc_plans;

复制代码

PLAN_ID

PLAN

NUM_PLAN_DIRECTIVES

CPU_METHOD

MGMT_METHOD

ACTIVE_SESS_POOL_MTH

PARALLEL_DEGREE_LIMIT_MTH

QUEUEING_MTH

SUB_PLAN

COMMENTS

STATUS

MANDATORY

1

12149

ORA$AUTOTASK_HIGH_SUB_PLAN

4

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

YES

Default 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

2

12150

DEFAULT_MAINTENANCE_PLAN

4

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Default plan for maintenance windows that prioritizes SYS_GROUP operations and allocates the remaining 5% to diagnostic operations and 25% to automated maintenance operations.

YES

3

12151

DEFAULT_PLAN

4

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Default, basic, pre-defined plan that prioritizes SYS_GROUP operations and allocates minimal resources for automated maintenance and diagnostics operations.

YES

4

12152

INTERNAL_QUIESCE

2

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Plan for quiescing the database.  This plan cannot be activated directly.  To activate, use the quiesce command.

YES

5

12153

INTERNAL_PLAN

1

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Internally-used plan for disabling the resource manager.

YES

6

78979

PLAN1

4

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

plan1

NO

7

12155

DSS_PLAN

8

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Example plan for DSS workloads that prioritizes DSS queries over ETL.

NO

8

12156

ETL_CRITICAL_PLAN

8

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Example plan for DSS workloads that prioritizes ETL and critical DSS queries.

NO

9

12147

MIXED_WORKLOAD_PLAN

6

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Example plan for a mixed workload that prioritizes interactive operations over batch operations.

NO

10

12148

ORA$AUTOTASK_SUB_PLAN

3

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

YES

Default 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

11

12154

APPQOS_PLAN

7

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Plan 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';

复制代码

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


下面做一个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_ID

PLAN

NUM_PLAN_DIRECTIVES

CPU_METHOD

MGMT_METHOD

ACTIVE_SESS_POOL_MTH

PARALLEL_DEGREE_LIMIT_MTH

QUEUEING_MTH

SUB_PLAN

COMMENTS

STATUS

MANDATORY

1

94845

PLAN2

4

RATIO

RATIO

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

PLAN2

NO

1. select   * from dba_rsrc_plan_directives  where plan='PLAN2';

复制代码

	PLAN	GROUP_OR_SUBPLAN	TYPE	CPU_P1	CPU_P2	CPU_P3	CPU_P4	CPU_P5	CPU_P6	CPU_P7	CPU_P8	MGMT_P1	MGMT_P2	MGMT_P3	MGMT_P4	MGMT_P5	MGMT_P6	MGMT_P7	MGMT_P8	ACTIVE_SESS_POOL_P1	QUEUEING_P1	PARALLEL_TARGET_PERCENTAGE	PARALLEL_DEGREE_LIMIT_P1	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_TIME	SWITCH_IO_MEGABYTES	SWITCH_IO_REQS	SWITCH_ESTIMATE	MAX_EST_EXEC_TIME	UNDO_POOL	MAX_IDLE_TIME	MAX_IDLE_BLOCKER_TIME	MAX_UTILIZATION_LIMIT	PARALLEL_QUEUE_TIMEOUT	SWITCH_TIME_IN_CALL	SWITCH_IO_LOGICAL	SWITCH_ELAPSED_TIME	PARALLEL_SERVER_LIMIT	UTILIZATION_LIMIT	PARALLEL_STMT_CRITICAL	COMMENTS	STATUS	MANDATORY
1	PLAN2	GROUP2	CONSUMER_GROUP	8	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	GROUP2		NO
2	PLAN2	SYS_GROUP	CONSUMER_GROUP	70	0	0	0	0	0	0	0	70	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	SYS_GROUP		NO
3	PLAN2	OTHER_GROUPS	CONSUMER_GROUP	2	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	OTHER_GROUPS		NO
4	PLAN2	GROUP1	CONSUMER_GROUP	20	0	0	0	0	0	0	0	20	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	GROUP1		NO

RATIO的不能有第二级CPU分配：


修改上限：

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';

复制代码

	PLAN	GROUP_OR_SUBPLAN	MGMT_P1	MAX_UTILIZATION_LIMIT
1	PLAN1	GROUP1	20	50
2	PLAN1	GROUP2	8	8
3	PLAN1	OTHER_GROUPS	2	30
4	PLAN1	SYS_GROUP	70	100

----------------------------------------------------------------------------------------------------------并行度（没有子计划）

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. );
    
34. dbms_resource_manager.update_plan_directive(
    
35.     plan => 'PLAN1',
    
36.     group_or_subplan => 'GROUP2',
    
37.     new_comment => '',
    
38.     new_switch_elapsed_time => NULL,
    
39.     new_mgmt_p1 => 8, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
40.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
41.     new_parallel_degree_limit_p1 => NULL ,
    
42.     new_parallel_target_percentage => NULL ,
    
43.     new_parallel_queue_timeout => NULL ,
    
44.     new_parallel_stmt_critical => 'false' ,
    
45.     new_switch_io_logical => NULL ,
    
46.     new_switch_io_reqs => NULL,
    
47.     new_switch_io_megabytes => NULL ,
    
48.     new_active_sess_pool_p1 => NULL,
    
49.     new_queueing_p1 => NULL,
    
50.     new_switch_group => NULL,
    
51.     new_switch_time => NULL,
    
52.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
53.     new_undo_pool => NULL ,
    
54.     new_max_idle_time => NULL,
    
55.     new_max_idle_blocker_time => NULL,
    
56.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
57. 
    
58. );
    
59. dbms_resource_manager.update_plan_directive(
    
60.     plan => 'PLAN1',
    
61.     group_or_subplan => 'OTHER_GROUPS',
    
62.     new_comment => '',
    
63.     new_switch_elapsed_time => NULL,
    
64.     new_mgmt_p1 => 2, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
65.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
66.     new_parallel_degree_limit_p1 => NULL ,
    
67.     new_parallel_target_percentage => NULL ,
    
68.     new_parallel_queue_timeout => NULL ,
    
69.     new_parallel_stmt_critical => 'false' ,
    
70.     new_switch_io_logical => NULL ,
    
71.     new_switch_io_reqs => NULL,
    
72.     new_switch_io_megabytes => NULL ,
    
73.     new_active_sess_pool_p1 => NULL,
    
74.     new_queueing_p1 => NULL,
    
75.     new_switch_group => NULL,
    
76.     new_switch_time => NULL,
    
77.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
78.     new_undo_pool => NULL ,
    
79.     new_max_idle_time => NULL,
    
80.     new_max_idle_blocker_time => NULL,
    
81.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
82. 
    
83. );
    
84. dbms_resource_manager.update_plan_directive(
    
85.     plan => 'PLAN1',
    
86.     group_or_subplan => 'SYS_GROUP',
    
87.     new_comment => '',
    
88.     new_switch_elapsed_time => NULL,
    
89.     new_mgmt_p1 => 65, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
90.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
91.     new_parallel_degree_limit_p1 => NULL ,
    
92.     new_parallel_target_percentage => NULL ,
    
93.     new_parallel_queue_timeout => NULL ,
    
94.     new_parallel_stmt_critical => 'false' ,
    
95.     new_switch_io_logical => NULL ,
    
96.     new_switch_io_reqs => NULL,
    
97.     new_switch_io_megabytes => NULL ,
    
98.     new_active_sess_pool_p1 => NULL,
    
99.     new_queueing_p1 => NULL,
    
100.     new_switch_group => NULL,
     
101.     new_switch_time => NULL,
     
102.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
     
103.     new_undo_pool => NULL ,
     
104.     new_max_idle_time => NULL,
     
105.     new_max_idle_blocker_time => NULL,
     
106.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
107. 
     
108. );
     
109. dbms_resource_manager.submit_pending_area();
     
110. 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. 
   
3. select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
   
4. from v_$session s
   
5. 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';
   

复制代码

	PLAN	GROUP_OR_SUBPLAN	PARALLEL_DEGREE_LIMIT_P1
1	PLAN1	OTHER_GROUPS
2	PLAN1	SYS_GROUP
3	PLAN1	GROUP1
4	PLAN1	GROUP2
5	PLAN1	PLAN2

1. SQL> create index i_big  on t_big ( line )  parallel 40 ;
   
2. 
   
3. Index created.
   
4. 
   
5. SQL> set linesize 10000
   
6. SQL> select  * from v$pq_sesstat;
   
7. 
   
8. STATISTIC                       LAST_QUERY SESSION_TOTAL
   
9. ------------------------------ ---------- -------------
   
10. Queries Parallelized                        0              0
    
11. DML Parallelized                        0              0
    
12. DDL Parallelized                        1              1
    
13. DFO Trees                                1              1
    
14. Server Threads                                8              0
    
15. Allocation Height                        4              0
    
16. Allocation Width                        1              0
    
17. Local Msgs Sent                      5381           5381
    
18. Distr Msgs Sent                         0              0
    
19. Local Msgs Recv'd                     5381           5381
    
20. Distr Msgs Recv'd                        0              0
    
21. 
    
22. 11 rows selected.
    
23. 
    
24. SQL>
    

复制代码

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';

复制代码

	PLAN	GROUP_OR_SUBPLAN	ACTIVE_SESS_POOL_P1	QUEUEING_P1
1	PLAN1	PLAN2
2	PLAN1	SYS_GROUP
3	PLAN1	GROUP1	2	4
4	PLAN1	GROUP2
5	PLAN1	OTHER_GROUPS

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';

复制代码

	PLAN	GROUP_OR_SUBPLAN	UNDO_POOL
1	PLAN1	OTHER_GROUPS
2	PLAN1	GROUP2
3	PLAN1	PLAN2
4	PLAN1	SYS_GROUP
5	PLAN1	GROUP1	8

undo池中两个表加起来算，两个会话也是加起来算的：

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;
    

复制代码

----------------------------------------------------------组切换：

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';
   

复制代码

	PLAN	GROUP_OR_SUBPLAN	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_TIME	SWITCH_ESTIMATE	MAX_EST_EXEC_TIME
1	PLAN1	SYS_GROUP		FALSE		FALSE
2	PLAN1	GROUP1	GROUP2	TRUE	5	TRUE
3	PLAN1	OTHER_GROUPS		TRUE		FALSE
4	PLAN1	GROUP2		TRUE		FALSE
5	PLAN1	PLAN2		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';

复制代码

	PLAN	GROUP_OR_SUBPLAN	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_IO_MEGABYTES	SWITCH_IO_REQS	SWITCH_ESTIMATE
1	PLAN1	OTHER_GROUPS		TRUE			FALSE
2	PLAN1	GROUP2		TRUE			FALSE
3	PLAN1	PLAN2		FALSE			FALSE
4	PLAN1	GROUP1	GROUP2	FALSE	40		FALSE
5	PLAN1	SYS_GROUP		FALSE			FALSE

组间切换优先于一切：



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. );
    
34. dbms_resource_manager.submit_pending_area();
    
35. 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';

复制代码

	PLAN	GROUP_OR_SUBPLAN	TYPE	CPU_P1	CPU_P2	CPU_P3	CPU_P4	CPU_P5	CPU_P6	CPU_P7	CPU_P8	MGMT_P1	MGMT_P2	MGMT_P3	MGMT_P4	MGMT_P5	MGMT_P6	MGMT_P7	MGMT_P8	ACTIVE_SESS_POOL_P1	QUEUEING_P1	PARALLEL_TARGET_PERCENTAGE	PARALLEL_DEGREE_LIMIT_P1	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_TIME	SWITCH_IO_MEGABYTES	SWITCH_IO_REQS	SWITCH_ESTIMATE	MAX_EST_EXEC_TIME	UNDO_POOL	MAX_IDLE_TIME	MAX_IDLE_BLOCKER_TIME	MAX_UTILIZATION_LIMIT	PARALLEL_QUEUE_TIMEOUT	SWITCH_TIME_IN_CALL	SWITCH_IO_LOGICAL	SWITCH_ELAPSED_TIME	PARALLEL_SERVER_LIMIT	UTILIZATION_LIMIT	PARALLEL_STMT_CRITICAL	COMMENTS	STATUS	MANDATORY
1	PLAN3	SYS_GROUP	CONSUMER_GROUP	50	0	0	0	0	0	0	0	50	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	Sys group		NO
2	PLAN3	OTHER_GROUPS	CONSUMER_GROUP	5	0	0	0	0	0	0	0	5	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	Other groups		NO
3	PLAN3	GROUP1	CONSUMER_GROUP	80	0	0	0	0	0	0	0	80	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	Group 1		NO
4	PLAN3	GROUP2	CONSUMER_GROUP	20	0	0	0	0	0	0	0	20	0	0	0	0	0	0	0						FALSE				FALSE												FALSE	Group 2		NO

1. select  * from dba_rsrc_plans  where plan='PLAN3';

复制代码

PLAN_ID

PLAN

NUM_PLAN_DIRECTIVES

CPU_METHOD

MGMT_METHOD

ACTIVE_SESS_POOL_MTH

PARALLEL_DEGREE_LIMIT_MTH

QUEUEING_MTH

SUB_PLAN

COMMENTS

STATUS

MANDATORY

1

94850

PLAN3

4

RATIO

RATIO

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Simple plan

NO

11g 的simple plan：

1. select * from dba_rsrc_plan_directives
   
2. where plan='PLAN3';

复制代码

	PLAN	GROUP_OR_SUBPLAN	TYPE	CPU_P1	CPU_P2	CPU_P3	CPU_P4	CPU_P5	CPU_P6	CPU_P7	CPU_P8	MGMT_P1	MGMT_P2	MGMT_P3	MGMT_P4	MGMT_P5	MGMT_P6	MGMT_P7	MGMT_P8	ACTIVE_SESS_POOL_P1	QUEUEING_P1	PARALLEL_DEGREE_LIMIT_P1	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_TIME	SWITCH_IO_MEGABYTES	SWITCH_IO_REQS	SWITCH_ESTIMATE	MAX_EST_EXEC_TIME	UNDO_POOL	MAX_IDLE_TIME	MAX_IDLE_BLOCKER_TIME	MAX_UTILIZATION_LIMIT	SWITCH_TIME_IN_CALL	COMMENTS	STATUS	MANDATORY
1	PLAN3	GROUP1	CONSUMER_GROUP	0	80	0	0	0	0	0	0	0	80	0	0	0	0	0	0					FALSE				FALSE							Level 2 Group 1		NO
2	PLAN3	SYS_GROUP	CONSUMER_GROUP	100	0	0	0	0	0	0	0	100	0	0	0	0	0	0	0					FALSE				FALSE							SYS Level 1		NO
3	PLAN3	OTHER_GROUPS	CONSUMER_GROUP	0	0	100	0	0	0	0	0	0	0	100	0	0	0	0	0					FALSE				FALSE							OTHER_GROUPS Level 3		NO
4	PLAN3	GROUP2	CONSUMER_GROUP	0	20	0	0	0	0	0	0	0	20	0	0	0	0	0	0					FALSE				FALSE							Level 2 Group 2		NO

1. select * from dba_rsrc_plans where plan='PLAN3';

复制代码

PLAN_ID

PLAN

NUM_PLAN_DIRECTIVES

CPU_METHOD

MGMT_METHOD

ACTIVE_SESS_POOL_MTH

PARALLEL_DEGREE_LIMIT_MTH

QUEUEING_MTH

SUB_PLAN

COMMENTS

STATUS

MANDATORY

1

78993

PLAN3

4

EMPHASIS

EMPHASIS

ACTIVE_SESS_POOL_ABSOLUTE

PARALLEL_DEGREE_LIMIT_ABSOLUTE

FIFO_TIMEOUT

NO

Simple 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_TIME	CONSUMER_GROUP_NAME	CPU_CONSUMED_TIME	CPU_WAIT_TIME
1	8/11/2018 4:19:36 PM	ORA$AUTOTASK	15	0
2	8/11/2018 4:19:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
3	8/11/2018 4:19:36 PM	SYS_GROUP	94	1
4	8/11/2018 4:19:36 PM	OTHER_GROUPS	85	1
5	8/11/2018 4:20:36 PM	ORA$AUTOTASK	0	0
6	8/11/2018 4:20:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
7	8/11/2018 4:20:36 PM	SYS_GROUP	0	0
8	8/11/2018 4:20:36 PM	OTHER_GROUPS	22	1
9	8/11/2018 4:21:35 PM	ORA$AUTOTASK	13	0
10	8/11/2018 4:21:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
11	8/11/2018 4:21:35 PM	SYS_GROUP	10	0
12	8/11/2018 4:21:35 PM	OTHER_GROUPS	2	0
13	8/11/2018 4:22:36 PM	ORA$AUTOTASK	13	0
14	8/11/2018 4:22:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
15	8/11/2018 4:22:36 PM	SYS_GROUP	0	0
16	8/11/2018 4:22:36 PM	OTHER_GROUPS	88	2
17	8/11/2018 4:23:36 PM	ORA$AUTOTASK	66	0
18	8/11/2018 4:23:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
19	8/11/2018 4:23:36 PM	SYS_GROUP	9	0
20	8/11/2018 4:23:36 PM	OTHER_GROUPS	5	1
21	8/11/2018 4:24:36 PM	ORA$AUTOTASK	14	0
22	8/11/2018 4:24:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
23	8/11/2018 4:24:36 PM	SYS_GROUP	3	0
24	8/11/2018 4:24:36 PM	OTHER_GROUPS	14	1
25	8/11/2018 4:25:36 PM	ORA$AUTOTASK	15	0
26	8/11/2018 4:25:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
27	8/11/2018 4:25:36 PM	SYS_GROUP	11	0
28	8/11/2018 4:25:36 PM	OTHER_GROUPS	4	2
29	8/11/2018 4:26:36 PM	ORA$AUTOTASK	15	0
30	8/11/2018 4:26:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
31	8/11/2018 4:26:36 PM	SYS_GROUP	0	0
32	8/11/2018 4:26:36 PM	OTHER_GROUPS	5	1
33	8/11/2018 4:27:36 PM	ORA$AUTOTASK	12	0
34	8/11/2018 4:27:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
35	8/11/2018 4:27:36 PM	SYS_GROUP	10	0
36	8/11/2018 4:27:36 PM	OTHER_GROUPS	8	1
37	8/11/2018 4:28:36 PM	ORA$AUTOTASK	32	0
38	8/11/2018 4:28:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
39	8/11/2018 4:28:36 PM	SYS_GROUP	0	0
40	8/11/2018 4:28:36 PM	OTHER_GROUPS	16	1
41	8/11/2018 4:29:36 PM	ORA$AUTOTASK	14	0
42	8/11/2018 4:29:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
43	8/11/2018 4:29:36 PM	SYS_GROUP	589	0
44	8/11/2018 4:29:36 PM	OTHER_GROUPS	138	3
45	8/11/2018 4:30:36 PM	ORA$AUTOTASK	14	0
46	8/11/2018 4:30:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
47	8/11/2018 4:30:36 PM	SYS_GROUP	6	0
48	8/11/2018 4:30:36 PM	OTHER_GROUPS	20	2
49	8/11/2018 4:31:36 PM	ORA$AUTOTASK	13	0
50	8/11/2018 4:31:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
51	8/11/2018 4:31:36 PM	SYS_GROUP	28	0
52	8/11/2018 4:31:36 PM	OTHER_GROUPS	6	1
53	8/11/2018 4:32:35 PM	ORA$AUTOTASK	53	0
54	8/11/2018 4:32:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
55	8/11/2018 4:32:35 PM	SYS_GROUP	0	0
56	8/11/2018 4:32:35 PM	OTHER_GROUPS	40	3
57	8/11/2018 4:33:36 PM	ORA$AUTOTASK	49	0
58	8/11/2018 4:33:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
59	8/11/2018 4:33:36 PM	SYS_GROUP	4935	1
60	8/11/2018 4:33:36 PM	OTHER_GROUPS	4	1
61	8/11/2018 4:34:35 PM	ORA$AUTOTASK	35	0
62	8/11/2018 4:34:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
63	8/11/2018 4:34:35 PM	SYS_GROUP	3139	0
64	8/11/2018 4:34:35 PM	OTHER_GROUPS	133	0
65	8/11/2018 4:35:36 PM	ORA$AUTOTASK	14	0
66	8/11/2018 4:35:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
67	8/11/2018 4:35:36 PM	SYS_GROUP	12	0
68	8/11/2018 4:35:36 PM	OTHER_GROUPS	3	1
69	8/11/2018 4:36:35 PM	ORA$AUTOTASK	14	0
70	8/11/2018 4:36:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
71	8/11/2018 4:36:35 PM	SYS_GROUP	0	0
72	8/11/2018 4:36:35 PM	OTHER_GROUPS	3	0
73	8/11/2018 4:37:36 PM	ORA$AUTOTASK	15	0
74	8/11/2018 4:37:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
75	8/11/2018 4:37:36 PM	SYS_GROUP	498	1
76	8/11/2018 4:37:36 PM	OTHER_GROUPS	133	1
77	8/11/2018 4:39:36 PM	ORA$AUTOTASK	34	0
78	8/11/2018 4:39:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
79	8/11/2018 4:39:36 PM	SYS_GROUP	87	0
80	8/11/2018 4:39:36 PM	OTHER_GROUPS	88	7
81	8/11/2018 4:40:36 PM	ORA$AUTOTASK	14	0
82	8/11/2018 4:40:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
83	8/11/2018 4:40:36 PM	SYS_GROUP	1045	0
84	8/11/2018 4:40:36 PM	OTHER_GROUPS	37	2
85	8/11/2018 4:41:36 PM	ORA$AUTOTASK	14	0
86	8/11/2018 4:41:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
87	8/11/2018 4:41:36 PM	SYS_GROUP	81	0
88	8/11/2018 4:41:36 PM	OTHER_GROUPS	5	1
89	8/11/2018 4:42:36 PM	ORA$AUTOTASK	25	0
90	8/11/2018 4:42:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
91	8/11/2018 4:42:36 PM	SYS_GROUP	467	0
92	8/11/2018 4:42:36 PM	OTHER_GROUPS	275	2
93	8/11/2018 4:43:36 PM	ORA$AUTOTASK	60	0
94	8/11/2018 4:43:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
95	8/11/2018 4:43:36 PM	SYS_GROUP	20	0
96	8/11/2018 4:43:36 PM	OTHER_GROUPS	308	1
97	8/11/2018 4:44:36 PM	ORA$AUTOTASK	479	0
98	8/11/2018 4:44:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
99	8/11/2018 4:44:36 PM	SYS_GROUP	117	0
100	8/11/2018 4:44:36 PM	OTHER_GROUPS	307	0
101	8/11/2018 4:45:36 PM	ORA$AUTOTASK	13	0
102	8/11/2018 4:45:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
103	8/11/2018 4:45:36 PM	SYS_GROUP	600	0
104	8/11/2018 4:45:36 PM	OTHER_GROUPS	1	1
105	8/11/2018 4:46:36 PM	ORA$AUTOTASK	14	0
106	8/11/2018 4:46:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
107	8/11/2018 4:46:36 PM	SYS_GROUP	0	0
108	8/11/2018 4:46:36 PM	OTHER_GROUPS	2	0
109	8/11/2018 4:47:36 PM	ORA$AUTOTASK	317	0
110	8/11/2018 4:47:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
111	8/11/2018 4:47:36 PM	SYS_GROUP	74	0
112	8/11/2018 4:47:36 PM	OTHER_GROUPS	92	1
113	8/11/2018 4:48:36 PM	ORA$AUTOTASK	14	0
114	8/11/2018 4:48:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
115	8/11/2018 4:48:36 PM	SYS_GROUP	0	0
116	8/11/2018 4:48:36 PM	OTHER_GROUPS	128	1
117	8/11/2018 4:49:36 PM	ORA$AUTOTASK	214	0
118	8/11/2018 4:49:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
119	8/11/2018 4:49:36 PM	SYS_GROUP	28	0
120	8/11/2018 4:49:36 PM	OTHER_GROUPS	225	2
121	8/11/2018 4:50:36 PM	ORA$AUTOTASK	15	0
122	8/11/2018 4:50:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
123	8/11/2018 4:50:36 PM	SYS_GROUP	51	0
124	8/11/2018 4:50:36 PM	OTHER_GROUPS	48	1
125	8/11/2018 4:51:36 PM	ORA$AUTOTASK	13	0
126	8/11/2018 4:51:36 PM	OTHER_GROUPS	24	1
127	8/11/2018 4:51:36 PM	SYS_GROUP	10	0
128	8/11/2018 4:51:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
129	8/11/2018 4:52:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
130	8/11/2018 4:52:36 PM	OTHER_GROUPS	149	0
131	8/11/2018 4:52:36 PM	ORA$AUTOTASK	14	0
132	8/11/2018 4:52:36 PM	SYS_GROUP	0	0
133	8/11/2018 4:53:35 PM	ORA$AUTOTASK	52	0
134	8/11/2018 4:53:35 PM	SYS_GROUP	138	0
135	8/11/2018 4:53:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
136	8/11/2018 4:53:35 PM	OTHER_GROUPS	18	1
137	8/11/2018 4:55:36 PM	SYS_GROUP	76	0
138	8/11/2018 4:55:36 PM	OTHER_GROUPS	235	4
139	8/11/2018 4:55:36 PM	ORA$AUTOTASK	14	0
140	8/11/2018 4:55:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
141	8/11/2018 4:56:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
142	8/11/2018 4:56:36 PM	OTHER_GROUPS	28	1
143	8/11/2018 4:56:36 PM	ORA$AUTOTASK	291	0
144	8/11/2018 4:56:36 PM	SYS_GROUP	0	0
145	8/11/2018 4:58:36 PM	GROUP1	0	0
146	8/11/2018 4:58:36 PM	GROUP2	0	0
147	8/11/2018 4:58:36 PM	OTHER_GROUPS	24	1
148	8/11/2018 4:58:36 PM	SYS_GROUP	3250	0
149	8/11/2018 4:58:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
150	8/11/2018 4:59:36 PM	GROUP1	0	0
151	8/11/2018 4:59:36 PM	GROUP2	0	0
152	8/11/2018 4:59:36 PM	OTHER_GROUPS	150	1
153	8/11/2018 4:59:36 PM	SYS_GROUP	1538	0
154	8/11/2018 4:59:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
155	8/11/2018 5:00:36 PM	SYS_GROUP	1	0
156	8/11/2018 5:00:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
157	8/11/2018 5:00:36 PM	OTHER_GROUPS	83	0
158	8/11/2018 5:00:36 PM	GROUP2	0	0
159	8/11/2018 5:00:36 PM	GROUP1	0	0
160	8/11/2018 5:01:36 PM	GROUP1	4	0
161	8/11/2018 5:01:36 PM	OTHER_GROUPS	2	1
162	8/11/2018 5:01:36 PM	SYS_GROUP	11	0
163	8/11/2018 5:01:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
164	8/11/2018 5:01:36 PM	GROUP2	0	0
165	8/11/2018 5:02:35 PM	GROUP1	0	0
166	8/11/2018 5:02:35 PM	GROUP2	0	0
167	8/11/2018 5:02:35 PM	OTHER_GROUPS	10	1
168	8/11/2018 5:02:35 PM	SYS_GROUP	1	0
169	8/11/2018 5:02:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
170	8/11/2018 5:03:36 PM	SYS_GROUP	11	0
171	8/11/2018 5:03:36 PM	OTHER_GROUPS	1332	1
172	8/11/2018 5:03:36 PM	GROUP2	0	0
173	8/11/2018 5:03:36 PM	GROUP1	0	0
174	8/11/2018 5:03:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
175	8/11/2018 5:04:35 PM	GROUP1	0	0
176	8/11/2018 5:04:35 PM	GROUP2	0	0
177	8/11/2018 5:04:35 PM	OTHER_GROUPS	49533	24592
178	8/11/2018 5:04:35 PM	SYS_GROUP	3	0
179	8/11/2018 5:04:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
180	8/11/2018 5:05:36 PM	GROUP1	0	0
181	8/11/2018 5:05:36 PM	GROUP2	0	0
182	8/11/2018 5:05:36 PM	OTHER_GROUPS	5	0
183	8/11/2018 5:05:36 PM	SYS_GROUP	165	0
184	8/11/2018 5:05:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
185	8/11/2018 5:06:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
186	8/11/2018 5:06:35 PM	GROUP1	0	0
187	8/11/2018 5:06:35 PM	GROUP2	0	0
188	8/11/2018 5:06:35 PM	OTHER_GROUPS	1	2
189	8/11/2018 5:06:35 PM	SYS_GROUP	0	0
190	8/11/2018 5:07:36 PM	GROUP1	0	0
191	8/11/2018 5:07:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
192	8/11/2018 5:07:36 PM	GROUP2	0	0
193	8/11/2018 5:07:36 PM	OTHER_GROUPS	283	1
194	8/11/2018 5:07:36 PM	SYS_GROUP	11	0
195	8/11/2018 5:08:35 PM	GROUP1	0	0
196	8/11/2018 5:08:35 PM	OTHER_GROUPS	475	3
197	8/11/2018 5:08:35 PM	SYS_GROUP	0	0
198	8/11/2018 5:08:35 PM	_ORACLE_BACKGROUND_GROUP_	0	0
199	8/11/2018 5:08:35 PM	GROUP2	0	0
200	8/11/2018 5:09:36 PM	SYS_GROUP	81	0
201	8/11/2018 5:09:36 PM	OTHER_GROUPS	538	2
202	8/11/2018 5:09:36 PM	GROUP2	0	0
203	8/11/2018 5:09:36 PM	GROUP1	0	0
204	8/11/2018 5:09:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
205	8/11/2018 5:10:37 PM	GROUP1	0	0
206	8/11/2018 5:10:37 PM	GROUP2	0	0
207	8/11/2018 5:10:37 PM	OTHER_GROUPS	1983	25
208	8/11/2018 5:10:37 PM	SYS_GROUP	0	0
209	8/11/2018 5:10:37 PM	_ORACLE_BACKGROUND_GROUP_	0	0
210	8/11/2018 5:11:36 PM	GROUP1	0	0
211	8/11/2018 5:11:36 PM	GROUP2	0	0
212	8/11/2018 5:11:36 PM	OTHER_GROUPS	166	1
213	8/11/2018 5:11:36 PM	SYS_GROUP	11	0
214	8/11/2018 5:11:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
215	8/11/2018 5:13:36 PM	SYS_GROUP	11	0
216	8/11/2018 5:13:36 PM	OTHER_GROUPS	2	0
217	8/11/2018 5:13:36 PM	GROUP2	0	0
218	8/11/2018 5:13:36 PM	GROUP1	0	0
219	8/11/2018 5:13:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
220	8/11/2018 5:14:36 PM	SYS_GROUP	4	0
221	8/11/2018 5:14:36 PM	OTHER_GROUPS	9	1
222	8/11/2018 5:14:36 PM	GROUP2	0	0
223	8/11/2018 5:14:36 PM	GROUP1	0	0
224	8/11/2018 5:14:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
225	8/11/2018 5:15:36 PM	OTHER_GROUPS	1	1
226	8/11/2018 5:15:36 PM	GROUP2	0	0
227	8/11/2018 5:15:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
228	8/11/2018 5:15:36 PM	GROUP1	0	0
229	8/11/2018 5:15:36 PM	SYS_GROUP	28	0
230	8/11/2018 5:16:36 PM	GROUP1	0	0
231	8/11/2018 5:16:36 PM	GROUP2	0	0
232	8/11/2018 5:16:36 PM	OTHER_GROUPS	17	2
233	8/11/2018 5:16:36 PM	SYS_GROUP	1	0
234	8/11/2018 5:16:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
235	8/11/2018 5:17:36 PM	GROUP1	0	0
236	8/11/2018 5:17:36 PM	OTHER_GROUPS	33	1
237	8/11/2018 5:17:36 PM	SYS_GROUP	12	0
238	8/11/2018 5:17:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
239	8/11/2018 5:17:36 PM	GROUP2	0	0
240	8/11/2018 5:18:36 PM	GROUP1	0	0
241	8/11/2018 5:18:36 PM	GROUP2	0	0
242	8/11/2018 5:18:36 PM	OTHER_GROUPS	21	1
243	8/11/2018 5:18:36 PM	SYS_GROUP	0	0
244	8/11/2018 5:18:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0
245	8/11/2018 5:19:36 PM	GROUP1	0	0
246	8/11/2018 5:19:36 PM	GROUP2	0	0
247	8/11/2018 5:19:36 PM	OTHER_GROUPS	83	1
248	8/11/2018 5:19:36 PM	SYS_GROUP	68	0
249	8/11/2018 5:19:36 PM	_ORACLE_BACKGROUND_GROUP_	0	0

1. SELECT name, consumed_cpu_time, cpu_wait_time
   
2. FROM v$rsrc_consumer_group;
   

复制代码

	NAME	CONSUMED_CPU_TIME	CPU_WAIT_TIME
1	GROUP1	0	0
2	GROUP2	0	0
3	OTHER_GROUPS	319	9
4	SYS_GROUP	1416	0
5	_ORACLE_BACKGROUND_GROUP_	0	0

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

资源消费者组映射（前提是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;

复制代码