|
还没有操作之前,观察到的图形界面:
jobs页面:
schedule的页面:
首先改掉这个schedule(普通的基于时间的):
- BEGIN
- sys.dbms_scheduler.set_attribute( name => '"SYS"."FILE_WATCHER_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=2');
- END;
复制代码 接下来,在做实验的过程中始终看者以下这一页:
- [root@station90 ~]# cd /lib64/
- [root@station90 lib64]# ls -l libpam
- libpamc.so.0 libpam_misc.so.0 libpam.so.0
- libpamc.so.0.82.1 libpam_misc.so.0.82.0 libpam.so.0.82.2
- [root@station90 lib64]# ls -l libpam.*
- lrwxrwxrwx. 1 root root 16 5月 13 06:24 libpam.so.0 -> libpam.so.0.82.2
- -rwxr-xr-x. 1 root root 55280 7月 10 2017 libpam.so.0.82.2
- [root@station90 lib64]# pwd
- /lib64
- [root@station90 lib64]# ln -s libpam.so.0.82.2 libpam.so
- [root@station90 lib64]# ls -l libpam.*
- lrwxrwxrwx 1 root root 16 6月 10 09:50 libpam.so -> libpam.so.0.82.2
- lrwxrwxrwx. 1 root root 16 5月 13 06:24 libpam.so.0 -> libpam.so.0.82.2
- -rwxr-xr-x. 1 root root 55280 7月 10 2017 libpam.so.0.82.2
复制代码- select * from cdb_scheduler_credentials;
- select * from cdb_scheduler_file_watchers;
- begin
- dbms_scheduler.create_file_watcher(
- file_watcher_name => 'filewatcher1',
- directory_path => '/home/oracle/myfile',
- file_name => 'botang*.txt',
- credential_name => 'credential1');
- end;
- select * from cdb_scheduler_file_watchers;
复制代码
- grant execute on filewatcher1 to hr;
- grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
- select * from cdb_tab_privs tp where tp.grantee='HR';
- select * from cdb_sys_privs sp where sp.grantee='HR';
- create table hr.tfilewatcher1 ( a varchar2(200) ) ;
- create or replace procedure hr.procfilewatcher ( p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
- is
- begin
- insert into hr.tfilewatcher1 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
- ||p_1.directory_path||' '
- ||p_1.actual_file_name||' '
- ||p_1.file_size ) ;
- commit;
- end;
- begin
- dbms_scheduler.create_program(
- program_name => 'HR.program10',
- program_type => 'STORED_PROCEDURE',
- program_action => 'HR.procfilewatcher',
- number_of_arguments => 1,
- enabled => false);
- end;
- begin
- dbms_scheduler.define_metadata_argument(
- program_name => 'HR.program10',
- metadata_attribute => 'event_message',argument_position => 1 ) ;
- end;
- begin
- dbms_scheduler.enable('HR.program10');
- end;
- BEGIN
- DBMS_SCHEDULER.CREATE_JOB(
- jOB_NAME=> 'hr.job10',
- PROGRAM_NAME=> 'hr.program10',
- EVENT_CONDITION => 'tab.user_data.file_size > 10',
- QUEUE_SPEC=> 'filewatcher1',
- AUTO_DROP=> FALSE,
- ENABLED=> true);
- END;
- begin
- dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
- end;
- select * from hr.tfilewatcher1;
复制代码
12c上创建的PLAN1:
- 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.create_plan( plan => 'PLAN1', comment => 'plan1',max_iops => NULL,max_mbps => NULL );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP2',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'OTHER_GROUPS',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'SYS_GROUP',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 60, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' 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 => 'PLAN1' , sid => 'cdb2' );
- END;
-
复制代码- select * from cdb_rsrc_plans rp
- where rp.plan like 'PLAN%';
- select * from cdb_rsrc_plan_directives rpd
- where rpd.plan='PLAN1';
-
- DECLARE
- spfileValue VARCHAR2(1000);
- execText VARCHAR2(1000);
- scopeValue VARCHAR2(30) := 'MEMORY';
- planName VARCHAR2(100) :='PLAN2';
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.create_plan( plan => 'PLAN2', comment => 'plan2',max_iops => NULL,max_mbps => NULL
- ,cpu_mth => 'RATIO' );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN2',
- group_or_subplan => 'GROUP1',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN2',
- group_or_subplan => 'GROUP2',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN2',
- group_or_subplan => 'OTHER_GROUPS',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN2',
- group_or_subplan => 'SYS_GROUP',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 60, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' 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 => 'PLAN2' , sid => 'cdb2' );
- END;
- 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_mgmt_p2 => 80
- );
- 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 => 'PLAN2',
- group_or_subplan => 'GROUP1',
- new_mgmt_p2 => 80
- );
- dbms_resource_manager.submit_pending_area();
- END;
复制代码
插件的emphasis和不是插件的ratio都不能有level2的分配:
subplan与group相当,只出现在cpu这个维度:
job class:
-
-
- BEGIN
- sys.dbms_scheduler.create_job_class(
- logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
- resource_consumer_group => 'GROUP2',
- comments => 'jobclass1',
- job_class_name => '"JOBCLASS1"');
- END;
复制代码- grant execute on jobclass1 to hr;
复制代码- BEGIN
- sys.dbms_scheduler.create_job(
- job_name => '"HR"."JOB6"',
- job_type => 'PLSQL_BLOCK',
- job_action => 'declare
- v1 number;
- begin
- select count(*) into v1 from t04209_uname a , t04209_uname;
- end;',
- start_date => systimestamp at time zone 'America/Los_Angeles',
- job_class => '"JOBCLASS1"',
- comments => 'job6',
- auto_drop => FALSE,
- enabled => TRUE);
- END;
复制代码- select * from cdb_scheduler_running_jobs;
复制代码
window:
-
-
- BEGIN
- DBMS_SCHEDULER.CREATE_WINDOW(
- window_name=>'"WINDOW1"',
- resource_plan=>'PLAN1',
- start_date=>to_timestamp_tz('2018-06-10 15:15:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
- duration=>numtodsinterval(1, 'minute'),
- repeat_interval=>'FREQ=MINUTELY;INTERVAL=3',
- end_date=>null,
- window_priority=>'HIGH',
- comments=>'');
- END;
复制代码- BEGIN
- DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
- group_name=>'"WINGROUP1"',
- window_list=>'"WINDOW1"');
- END;
复制代码
看一下CPU的控制 效果:
- select * from cdb_scheduler_running_jobs;
-
- select * from V$RSRC_CONSUMER_GROUP;
复制代码
- 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 => 8
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select * from cdb_rsrc_plan_directives rpd
- where rpd.plan='PLAN1';
- select s.terminal , s.status,s.resource_consumer_group
- from v_$session s
- where s.terminal in ('pts/2', 'pts/8');
-
复制代码
做以下实验时,请把timing打开
KILL SESSION:
- SQL> select count(*) from t04209_uname a , T_BANK_INVOICE b , T_BANK_INVOICE c , T_BANK_INVOICE d, T_BANK_INVOICE e ;
- select count(*) from t04209_uname a , T_BANK_INVOICE b , T_BANK_INVOICE c , T_BANK_INVOICE d, T_BANK_INVOICE e
- *
- ERROR at line 1:
- ORA-56736: elapsed time limit exceeded - session terminated
- Elapsed: 00:00:11.58
复制代码
CANCEL SQL:
- SQL> select count(*) from t04209_uname a , T_BANK_INVOICE b , T_BANK_INVOICE c , T_BANK_INVOICE d, T_BANK_INVOICE e ;
- select count(*) from t04209_uname a , T_BANK_INVOICE b , T_BANK_INVOICE c , T_BANK_INVOICE d, T_BANK_INVOICE e
- *
- ERROR at line 1:
- ORA-56735: elapsed time limit exceeded - call aborted
- Elapsed: 00:00:11.35
复制代码
SWITCH SESSION:
- 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 => 'GROUP2',
- new_switch_time => NULL,
- new_switch_estimate => case 'true' 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;
-
复制代码- SQL> select count(*) from t04209_uname a , T_BANK_INVOICE b , T_BANK_INVOICE c , T_BANK_INVOICE d, T_BANK_INVOICE e ;
- COUNT(*)
- ----------
- 1000000000
- Elapsed: 00:00:41.91
- SQL>
复制代码
- 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 => 10,
- new_max_idle_blocker_time => 3,
- new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.submit_pending_area();
- END;
复制代码
如何组间,无争用时,也设置上限:
- 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_max_utilization_limit => 20,
- 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 => 'GROUP2',
- 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_max_utilization_limit => 10,
- new_mgmt_p1 => 10, 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_max_utilization_limit => 60,
- new_mgmt_p1 => 60, 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_max_utilization_limit => 10,
- new_mgmt_p1 => 10, 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;
-
复制代码- select * from cdb_rsrc_group_mappings;
- select * from cdb_rsrc_mapping_priority;
- begin
- dbms_resource_manager.clear_pending_area;
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.set_consumer_group_mapping(
- attribute => dbms_resource_manager.client_machine,
- value => 'station90.example.com',consumer_group => 'GROUP2');
- dbms_resource_manager.submit_pending_area;
- end;
-
- select s.resource_consumer_group
- from v_$session s
- where s.terminal='pts/2';
-
-
- 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;
复制代码 在12c数据库上,RATIO,只是省了pending area:
- begin
- dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
- consumer_group1 => 'group1',
- group1_cpu => 50,
- consumer_group2 => 'group2',
- group2_cpu => 30,
- consumer_group3 => 'sys_group',
- group3_cpu => 20);
- end;
复制代码 在11g数据库上,EMPHASIS,而且还是3级:
|
|