Bo's Oracle Station

查看: 2463|回复: 0

课程第58/59次(2018-06-10星期日上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-6-10 09:44:22 | 显示全部楼层 |阅读模式
还没有操作之前,观察到的图形界面:

jobs页面:
filewatcher1.png

schedule的页面:
filewatcher2.png

首先改掉这个schedule(普通的基于时间的):

  1. BEGIN
  2. sys.dbms_scheduler.set_attribute( name => '"SYS"."FILE_WATCHER_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=2');
  3. END;
复制代码
接下来,在做实验的过程中始终看者以下这一页:
filewatcher3.png


  1. [root@station90 ~]# cd /lib64/
  2. [root@station90 lib64]# ls -l libpam
  3. libpamc.so.0           libpam_misc.so.0       libpam.so.0            
  4. libpamc.so.0.82.1      libpam_misc.so.0.82.0  libpam.so.0.82.2      
  5. [root@station90 lib64]# ls -l libpam.*
  6. lrwxrwxrwx. 1 root root    16  5月 13 06:24 libpam.so.0 -> libpam.so.0.82.2
  7. -rwxr-xr-x. 1 root root 55280  7月 10 2017 libpam.so.0.82.2
  8. [root@station90 lib64]# pwd
  9. /lib64
  10. [root@station90 lib64]# ln -s    libpam.so.0.82.2    libpam.so
  11. [root@station90 lib64]# ls -l libpam.*
  12. lrwxrwxrwx  1 root root    16  6月 10 09:50 libpam.so -> libpam.so.0.82.2
  13. lrwxrwxrwx. 1 root root    16  5月 13 06:24 libpam.so.0 -> libpam.so.0.82.2
  14. -rwxr-xr-x. 1 root root 55280  7月 10 2017 libpam.so.0.82.2
复制代码
  1. select  * from cdb_scheduler_credentials;

  2. select * from cdb_scheduler_file_watchers;

  3. begin
  4.    dbms_scheduler.create_file_watcher(
  5.        file_watcher_name => 'filewatcher1',
  6.        directory_path => '/home/oracle/myfile',
  7.        file_name => 'botang*.txt',
  8.        credential_name => 'credential1');
  9. end;

  10. select * from cdb_scheduler_file_watchers;
复制代码

filewatcher4.png


  1. grant execute on filewatcher1 to hr;
  2. grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;

  3. select  * from cdb_tab_privs tp where tp.grantee='HR';

  4. select  * from cdb_sys_privs sp where sp.grantee='HR';

  5. create table hr.tfilewatcher1 ( a  varchar2(200)  ) ;  

  6. create or replace procedure hr.procfilewatcher ( p_1   SYS.SCHEDULER_FILEWATCHER_RESULT)
  7. is
  8. begin
  9.   insert into hr.tfilewatcher1  values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
  10.                                                       ||p_1.directory_path||'        '
  11.                                                       ||p_1.actual_file_name||'       '
  12.                                                       ||p_1.file_size  ) ;
  13.   commit;
  14. end;


  15. begin
  16.     dbms_scheduler.create_program(
  17.      program_name => 'HR.program10',
  18.      program_type => 'STORED_PROCEDURE',
  19.      program_action => 'HR.procfilewatcher',
  20.      number_of_arguments => 1,
  21.      enabled => false);
  22. end;

  23. begin
  24.    dbms_scheduler.define_metadata_argument(
  25.    program_name => 'HR.program10',
  26.    metadata_attribute => 'event_message',argument_position => 1 ) ;
  27. end;

  28. begin
  29.   dbms_scheduler.enable('HR.program10');
  30. end;

  31. BEGIN
  32.   DBMS_SCHEDULER.CREATE_JOB(
  33.    jOB_NAME=> 'hr.job10',
  34.    PROGRAM_NAME=> 'hr.program10',
  35.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
  36.    QUEUE_SPEC=> 'filewatcher1',
  37.    AUTO_DROP=> FALSE,
  38.    ENABLED=> true);
  39. END;




  40. begin
  41.    dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
  42. end;



  43. select  * from  hr.tfilewatcher1;
复制代码

12c上创建的PLAN1:
  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.create_plan( plan => 'PLAN1', comment => 'plan1',max_iops => NULL,max_mbps => NULL );
  10. dbms_resource_manager.create_plan_directive(
  11.     plan => 'PLAN1',
  12.     group_or_subplan => 'GROUP1',
  13.     comment => '',
  14.     switch_elapsed_time => NULL,
  15.      max_utilization_limit => '',
  16.     mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  17.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  18.     parallel_degree_limit_p1 => NULL ,
  19.     parallel_target_percentage => NULL ,
  20.     parallel_queue_timeout => NULL ,
  21.     parallel_stmt_critical => 'false' ,
  22.     switch_io_logical => NULL ,
  23.     switch_io_reqs => NULL ,
  24.    switch_io_megabytes => NULL ,
  25.     active_sess_pool_p1 => NULL,
  26.     queueing_p1 => NULL,
  27.     switch_group => '',
  28.     switch_time => NULL,
  29.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  30.     undo_pool => NULL ,
  31.     max_idle_time => NULL,
  32.     max_idle_blocker_time => NULL,
  33.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  34. );
  35. dbms_resource_manager.create_plan_directive(
  36.     plan => 'PLAN1',
  37.     group_or_subplan => 'GROUP2',
  38.     comment => '',
  39.     switch_elapsed_time => NULL,
  40.      max_utilization_limit => '',
  41.     mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  42.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  43.     parallel_degree_limit_p1 => NULL ,
  44.     parallel_target_percentage => NULL ,
  45.     parallel_queue_timeout => NULL ,
  46.     parallel_stmt_critical => 'false' ,
  47.     switch_io_logical => NULL ,
  48.     switch_io_reqs => NULL ,
  49.    switch_io_megabytes => NULL ,
  50.     active_sess_pool_p1 => NULL,
  51.     queueing_p1 => NULL,
  52.     switch_group => '',
  53.     switch_time => NULL,
  54.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  55.     undo_pool => NULL ,
  56.     max_idle_time => NULL,
  57.     max_idle_blocker_time => NULL,
  58.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  59. );
  60. dbms_resource_manager.create_plan_directive(
  61.     plan => 'PLAN1',
  62.     group_or_subplan => 'OTHER_GROUPS',
  63.     comment => '',
  64.     switch_elapsed_time => NULL,
  65.      max_utilization_limit => '',
  66.     mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  67.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  68.     parallel_degree_limit_p1 => NULL ,
  69.     parallel_target_percentage => NULL ,
  70.     parallel_queue_timeout => NULL ,
  71.     parallel_stmt_critical => 'false' ,
  72.     switch_io_logical => NULL ,
  73.     switch_io_reqs => NULL ,
  74.    switch_io_megabytes => NULL ,
  75.     active_sess_pool_p1 => NULL,
  76.     queueing_p1 => NULL,
  77.     switch_group => '',
  78.     switch_time => NULL,
  79.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  80.     undo_pool => NULL ,
  81.     max_idle_time => NULL,
  82.     max_idle_blocker_time => NULL,
  83.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  84. );
  85. dbms_resource_manager.create_plan_directive(
  86.     plan => 'PLAN1',
  87.     group_or_subplan => 'SYS_GROUP',
  88.     comment => '',
  89.     switch_elapsed_time => NULL,
  90.      max_utilization_limit => '',
  91.     mgmt_p1 => 60, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  92.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  93.     parallel_degree_limit_p1 => NULL ,
  94.     parallel_target_percentage => NULL ,
  95.     parallel_queue_timeout => NULL ,
  96.     parallel_stmt_critical => 'false' ,
  97.     switch_io_logical => NULL ,
  98.     switch_io_reqs => NULL ,
  99.    switch_io_megabytes => NULL ,
  100.     active_sess_pool_p1 => NULL,
  101.     queueing_p1 => NULL,
  102.     switch_group => '',
  103.     switch_time => NULL,
  104.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  105.     undo_pool => NULL ,
  106.     max_idle_time => NULL,
  107.     max_idle_blocker_time => NULL,
  108.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  109. );
  110. dbms_resource_manager.submit_pending_area();
  111. select value into spfileValue from v$parameter where name = 'spfile';
  112. IF spfileValue IS NOT NULL then
  113. EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
  114. END IF;
  115. dbms_resource_manager.switch_plan( plan_name => 'PLAN1' , sid => 'cdb2' );
  116. END;
  117.                         
复制代码
  1. select  * from cdb_rsrc_plans  rp
  2. where rp.plan like 'PLAN%';

  3. select  *  from cdb_rsrc_plan_directives  rpd
  4. where rpd.plan='PLAN1';

  5. DECLARE
  6. spfileValue VARCHAR2(1000);
  7. execText VARCHAR2(1000);
  8. scopeValue VARCHAR2(30) := 'MEMORY';
  9. planName VARCHAR2(100) :='PLAN2';
  10. BEGIN
  11. dbms_resource_manager.clear_pending_area();
  12. dbms_resource_manager.create_pending_area();
  13. dbms_resource_manager.create_plan( plan => 'PLAN2', comment => 'plan2',max_iops => NULL,max_mbps => NULL
  14. ,cpu_mth => 'RATIO' );
  15. dbms_resource_manager.create_plan_directive(
  16.     plan => 'PLAN2',
  17.     group_or_subplan => 'GROUP1',
  18.     comment => '',
  19.     switch_elapsed_time => NULL,
  20.      max_utilization_limit => '',
  21.     mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  22.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  23.     parallel_degree_limit_p1 => NULL ,
  24.     parallel_target_percentage => NULL ,
  25.     parallel_queue_timeout => NULL ,
  26.     parallel_stmt_critical => 'false' ,
  27.     switch_io_logical => NULL ,
  28.     switch_io_reqs => NULL ,
  29.    switch_io_megabytes => NULL ,
  30.     active_sess_pool_p1 => NULL,
  31.     queueing_p1 => NULL,
  32.     switch_group => '',
  33.     switch_time => NULL,
  34.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  35.     undo_pool => NULL ,
  36.     max_idle_time => NULL,
  37.     max_idle_blocker_time => NULL,
  38.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  39. );
  40. dbms_resource_manager.create_plan_directive(
  41.     plan => 'PLAN2',
  42.     group_or_subplan => 'GROUP2',
  43.     comment => '',
  44.     switch_elapsed_time => NULL,
  45.      max_utilization_limit => '',
  46.     mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  47.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  48.     parallel_degree_limit_p1 => NULL ,
  49.     parallel_target_percentage => NULL ,
  50.     parallel_queue_timeout => NULL ,
  51.     parallel_stmt_critical => 'false' ,
  52.     switch_io_logical => NULL ,
  53.     switch_io_reqs => NULL ,
  54.    switch_io_megabytes => NULL ,
  55.     active_sess_pool_p1 => NULL,
  56.     queueing_p1 => NULL,
  57.     switch_group => '',
  58.     switch_time => NULL,
  59.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  60.     undo_pool => NULL ,
  61.     max_idle_time => NULL,
  62.     max_idle_blocker_time => NULL,
  63.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  64. );
  65. dbms_resource_manager.create_plan_directive(
  66.     plan => 'PLAN2',
  67.     group_or_subplan => 'OTHER_GROUPS',
  68.     comment => '',
  69.     switch_elapsed_time => NULL,
  70.      max_utilization_limit => '',
  71.     mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  72.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  73.     parallel_degree_limit_p1 => NULL ,
  74.     parallel_target_percentage => NULL ,
  75.     parallel_queue_timeout => NULL ,
  76.     parallel_stmt_critical => 'false' ,
  77.     switch_io_logical => NULL ,
  78.     switch_io_reqs => NULL ,
  79.    switch_io_megabytes => NULL ,
  80.     active_sess_pool_p1 => NULL,
  81.     queueing_p1 => NULL,
  82.     switch_group => '',
  83.     switch_time => NULL,
  84.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  85.     undo_pool => NULL ,
  86.     max_idle_time => NULL,
  87.     max_idle_blocker_time => NULL,
  88.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  89. );
  90. dbms_resource_manager.create_plan_directive(
  91.     plan => 'PLAN2',
  92.     group_or_subplan => 'SYS_GROUP',
  93.     comment => '',
  94.     switch_elapsed_time => NULL,
  95.      max_utilization_limit => '',
  96.     mgmt_p1 => 60, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
  97.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
  98.     parallel_degree_limit_p1 => NULL ,
  99.     parallel_target_percentage => NULL ,
  100.     parallel_queue_timeout => NULL ,
  101.     parallel_stmt_critical => 'false' ,
  102.     switch_io_logical => NULL ,
  103.     switch_io_reqs => NULL ,
  104.    switch_io_megabytes => NULL ,
  105.     active_sess_pool_p1 => NULL,
  106.     queueing_p1 => NULL,
  107.     switch_group => '',
  108.     switch_time => NULL,
  109.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
  110.     undo_pool => NULL ,
  111.     max_idle_time => NULL,
  112.     max_idle_blocker_time => NULL,
  113.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  114. );
  115. dbms_resource_manager.submit_pending_area();
  116. select value into spfileValue from v$parameter where name = 'spfile';
  117. IF spfileValue IS NOT NULL then
  118. EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
  119. END IF;
  120. dbms_resource_manager.switch_plan( plan_name => 'PLAN2' , sid => 'cdb2' );
  121. END;


  122. DECLARE
  123. spfileValue VARCHAR2(1000);
  124. execText VARCHAR2(1000);
  125. scopeValue VARCHAR2(30) := 'MEMORY';
  126. planName VARCHAR2(100) :='PLAN1';

  127. BEGIN
  128. dbms_resource_manager.clear_pending_area();
  129. dbms_resource_manager.create_pending_area();
  130. dbms_resource_manager.update_plan_directive(
  131.     plan => 'PLAN1',
  132.     group_or_subplan => 'GROUP1',
  133.     new_mgmt_p2 => 80   
  134. );
  135. dbms_resource_manager.submit_pending_area();
  136. END;

  137. BEGIN
  138. dbms_resource_manager.clear_pending_area();
  139. dbms_resource_manager.create_pending_area();
  140. dbms_resource_manager.update_plan_directive(
  141.     plan => 'PLAN2',
  142.     group_or_subplan => 'GROUP1',
  143.     new_mgmt_p2 => 80   
  144. );
  145. dbms_resource_manager.submit_pending_area();
  146. END;

复制代码


插件的emphasis和不是插件的ratio都不能有level2的分配:

Screenshot-Error.png

subplan与group相当,只出现在cpu这个维度:

a.png


job class:
  1.         
  2.                         
  3. BEGIN
  4. sys.dbms_scheduler.create_job_class(
  5. logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
  6. resource_consumer_group => 'GROUP2',
  7. comments => 'jobclass1',
  8. job_class_name => '"JOBCLASS1"');
  9. END;
复制代码
  1.   grant execute on jobclass1 to hr;
复制代码
  1. BEGIN
  2. sys.dbms_scheduler.create_job(
  3. job_name => '"HR"."JOB6"',
  4. job_type => 'PLSQL_BLOCK',
  5. job_action => 'declare
  6. v1 number;
  7. begin
  8.    select  count(*)  into v1 from  t04209_uname a , t04209_uname;
  9. end;',
  10. start_date => systimestamp at time zone 'America/Los_Angeles',
  11. job_class => '"JOBCLASS1"',
  12. comments => 'job6',
  13. auto_drop => FALSE,
  14. enabled => TRUE);
  15. END;
复制代码
  1. select  * from cdb_scheduler_running_jobs;
复制代码

window:

  1.         
  2.                         
  3. BEGIN
  4. DBMS_SCHEDULER.CREATE_WINDOW(
  5. window_name=>'"WINDOW1"',
  6. resource_plan=>'PLAN1',
  7. start_date=>to_timestamp_tz('2018-06-10 15:15:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  8. duration=>numtodsinterval(1, 'minute'),
  9. repeat_interval=>'FREQ=MINUTELY;INTERVAL=3',
  10. end_date=>null,
  11. window_priority=>'HIGH',
  12. comments=>'');
  13. END;
复制代码
  1. BEGIN
  2. DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
  3. group_name=>'"WINGROUP1"',
  4. window_list=>'"WINDOW1"');
  5. END;
复制代码

看一下CPU的控制 效果:
  1. select  * from cdb_scheduler_running_jobs;
  2.   
  3. select  * from V$RSRC_CONSUMER_GROUP;
复制代码

  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 => 8
  8. );
  9. dbms_resource_manager.submit_pending_area();
  10. END;

  11. select * from cdb_rsrc_plan_directives rpd
  12. where rpd.plan='PLAN1';

  13. select s.terminal , s.status,s.resource_consumer_group
  14. from v_$session s
  15. where s.terminal in ('pts/2', 'pts/8');

  16.    
复制代码

Screenshot.png

做以下实验时,请把timing打开

KILL SESSION:
  1. SQL> select  count(*) from t04209_uname a , T_BANK_INVOICE  b , T_BANK_INVOICE c ,  T_BANK_INVOICE d, T_BANK_INVOICE e ;
  2. select        count(*) from t04209_uname a , T_BANK_INVOICE  b , T_BANK_INVOICE c ,  T_BANK_INVOICE d, T_BANK_INVOICE e
  3. *
  4. ERROR at line 1:
  5. ORA-56736: elapsed time limit exceeded - session terminated


  6. Elapsed: 00:00:11.58
复制代码

CANCEL SQL:

  1. SQL> select  count(*) from t04209_uname a , T_BANK_INVOICE  b , T_BANK_INVOICE c ,  T_BANK_INVOICE d, T_BANK_INVOICE e ;
  2. select        count(*) from t04209_uname a , T_BANK_INVOICE  b , T_BANK_INVOICE c ,  T_BANK_INVOICE d, T_BANK_INVOICE e
  3.                       *
  4. ERROR at line 1:
  5. ORA-56735: elapsed time limit exceeded - call aborted


  6. Elapsed: 00:00:11.35
复制代码

SWITCH SESSION:
switch.png
  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 => 'GROUP2',
  26.     new_switch_time => NULL,
  27.     new_switch_estimate => case 'true' when 'false' then false when 'true' then true else false end ,
  28.     new_undo_pool => NULL ,
  29.     new_max_idle_time => NULL,
  30.     new_max_idle_blocker_time => NULL,
  31.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  32. );
  33. dbms_resource_manager.submit_pending_area();
  34. END;
  35.                         
复制代码
  1. SQL> select  count(*) from t04209_uname a , T_BANK_INVOICE  b , T_BANK_INVOICE c ,  T_BANK_INVOICE d, T_BANK_INVOICE e ;

  2.   COUNT(*)
  3. ----------
  4. 1000000000

  5. Elapsed: 00:00:41.91
  6. SQL>

复制代码

Screenshot-1.png


  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 => 10,
  30.     new_max_idle_blocker_time => 3,
  31.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

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


如何组间,无争用时,也设置上限:

  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_max_utilization_limit => 20,
  15.     new_mgmt_p1 => 20, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  16.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  17.     new_parallel_degree_limit_p1 => NULL ,
  18.     new_parallel_target_percentage => NULL ,
  19.     new_parallel_queue_timeout => NULL ,
  20.     new_parallel_stmt_critical => 'false' ,
  21.     new_switch_io_logical => NULL ,
  22.     new_switch_io_reqs => NULL,
  23.     new_switch_io_megabytes => NULL ,
  24.     new_active_sess_pool_p1 => NULL,
  25.     new_queueing_p1 => NULL,
  26.     new_switch_group => 'GROUP2',
  27.     new_switch_time => NULL,
  28.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  29.     new_undo_pool => NULL ,
  30.     new_max_idle_time => NULL,
  31.     new_max_idle_blocker_time => NULL,
  32.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

  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_max_utilization_limit => 10,
  40.     new_mgmt_p1 => 10, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
  41.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
  42.     new_parallel_degree_limit_p1 => NULL ,
  43.     new_parallel_target_percentage => NULL ,
  44.     new_parallel_queue_timeout => NULL ,
  45.     new_parallel_stmt_critical => 'false' ,
  46.     new_switch_io_logical => NULL ,
  47.     new_switch_io_reqs => NULL,
  48.     new_switch_io_megabytes => NULL ,
  49.     new_active_sess_pool_p1 => NULL,
  50.     new_queueing_p1 => NULL,
  51.     new_switch_group => NULL,
  52.     new_switch_time => NULL,
  53.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
  54.     new_undo_pool => NULL ,
  55.     new_max_idle_time => NULL,
  56.     new_max_idle_blocker_time => NULL,
  57.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

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

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

  108. );
  109. dbms_resource_manager.submit_pending_area();
  110. END;
  111.                         
复制代码
  1. select * from cdb_rsrc_group_mappings;

  2. select  * from cdb_rsrc_mapping_priority;

  3. begin
  4.   dbms_resource_manager.clear_pending_area;
  5.   dbms_resource_manager.create_pending_area;
  6.   dbms_resource_manager.set_consumer_group_mapping(
  7.   attribute => dbms_resource_manager.client_machine,
  8.   value => 'station90.example.com',consumer_group => 'GROUP2');
  9.   dbms_resource_manager.submit_pending_area;
  10. end;
  11.   
  12.    select  s.resource_consumer_group
  13.     from v_$session s
  14.      where s.terminal='pts/2';
  15.      
  16.      
  17.      BEGIN
  18. dbms_resource_manager.clear_pending_area();
  19. dbms_resource_manager.create_pending_area();
  20. dbms_resource_manager.set_consumer_group_mapping_pri(
  21.     EXPLICIT => 1,  CLIENT_MACHINE => 2,
  22.     SERVICE_MODULE_ACTION => 3,
  23.     SERVICE_MODULE => 4,
  24.     MODULE_NAME_ACTION => 5,
  25.     MODULE_NAME => 6,
  26.     SERVICE_NAME => 7,
  27.     ORACLE_USER => 8,
  28.     CLIENT_PROGRAM => 9,
  29.     CLIENT_OS_USER => 10,
  30.     CLIENT_ID => 11
  31. );
  32. dbms_resource_manager.submit_pending_area();
  33. END;
复制代码
在12c数据库上,RATIO,只是省了pending area:
  1. begin
  2.   dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
  3.   consumer_group1 => 'group1',
  4.   group1_cpu => 50,
  5.   consumer_group2 => 'group2',
  6.   group2_cpu => 30,
  7.   consumer_group3 => 'sys_group',
  8.   group3_cpu => 20);
  9.   end;
复制代码
在11g数据库上,EMPHASIS,而且还是3级:
11.png



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 17:35 , Processed in 0.058041 second(s), 27 queries .

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