资源消费者组和资源计划
资源消费者组创建的语句：BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => ?,comment => ? , cpu_mth => ?);
dbms_resource_manager.submit_pending_area();
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
END;END;
让用户默认是有资格进的两个组的其中的一个：
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'GROUP1'
);
END;
DECLARE
spfileValue VARCHAR2(1000);
scopeValue VARCHAR2(10) := 'MEMORY';
planName VARCHAR2(100) :=?;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => ?, comment => ?,max_iops => ?,max_mbps => ? );
dbms_resource_manager.create_plan_directive(
plan => ?,
group_or_subplan => ?,
comment => ?,
mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
parallel_degree_limit_p1 => ? ,
switch_io_reqs => ? ,
switch_io_megabytes => ?
,
active_sess_pool_p1 => ?,
queueing_p1 => ?,
switch_group => ?,
switch_time => ?,
switch_estimate => case ? when 'false' then false when 'true' then true else false end,
max_est_exec_time => ?,
undo_pool => ? ,
max_idle_time => ?,
max_idle_blocker_time => ?,
switch_for_call => case ? when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => ?,
group_or_subplan => ?,
comment => ?,
mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
parallel_degree_limit_p1 => ? ,
switch_io_reqs => ? ,
switch_io_megabytes => ?
,
active_sess_pool_p1 => ?,
queueing_p1 => ?,
switch_group => ?,
switch_time => ?,
switch_estimate => case ? when 'false' then false when 'true' then true else false end,
max_est_exec_time => ?,
undo_pool => ? ,
max_idle_time => ?,
max_idle_blocker_time => ?,
switch_for_call => case ? when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => ?,
group_or_subplan => ?,
comment => ?,
mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
parallel_degree_limit_p1 => ? ,
switch_io_reqs => ? ,
switch_io_megabytes => ?
,
active_sess_pool_p1 => ?,
queueing_p1 => ?,
switch_group => ?,
switch_time => ?,
switch_estimate => case ? when 'false' then false when 'true' then true else false end,
max_est_exec_time => ?,
undo_pool => ? ,
max_idle_time => ?,
max_idle_blocker_time => ?,
switch_for_call => case ? when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => ?,
group_or_subplan => ?,
comment => ?,
mgmt_p1 => ?, mgmt_p2 => ?, mgmt_p3 => ?, mgmt_p4 => ?,
mgmt_p5 => ?, mgmt_p6 => ?, mgmt_p7 => ?, mgmt_p8 => ? ,
parallel_degree_limit_p1 => ? ,
switch_io_reqs => ? ,
switch_io_megabytes => ?
,
active_sess_pool_p1 => ?,
queueing_p1 => ?,
switch_group => ?,
switch_time => ?,
switch_estimate => case ? when 'false' then false when 'true' then true else false end,
max_est_exec_time => ?,
undo_pool => ? ,
max_idle_time => ?,
max_idle_blocker_time => ?,
switch_for_call => case ? when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.submit_pending_area();
select value into spfileValue from v$parameter where name = 'spfile';
IF spfileValue IS NOT NULL then
EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
END IF;
dbms_resource_manager.switch_plan( plan_name => ? , sid => ? , allow_scheduler_plan_switches => FALSE );
END;
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
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
select * from dba_rsrc_plan_directiveswhere plan='PLAN1';
-----------------------------------------------------------
下面做一个ratio的：
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'PLAN2',
comment => 'PLAN2',
cpu_mth =>'RATIO'
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'SYS_GROUP',
comment => 'SYS_GROUP',
mgmt_p1 => 70
);
dbms_resource_manager.create_plan_directive(
plan =>'PLAN2' ,
group_or_subplan => 'OTHER_GROUPS',
comment => 'OTHER_GROUPS',
mgmt_p1 => 2
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP1',
comment => 'GROUP1',
mgmt_p1 => 20
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP2',
comment => 'GROUP2',
mgmt_p1 => 8 );
dbms_resource_manager.submit_pending_area;
END;
select* from dba_rsrc_plansp
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
select * from dba_rsrc_plan_directiveswhere 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分配：
修改上限：
begin
dbms_resource_manager.clear_pending_area;
dbms_resource_manager.create_pending_area;
dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
group_or_subplan =>'SYS_GROUP',
new_max_utilization_limit => 100);
dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
group_or_subplan =>'OTHER_GROUPS',
new_max_utilization_limit => 30);
dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
group_or_subplan =>'GROUP1',
new_max_utilization_limit => 50);
dbms_resource_manager.update_plan_directive(plan => 'PLAN1',
group_or_subplan =>'GROUP2',
new_max_utilization_limit => 8);
dbms_resource_manager.submit_pending_area;
end;
上下限都设置好的情况：
select d.plan, d.group_or_subplan,d.mgmt_p1,d.max_utilization_limit
from dba_rsrc_plan_directivesd where plan='PLAN1';
PLANGROUP_OR_SUBPLANMGMT_P1MAX_UTILIZATION_LIMIT
1PLAN1GROUP12050
2PLAN1GROUP288
3PLAN1OTHER_GROUPS230
4PLAN1SYS_GROUP70100
----------------------------------------------------------------------------------------------------------并行度（没有子计划）
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => 1 ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => NULL,
new_switch_time => NULL,
new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
new_undo_pool => NULL ,
new_max_idle_time => NULL,
new_max_idle_blocker_time => NULL,
new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP2',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 8, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => NULL ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => NULL,
new_switch_time => NULL,
new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
new_undo_pool => NULL ,
new_max_idle_time => NULL,
new_max_idle_blocker_time => NULL,
new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'OTHER_GROUPS',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 2, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => NULL ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => NULL,
new_switch_time => NULL,
new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
new_undo_pool => NULL ,
new_max_idle_time => NULL,
new_max_idle_blocker_time => NULL,
new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'SYS_GROUP',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 65, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => NULL ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => NULL,
new_switch_time => NULL,
new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
new_undo_pool => NULL ,
new_max_idle_time => NULL,
new_max_idle_blocker_time => NULL,
new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.submit_pending_area();
END;
--------
# watch -n 0.1 "ps aux | grep ora_p0"
同时进行以下操作：
SQL> conn hr/oracle_4U
Connected.
SQL> !ps
PID TTY TIME CMD
2031 pts/3 00:00:00 sqlplus
4294 pts/3 00:00:00 ps
select* from v$rsrc_plan;
select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.TERMINAL='pts/3';以上观察不到并行度，
改变：
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_parallel_degree_limit_p1 => -1
);
dbms_resource_manager.submit_pending_area;
end;
select d.plan, d.group_or_subplan,d.parallel_degree_limit_p1
from dba_rsrc_plan_directivesd where plan='PLAN1';
PLANGROUP_OR_SUBPLANPARALLEL_DEGREE_LIMIT_P1
1PLAN1OTHER_GROUPS
2PLAN1SYS_GROUP
3PLAN1GROUP1
4PLAN1GROUP2
5PLAN1PLAN2
SQL> create index i_bigon t_big ( line )parallel 40 ;
Index created.
SQL> set linesize 10000
SQL> select* from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 0 0
DDL Parallelized 1 1
DFO Trees 1 1
Server Threads 8 0
Allocation Height 4 0
Allocation Width 1 0
Local Msgs Sent 5381 5381
Distr Msgs Sent 0 0
Local Msgs Recv'd 5381 5381
Distr Msgs Recv'd 0 0
11 rows selected.
SQL>
select d.plan, d.group_or_subplan,d.active_sess_pool_p1,d.queueing_p1
from dba_rsrc_plan_directivesd where plan='PLAN1';
PLANGROUP_OR_SUBPLANACTIVE_SESS_POOL_P1QUEUEING_P1
1PLAN1PLAN2
2PLAN1SYS_GROUP
3PLAN1GROUP124
4PLAN1GROUP2
5PLAN1OTHER_GROUPS
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_active_sess_pool_p1 => -1,
new_queueing_p1=>-1
);
dbms_resource_manager.submit_pending_area;
end;undo池：
select d.plan, d.group_or_subplan,d.undo_pool
from dba_rsrc_plan_directivesd where plan='PLAN1';
PLANGROUP_OR_SUBPLANUNDO_POOL
1PLAN1OTHER_GROUPS
2PLAN1GROUP2
3PLAN1PLAN2
4PLAN1SYS_GROUP
5PLAN1GROUP18
undo池中两个表加起来算，两个会话也是加起来算的：
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_undo_pool => -1
);
dbms_resource_manager.submit_pending_area;
end;
----------------------------------------------------------组切换：
select d.plan, d.group_or_subplan,
d.switch_group,
d.switch_for_call,
d.switch_time,
d.switch_estimate,
d.max_est_exec_time
from dba_rsrc_plan_directivesd 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
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_switch_for_call =>true,
new_switch_estimate=>false
);
dbms_resource_manager.submit_pending_area;
end;
select d.plan, d.group_or_subplan,
d.switch_group,
d.switch_for_call,
d.switch_io_megabytes,
d.switch_io_reqs,
d.switch_estimate
from dba_rsrc_plan_directivesd 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
组间切换优先于一切：
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
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_comment => '',
new_switch_elapsed_time => NULL,
new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
new_parallel_degree_limit_p1 => NULL ,
new_parallel_target_percentage => NULL ,
new_parallel_queue_timeout => NULL ,
new_parallel_stmt_critical => 'false' ,
new_switch_io_logical => NULL ,
new_switch_io_reqs => NULL,
new_switch_io_megabytes => NULL ,
new_active_sess_pool_p1 => NULL,
new_queueing_p1 => NULL,
new_switch_group => NULL,
new_switch_time => NULL,
new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
new_undo_pool => NULL ,
new_max_idle_time => 60,
new_max_idle_blocker_time => NULL,
new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.submit_pending_area();
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的：
begin
dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
consumer_group1 => 'GROUP1',group1_cpu => 80,
consumer_group2 => 'GROUP2',group2_cpu => 20 );
end;
select* from dba_rsrc_plan_directives
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
select* from dba_rsrc_planswhere 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：
select * from dba_rsrc_plan_directives
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
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
---------------------------------------------
查看资源计划控制历史：
SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
FROM v$rsrcmgrmetric_history
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
SELECT name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;
NAMECONSUMED_CPU_TIMECPU_WAIT_TIME
1GROUP100
2GROUP200
3OTHER_GROUPS3199
4SYS_GROUP14160
5_ORACLE_BACKGROUND_GROUP_00
-----------------------------------------
资源消费者组映射（前提是SCOTT既能进group1也能进group2）：
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'SCOTT',
'GROUP2'
);
dbms_resource_manager.submit_pending_area();
END;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.client_machine,
'station90.example.com',
'GROUP1'
);
dbms_resource_manager.submit_pending_area();
END;调节映射的优先级：
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
EXPLICIT => 1,CLIENT_MACHINE => 2,
SERVICE_MODULE_ACTION => 3,
SERVICE_MODULE => 4,
MODULE_NAME_ACTION => 5,
MODULE_NAME => 6,
SERVICE_NAME => 7,
ORACLE_USER => 8,
CLIENT_PROGRAM => 9,
CLIENT_OS_USER => 10,
CLIENT_ID => 11
);
dbms_resource_manager.submit_pending_area();
END;
页:
[1]