第35次活动：2017-11-22（星期三晚上7:00-9:30）
select* from dba_rsrc_plan_directives dwhere d.plan='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_switch_time=> -1
);
dbms_resource_manager.submit_pending_area();
END;
----
selectbytes/1024/1024from dba_segments s where s.segment_name='T_BIG';
----
select s.USERNAME,s.RESOURCE_CONSUMER_GROUP, terminal
from v_$session s
where s.TERMINAL in ('pts/2');
----
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_io_megabytes=> 20
);
dbms_resource_manager.submit_pending_area();
END;
---
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_max_idle_time=> 30
);
dbms_resource_manager.submit_pending_area();
END;
如果要切进去的组活跃会话指标已满，就切不进去（在原组运行）：
关于simple plan:
begin
dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
consumer_group1 => 'GROUP1',
group1_cpu => 85,
consumer_group2 => 'GROUP2',
group2_cpu =>15);
end;
max_utilization_limit：
select* from dba_rsrc_plan_directives d
where d.plan='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_max_utilization_limit => 90
);
dbms_resource_manager.submit_pending_area();
END;
最终看CPU怎么控制：
SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
FROM v$rsrcmgrmetric_history
ORDER BY begin_time;
----
SELECT name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;
select* from dba_rsrc_group_mappings;
selects.USERNAME , s.RESOURCE_CONSUMER_GROUP,
s.STATUSfrom v_$session s
where s.MACHINE ='WORKGROUP2\STATION199';
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,
'192.168.0.199',
NULL
);
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.client_machine,
'WORKGROUP2\STATION199',
'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_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
);
dbms_resource_manager.submit_pending_area();
END;
select* from dba_rsrc_mapping_priority;
页:
[1]